Иво Недев Иво Недев - 4 months ago 14
ASP.NET (C#) Question

Minimize code repeatednesses when calling Stored Procedures

I'm using a certain method body to call stored procedures, with the following sample code:

public void StoredProcedureThatIsBeingcalled(int variable_1, int variable_2, out DataSet ds)
{
using (SqlConnection con = new SqlConnection(DatabaseConnectionString))
{
ds = new DataSet("DsToGoOut");
using (SqlCommand cmd = new SqlCommand("StoredProcedureThatIsBeingcalled", DbConn.objConn))
{
cmd.CommandType = CommandType.StoredProcedure;


cmd.Parameters.Add(new SqlParameter("@variable_1", variable_1));
cmd.Parameters.Add(new SqlParameter("@variable_2", variable_2));
try
{
con.Open();
SqlDataAdapter objDataAdapter = new SqlDataAdapter();
objDataAdapter.SelectCommand = cmd;

objDataAdapter.Fill(ds);

con.Close();
}
catch (Exception ex)
{

//sql_log_err
}

}
}
}


What bugs me I have most of the above code repeating time and time again in my cs file for every different procedure I call.

Obviously I can clear it up and have the one Function being called with the procedure name as a variable, but how do I feed it different number of Parameters (with different Data Types - int,string bool - never anything else) for the different procedures I use ?

I can have few different functions with different number of parameters(0-10), but I feel there is a better way of doing this ?

Answer

You can use a helper class to encapsulate sql parameters and create a single method to handle all dataset fills like this:

Helper class:

private class SqlParamDefinition
{

    public SqlParamDefinition(string name, SqlDbType dbType, object value)
    {
        this.Name = name;
        this.DbType = dbType;
        this.Value = value;
    }

    public string Name { get; }
    public SqlDbType DbType { get; }

    public object Value { get; }


}

Execute method (based on the method you posted):

public DataSet ExecuteSelectProcedure(string procedeureName, params SqlParamDefinition[] parameters)
{
    var ds = new DataSet();
    using (var con = new SqlConnection(DatabaseConnectionString))
    {

        using (var cmd = new SqlCommand(procedeureName, DbConn.objConn))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            for(int i = 0; i < parameters.Length; i++)
            {
                var param = parameters[i];
                cmd.Parameters.Add(new SqlParameter(param.Name, param.DbType).Value = param.Value);
            }

            try
            {
                con.Open();
                var objDataAdapter = new SqlDataAdapter();
                objDataAdapter.SelectCommand = cmd;

                objDataAdapter.Fill(ds);

                con.Close();
            }
            catch (Exception ex)
            {

                //sql_log_err
            }

        }
    }
    return ds;
}

Calling example:

var parameters = new SqlParamDefinition[]
{
    new SqlParamDefinition("@Param1", SqlDbType.VarChar, "value1"),
    new SqlParamDefinition("@Param2", SqlDbType.VarChar, "value2"),
    new SqlParamDefinition("@Param3", SqlDbType.Int, 123),
};

var ds = ExecuteSelectProcedure("Strong procedure name", parameters);
Comments