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:

var sqlCommand = new SqlCommand();

var sql = "SELECT * FROM TBL_USER_MASTER WHERE USER_NAME = @parameter";
sqlCommand.CommandText = sql;

// Add The Parameter
sqlCommand.Parameters.AddWithValue("parameter", "sebastian");

// Execute the query and do something with it
var reader = sqlCommand.ExecuteReader();

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):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CSharpSandBox
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnection"].ConnectionString))
            {
                connection.Open();

                var sqlCommand = new SqlCommand();
                sqlCommand.Connection = connection;
                sqlCommand.CommandType = CommandType.Text;

                var sql = "SELECT * FROM TBL_USER_MASTER WHERE USER_ID IN ({0}) OR USER_NAME IN ({1})";

                var idList = new List<int> { 100, 50, 40, 20, 10 };
                var idParameterList = new List<string>();
                var index = 0;
                foreach (var id in idList)
                {
                    var paramName = "@idParam" + index;
                    sqlCommand.Parameters.AddWithValue(paramName, id);
                    idParameterList.Add(paramName);
                    index++;
                }

                var nameList = new List<string> { "sebastian", "kuuro", "svenbit" };
                var nameParameter = new List<string>();
                index = 0; // Reset the index
                foreach (var name in nameList)
                {
                    var paramName = "@nameParam" + index;
                    sqlCommand.Parameters.AddWithValue(paramName, name);
                    nameParameter.Add(paramName);
                    index++;
                }

                sqlCommand.CommandText = string.Format(sql, string.Join(",", idParameterList), string.Join(",", nameParameter));

                using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
                {
                    var resultTable = new DataTable();
                    resultTable.Load(sqlReader);

                    // Do something with the result table
                }

                connection.Close();
            }
        }
    }
}

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

SELECT * FROM TBL_USER_MASTER
WHERE USER_ID IN (@idParam0,@idParam1,@idParam2,@idParam3,@idParam4)
OR
USER_NAME IN (@nameParam0,@nameParam1,@nameParam2)

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.