Parameterising the IN clause of an SQL SELECT in .NET

I’m a fan of parameterized queries with a strong dislike for building SQL (or other magic strings). Encoding, escaping errors, localization formatting problems and injection can run rampant when you think everything is a string.

Problem

Today I found myself a quandary as I needed to SELECT records based on a list of values I had. e.g.

SELECT * FROM Products WHERE ProductCode IN ('ABC123', 'DEF456', 'GHI789')

At first glance the relevant parameterized version might look like:

SELECT * FROM Products WHERE ProductCode IN (@productlist)

The problem here however is that if you put a comma-separate list of items into a string parameter named @productlist then it sends this to the database server:

SELECT * FROM Products WHERE ProductCode IN ('ABC123, DEF456, GHI789')

That’s not what we want at all.

This hack isn’t pretty and it has some limitations:

• Only works with named parameters
• Could upset some DB providers that don’t like having command parameters removed or command text modified
• Parameter name to replace must be totally unique, i.e. not exist as a subset of another parameter name
• Only preserves the basic IDbCommand declared properties

It should however work across DB providers and types.

Usage

The previous example would mean we use exactly the expected parameterized version with @productlist in place.

Add the parameter as you’d expect but instead of assigning a string/numeric to it assign something IEnumerable.

Finally call this method against the command and parameter before you execute it for the ‘magic’ to happen:

The ‘magic’

public void ExpandDbArrayParameter(IDbCommand cmd, IDbDataParameter parameter) {
  if (parameter.Value is IEnumerable) {
    int index = 0;
    StringBuilder newParameterSQL = new StringBuilder();
    foreach(Object value in (IEnumerable) parameter.Value) {
      String valueParameterName = String.Format("{0}{1}", parameter.ParameterName, ++index);
      IDataParameter valueParameter = cmd.CreateParameter();
      valueParameter.DbType = parameter.DbType;
      valueParameter.Direction = parameter.Direction;
      valueParameter.ParameterName = valueParameterName;
      valueParameter.SourceColumn = parameter.SourceColumn;
      valueParameter.SourceVersion = parameter.SourceVersion;
      valueParameter.Value = value;
      cmd.Parameters.Add(valueParameter);

      if (index == 1)
        newParameterSQL.Append(valueParameterName);
      else
        newParameterSQL.Append("," + valueParameterName);
      }
      cmd.Parameters.Remove(parameter);
      cmd.CommandText = cmd.CommandText.Replace(parameter.ParameterName, newParameterSQL.ToString());
    }
}

[)amien