SqlParameter and IN statement

Question

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)+")");

Solution 1

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))

Solution 2

"dapper" has some voodoo for this:

var ids = new List<int> {1,2,3,4,5};
var rows = conn.Query<SomeType>("select * from table where id in @ids",
      new { ids }).ToList();

You'll notice the lack of parenthesis in the in usage; "dapper" spots this, and performs parameter expansion automatically. Plus it handles all the command details for you, including mapping the data back into SomeType instances.

In the above example, it will expand to be equivalent to (approximately):

int ids0 = 1, ids1 = 2, ids2 = 3, ids3 = 4, ids4 = 5;
var rows = conn.Query<SomeType>(
    "select * from table where id in (@ids0,@ids1,@ids2,@ids3,@ids4)",
      new { ids0, ids1, ids2, ids3, ids4 }).ToList();

Solution 3

SQL Server has one data structure that's ideal for holding multiple values of the same "type" - and that's a table.

Thankfully, in recent years, they introduced Table-Valued Parameters so that you can construct a table in your client code and pass that across to the SQL Server in a natural way, e.g.:

// Assumes connection is an open SqlConnection.
using (connection)
{
// Create a DataTable with the modified rows.
DataTable addedCategories = CategoriesDataTable.GetChanges(
    DataRowState.Added);

// Define the INSERT-SELECT statement.
string sqlInsert = 
    "INSERT INTO dbo.Categories (CategoryID, CategoryName)"
    + " SELECT nc.CategoryID, nc.CategoryName"
    + " FROM @tvpNewCategories AS nc;"

// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand(
    sqlInsert, connection);
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue(
    "@tvpNewCategories", addedCategories);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.CategoryTableType";

// Execute the command.
insertCommand.ExecuteNonQuery();
}

Solution 4

If you declare

List<int> Ids = new List<int>();

and add there the ids you want. It's easy to convert it in a comma seperated list as follows

string listOfIds = string.Join(",", Ids.Select(Id => Id.ToString()).ToArray());

Then you pass this string as a parameter to your parameterized query.

Solution 5

which version of sql server you are using? You should try out table value parameter if you are using sql server 2008 or later

Check http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters

Pass table valued parameter using ADO.Net

View more solutions

Comments

[ta.speot.is]

This is the best way, but caveat: TVPs can suck with stored procedures due to parameter sniffing.

[Alexander]

Won't work because I don't ask SELECT * FROM table WHERE id IN ("1,2,3"), I ask SELECT * FROM table WHERE id IN (1,2,3)