Home / Questions / 377273
Accepted
Franco Torres
Asked: 2020-07-30 11:51:51 +0800 CST 2020-07-30 11:51:51 +0800 CST

How to create a generic method that allows me to execute any Stored Procedure with parameters in C#?

  • 772

I am creating a project in C# with sql server 2017 and I have the DAO_Socio class, which is responsible for assembling the objects that communicate with the Database:

public Inscripcion getSociosPorNombre(string nombreSocio)
            {   
                string sp, columna, parametro;
                sp = "sp_listar_socios_por_nombre";
                columna = "@nombre";
                parametro = nombreSocio;
                DataTable tabla = getSociosPorNombre(sp,columna,parametro);
                Inscripcion inscripcion = new Inscripcion();
                foreach (DataRow fila in tabla.Rows)
                {
                    inscripcion = mapper(fila);              
                }
                return inscripcion;
            }

In turn, the DBHelper class is responsible for communicating with the database.

The following method allows me to bring a table from the database, through a stored procedure:

public DataTable getSociosPorNombre(string storedProcedure, string nombreColumna, string parametro)
        {
            SqlConnection cnn = new SqlConnection();
            SqlCommand cmd = new SqlCommand();
            DataTable tabla = new DataTable();
            
            try
            {
                cnn.ConnectionString = string_conexion;
                cnn.Open();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = storedProcedure;
                cmd.Parameters.AddWithValue(nombreColumna,parametro);
                tabla.Load(cmd.ExecuteReader());
                return tabla;
            }
            catch (SqlException ex)
            {
                MessageBox.Show("EXPLOTO EL HELPER", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                throw (ex);
            }
            finally
            {
                this.CloseConnection(cnn);
            }
        }

The problem with this method is that I can only pass a single parameter to it. How do I make the method execute any type of SP, with any number of parameters without having to specify the name of the column and the value that I pass to it.

c#

1 Answers

  1. Best Answer

    You can use the AddRange function to add a list of SqlParameter like so:

     public DataTable getSociosPorNombre(string storedProcedure,SqlParameter[] sqlParameters)
    {
        SqlConnection cnn = new SqlConnection();
        SqlCommand cmd = new SqlCommand();
        DataTable tabla = new DataTable();

        try
        {
            cnn.ConnectionString = string_conexion;
            cnn.Open();
            cmd.Connection = cnn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            cmd.Parameters.AddRange(sqlParameters);
            tabla.Load(cmd.ExecuteReader());
            return tabla;
        }
        catch (SqlException ex)
        {
            MessageBox.Show("EXPLOTO EL HELPER", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
            throw (ex);
        }
        finally
        {
            this.CloseConnection(cnn);
        }
    }

    and you could consume it in multiple ways, here is one way:

    List<SqlParameter> listaParametros = new List<SqlParameter>();
        
        var param1 = new SqlParameter("parametro1", "valor1");
        param1.SqlDbType = SqlDbType.VarChar;
        listaParametros.Add(param1);

        var param2 = new SqlParameter("parametro2", 255);
        param2.SqlDbType = SqlDbType.Int;
        listaParametros.Add(param2);

        var param3 = new SqlParameter("parametro3", DateTime.Now);
        param3.SqlDbType = SqlDbType.DateTime;
        listaParametros.Add(param3);


        var resultado =  getSociosPorNombre("miStoreProcedure", listaParametros.ToArray());
    • 1