Roy">
Click here to Skip to main content
15,064,485 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 14 Feb 2019

Stats

18.9K views
98 downloads
9 bookmarked

SqlServer: Parameterized Query With IN() Clause C#

Rate me:
Please Sign up or sign in to vote.
4.67/5 (3 votes)
14 Feb 2019CPOL1 min read
A utility class to send parameters for IN() operator in SQL using parameterized queries

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:

C#
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

C#
/*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

C#
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():

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

Parameterized Query With SqlCommand

C#
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().

C#
/*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:

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

XML
<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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DiponRoy
Bangladesh Bangladesh
No Biography provided

Comments and Discussions

 
Questionstring.Format Pin
canbax15-Feb-19 4:57
Membercanbax15-Feb-19 4:57 
AnswerRe: string.Format Pin
wmjordan17-Feb-19 20:13
professionalwmjordan17-Feb-19 20:13 
Buddy, shouldn't we tell him to use a StringBuilder instead of many String.Format calls and string concatenations?
GeneralRe: string.Format Pin
canbax19-Feb-19 18:38
Membercanbax19-Feb-19 18:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.