We're hiring!

We're actively seeking designers and developers for our all three of our locations.

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.
 

Drew Colthorp (28 Posts)

This entry was posted in .NET / WPF and tagged , , , , . Bookmark the permalink. Both comments and trackbacks are currently closed.

One Comment

  1. Ben Rousch
    Posted January 21, 2013 at 10:22 am

    I’ve run into a similar problem using ODBC in a Python project, but I hadn’t thought about faking in the names. I’ll have to give your pattern a try. Thanks for the idea.