2008/10/19

A generic method to call Stored Procedures

Scenario:
1.You need to call many Stored Procedures (Sproc), but you don't want to create a method for each of them.
2.You have some Sprocs, and each of them has different numbers of input parameters.

Here is what I did:
private DataSet DBConnectionForGettingData(string sprocName, Dictionary<string, string> paramList)
{
  using (SqlConnection conn = new SqlConnection(str_ConnectionString))
  {
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = sprocName;
    cmd.Connection = conn;

    //loop through the dictionary
    foreach (string key in paramList.Keys)
    {
      cmd.Parameters.AddWithValue(key, paramList[key]);
    }

    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    //release the resource
    cmd = null;
    da = null;

    return ds;
  }
}
The paramList is the collection of parameters. All you need to do is create a Dictionary object and add all the parameters to this object. You can also adjust the arguments of this method to make it more flexible.

3 comments:

  1. Thanks a lot.

    Jeffrey A. Reyes

    ReplyDelete
  2. Cool, thanks for sharing! Just what I was looking for.

    ReplyDelete
  3. Can yo write a Vb.net version?

    ReplyDelete