Minimize code repeatednesses when calling Stored Procedures

Question

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 1

Update

I know this is a very old question (and in fact, I only stumbled upon it when searching another old answer I gave someone else for closing as duplicate), but I have recently released a git hub project that answers this very need. It minimizes code repetition when using ADO.Net by encapsulating the Connection, Command, Parameters, and DataAdapters.
If you want to give it a try I would be glad to know what you think of it.

First version

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

Answer 2

I had this issue; I was calling stored procedures on multiple databases. You could store the stored procedure details e.g. name, input parameters, output parameters etc in database tables and then use a factory method to populate an object (clsStoredProcedure in the example below). The code would look something like this (I have not tested the code):

public void StoredProcedureThatIsBeingcalled(clsStoredProcedure objStoredProcedure)
 {
     using (SqlConnection con = new SqlConnection(objStoredProcedure.ConnectionString))
     {
         ds = new DataSet("DsToGoOut");
         using (SqlCommand cmd = new SqlCommand(objStoredProcedure.Name, DbConn.objConn))
         {
             cmd.CommandType = CommandType.StoredProcedure;
foreach (Parameter p in clsStoredProcedure.Parameters)
{
       cmd.Parameters.Add(new SqlParameter(p.name, p.value));
}

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

                 objDataAdapter.Fill(ds);

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

                 //sql_log_err
             }

         }
     }
 }

If you are connecting to Oracle databases and SQL Databases then you can use dbConnection, dbCommand etc to connect to the databases.