Stored procedure doesn't return an int value

Question

MySql Procedure Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_CreateCliente`(IN nome_cliente VARCHAR(45))
BEGIN
    Select 20;
    INSERT INTO clienti ( nome_cliente )
    VALUES ( nome_cliente );
    Select id_cliente from clienti;
END

C# code in the controller page:

ClienteInfo CI = new ClienteInfo();
DboUser objdbo = new DboUser();
int id_cliente = 0;

CI.nome_cliente = txtNomeCliente.Text;

id_cliente = objdbo.CreateClienteInfo(CI);

DboUser class:

public int CreateClienteInfo(ClienteInfo CI)
{
        int result;
        MySqlConnection conn = new MySqlConnection();
        DbConnection db = new DbConnection();
        conn = db.ConnessioneDb();

        MySqlCommand cmd = new MySqlCommand(Costanti.StoredProcedures.USP_CreateCliente, conn);
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@nome_cliente", CI.nome_cliente);

        result = cmd.ExecuteNonQuery();
        conn.Close();
        return result;
    }

I want my C# code to retrieve the id of my customer after inserting it into the database, so I can save it to the session and retrieve it again later in a page that will print a document with the customer's informations.

My id_cliente returns 0, do you notice any syntax error? Did I do something wrong?

I'm 90% sure it's a problem dealing with the stored precedure tough, cause the customer is inserted correctly

Why ExecuteNonQuery and not executescaler or executereader ? — Amit, Jan 14 '15 at 11:08
Why 90% sure? Why not 100%? LOL. Yes problem in SP and calling code also. See Steve's answer. — Ankush Madankar, Jan 14 '15 at 11:15
Also close connection in `finally` block since executing database operation might have exceptions. — Ankush Madankar, Jan 14 '15 at 11:17

score 3 · Accepted Answer · edited May 23 '17 at 10:33

Change this line

result = cmd.ExecuteNonQuery();

to

result = Convert.ToInt32(cmd.ExecuteScalar());

but you should also change your stored procedure because it doesn't return the last id generated for you by the AUTO_INCREMENT column id_cliente

CREATE DEFINER=`root`@`localhost` PROCEDURE `USP_CreateCliente`(IN nome_cliente VARCHAR(45))
BEGIN
    INSERT INTO clienti ( nome_cliente ) VALUES ( nome_cliente );
    Select LAST_INSERT_ID();
END

In MySql, to get the generated auto_increment value, you could use LAST_INSERT_ID(), next, your C# code don't need to use ExecuteNonQuery, which returns just the number of rows that you have changed, added or deleted, but you use ExecuteScalar which returns the first column of the first row of the last SELECT command executed by your stored procedure. (It is the SELECT LAST_INSERT_ID())

Also, to complete the answer, you don't really need a stored procedure for this kind of simple work. The advantages should be minimal while the problems related to a different piece of software to maintain are self evident.

Your C# code could be (removed the usage of your DbConnection class because it is not clear what it does)

public int CreateClienteInfo(ClienteInfo CI)
{

    int result;
    string cmdText = @"INSERT INTO clienti ( nome_cliente ) VALUES ( nome_cliente );
                       Select LAST_INSERT_ID();";

    using(MySqlConnection conn = new MySqlConnection(....connectionstring .....))
    using(MySqlCommand cmd = new MySqlCommand(cmdText, conn);
    {
        conn.Open()
        cmd.Parameters.AddWithValue("@nome_cliente", CI.nome_cliente);
        result = Convert.ToInt32(cmd.ExecuteScalar())
        return result;
    }
}

Here you use the possibility to pass batch commands to your MySql engine, meaning two commandtexts with the same MySqlCommand separating them with the semicolon

My DbConnection class just has the connection string inside it. In the DboUser class I don't only have this "Create Customer" method, I also have "Create User", "Change Password", "GetInformation" (for the user panel control), and so on. If I change the password or the database name in MySql I'll have to change 30 connection strings, with my DbConnection class I do it only once. It's not a huge project and I isolated some code to show you my problem but I was told to use stored procedures cause my application is getting bigger and I'll have maintenance problems someday — Gino Perla, Jan 14 '15 at 11:27
No problem, it is just an advice from an old programmer. Stored Procedure are not always the best answer for maintenability scenarios. For the class DbConnection I don't really know what to say. Usually these utility class creates more problems. An ORM should be a better approach (search for Dapper. It is behind this site and it is free). Ciao — Steve, Jan 14 '15 at 11:32

Stored procedure doesn't return an int value

1 Answers1