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

Answer 3

I know it's not what you're asking, but I have a much simpler and more robust solution to offer.

The Microsoft Patterns and Practices library includes a Data Access Application block that is incredibly powerful and easy to use. A sample for executing a stored procedure and returning a dataset is shown below from our actual code:

 object[] ParameterValues = new object[] {"1",DateTime.Now, 12, "Completed", txtNotes.Text};
 Database db = DatabaseFactory.CreateDatabase("ConnectionStringName");
 DataSet ds =  = db.ExecuteDataSet("StoredProcName", ParameterValues);

It doesn't matter if the Connection is OleDb, ODBC, etc. The ConnectionStringName in the first line of code is just the name of the Consternating as defined in the .config file. You pass in a Connection String name, stored proc name, and an array of objects, which make up the parameters. This is just one of the many sweet functions available.

You'll get everything you're trying to build and then some.

The official site is here: http://msdn.microsoft.com/en-us/library/ff648951.aspx

To save you some searching, the Data classes documentation are found here: http://msdn.microsoft.com/en-us/library/microsoft.practices.enterpriselibrary.data(PandP.50).aspx

(and it's free from Microsoft, and updated regularly.)

Answer 4

This answer is intended for slightly more specific purpose than what you're doing, but building on @Dismissile's answer, I used a Dictionary to supply the parameter name and value to a foreach loop in my personal project.

using( IDbCommand dbCommand = dbConnection.CreateCommand() )
{
    dbCommand.CommandText = Properties.Settings.Default.UpdateCommand;
    Dictionary<string,object> values = new Dictionary<string,object>()
    {
        {"@param1",this.Property1},
        {"@param2",this.Property2},
        // ...
    };
    foreach( var item in values )
    {
        var p = dbCommand.CreateParameter();
        p.ParameterName = item.Key;
        p.Value = item.Value;
        dbCommand.Parameters.Add(p);
    }
}

Answer 5

Your Parameters parameter needs to be of type IDataParameter[] and, given the error text, the concrete implementation needs be a SqlParameter[] type.

If you wish to keep your signature, you'll need a factory to derive the necessary concrete implementation.

Answer 6

Add using System.Data.SqlClient; and cmd.Parameters.Add(new SqlParameter("@parameterName", value));