title: Adding parameters to IDbCommand
body: <p>I am creating a small helper function to return a <code>DataTable</code>. I would like to work across all providers that <code>ADO.Net</code> supports, so I thought about making everything use <code>IDbCommand</code> or <code>DbCommand</code> where possible.</p>
  
  <p>I have reached a stumbling block with the following code:</p>
  
  <pre><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 &amp;&amp; Parameters.Length &gt; 0)
              {
                  for (Int32 i = 0; i &lt; Parameters.Length; i++)
                  {
                      cmd.Parameters.Add(Parameters.GetValue(i));
                  }
              }
              dt.Load(cmd.ExecuteReader(), LoadOption.OverwriteChanges);
          }
          return dt;
      }
  </code></pre>
  
  <p>When this code is executed, I receive an <code>InvalidCastException</code> which states the following:</p>
  
  <blockquote>
    <p>The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.</p>
  </blockquote>
  
  <p>The code falls over on the line:</p>
  
  <pre><code>cmd.Parameters.Add(Parameters.GetValue(i));
  </code></pre>
  
  <p>Any ideas? </p>
  
  <p>Any improvements to the above code is appreciated.</p>
  
  <hr>
  
  <p>Actual solution: </p>
  
  <pre><code>    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 &amp;&amp; Parameters.Length &gt; 0)
              {
                  MatchCollection cmdParams = regParameters.Matches(cmd.CommandText);
                  List&lt;String&gt; param = new List&lt;String&gt;();
                  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;
      } 
  </code></pre>
  
  <p>Thanks for ideas/links etc. :)</p>