C# best way to call MySQL Stored Procedures, Functions

Question

Hello I wrote my DAL calling Stored Procedures, but I still don't know if I should use ExecuteScalar, NonQuery or Reader for some procedures.

For example I wrote this function that I want to call

CREATE FUNCTION `retornarIdArea`(cod longtext) RETURNS int(11)
BEGIN
DECLARE id int;
    select AreaId into id FROM area where Codigo = cod;
    return id;
END

this procedure that should return a dataset

CREATE PROCEDURE `consultar_n_asunto`(in asun longtext, in est int)
BEGIN
    select * from notas where Asunto LIKE CONCAT('%',CONCAT(asun,'%')) AND Estado = est;
END$$

And last a procedure that inserts some data, and I validate that with a select using output Parameter.

CREATE PROCEDURE `registrar_dest`(in nomb longtext,
in dir longtext, in inst int, in mail longtext, in tel longtext,
in act int, out res tinyint(1))
BEGIN
    -- verificar que no exista el destinatario
    select count(*) into res from destinatario WHERE Nombre = nomb AND 
    Direccion = dir AND Email = mail AND Telefono = tel AND Activo = act;

    IF res = 0 THEN
        INSERT INTO destinatario (Nombre, Direccion, InstitucionId, Email, Telefono, Activo)
        VALUES (nomb, dir, inst, mail, tel, act);
        select count(*) into res from destinatario WHERE Nombre = nomb AND 
        Direccion = dir AND Email = mail AND Telefono = tel AND Activo = act;
    ELSE 
        set res = -1;
    END IF;
END$$

Now I wrote in C# this to return values from FUNCTIONS

    public object ejecutarFuncion()
    {
        using (MySqlConnection conn = new MySqlConnection(stringDeConexion))
        {
            using (MySqlCommand cmd = new MySqlCommand(procedimiento, conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                //Se abre la conexión
                conn.Open();
                //existen parámetros los recorremos y agregamos.
                foreach (KeyValuePair<string, object> pars in parametros)
                {
                    cmd.Parameters.Add(new MySqlParameter(pars.Key, pars.Value));
                }
                //Se crea la variable de retorno
                cmd.Parameters.Add(new MySqlParameter(nombreOut, tipoParOut));
                cmd.Parameters[nombreOut].Direction = System.Data.ParameterDirection.ReturnValue;
                cmd.ExecuteNonQuery();
                // Cerramos conexión
                conn.Close();
                return cmd.Parameters[nombreOut].Value;
            }
        }
    }

If you see I do a ExecuteNonQuery() here, but should I used Execute Scalar? or just use the return value from parms?

Then I wrote this method to execute Procedures with output values (some of them do update, insert) operations, I use the output value to check if operation was done correctly.

public object ejecutarProcedimientoConOutput()
{
    using (MySqlConnection conn = new MySqlConnection(stringDeConexion))
    {
        using (MySqlCommand cmd = new MySqlCommand(procedimiento, conn))
        {
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            //Se abre la conexión
            conn.Open();
            //verificamos si se mando la lista de parámetros
            if (parametros.Count > 0)
            {
                //existen parámetros los recorremos y agregamos.
                foreach (KeyValuePair<string, object> pars in parametros)
                {
                    cmd.Parameters.Add(new MySqlParameter(pars.Key, pars.Value));
                    cmd.Parameters[pars.Key].Direction = System.Data.ParameterDirection.Input;
                }
            }
            cmd.Parameters.Add(new MySqlParameter(nombreOut, tipoParOut));
            cmd.Parameters[nombreOut].Direction = System.Data.ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            conn.Close();
            return cmd.Parameters[nombreOut].Value;
        }
    }
}

Again if I'm doing an update or insert operation, but I use output parameter to check if it was done, you can check the insert stored procedure should I use ExecuteNonQuery or ExecuteScalar?

And for procedures that returns datasets is the same as the method I wrote above, but without the output parameter, I'm using executeReader since I only do a select and not using output parameter.

I just want to know if I'm using the correct execute commands for those procedures.

Answer 1

• If you want the number of rows affected by your query to be returned then use ExecuteNonReader().

• If you want the first column of the first row to be returned then use ExecuteScalar.

• If you require a SqlDataReader to be created so you can iterate over the result then use ExecuteReader.

Therefore you could use ExecuteScalar for your function retornarIdArea since you are only ever returning one column and one row (the id) so this function fits the context.

For your procedure consultar_n_asunto you could use ExecuteReader as you are selecting multiple rows and expect the result set to be returned.