More concise way of adding parameters to vendor independent IDataParameterCollection?

Question

On every ADO.NET connector we have AddWithValue and hence adding parameters was a breeze. I am surprised that that overload is not available on IDataParameterCollection or even on DbParameterCollection. How can I achieve the same with minimal fuss (from both caller and callee side) when writing a vendor independent layer (coding against interface)?

Right now I do:

public void Execute(string query, Action<IDbCommand> parameterizer)

Execute(query, cmd =>
    {
        var param = cmd.CreateParameter();
        param.ParameterName = "user_id";
        param.Value = 1;
        cmd.Parameters.Add(param);

        param = cmd.CreateParameter();
        param.ParameterName = "mf";
        param.Value = "6CE71037";
        cmd.Parameters.Add(param);
    });

That's too much work isnt it. Otherwise I can change signature:

public void Execute(string query, IEnumerable<KeyValuePair<string, object>> parameters)

var parameters = new Dictionary<string, object> 
                 { { "user_id", 1 }, { "mf", "6CE71037" } };
Execute(query, parameters);

This approach requires I write another loop inside the callee (Execute method). This is almost what I want but just seeing if there is a more concise approach. For instance like this:

public void Execute(string query, Action<IDataParameterCollection> parameterizer)

Execute(query, p =>
    {
        p["user_id"] = 1;
        p["mf"] = "6CE71037";
    });

This gives a runtime exception saying Unable to cast object of type 'System.Int32' to type 'System.Data.Common.DbParameter. I understand the error. Just wondering if there is a more elegant pattern..

Are you aware of Dapper? http://code.google.com/p/dapper-dot-net/ — tomfanning, Feb 13 '13 at 13:23
Don't quite understand your answer. Dapper gets around the entire issue by not requiring to to add parameter values *at all*. — tomfanning, Feb 13 '13 at 14:21
@tomfanning its not as if dapper doesn't require parameters to be added at all, it needs to be, but in a different way. What I meant is right now the application is built on this simple (the one in my code) DbAL which we might replace with a bigger solution (like dapper or massive or fluentdata) if application and complexity scale. And I have a special disliking for anything that deals with lot of reflection if I can get away without it. And it will be too much work for me to change the queries considering our field names don't match model properties.. — nawfal, Feb 13 '13 at 16:14

score 7 · Accepted Answer · edited Aug 19 '15 at 15:55

Why not use a dictionary in your last signature:

public void Execute(string query, Action<Dictionary<string, object>> parameterizer)

Execute(query, p =>
    {
        p["user_id"] = 1;
        p["mf"] = "6CE71037";
    });

Then, the Execute method can use the Action to populate a Dictionary and create the parameters based on it:

var parameters = new Dictionary<string, object>();
parametrizer(parameters);
foreach (var pair in parameters)
{
    var parameter = f.CreateParameter();
    parameter.ParameterName = pair.Key;
    parameter.Value = pair.Value;
    cmd.Parameters.Add(parameter);
}

Another solution if you want to pass the actual IDbCommand would be to use an extension method:

public static void AddWithValue<T>(this IDbCommand command, string name, T value)
{
    var parameter = command.CreateParameter();
    parameter.ParameterName = name;
    parameter.Value = value;
    command.Parameters.Add(parameter);
}

Calling this would look like:

Execute(query, cmd =>
    {
        cmd.AddWithValue("user_id", 1);
        cmd.AddWithValue("mf", "6CE71037");
    });

hmm, thats a decent option indeed. I would have loved to avoid the inner loop and delegate the actual command or parameter object to callers. +1 still. — nawfal, Feb 13 '13 at 13:22
thanks for the extension method i think its the nicest way to go :) — Dizzle, Aug 03 '17 at 10:41

More concise way of adding parameters to vendor independent IDataParameterCollection?

1 Answers1