0

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?

PHeiberg
  • 29,411
  • 6
  • 59
  • 81
Magier
  • 437
  • 1
  • 6
  • 18

2 Answers2

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.

PHeiberg
  • 29,411
  • 6
  • 59
  • 81
  • `PropertyInfo`is available in `System.Reflection`. Add `using System.Reflection;` to your code. – PHeiberg Dec 16 '15 at 08:44
  • Yes sorry, I did not realize Reflection. But the GetValue() Method still does not allow only 1 arguement. – Magier Dec 16 '15 at 09:12
  • 1
    If you're using .NET 2.0 the overload I used is not there and you must pass an additional parameter to `GetValue` - `parameter.GetValue(parameters, null)`. – PHeiberg Dec 16 '15 at 09:19
  • Yes, 2.0. I used your sample and created a solution that works and is easy to use. Any comments / thoughts on this? – Magier Dec 16 '15 at 09:28
  • 1
    Post it as a separate answer if you feel it's a contribution, not in your question. As a user of your method, I'd feel it's confusing if not all properties (such as DbType, etc) of the `SqlParameter` you pass to the method are actually used in the query. I feel it would be better to use another type if all you're interested in are the names and the values. Either like in my answer or to use another `KeyValuePair` type. Feel free to upvote/accept my answer if that's what led you to a solution. – PHeiberg Dec 16 '15 at 09:45
  • I have revieed your comment and you are right. I retried you sample but now I have an issue with the class ConnectionStringSettings. It seems not to exist in 2.0. Is this something you expect me to create myself? :-) – Magier Feb 19 '16 at 12:24
  • @Magier - It's available in 2.0 (see docs [here](https://msdn.microsoft.com/en-us/library/system.configuration.connectionstringsettings(v=vs.80).aspx)). You just need to add a reference to the `System.Configuration` assembly. – PHeiberg Feb 19 '16 at 12:49
  • I went through this documentation already, but it's still marked as wrong... and the System.Configuration reference is not used... VS suggests to create my own implementation of ConnectionStringSettings... ConfigurationSettings is the closest class avaibable in System.Configuration... – Magier Feb 19 '16 at 12:56
1

I ended up with the following solution: 

SqlParameter[] parameters = {
new SqlParameter("@p1", SqlDbType.Int) { Value = 999},
new SqlParameter("@p2",  SqlDbType.Char, 30, "source") { Value = "test"}
}; 
da.RunProcedure("[dbo].[SP1]", parameters, out rowsAffected);

The RunProcedure accepts IDataParameter[] parameters and forwards this to an command builder method that adds each single of them into the SqlParameters Property of my SqlCommand object:

private static SqlCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand( storedProcName, GetDBConnection() );
            command.CommandType = CommandType.StoredProcedure;

            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    command.Parameters.Add( parameter );
                }
            }
            return command;    
        }

This works fine and this way I can add each Param with 1 single line of code (that was my destination #1) incl. all Properties of SqlParameter available (use SqlDBType if required, this is up to the user).

Magier
  • 437
  • 1
  • 6
  • 18