Using multiple parameters in WHERE

Question

I have a list of ints called idList. I'd like to update a the TaskState in Tasks table for each id in that idList. This is how my code looks now :

using (SqlConnection dataconnection = new SqlConnection(DB))
{
    SqlCommand sqlCommandDelete = new SqlCommand()
    {
        Connection = dataconnection
    };

    dataconnection.Open();

    foreach (var id in idList)
    {
         qlCommandDelete.CommandText = $"UPDATE Tasks SET TaskState = 2 WHERE TaskID = {id}";
         sqlCommandDelete.ExecuteNonQuery();
    }
}

Now the problem is that I execute each time the loop fires. Is there maybe a way of connecting all the TaskIDs and executing only once?

For example, `$"UPDATE ... WHERE TaskID IN ({String.Join(",", idList)})"` — Evk, Nov 29 '17 at 09:38

score 2 · Accepted Answer · answered Nov 29 '17 at 09:37

2

You can do it like this

var ids = idList.Aggregate((x, y) => $"{x}, {y}")

Then write sql text as follows

qlCommandDelete.CommandText = $"UPDATE Tasks SET TaskState = 2 WHERE TaskID in ({ids})";

answered Nov 29 '17 at 09:37

Potato

397
4
16

1

+1 assuming TaskId is an Injection safe type like Int. Just use `String.Join` - saves the concatenation problem. There's [ultimately a size limit](https://stackoverflow.com/a/8808935/314291) on the query, though, so batching might still be necessary with a large number of TaskIds. For strings, [parameterization is needed](https://stackoverflow.com/a/337792/314291) – StuartLC Nov 29 '17 at 09:39

Using multiple parameters in WHERE

1 Answers1