A Utility Method to Run stored procedures and Get results in a Data Table in C# ASP.NET


Hi,

I have just came into a problem where I need to execute many different stored procedures which may output in different ways. Instead of writing seperate codes for each of the stored procedures I build a utility method which will take the stored procedure name and the parameter list as input and it will return a Datatable objec populated as result.

Now we will go through the code:

//This procedure takes Stored procedure name and a Arraylist of parameters as argument.
//The array list is a colection of SqlParameter type of onjects
//I will explain it later

public DataTable GetData(string StoreProcName,ArrayList parameters)
{
SqlConnection _sqlConn = null;
DataTable _dt = new DataTable();
SqlDataAdapter _sqDa = null;

try
{

if (null == _connection)  //I have stored the connection string in this variable
{
throw new Exception(“Connection Null”);
}

//Open a new connection
_sqlConn = new SqlConnection(_connection);

_sqlConn.Open();

SqlCommand cmd = new SqlCommand();
cmd.Connection = _sqlConn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = StoreProcName; //Specify the stored procedure name

//If parameter list is non null the fill the parameter data for this
//Stored procedure.

if (null != parameters)
{
foreach (Object obj in parameters)
{
cmd.Parameters.Add((SqlParameter)obj);
}
}

//Open a new SQL Data Adapter type of object to populate the data table

_sqDa = new SqlDataAdapter(cmd);
_sqDa.Fill(_dt); //Fill the data table

_sqlConn.Close(); //Close the connection

return _dt;
}
catch (Exception ex)
{
return null;
}
finally
{
if (ConnectionState.Open == _sqlConn.State)
{
_sqlConn.Close();
}
}
}

Now I will let you know how to call the stored procedure.

ArrayList _parameterList = new ArrayList(); //Declare a Array List

SqlParameter parameter1 = new SqlParameter(); //Declare a SQL Parameter object

parameter1.ParameterName = “@fisrtinput”; // Give the parameter name
parameter1.SqlDbType = SqlDbType.VarChar;     // Give the data type
parameter1.Value = “First Value”; //Give the value
_parameterList.Add(parameter1); // Add it to the array list

SqlParameter parameter2 = new SqlParameter(); // Declare a second variable
parameter2.ParameterName = “@secondinput”; //Populate as before
parameter2.SqlDbType = SqlDbType.VarChar;
parameter2.Value = “Second_Value”;
_parameterList.Add(parameter2); //Add it to the Array list

Now the Array list has been populated. We will just call the Method with the Array List.

DataTable _dt = GetData(“PROCEDURE_NAME”,  _parameterList);

Now the data table is populated. You can work with that as you like.

Happy Coding 🙂

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s