Adding parameters to IDbCommand

Question

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 1

I believe IDbCommand has a CreateParameter() method:

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

Answer 2

You could add the code of the accepted answer to an extension method:

public static class DbCommandExtensionMethods {     public static void AddParameter (this IDbCommand command, string name, object value)     {         var parameter = command.CreateParameter();         parameter.ParameterName = name;         parameter.Value = value;         command.Parameters.Add(parameter);     } }