IDbCommand named parameters for both MySQL and SQL Server

Question

I've been trying to create some DB independent code like the following:

IDbCommand command = connection.CreateCommand();
command.CommandText = "...";

IDbDataParameter param1 = command.CreateParameter();
param1.ParameterName = "param1";
param1.Value = "value";
command.Parameters.Add(param1);

The command text that works for mysql is:

select * from mytable where field1 = ?param1

The command text that works for sqlserver is:

select * from mytable where field1 = @param1

Is there some form that will work for both?

EDIT:

SQL Server 2008R2
MySQL 5.0.X

Note that i've edited my answer to provide a dynamic approach using reflection. — Tim Schmelter
– Tim Schmelter, Commented Jan 13, 2014 at 17:18

Community · Accepted Answer · 2017-05-23 11:45:21Z

No, i think there's no such method. So you have to provide your own:

public static String GetProviderParameter(string paramName, IDbConnection con)
{
    string prefix = "";
    if(con is System.Data.SqlClient.SqlConnection)
        prefix = "@";
    else if(con is System.Data.OleDb.OleDbConnection)
        prefix =  "?";
    else if(con is System.Data.Odbc.OdbcConnection)
        prefix =  "?";
    else if(con is MySql.Data.MySqlClient.MySqlConnection)
        prefix =  "?";

    return prefix + paramName;
}

Usage:

param1.ParameterName = GetProviderParameter("param1", connection);

Or you could use this extension which uses reflection to use the protected method GetParameterName from the DbCommandBuilder class:

public static class Db
{
    static readonly Func<DbConnection, DbProviderFactory> getDbProviderFactory = 
        (Func<DbConnection, DbProviderFactory>)Delegate.CreateDelegate(typeof(Func<DbConnection, DbProviderFactory>), typeof(DbConnection).GetProperty("DbProviderFactory", BindingFlags.Instance | BindingFlags.NonPublic).GetGetMethod(true));
    static readonly Func<DbCommandBuilder, string, string> getParameterName =
        (Func<DbCommandBuilder, string, string>)Delegate.CreateDelegate(typeof(Func<DbCommandBuilder, string, string>), typeof(DbCommandBuilder).GetMethod("GetParameterName", BindingFlags.Instance | BindingFlags.NonPublic, Type.DefaultBinder, new Type[] { typeof(string) }, null));

    public static DbProviderFactory GetProviderFactory(this DbConnection connection)
    {
        return getDbProviderFactory(connection);
    }

    public static string GetParameterName(this DbConnection connection, string paramName)
    {
        DbCommandBuilder builder = GetProviderFactory(connection).CreateCommandBuilder();

        return getParameterName(builder, paramName);
    }
}

Then it's simple as:

param1.ParameterName = connection.GetParameterName("param1");

Mike Perrenoud · Accepted Answer · 2014-01-13 16:13:54Z

0

You can use this for both:

select * from mytable where field1 = @param1

See the documentation for MySql here.

answered Jan 13, 2014 at 16:13

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

1 Comment

Andres A. Over a year ago

You're right, this works for 5.1.x+... unfortunately I've got to use 5.0.x

Collectives™ on Stack Overflow

IDbCommand named parameters for both MySQL and SQL Server

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related