SqlServer: Parameterized Query With IN() Clause C#

• Download VS2017 console solution - 7.2 KB

Introduction

Using parameterized queries is simple:

1. Create the SqlCommand command string with parameters.
2. Declare a SqlParameter object, assigning values as appropriate.
3. Assign the SqlParameter object to the SqlCommand object’s Parameters property.

But things get different when we have to work with IN() clause especially with an unknown number of objects or a list.

IN() Clause Helper

This class will help us to create both SQL string and SQL parameters:

public class SqlServerInClauseParam<T>
{
    public const char ParamIndicator = '@';     /*@paramName*/
    public readonly string Prefix;
    public const string Suffix = "Param";

    public readonly SqlDbType DbDataType;
    public readonly List<T> Data;

    public SqlServerInClauseParam(SqlDbType dataType, List<T> data, string prefix = "")
    {
        Prefix = prefix;
        DbDataType = dataType;
        Data = data;
    }

    private string Name(int index)
    {
        var name = String.Format("{0}{1}{2}", Prefix, index, Suffix);
        return name;
    }

    public string ParamsString()
    {
        string listString = "";
        for (int i = 0; i < Data.Count; i++)
        {
            if (!String.IsNullOrEmpty(listString))
            {
                listString += ", ";
            }
            listString += String.Format("{0}{1}", ParamIndicator, Name(i));
        }
        return listString;
    }

    private List<SqlParameter> ParamList()
    {
        var paramList = new List<SqlParameter>();
        for (int i = 0; i < Data.Count; i++)
        {
            var data = new SqlParameter 
                       { ParameterName = Name(i), SqlDbType = DbDataType, Value = Data[i] };
            paramList.Add(data);
        }
        return paramList;
    }

    public SqlParameter[] Params()
    {
        var paramList = ParamList();
        return paramList.ToArray();
    }

    public SqlParameter[] Params(params SqlParameter[] additionalParameters)
    {
        var paramList = ParamList();
        foreach (var param in additionalParameters)
        {
            paramList.Add(param);
        }
        return paramList.ToArray();
    }
}

• ParamsString() will create parameter names string which will be added inside IN().
• Params() will provide all the SqlParameter list for SQL command.
• We call also pass additional or existing SqlParameters to Params().

SQL Query Build

/*data*/
byte isActive = 1;
List<string> emails = new List<string>()
{
    "Jeff@gmail.com",
    "Tom@gmail.com"
};
List<int> userTypes = new List<int>()
{
    3, 4
};

/*IN() params*/
SqlServerInClauseParam<string> emailParam = 
  new SqlServerInClauseParam<string>(SqlDbType.VarChar, emails, "email");  /*IN() clause param*/
SqlServerInClauseParam<int> userTypeParam = 
  new SqlServerInClauseParam<int>(SqlDbType.Int, userTypes, "userType");   /*IN() clause param*/
/*regular param*/
SqlParameter isActiveParam = new SqlParameter("isActiveParam", SqlDbType.Bit) 
                            { Value = isActive };                    /*regular param*/

/*sql*/
string sql = String.Format(@"
SELECT * 
FROM Employee 
    WHERE Email IN ({0})
    OR UserType IN ({1})
    AND IsActive = @isActiveParam;",
emailParam.ParamsString(), userTypeParam.ParamsString()        /*using IN() clause param class*/
);

new SqlServerInClauseParam<string>(SqlDbType.VarChar, emails, "email");

• SqlDbType.VarChar SQL data type
• emails the actual data list
• string data type of the list
• "email" parameter name prefix, important if we are going to use multiple IN() clause in a single query

Parameterized Query With Entity Framework

List<SqlParameter> paramList = new List<SqlParameter>();
paramList.AddRange(emailParam.Params());
paramList.AddRange(userTypeParam.Params());
paramList.Add(isActiveParam);
var db = new UmsSqlDbContext();
List<Employee> list = db.Database.SqlQuery<Employee>
                      (sql, paramList.ToArray()).ToList();     /*paramList.ToArray() is important*/

Passing additional SqlParameters to Params():

/*we can also do*/ 
//List<Employee> list = db.Database.SqlQuery<Employee>
//            (sql, emailParam.Params(userTypeParam.Params(isActiveParam))).ToList();

Parameterized Query With SqlCommand

SqlConnection connection = new SqlConnection
    (ConfigurationManager.ConnectionStrings["UmsDbContext"].ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddRange(emailParam.Params());
command.Parameters.AddRange(userTypeParam.Params());
command.Parameters.Add(isActiveParam);
var reader = command.ExecuteReader();

List<Employee> list = new List<Employee>();
while (reader.Read())
{
    list.Add(new Employee
    {
        Id = Convert.ToInt32(reader["Id"]),
        Name = reader["Name"].ToString(),
        Email = reader["Email"].ToString(),
        UserType = Convert.ToInt32(reader["UserType"]),
        IsActive = Convert.ToBoolean(reader["IsActive"])
    });
}
connection.Close();

Rather than creating a list, passing additional SqlParameters to Params().

/*we can also do*/ 
//command.Parameters.AddRange(emailParam.Params(userTypeParam.Params(isActiveParam)));

Data

DB, Table & Data Rows

Find DbWithData.sql inside the attached solution as below:

USE [Ums]
GO
/****** Object:  Table [dbo].[Employee]    Script Date: 2/10/2019 1:01:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](100) NULL,
    [Email] [varchar](100) NULL,
    [UserType] [int] NULL,
    [IsActive] [bit] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
                   ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 

GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (1, N'Jeff', N'Jeff@gmail.com', 1, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (2, N'Tom', N'Tom@gmail.com', 2, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (3, N'Dan', N'Dan@gmail.com', 3, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (4, N'Ban', N'Ban@gmail.com', 4, 1)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Db Connection String

Change the DB connection at App.config as needed:

<connectionStrings>
    <add name="UmsDbContext" connectionString="Server=L-156151377\SQLEXPRESS;
     Database=Ums;user id=sa;password=pro@123;Integrated Security=false;" 
     providerName="System.Data.SqlClient"/>
</connectionStrings>

Other Databases

If we need to do the same for other databases, we only have to introduce a few modifications at:

• public const char ParamIndicator = '@'; /*@paramName*/
• public readonly SqlDbType DbDataType;
• Name(int index) method if needed

About the Download File

Find working VS2017 console solution as the attachment. Create Db and change the connection string.