Home / Questions / 47566
Accepted
Alejandro Ricotti
Asked: 2020-02-04 05:33:26 +0800 CST 2020-02-04 05:33:26 +0800 CST

Methods to call Store Procedure from C#

  • 772

I have this method to execute stored procedures

    public void EjecutarSP(string sp)
    {
        try
        {
            SqlCommand cmd = new SqlCommand(sp, ConexionBD.con);
            cmd.CommandType = CommandType.StoredProcedure;
            ConexionBD.Conectar();
            cmd.ExecuteNonQuery();
            ConexionBD.Desconectar();
        }
        catch (Exception ex)
        {
            throw new Exception(" Error al ejecutar procedimiento almacenado ", ex);
        }
    }

This method serves me generically when the stored procedures do not receive or return any value, I wanted to know if there is any way to make them generic for when the stored procedure receives and returns parameters, I think that for the return part I can use ExecuteReader that returns a table and then work on it in the application, but for the part of sending parameters I don't know if something generic can be done since not all stored procedures receive the same number of parameters

c#

2 Answers

  1. Best Answer

    This method will help you execute Stored Procedures using generic data types:

    public IEnumerable<T> EjecutarStoredProcedure<T>(string storedProcedure, string connectionString, SqlParameter[] parameters, Func<SqlDataReader, T> body)
{
    List<T> results = new List<T>();

    using (Connection connection = new Connection())
    {
        SqlCommand command = connection.CreateSPConnection(storedProcedure, connectionString);
        command.Parameters.AddRange(parameters);
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            results.Add(body(reader));
        }
        reader.Close();
    }
    return results;
}

    Now, to be able to make a call to the method, it would be as follows:

    public IEnumerable<UserEntity> GetUser()
{
    try
    {
        return ProceduresExecution.Instance.EjecutarStoredProcedure<UserEntity>("NombreStoredProcedure",
            GlobalData.ConnectionStringDefault,
            new SqlParameter[]{
                new SqlParameter("@inUserId", 123),
                new SqlParameter("@OtroParametro", "OtroParametro")
            },
            reader =>
            {
                return new UserEntity
                {   
                    //En esta sección van todas las propiedades de la Entidad UserEntity y ["Aqui"] van mapeados a los nombres de las columnas que recuperaste en la consulta de base de datos
                    UserId = (int)reader["UserId"],
                    Nombre = reader["Nombre"].ToString()
                    UserTypeId = (int)reader["UserTypeId"]              };
            });
    }
    catch (Exception e)
    {
        throw new Exception("Error al obtener el usuario" + ex.message);
    }
}

    Sending to method

    new SqlParameter[] {
    new SqlParameter("@inUserId", 123),
    new SqlParameter("@OtroParametro", "OtroParametro")
},

    You are sending all the desired parameters so that you can finally receive them automatically in the type of the class that you want in a generic way.

    • 1

  2. If you want you could use the Sql Helper class that already has the overloaded methods you want, or another is to adapt it to the needs you have.

    Check out this tutorial , which uses such a class.

    • 0