How to pass sqlparameter to IN()? [duplicate]

Question

For some reason the Sqlparameter for my IN() clause is not working. The code compiles fine, and the query works if I substitute the parameter with the actual values

StringBuilder sb = new StringBuilder();             foreach (User user in UserList)             {                 sb.Append(user.UserId + ",");             }              string userIds = sb.ToString();             userIds = userIds.TrimEnd(new char[] { ',' });   SELECT userId, username  FROM Users  WHERE userId IN (@UserIds)  

Answer 1

You have to create one parameter for each value that you want in the IN clause.

The SQL needs to look like this:

SELECT userId, username  FROM Users  WHERE userId IN (@UserId1, @UserId2, @UserId3, ...)  

So you need to create the parameters and the IN clause in the foreach loop.
Something like this (out of my head, untested):

StringBuilder sb = new StringBuilder(); int i = 1;  foreach (User user in UserList) {     // IN clause     sb.Append("@UserId" + i.ToString() + ",");      // parameter     YourCommand.Parameters.AddWithValue("@UserId" + i.ToString(), user.UserId);      i++; } 

Answer 2

Possible "cleaner" version:

StringBuilder B = new StringBuilder(); for (int i = 0; i < UserList.Count; i++)      YourCommand.Parameters.AddWithValue($"@UserId{i}", UserList[i].UserId); B.Append(String.Join(",", YourCommand.Parameters.Select(x => x.Name)));