Methods to call Store Procedure from C #

Question

Methods to call Store Procedure from C #

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

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 believe that for the return part I can use ExecuteReader that it returns a table and then work it in the application, but for the parameter sending part, I do not know if something generic can be done since not all the stored procedures receive the same amount of parameters.

sql c# sql-server

asked by Alejandro Ricotti 03.02.2017 в 14:33

source

2 answers

0

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

See this tutorial , which uses that class.

answered by 03.02.2017 в 14:45

Can you save several modal windows in a separate file? Is there any way to create autoscroll to a listview ?, in Android Studio

score 1 · Accepted 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, in order 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);
    }
}

When submitting to the 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 class you want in a generic way.