Multiple Id's in In clause of SQL Query C# [closed]

Solution 1:

The correct way to send user input to the database is using parameters. The IN operator often confuses inexperienced developers since a lot of them try to use it with a single parameter that contains comma delimited values and expect it to return results. However that is a mistake since the IN operator expect a list of values, not a single value containing a list.

So, to parameterize a query for the IN operator what you need to do is break down the comma separated string in your code and provide the query with a parameter for each value.

Here is a basic example:

var userInput = "1,2,3,4,5,6";
var values = userInput.Split(',');

using(var command = new OdbcCommand())
{
    var sql = "SELECT * FROM table where id IN(";

    for(int i=0; i < values.Length; i++) {
        sql = $"{sql} @{i},";
        command.Parameters.Add($"@{i}", OdbcType.Int).Value = values[i];
    }

    command.CommandText = sql.TrimEnd(',') +");";
    command.Connection = con;
    using(var reader = Command.ExecuteReader())
    {
        while(reader.Read())
        {
            // do your stuff with the data
        }
    }
}