Calling SQL Server stored procedures from Microsoft.NET

Introduction

Here I assume that reader knows how to write SQL Server stored procedure. My intension is to describe different ways of calling a stored procedure. I try to keep it as easy as simple.

Background

I wanted to create an ASP.NET page that would pass parameters to a SQL Server stored procedure to process data into a database. It surprised me that I couldn't find online sample through Google, which motivated me to put together this short article.

Using the code

At first I want to show how we can call a simple stored procedure which has no parameters. To do this let Database has a simple stored procedure named SP1. So we can call the procedure in the following way:

string sConstr = ConfigurationManager.ConnectionStrings["urconnectionstring "].ConnectionString;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SP1";

SqlConnection Conn = new SqlConnection(sConstr);
Conn.Open();

sqlCmd.Connection = Conn;
sqlCmd.CommandType = CommandType.StoredProcedure;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

DataTable dt = new DataTable("tbl");
da.Fill(dt);

sqlCmd.Dispose();
Conn.Close();

Now I want to give an example on how we can call a stored procedure which has input parameter. Let first parameter is @Param1 and the second one is @Param2. So looks at the below code segment:

string sConstr = ConfigurationManager.ConnectionStrings["urconnectionstring "].ConnectionString;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "SP2";

SqlConnection Conn = new SqlConnection(sConstr);
Conn.Open();

sqlCmd.Connection = Conn;
sqlCmd.CommandType = CommandType.StoredProcedure;

sqlCmd.Parameters.Add(new SqlParameter("@Param1", SqlDbType.Real, 18));
sqlCmd.Parameters["@Param1"].Value = 8;

sqlCmd.Parameters.Add(new SqlParameter("@Param2", SqlDbType.Real, 18));
sqlCmd.Parameters["@Param2"].Value = 10;

SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

DataTable dt = new DataTable("tbl");
da.Fill(dt);

sqlCmd.Dispose();
Conn.Close();

In the above way we can call stored procedure which has parameters. If anyone examines the above code module they will see that here I bind parameters one after another which is not an efficient way especially when anyone thinks about reusability. Now my concern is that how we can write a method which executes the stored procedures & we can reuse it. My approach is in each application we use some common utility classes. May be UI utility/DB utility. So one can write a static DB utility class where he can write the below static module named ExecSP:

  public static DataTable ExecSP(string procName, string[] paramName, Object[] paramValue)
  {
      string sConstr = ConfigurationManager.ConnectionStrings["urconnectionstring"].ConnectionString;
SqlCommand sqlCmd = new SqlCommand();
      sqlCmd.CommandText = procName;

      for (int i = 0; i < paramName.Length; i++)
          sqlCmd.Parameters.AddWithValue(paramName[i], paramValue[i]);

      SqlConnection Conn = new SqlConnection(sConstr);
      Conn.Open();

      sqlCmd.Connection = Conn;
      sqlCmd.CommandType = CommandType.StoredProcedure;

      SqlDataAdapter da = new SqlDataAdapter(sqlCmd);

      DataTable dt = new DataTable("tbl");
      da.Fill(dt);

      sqlCmd.Dispose();
      Conn.Close();
      return dt;
  }

This method takes 3 parameters. The first one is the stored procedure name, second one is the parameters name & the last one is corresponding parameters value. Now I want to show how we invoke the procedures from UI using above method:

DataTable dt = new DataTable();
dt=clsDbUtility.ExecSP("SP2", new string[] { "@Param1","@Param2" }, new object[] { 8,10 });

Also anyone can use the above static method to invoke procedures which has no parameter in the following way:

DataTable dt = new DataTable();
dt = clsDbUtility.ExecSP("Sp1", new string[] { }, new object[] { });