SqlServer中WHERE IN和LIKE的参数化查询方案：XML与DataTable传参

使用XML类型参数实现Where In

在使用XML参数前，需了解XQuery语言及其相关支持函数。XQuery用于从XML文档中提取元素及属性，XPath是其子集，两者共享相同的数据模型和函数运算符。
主要涉及SQL Server的XML支持函数包括：query()、nodes()、exist()、value()和modify()。

在XML传参实现WHERE IN时，推荐使用exist()方法而非value()方法，因为前者在谓词比较中性能更优。以下是具体实现逻辑：

第1步：使用Exist方法（推荐高性能方案）

目的：通过XML节点内容与数据库列进行匹配，获取最佳执行性能。

核心代码：

// XML数据结构：
// <root>
//   <UserID>1</UserID>
//   <UserID>2</UserID>
//   <UserID>5</UserID>
// </root>

SqlCommand comm = conn.CreateCommand();
// 使用sql:column()引用当前表中的UserID列进行比较
comm.CommandText = @"select * from Users where @xml.exist('/root/UserID[text()=sql:column(""UserID"")]')=1";
comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xmlString });

注意事项：
1. XQuery表达式严格区分大小写，书写时需保持一致。
2. sql:column()中的列名必须使用双引号（如"UserID"），若使用单引号需转义为连续两个单引号（''UserID''）。

第2步：嵌套结构XML的处理

当XML结构包含多层节点（如User节点下包含UserID）时，路径需相应调整。

代码示例：

// 目标XML：/root/User/UserID
comm.CommandText = @"select * from Users where @xml.exist('/root/User[UserID=sql:column(""UserID"")]')=1";

若使用Value方法提取列表，语句如下（性能略低，仅作对比）：

comm.CommandText = @"SELECT * FROM Users WHERE UserID IN (
  SELECT T.c.value('UserID[1]','int') FROM @xml.nodes('/root/User') AS T(c)
)";

第3步：其他常见XML结构的查询适配

根据实际业务传入的XML格式，灵活修改nodes()中的XPath路径及exist()的谓词部分。始终建议优先使用exist()以利用其短路评估特性提升性能。

使用Table-Valued Parameters (TVP) 实现DataTable传参

Table-Valued Parameters (TVP) 自SQL Server 2008起支持，适合批量数据传递。当参数数量小于1000且对性能有较高要求时，TVP表现出色。

第4步：创建用户定义表类型

首先在数据库端定义表结构，作为C#端DataTable映射的依据。

CREATE TYPE IntCollectionTVP AS TABLE(ID int)
GO

第5步：C#端构建DataTable并调用

目的：将内存中的DataTable对象作为结构化参数传递给存储过程或SQL语句。

操作步骤：
1. 构造与定义类型列数一致的DataTable。
2. 添加SqlParameter，设置SqlDbType.Structured并指定TypeName。

DataTable tvpDt = new DataTable();
tvpDt.Columns.Add("myid", typeof(int)); // 列名可自定义，但类型需一致
tvpDt.Rows.Add(1);
tvpDt.Rows.Add(2);
tvpDt.Rows.Add(3);

SqlCommand comm = conn.CreateCommand();
comm.CommandText = @"SELECT * FROM Users(nolock) WHERE EXISTS (
  SELECT 1 FROM @MyTvp tvp WHERE tvp.ID=Users.UserID
)";

// 关键配置：TypeNames对应数据库中的类型名
SqlParameter param = new SqlParameter("@MyTvp", SqlDbType.Structured);
param.TypeName = "IntCollectionTVP";
param.Value = tvpDt;
comm.Parameters.Add(param);

第6步：参数配置关键点解析

• 列名无关性：DataTable的列名无需与TVP定义的列名完全一致，但数据类型必须隐式兼容，建议保持一致以避免转换开销。
• 类型一致性：查询条件字段类型应与TVP字段类型一致，防止隐式转换导致索引失效或性能下降。

总结与方案选择

针对SQL Server中WHERE IN和LIKE的参数化需求，常见的实现手段包括字符串拼接（不推荐）、动态SQL、临时表、XML参数及TVP。综合性能与维护性考量：

• TVP方案：适合Sql Server 2008及以上环境，强类型约束好，大数据量传输效率高，是结构化集合参数的首选。
• XML方案：适合需要灵活解析复杂XML结构的场景，但要求开发者熟悉XQuery语法，注意大小写敏感问题。

合理选择上述方案，可有效兼顾系统安全与查询性能。

以上就是SqlServer中WHERE IN和LIKE的参数化查询方案的详细内容，更多关于Sql Server参数化传参技巧的资料请关注本站其它相关文章！