Pass Parameters Generically

Question

I am wanting to create a generic database class so all our developers can follow the exact same syntax by just importing a database dll. That being said, I am somewhat stuck on how to round off this part. I need to find a way to pass an unknown amount of parameters through this method. I tried doing a list, but didn't seem to get anywhere with it. Also just for clarity sake the db.ValidateConnection(Connection) portion just makes sure the connection isn't already in use or open, then opens it.

Want to call this method:

public static SqlDataReader ExecuteReader(string CommandName, 
                                          SqlConnection Connection,
                                          PARAMETERS??!?!?)
{
    using (SqlCommand cmd = new SqlCommand(CommandName, Connection))
    {
        // Ensure we are executing a stored procedure.
        cmd.CommandType = CommandType.StoredProcedure;

        // Ensure our connection is not already open and then open it.
        db.ValidateConnection(Connection);

        // Return our SqlDataReader object with the desired execution results.
        return cmd.ExecuteReader();
    }
}

With this idea of code:

// Want to add these Parameters..//
    cmd.Parameters.AddWithValue("@Param0", Param0);
    cmd.Parameters.AddWithValue("@Param1", Param1);
    cmd.Parameters.AddWithValue("@Param2", Param2);

    // Or These Parameter...
    cmd.Parameters.AddWithValue("@Param2", Param2);

    // Or NO Parameters...

    // To this Execution statement.
    ExecuteReader(CommandName: "[MyStoredProcedure]",
                  Connection: "MyConnection",
                  PARAMTERS??!?!?!: "ListOfAllParametersToAttach")

--UPDATE

Final working code! Invoked in our .dll:

 public static SqlDataReader ExecuteReader(string CommandName,
                                          SqlConnection Connection,
                                          params SqlParameter[] Parameters)
{
    // ExecuteReader
    //
    // @CommandName string :
    //     Name of the stored procedure we are looking to execute.
    //
    // @Connection SqlConnection :
    //     SQL Connection to be used when executing the provided @CommandName.
    //
    // @Parameters params SqlParameter[] :
    //     If any parameters exist this will contain the list of we will attach to our SqlCommand.

    using (SqlCommand cmd = new SqlCommand(CommandName, Connection))
    {
        // Ensure we are executing a stored procedure.
        cmd.CommandType = CommandType.StoredProcedure;

        // Iterate through our list and add all required parameters.
        foreach (SqlParameter Parameter in Parameters)
        {
            cmd.Parameters.Add(Parameter);
        }

        // Ensure our connection is not already open and then open it.
        db.ValidateConnection(Connection);

        // Return our SqlDataReader object with the desired execution results.
        return cmd.ExecuteReader();
    }
}

Called from application :

using (SqlDataReader dr = SMCConnect.ExecuteReader("[ProcedureName]",
                                                    con,
                                                    new SqlParameter("@ParameterName", ParameterName))) { }

Answer 1

I'd use params SqlParameter[]. Which is called as "variable length parameters" or Variadic.

public static SqlDataReader ExecuteReader(string CommandName,
                                  SqlConnection Connection,
                                  params SqlParameter[] parameters)
{
    using (SqlCommand cmd = new SqlCommand(CommandName, Connection))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        foreach (var param in parameters)
        {
            cmd.Parameters.Add(param);
        }
        //Do something..                
        return cmd.ExecuteReader();
    }
}

Use case:

ExecuteReader("MyStoredProcedure",myConnection,new SqlParameter("@param1",value1),new SqlParameter("@param2",value2),...);

Answer 2

Use params[] KeyValuePair<string, object> in your signature, like this:

public static SqlDataReader ExecuteReader(string CommandName, 
                                      SqlConnection Connection,
                                      params[] KeyValuePair<string, object> parameters)
//...
foreach (var p in parameters)
   cmd.Parameters.Add(p.Key, p.Value)
//...

Answer 3

public static SqlDataReader ExecuteReader(string CommandName, 
                                          SqlConnection Connection,
                                          Dictionary<string, object> parameters)

and then

foreach (var key in parameters.Keys)
{
  cmd.Parameters.AddWithValue(key, parameters[key]);
}