Create the
SqlCommand
command
string
with parameters.
Declare a
SqlParameter
object, assigning values as appropriate.
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 =
'
@'
;
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
SqlParameter
s to
Params()
.
SQL Query Build
byte
isActive =
1
;
List<string> emails =
new
List<string>()
"
Jeff@gmail.com"
,
"
Tom@gmail.com"
List<int> userTypes =
new
List<int>()
3
,
4
SqlServerInClauseParam<string> emailParam =
new
SqlServerInClauseParam<string>(SqlDbType.VarChar, emails,
"
email"
);
SqlServerInClauseParam<int> userTypeParam =
new
SqlServerInClauseParam<int>(SqlDbType.Int, userTypes,
"
userType"
);
SqlParameter isActiveParam =
new
SqlParameter(
"
isActiveParam"
, SqlDbType.Bit)
{ Value = isActive };
string
sql =
String
.Format(
@"
SELECT *
FROM Employee
WHERE Email IN ({0})
OR UserType IN ({1})
AND IsActive = @isActiveParam;"
,
emailParam.ParamsString(), userTypeParam.ParamsString()
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();
Passing additional
SqlParameters
to
Params()
:
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
SqlParameter
s to
Params()
.
DB, Table & Data Rows
Find
DbWithData.sql
inside the attached solution as below:
USE
[Ums]
SET
ANSI_NULLS
ON
SET
QUOTED_IDENTIFIER
ON
SET
ANSI_PADDING
ON
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
]
SET
ANSI_PADDING
OFF
SET
IDENTITY_INSERT
[dbo].[Employee]
ON
INSERT
[dbo].[Employee] ([Id], [Name], [Email], [UserType], _
[IsActive])
VALUES
(
1
, N
'
Jeff'
, N
'
Jeff@gmail.com'
,
1
,
1
)
INSERT
[dbo].[Employee] ([Id], [Name], [Email], [UserType], _
[IsActive])
VALUES
(
2
, N
'
Tom'
, N
'
Tom@gmail.com'
,
2
,
1
)
INSERT
[dbo].[Employee] ([Id], [Name], [Email], [UserType], _
[IsActive])
VALUES
(
3
, N
'
Dan'
, N
'
Dan@gmail.com'
,
3
,
1
)
INSERT
[dbo].[Employee] ([Id], [Name], [Email], [UserType], _
[IsActive])
VALUES
(
4
, N
'
Ban'
, N
'
Ban@gmail.com'
,
4
,
1
)
SET
IDENTITY_INSERT
[dbo].[Employee]
OFF
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.
instead of string.Format using "string interpolation"
$ - string interpolation - C# Reference | Microsoft Docs
[
^
] might be neater
Sign In
·
View Thread
Buddy, shouldn't we tell him to use a
StringBuilder
instead of many
String.Format
calls and string concatenations?
Sign In
·
View Thread
It might be important for performance. If you are not going to repeat it more than 1k, it does not affect performance that much.
My consideration here is not performance but readability
Sign In
·
View Thread
