Stuart Blackler Stuart Blackler - 3 years ago 166
C# Question

Adding parameters to IDbCommand

I am creating a small helper function to return a

DataTable
. I would like to work across all providers that
ADO.Net
supports, so I thought about making everything use
IDbCommand
or
DbCommand
where possible.

I have reached a stumbling block with the following code:

private static DataTable QueryImpl(ref IDbConnection conn, String SqlToExecute, CommandType CommandType, Array Parameters)
{
SetupConnection(ref conn);
// set the capacity to 20 so the first 20 allocations are quicker...
DataTable dt = new DataTable();
using (IDbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = SqlToExecute;
cmd.CommandType = CommandType;
if (Parameters != null && Parameters.Length > 0)
{
for (Int32 i = 0; i < Parameters.Length; i++)
{
cmd.Parameters.Add(Parameters.GetValue(i));
}
}
dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);
}
return dt;
}


When this code is executed, I receive an
InvalidCastException
which states the following:


The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.


The code falls over on the line:

cmd.Parameters.Add(Parameters.GetValue(i));


Any ideas?

Any improvements to the above code is appreciated.




Actual solution:

private static readonly Regex regParameters = new Regex(@"@\w+", RegexOptions.Compiled);
private static DataTable QueryImpl(ref DbConnection conn, String SqlToExecute, CommandType CommandType, Object[] Parameters)
{
SetupConnection(ref conn);
DataTable dt = new DataTable();
using (DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = SqlToExecute;
cmd.CommandType = CommandType;
if (Parameters != null && Parameters.Length > 0)
{
MatchCollection cmdParams = regParameters.Matches(cmd.CommandText);
List<String> param = new List<String>();
foreach (var el in cmdParams)
{
if (!param.Contains(el.ToString()))
{
param.Add(el.ToString());
}
}
Int32 i = 0;
IDbDataParameter dp;
foreach (String el in param)
{
dp = cmd.CreateParameter();
dp.ParameterName = el;
dp.Value = Parameters[i++];
cmd.Parameters.Add(dp);
}
}
dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);
}
return dt;
}


Thanks for ideas/links etc. :)

Answer Source

I believe IDbCommand has a CreateParameter() method:

var parameter = command.CreateParameter();
parameter.ParameterName = "@SomeName";
parameter.Value = 1;

command.Parameters.Add(parameter);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download