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

最新推荐文章于 2024-08-20 14:34:43 发布

寒冰屋

最新推荐文章于 2024-08-20 14:34:43 发布

阅读量8.1k

点赞数 2

分类专栏：数据库 CSharp.NET 文章标签： SqlServer IN()

CSharp.NET 同时被 2 个专栏收录

1259 篇文章 61 订阅

订阅专栏

数据库

229 篇文章 14 订阅

订阅专栏

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

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

介绍

使用参数化查询很简单：

使用参数创建SqlCommand命令string。
声明一个SqlParameter对象，根据需要分配值。
将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>()
{
    "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数据类型
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'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连接字符串

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

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