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))
{
cnn.Open();
// 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)
cmd.Parameters.Add(param);
}
// Define the data adapter and fill the dataset
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
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!!!
Regards,
S.Rajeswari
Steadfast Technology