Click here to Skip to main content
15,119,954 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a list of prduct ID's in List PrductID;
which i want to use in c# query formation in IN clause.

What I have tried:

i can convernt that list into comma seperated values in the form of single string and use in query like below.
"SELECT * from Emp where empID in ("+strProductID+")";
and with DataReader i can execute the query but again i can't user this 'strProductID' directly in query which is not secure...
so i want to convert each value of list into sqlparameter so that it will be secure from sqlinjection.
Updated 29-Jul-18 11:00am
Gerry Schmitz 29-Jul-18 7:44am
Where did you get your "list of product ID's"?
Mike V Baker 29-Jul-18 12:44pm
Create a SP in the sql server database that takes the list of products id's in a single param?

1 solution

There are a few possible answers to your question. If the list of product IDs are coming from a database, you're best bet is to change your query to either use a JOIN or an EXISTS with a sub-select (using whatever WHERE clause limits the product IDs to the desired ones).

If the list can be long, and is not coming from a database, I would suggest using a stored procedure with a table valued parameter and then pursue the same technique suggested above. For more information on table valued parameters, see:

Use Table-Valued Parameters (Database Engine) | Microsoft Docs[^]

While generally not a good approach, if you know your list will be short and merely want to construct a command with parameters (to avoid SQL injection), something like the following should work:

var builder = new StringBuilder(1024);
int count = list.Count;

builder.Append("SELECT * from Emp where empID in (");
for (int index = 1; index <= count; index++)
  if (index > 1)


using (var command = new SqlCommand(builder.ToString(), connection))
  SqlParameterCollection parameters = command.Parameters;
  for (int index = 1; index <= count; index++)
    parameters.AddWithValue($"p{index}", list[index]);

  // Do what you want with the command

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900