SqlParameter and IN statement
c#, sql, sql-server
Solution
I use a helper method like this
/// <summary>
/// Adds a sequence of parameters to an existing parameter collection
/// </summary>
/// <typeparam name="T">Type of parameter values</typeparam>
/// <param name="parameters">Existing parameter collection</param>
/// <param name="pattern">Name pattern of parameters. Must be a valid <see langword="int"/> format string</param>
/// <param name="parameterType">Database type of parameters</param>
/// <param name="length">Length of parameter. 0 for numeric parameters</param>
/// <param name="values">Sequence of values</param>
/// <returns>Comma separated string of parameter names</returns>
public static string AddParameters<T>(SqlParameterCollection parameters,
string pattern,
SqlDbType parameterType,
int length,
IEnumerable<T> values) {
if (parameters == null)
throw new ArgumentNullException("parameters");
if (pattern == null)
throw new ArgumentNullException("pattern");
if (values == null)
throw new ArgumentNullException("values");
if (!pattern.StartsWith("@", StringComparison.CurrentCultureIgnoreCase))
throw new ArgumentException("Pattern must start with '@'");
var parameterNames = new List<string>();
foreach (var item in values) {
var parameterName = parameterNames.Count.ToString(pattern, CultureInfo.InvariantCulture);
parameterNames.Add(parameterName);
parameters.Add(parameterName, parameterType, length).Value = item;
}
return string.Join(",", parameterNames.ToArray());
}
It is used like this
string sql = "SELECT col1 " +
"FROM Table " +
"WHERE col2 IN ({@Values}) ";
var paramNames = SqlHelper.AddParameters(command.Parameters,
"@Value0",
SqlDbType.Int,
0,
listOfInts);
command.CommandText = sql.Replace("{@Values}", paramNames);
...
(Result is a query like `SELECT ... IN (@Value0, @Value1, @Value2)`)
Problem
I need following query: ``` createList(string commaSeparatedElements) { ... SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+commaSeparatedElements+")"); ... } ``` I would like to write it using a parameterized query, so every element from the string is checked to prevent against Sql-Injections. Pseudo-code: ``` createList(string commaSeparatedElements) { ... SqlParameterList elements = new SqlParameterList("@elements", SqlDbType.Int); SqlParameterList.Values = commaSeparatedElements.split(new Char[1] {','}); SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN (@elements)"); query.Parameters.Add(elements); ... } ``` Does anything like that exist in C#, or will I have to write it by myself? EDIT: Thanks for all the answers. As I try not to use code I don't understand (too many bad experiences in the last days), dapper and table-valued parameters, even though they may be perfect for my needs, are off-limits. I just made a loop. ``` string[] elements = commaSeparatedElements.split(new Char[1] {','}); StringList idParamList = new StringList(); for(int i=0;i<elements.Count;i++) { query.Parameters.AddWithValue("@element"+i,Convert.ToInt32(elements[i])); idParamList.Add("@element" + i); } SqlCommand query = new SqlCommand("SELECT * FROM table WHERE id IN ("+String.Join(",",idParamList)+")"); ```