SqlParameter and IN statement [duplicate]

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

Answer 1

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

Answer 2

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();
}

Answer 3

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.