Suppose you are writing a Helper class in your .Net Project that uses ADO.Net in the Data Layer. And you need to call Stored Procedures a lot. Writing a generic Helper method that takes in an Array of SqlParameters can be used so that you don’t have re-write the same code of calling the Stored Procedure again and again.
Below is the code that I’ve used as a general approach to call Stored Procedure and return DataSet:
public static DataSet ExecuteProcedureReturnDataSet(string connString, string procName,
params SqlParameter[] paramters)
{
DataSet result = null;
using (var sqlConnection = new SqlConnection(connString))
{
using (var command = sqlConnection.CreateCommand())
{
using (SqlDataAdapter sda = new SqlDataAdapter(command))
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = procName;
if (paramters != null)
{
command.Parameters.AddRange(paramters);
}
result = new DataSet();
sda.Fill(result);
}
}
}
return result;
}Another way to call Stored Procedure would be to return a single value from the Stored Procedure like a string. You can use the below method to return only a String:
public static string ExecuteProcedureReturnString(string connString, string procName,
params SqlParameter[] paramters)
{
string result = "";
using (var sqlConnection = new SqlConnection(connString))
{
using (var command = sqlConnection.CreateCommand())
{
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = procName;
if (paramters != null)
{
command.Parameters.AddRange(paramters);
}
sqlConnection.Open();
var ret = command.ExecuteScalar();
if (ret != null)
result = Convert.ToString(ret);
}
}
return result;
}Example of SqlParameter array to be passed to the above methods can be as follows:
SqlParameter[] params =
{
new SqlParameter("@name", name),
new SqlParameter("@year", year)
};
InTheTechPit 