A generic method to call Stored Procedures

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();

    //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.


  1. Thanks a lot.

    Jeffrey A. Reyes

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

  3. Can yo write a Vb.net version?