how to write sql query text for IN clause in C#

I want to know how can I write a sql query in C# where I have to use multiple strings in the IN clause.

I have a List ListSessionId, and I want to use each of them in my IN clause.

Something like:

foreach (var sessionid in ListSessionId)
{
    query = " SELECT OM.ORDERCONFIRMATIONID AS OCN ";
    query += " FROM [DGS].[DGSCRM].[LEAD] ";
    query += " WHERE ";
    query += " SESSIONID in ('" + sessionid + "') ";
}

so as my query is something like:

SELECT OM.ORDERCONFIRMATIONID AS OCN
FROM [DGS].[DGSCRM].[LEAD]
WHERE
SESSIONID in ('sessionid1', 'sessionid2', 'sessionid3')

Answer

While you can solve this with a string .join or just iterating your loop to build the stuff between the parentheses in your IN clause, it will leave you wide open for SQL injection.

To avoid that you MUST parameterize your inputs to the SQL string. We do this by using the sql command .Parameters class.

It will look something like:

var query = "SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in ({0})"; 

var idParameterList = new List<string>();
var index = 0;
foreach (var sessionid in ListSessionId)
{
    var paramName = "@idParam" + index;
    sqlCommand.Parameters.AddWithValue(paramName, sessionid);
    idParameterList.Add(paramName);
    index++;
}
sqlCommand.CommandText = String.Format(query, string.Join(",", idParameterList));

Note that I’m totally guessing at your sqlCommand variable name, but you can swap that out as needed.

With this we are splitting our SQL string/command up into two chunks

1. The sql itself. This ends up being submitted to the database looking like SELECT OM.ORDERCONFIRMATIONID AS OCN FROM [DGS].[DGSCRM].[LEAD] SESSIONID in (@idParam0, @idParam1, @idParam3, ... @idParamN). The database gets this sql string and compiles it as if it had values in it. It determines the execution path and just sits on it waiting for the values to come through in the second part

2. The parameter’s values come in. This is the list we are building in the loop. Each value in your ListSessionId gets paired up with a new sqlCommand.Parameters which takes a name @idParam0 or @idParam1, etc.

Because your SQL is compiled without any of the actual stuff in your ListSessionID there is no way for bad SQL to show up and get executed in your database. It’s compiled and ready to execute. So when the parameters hit, it runs the plan for the sql statement and barfs back results. Nice and clean.