If you are using some legacy SQL in C# you’ll occasionally need to write a WHERE IN.
Yon don’t want to end up writing something like the below, either directly or through some sort of loop.
string cmdText = "SELECT * FROM Members WHERE MemberId IN (100, 200, 300, 400);
SqlCommand cmd = new SqlCommand(cmdText);
Instead you should parameterize the sql. This involves two steps.
- Add parameters to the sql string.
- 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.
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
namespace SqlWhereInParameters
{
public static class SqlWhereInParamBuilder
{
public static string BuildWhereInClause<T>(string partialClause, string paramPrefix, IEnumerable<T> parameters)
{
string[] parameterNames = parameters.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString())
.ToArray();
string inClause = string.Join(",", parameterNames);
string whereInClause = string.Format(partialClause.Trim(), inClause);
return whereInClause;
}
public static void AddParamsToCommand<T>(this SqlCommand cmd, string paramPrefix, IEnumerable<T> parameters)
{
string[] parameterValues = parameters.Select((paramText) => paramText.ToString()).ToArray();
string[] parameterNames = parameterValues.Select(
(paramText, paramNumber) => "@" + paramPrefix + paramNumber.ToString()
).ToArray();
for (int i = 0; i < parameterNames.Length; i++)
{
cmd.Parameters.AddWithValue(parameterNames[i], parameterValues[i]);
}
}
}
}
This is the usage
using System.Collections.Generic;
using System.Data.SqlClient;
namespace SqlWhereInParameters
{
class Program
{
static void Main(string[] args)
{
Program p = new Program();
p.RunCommandBulderForFullSelectExample();
}
private void RunCommandBulderForFullSelectExample()
{
List<long> memberIds = new List<long> { 100, 200, 300, 400 };
string parameterPrefix = "MemberId";
string cmdText = "SELECT * FROM Member WHERE Name IN ({0})";
cmdText = SqlWhereInParamBuilder.BuildWhereInClause(cmdText, parameterPrefix, memberIds);
SqlCommand cmd = new SqlCommand(cmdText);
cmd.AddParamsToCommand(parameterPrefix, memberIds);
}
}
}