Coding Kuuro C#

Using SqlParameter with SQL’s IN Clause in C#

Using SqlParameter in SqlCommand is a great way to pass variable into SQL statement and prevent SQL Injection, it is rather simple to implement as well, basically all you  need to do is to declare the parameter name in your SQL statement or stored procedure and make sure that you add the parameter with the specified parameter name into the SQL command parameters. Here’s an example of basic implementation of SqlParameter:

That is rather easy and simple, isn’t it? But what if you want to use the SqlParameter in conjunction with the IN clause? For an example, you have a list of strings or an array of string which you are going to include in an IN clause as a condition, how to do it with SqlParameter? Well, you can not straight away pass the list of strings or the string array into the value of the SqlParameter as it will cause an exception.

However the approach is rather similar, instead of passing the entire list or array of string as the SqlParameter value you will need to define individual parameter for each string and fortunately you do not have to define each parameter one by one as you can just loop through the list of strings that you have and add them into the parameter list. The code snippet below will show you an example how to do it (full example):

Where the method used in the code snippet above will produce the following SQL statement:

And if you notice you can use the method above not only for string list, but also works with other data types (e.q int). You can even simplify the method above using LINQ, but I’m not going to show you how to do it here as the purpose is just to show you how to implement SqlParameter with IN clause.

  • Pam Flores

    Thanks a lot 😀

  • Knútur Óli Magnússon

    Code works but code analysis still complains. CA2100 Review SQL queries for security vulnerabilities

    • Could be false positive as we are still passing the parameter via SqlParameter and not concatenating the input/parameter directly into the string. However you might want to explicitly specify the data type of the parameter instead of using AddWithValue() as the parameter will be passed as NVARCHAR data type when you use AddWithValue().

  • Rama

    Thank you!!

  • superman626

    Oracle doesnt accept more then 1000 parameter, will this work with “where in” clause that have more 1000 parameters???

    • It won’t work if the list contains more than 1000 parameters due to oracle limit, maybe you can breakdown the list first if it contain more than 1000 items, hence it will be divided into multiple IN clause, I think it will work.