Generic procedure execution method for DAL incl. parameters

Question

I am trying to create a "generic" method in a data access layer that executes a passed stored procedure in Sql Server and also takes a list / array / collection of SqlParameters, to make the usage of a stored procedure call within other parts of the code easier (without requirement to care for connection, command objects etc).

The goal is sth. like this:

int iAffectedRows = dal.RunProcedure("dbo.mySP", parameters);

The parameters should of course be defined previously but without the types. I want them to be created using the AddwithValue() method of SqlParameterCollection class.

It looks like it's impossible because the SqlParameterCollection class can't be instanciated. Look at this discussion.

Anyone knows how to create this?

Answer 1

It's not a good idea to send in a DbParameterCollection (SqlParameterCollection), since it's tightly coupled (which you have discovered) with the ADO.NET infrastructure that you're trying to abstract away. It's better to map your own parameter representation to the collection inside your method.

You can solve it using something like this:

public class DataAccess
{
    private ConnectionStringSettings _settings;

    public DataAccess(ConnectionStringSettings settings)
    {
        _settings = settings;
    }

    public int RunProcedure(string name, dynamic parameters)
    {
        using (var conn = CreateConnection())
        using (var command = CreateCommand(conn, name, parameters))
        {
            return command.ExecuteNonQuery();
        }
    }

    private DbConnection CreateConnection()
    {
        var factory = DbProviderFactories.GetFactory(_settings.ProviderName);
        var connection = factory.CreateConnection();
        connection.ConnectionString = _settings.ConnectionString;
        connection.Open();
        return connection;
    }

    private DbCommand CreateCommand(DbConnection conn, string commandText,
        object parameters)
    {
        var cmd = conn.CreateCommand();
        cmd.CommandText = commandText;
        cmd.CommandType = CommandType.StoredProcedure;
        foreach(PropertyInfo parameter in parameters.GetType().GetProperties())
        {
            var commandParameter = cmd.CreateParameter();
            commandParameter.ParameterName = "@" + parameter.Name;
            commandParameter.Value = parameter.GetValue(parameters);
            cmd.Parameters.Add(commandParameter);
        }
        return cmd;
    }
}

Callable with a syntax like this:

dal.RunProcedure("dbo.mySP", new { 
    Parameter1 = value1, 
    Parameter2 = value2
});

You can greatly simplify the code if you only want to support SqlClient.

But instead of rolling this on your own, use a ready made stable library, such as Dapper.