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..
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");
});