Passing a collection of SQL parameters to a function

When writing ASP.NET CRUD applications in C #, I often find myself inserting a lot of repetitive boilerplate code to fetch SQL Server queries into data tables, get scalar values ​​from queries into variables, call stored procedures, etc.

I recently tried to refactor this by creating generic classes and methods to handle these basic tasks. However, to work properly, they must be able to accept an arbitrary number of parameters. An inline class SqlParameterCollection

seemed like the obvious first choice, but unfortunately this class cannot be generated from user code. The next option was to pass in a List<SqlParameter>

function and then use foreach

it to add its content to the built-in parameter collection SqlCommand

. It worked, but the declaration is a little awkward.

To create a list in code before calling the function, I have to do something like this:

List<SqlParameter> ParameterList = new List<SqlParameter>
{
    new SqlParameter() { ParameterName = "@Parameter1", SqlDbType = SqlDbType.VarChar, Value = InputVariable1 },
    new SqlParameter() { ParameterName = "@Parameter2", SqlDbType = SqlDbType.VarChar, Value = InputVariable2 },
    new SqlParameter() { ParameterName = "@Parameter3", SqlDbType = SqlDbType.VarChar, Value = InputVariable3 }
};

      

        
        
        
      

    

Difficult to read on small monitors and still contains a lot of repetitive patterns. So, my next step was to create a custom class with an overloaded Add method so that it wasn't necessary. I read that this is bad for subclassing List

in C #, so I used Collection

:

public class ParameterCollection : Collection<SqlParameter>
{
    public void Add(string    ParameterName,
                    SqlDbType ParameterType,
                    object    ParameterValue)
    {
        // Create the parameter and add it to the list
        SqlParameter Parameter = new SqlParameter();
        Parameter.ParameterName = ParameterName;
        Parameter.SqlDbType     = ParameterType;
        Parameter.Value         = ParameterValue;
        base.Add(Parameter);

        // Done
        return;
    }
}

      

        
        
        
      

    

Then I can declare the parameter set like this:

ParameterCollection Parameters = new ParameterCollection
{
    // name          type               value
    { "@Parameter1", SqlDbType.VarChar, InputVariable1 },
    { "@Parameter2", SqlDbType.VarChar, InputVariable2 },
    { "@Parameter3", SqlDbType.VarChar, InputVariable3 }
};

      

        
        
        
      

    

My question is this: is there a faster / easier way to accomplish this task that I am missing? And are there potential hidden pitfalls or bad practices in the way I am doing this now?