Faking Named Parameters in .NET when Using ODBC

On my current project, we wanted to use named parameters in our queries. Unfortunately, we are using the MySQL ODBC driver for .NET, and named parameters are not supported. Positional parameters are supported, so we created a little utility to help.

The helper function is straightforward to use. Instead of using connection.CreateCommand, you pass your statement and parameters to connection.CreateCommandWithNamedParamaters, like:

var myDictionary = new Dictionary<string,object> {{"bar", 1}};
var cmd = connection.CreateCommandWithNamedParameters("select * from table where foo = ?bar", myDictionary);
var reader = cmd.ExecuteReader();

CreateCommandWithNamedParameters is as simple as

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Odbc;
using System.Linq;
using System.Text.RegularExpressions;
//
namespace Codes
{
    internal static class OdbcConnectionExtensions {
        static readonly Regex NamedParameterPattern = new Regex(@"\?(\w+)");

        public static OdbcCommand CreateCommandWithNamedParameters(this OdbcConnection connection, string sql, IDictionary<string, object> parameters)
        {
            var cmd = connection.CreateCommand();
            var parameterIndex = 0;
            cmd.CommandText = NamedParameterPattern.Replace(sql, (m) => {
                var key = m.Groups[1].Value;
                var value = parameters[key];
                var parameterName = string.Format("{0}_{1}", key, parameterIndex++);

                if ((value as string) != null || (value as IEnumerable) == null) {
                    cmd.Parameters.AddWithValue(parameterName, value ?? DBNull.Value);
                    return "?";
                } else {
                    var enumerable = ((IEnumerable)value).Cast<object>();
                    var i = 0;
                    foreach (var el in enumerable) {
                        var elementName = string.Format("{0}_{1}", parameterName, i++);
                        cmd.Parameters.AddWithValue(elementName, el ?? DBNull.Value);
                    }
                    return string.Join(",", enumerable.Select(_ => "?"));
                }
            });
            return cmd;
        }
    }
}

This code is in the public domain. Use it however you wish.