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.
- 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
6 responses