Parameterized SQL WHERE IN clause C#

Jan 28, 2016 # Extension Method, Parameters, SQL, WHERE IN, C#

If you are using some legacy SQL in C# you’ll occasionally need to write a WHERE IN. You don’t want to end up writing something like the below, either directly or through some sort of loop.

1string cmdText = "SELECT *  FROM Members WHERE MemberId IN (100, 200, 300, 400);
2SqlCommand cmd = new SqlCommand(cmdText);

Instead, you should parameterize the sql. This involves two steps.

1. Add parameters to the sql string.
2. Add the parameters to `SqlCommand`.

Add a class to your project like SqlWhereInParamBuilder, I chose to use a static method and an extension method, but you can do it in any number of other ways.

 1using System.Collections.Generic;
 2using System.Data.SqlClient;
 3using System.Linq;
 4
 5namespace SqlWhereInParameters
 6{
 7    public static class SqlWhereInParamBuilder
 8    {
 9        public static string BuildWhereInClause<T>(string partialClause, string paramPrefix, IEnumerable<T> parameters)
10        {
11            string[] parameterNames = parameters.Select(
12                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
13                .ToArray();
14
15            string inClause = string.Join(",", parameterNames);
16            string whereInClause = string.Format(partialClause.Trim(), inClause);
17            
18            return whereInClause;
19        }
20
21        public static void AddParamsToCommand<T>(this SqlCommand cmd, string paramPrefix, IEnumerable<T> parameters)
22        {
23            string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
24
25            string[] parameterNames = parameterValues.Select(
26                (paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
27                ).ToArray();
28
29            for (int i = 0; i < parameterNames.Length; i++)
30            {
31                cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
32            }
33        }
34    }
35}

This is the usage

 1using System.Collections.Generic;
 2using System.Data.SqlClient;
 3
 4namespace SqlWhereInParameters
 5{
 6    class Program
 7    {
 8        static void Main(string[] args)
 9        {
10            Program p = new Program();
11            p.RunCommandBulderForFullSelectExample();
12        }
13
14        private void RunCommandBulderForFullSelectExample()
15        {
16            List<long> memberIds = new List<long> { 100, 200, 300, 400 };
17            string parameterPrefix = "MemberId";
18            string cmdText = "SELECT *  FROM Member WHERE Name IN ({0})";
19
20            cmdText = SqlWhereInParamBuilder.BuildWhereInClause(cmdText, parameterPrefix, memberIds);
21
22            SqlCommand cmd = new SqlCommand(cmdText);
23            cmd.AddParamsToCommand(parameterPrefix, memberIds);
24        }
25    }
26}

Extension Method Parameters SQL WHERE IN C#

Parameterized SQL WHERE IN clause C#

Related