Create a method that execute a SP irrespective of number of parameters.

We as a developer always use Stored Procedure for retrieving results from database. We write code to execute stored procedure and pass all the required parameters using Command Object. But writing all code including connection string, command object, AddWithValue() parameters and executing it every time when you have to execute a stored procedure with different parameters is a tedious task. As a developer, we must be lazy while writing our code which give rise to re-usability. This post will explain how to execute Stored Procedure irrespective of the number parameters where in we just have to pass command object and an empty DataSet object to retrieve the result.

Step 1: Create a method that returns DataTable and accepts two parameters: a command of type SqlCommand and dataSet of type DataSet. The Prototype is as follows:

public DataTable ExecuteSp(SqlCommand cmd, DataSet ds)

This method returns the DataTable.

Step 2: Now write the definition for the above method i.e., ExecuteSP(). In try block, Create a variable dt of type DataTable that will get returned as output of this method. First we created an SqlDataAdapter that will be used to fill the DataSet. Then we open the connection using the SqlConnection object that was initialized with the connection string defined in the web.config file.

Than we set the SelectCommand property of the data adapter to cmd i.e., command object that was passed as a parameter.

Next we use the Fill() method of the SqlDataAdapter to fill the DataSet. Data Adapter acts as a bridge between the DataSet and the Data Source. After filling the data set we fetch the required table and assign it to the DataTable variable dt. And than return this dt.

Step 3: If anything goes wrong while doing the above task, the control goes to the catch block where we close the connection and than again open the connection and perform the same task again to return the required data table.

Step 4: Finally we close the connection.

Here is the complete method:

public DataTable ExecuteSp(SqlCommand cmd, DataSet ds)

{

DataTable dt;

try

{

SqlDataAdapter ad = new SqlDataAdapter();

con.Open();

ad.SelectCommand = cmd;

ad.Fill(ds);

dt = ds.Tables[0];

return dt;

}

catch

{

con.Close();

con.Dispose();

SqlDataAdapter ad = new SqlDataAdapter();

con.Open();

ad.SelectCommand = cmd;

ad.Fill(ds);

dt = ds.Tables[1];

return dt;

}

finally

{

con.Close();

con.Dispose();

}

}

Step 5: Now to execute the method, we need to set the command object with the proper stored procedure name and parameters.




DataSet ds = new DataSet();

SqlCommand cmd = new SqlCommand("sp_getEmployeeDetails", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@EmpId", EmployeeId);

cmd.Parameters.AddWithValue("@Date", date);

return ExecuteSp(cmd, ds);