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[] { });