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

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) 

Solution 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++;
}

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