SqlServer：带IN()子句C＃的参数化查询

目录

介绍

IN()子句帮助类

SQL查询构建

具有实体框架的参数化查询

使用SqlCommand进行参数化查询

数据

数据库，表和数据行

Db连接字符串

其他数据库

---

一个实用程序类，使用参数化查询在SQL中为IN()运算符发送参数

• 下载VS2017控制台解决方案 - 7.2 KB

介绍

使用参数化查询很简单：

1. 使用参数创建SqlCommand命令string。
2. 声明一个SqlParameter对象，根据需要分配值。
3. 将SqlParameter对象分配给SqlCommand对象的Parameters属性。

但是当我们必须处理IN()子句时，特别是对于未知数量的对象或列表，情况会有所不同。

IN()子句帮助类

该类将帮助我们创建SQL string和SQL参数：

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()将创建将添加到IN()内部的参数名称string。
• Params()将提供SQL命令的所有SqlParameter列表。
• 我们调用也传递额外的或现有SqlParameter给Params()。

SQL查询构建

/*data*/
byte isActive = 1;
List<string> emails = new List<string>()
{
    "[email protected]",
    "[email protected]"
};
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数据类型
• emails 实际的数据清单
• string 列表的数据类型
• "email"参数名称前缀，如果我们要在单个查询中使用多个IN()子句，这很重要

具有实体框架的参数化查询

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

传递额外的SqlParameters给Params()：

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

使用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();

而不是创建一个列表，传递额外的SqlParameter给 Params()。

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

数据

数据库，表和数据行

在附加的解决方案中找到DbWithData.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'[email protected]', 1, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (2, N'Tom', N'[email protected]', 2, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (3, N'Dan', N'[email protected]', 3, 1)
GO
INSERT [dbo].[Employee] ([Id], [Name], [Email], [UserType], _
                  [IsActive]) VALUES (4, N'Ban', N'[email protected]', 4, 1)
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO

Db连接字符串

根据需要更改App.config上的数据库连接：

<connectionStrings>
    <add name="UmsDbContext" connectionString="Server=L-156151377\SQLEXPRESS;
     Database=Ums;user id=sa;[email protected];Integrated Security=false;" 

     providerName="System.Data.SqlClient"/>
</connectionStrings>

其他数据库

如果我们需要对其他数据库执行相同的操作，我们只需要在以下位置引入一些修改：

• public const char ParamIndicator = '@'; /*@paramName*/
• public readonly SqlDbType DbDataType;
• Name(int index) 方法，如果需要

 

原文地址：https://www.codeproject.com/Tips/1276827/SqlServer-Parameterized-Query-With-IN-Clause-Cshar