How to pass stored procedure name as parameter in DAL

Question

I have more than one function which simply fetches data from DB. The difference among the function is the stored procedure name (uspLoadStudents,uspLoadMarks). To optimize, make it as one function and passes the SP.

public DataSet LoadSubjects()
{
    string SqlDBConnection = Utils.GetConnectionString();
    DataSet ds = new DataSet();
    SqlConnection sqlConn = new SqlConnection(SqlDBConnection);
    SqlCommand sqlCmd = new SqlCommand("uspLoadSubjects", sqlConn);
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlConn.Open();
    DataTable dt = new DataTable();
    dt.Load(sqlCmd.ExecuteReader());
    ds.Tables.Add(dt);
    sqlConn.Close();
    return ds;
}

Answer 1

Information like sql command, stored procedure name, should be part of your Data Access Layer, instead it is a helper class inside the data access layer. Try this:

public static class DALHelper
{
    public static DataSet ExecuteProcedure(string procedureName)
    {
        string sqlDBConnection = Utils.GetConnectionString();    
        DataSet ds = new DataSet();

        using (SqlConnection sqlConn = new SqlConnection(sqlDBConnection))
        {
            using(SqlCommand sqlCmd = new SqlCommand(procedureName, sqlConn))
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    sqlConn.Open();

                    using (var adapter = new SqlDataAdpter(sqlCmd))
                    {
                        adapter.Fill(ds);
                    }            
                }
                catch
                {
                    throw;
                }
                finally
                {
                    sqlConn.Close();
                }
            }
        }

        return ds;
    }
}

Implement a method to use this helper, for sample:

public DataSet LoadSubjects()
{
   return DALHelper.ExecuteProcedure("uspLoadStudents");
}

Answer 2

This?

public DataSet ExecProc(string procName)
 {
     string SqlDBConnection = Utils.GetConnectionString();
     DataSet ds = new DataSet();
     SqlConnection sqlConn = new SqlConnection(SqlDBConnection);
     SqlCommand sqlCmd = new SqlCommand(procName, sqlConn);
     sqlCmd.CommandType = CommandType.StoredProcedure;
     sqlConn.Open();
     DataTable dt = new DataTable();
     dt.Load(sqlCmd.ExecuteReader());
     ds.Tables.Add(dt);
     sqlConn.Close();
     return ds;
 }

Answer 3

try this

public static DataSet getDataSet(string sp_name, string[] param_names, object[] param_values)
        {
            SqlDataAdapter sqlda = new SqlDataAdapter();
            SqlCommand sqlcmd = new SqlCommand();
            DataSet set = new DataSet();
            try
            {
                sqlcmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                sqlcmd.CommandType = CommandType.StoredProcedure;
                sqlcmd.CommandText = sp_name;                        
                sqlda.SelectCommand = sqlcmd;
                sqlda.Fill(set);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (sqlcmd.Connection.State == ConnectionState.Open)
                    sqlcmd.Connection.Close();
            }
            return set;
        }