Common function to Retrieve a DataTable using a StoredProcedure

This resource explains a common function code which can be used for all StoredProcedures in common to Retrieve the datas from database using stored procedure. This common function retrieves data to DataTable.

This common function can be used to retrieve the datas from database using stored procedure to datatable

public static DataTable ExecuteDataTable(string storedProcedureName,
params SqlParameter[] arrParam)
DataTable dt = new DataTable();

// Open the connection
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["AppDB"].ConnectionString))

// Define the command
using (SqlCommand cmd = new SqlCommand())
cmd.Connection = cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storedProcedureName;

// Handle the parameters
if (arrParam != null)
foreach (SqlParameter param in arrParam)

// Define the data adapter and fill the dataset
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
return dt;

The function required two inputs
1)StoredProcedure name as String
2)Parameters to be passed to Stored procedure if any

Hope this helps!!!

Steadfast Technology


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: