C# 调用 MySQL 存储过程、函数的最佳方式

C# best way to call MySQL Stored Procedures, Functions

提问by GlacialVoid

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.

您好，我编写了调用存储过程的 DAL，但我仍然不知道对于某些过程是否应该使用 ExecuteScalar、NonQuery 或 Reader。

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

现在我用 C# 写了这个来从 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?

    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;
            }
        }
    }

如果你看到我在这里做了一个 ExecuteNonQuery()，但我应该使用 Execute Scalar 吗？还是只使用 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?

同样，如果我正在执行更新或插入操作，但我使用输出参数来检查它是否已完成，您可以检查插入存储过程，我应该使用 ExecuteNonQuery 还是 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.

对于返回数据集的过程，与我上面写的方法相同，但没有输出参数，我使用的是 executeReader，因为我只执行选择而不使用输出参数。

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

我只想知道我是否为这些过程使用了正确的执行命令。

采纳答案by Darren

• 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 SqlDataReaderto be created so you can iterate over the result then use ExecuteReader.

• 如果您希望返回受查询影响的行数，请使用ExecuteNonReader().

• 如果您希望返回第一行的第一列，请使用 ExecuteScalar.

• 如果您需要SqlDataReader创建 a 以便您可以迭代结果，则使用ExecuteReader.

Therefore you could use ExecuteScalarfor your function retornarIdAreasince you are only ever returning one column and one row (the id) so this function fits the context.

因此你可以使用ExecuteScalar你的函数，retornarIdArea因为你只返回一列和一行（id），所以这个函数适合上下文。

For your procedure consultar_n_asuntoyou could use ExecuteReaderas you are selecting multiple rows and expect the result set to be returned.

对于您的过程，consultar_n_asunto您可以ExecuteReader在选择多行并期望返回结果集时使用。