【转】Sql Server参数化查询之where in和like实现之xml和DataTable传参

时间：2022-03-14 04:33

转载至：

在上一篇中介绍了在Sql Server使用参数化查询where in的几种实现方案，遗漏了xml和表值参数，这里做一个补充

文章导读

，DataTable传参

方案5 使用xml参数

对sql server xml类型参数不熟悉的童鞋需要先了解下XQuery概念，这里简单提下XQuery 是用来从 XML 文档查找和提取元素及属性的语言，简单说就是用于查询xml的语言说到这就会牵着到XPath，其实XPath是XQuery的一个子集，XQuery 1.0 和 XPath 2.0 共享相同的数据模型，并支持相同的函数和运算符，XPath的方法均适用于XQuery，假如您已经学习了 XPath，那么学习 XQuery 也不会有问题。详见

XQuery概念了解后需要进一步了解下Sql Server对xml的支持函数，主要为、、、、，详见

使用xml方式实现where in时有两种实现方式，使用value和exist，在这里推荐使用exist方法，msdn是这样描述的：

D.使用 exist() 方法而不使用 value() 方法

由于性能原因，不在谓词中使用 value() 方法与关系值进行比较，而改用具有 sql:column() 的 exist()。

使用xml的value方法实现(不推荐)

DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <UserID>1</UserID>
            <UserID>2</UserID>
            <UserID>5</UserID>
        </root>";
    SqlCommand comm = conn.CreateCommand();
    //不推荐使用value方法实现，性能相对exist要低
    comm.CommandText = @"select * from Users
            where exists
            (
                select 1 from @xml.nodes(‘/root/UserID‘) as T(c)
                where T.c.value(‘text()[1]‘,‘int‘)= Users.UserID
            )";

    //也可以这样写，结果是一样的
    //comm.CommandText = @"select * from Users
    //                    where UserID in
    //                    (
    //                        select T.c.value(‘text()[1]‘,‘int‘) from @xml.nodes(‘/root/UserID‘) as T(c)
    //                    )
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}

使用xml的exist方法实现(推荐)

DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <UserID>1</UserID>
            <UserID>2</UserID>
            <UserID>5</UserID>
        </root>";
    SqlCommand comm = conn.CreateCommand();
    
    //使用xml的exist方法实现这样能够获得较高的性能
    comm.CommandText = @"select * from Users where @xml.exist(‘/root/UserID[text()=sql:column(""UserID"")]‘)=1";    
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}

列举下不同xml结构的查询方法示例，在实际使用中经常因为不同的xml结构经常伤透了脑筋

DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <User>
                <UserID>1</UserID>
            </User>
            <User>
                <UserID>2</UserID>
            </User>    
            <User>
                <UserID>5</UserID>
            </User>    
        </root>";
    SqlCommand comm = conn.CreateCommand();
   
    //不推荐使用value方法实现，性能相对exist要低
    comm.CommandText = @"select * from Users
            where UserID in
            (
                select T.c.value(‘UserID[1]‘,‘int‘) from @xml.nodes(‘/root/User‘) as T(c)
            )";                   
    //也可以这样写，结果是一样的
    //comm.CommandText = @"select * from Users
    //                    where exists
    //                    (
    //                        select 1 from @xml.nodes(‘/root/User‘) as T(c)
    //                        where T.c.value(‘UserID[1]‘,‘int‘) = Users.UserID
    //                    )";
    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}

DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    string xml = @"
        <root>
            <User>
                <UserID>1</UserID>
            </User>
            <User>
                <UserID>2</UserID>
            </User>    
            <User>
                <UserID>5</UserID>
            </User>    
        </root>";
    SqlCommand comm = conn.CreateCommand();
    //使用xml的exist方法实现这样能够获得较高的性能
    comm.CommandText = @"select * from Users where @xml.exist(‘/root/User[UserID=sql:column(""UserID"")]‘)=1";

    comm.Parameters.Add(new SqlParameter("@xml", SqlDbType.Xml) { Value = xml });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(dt);
    }
}

使用xml参数时需要注意点:

　　1.不同于SQL语句默认不区分大小写，xml的XQuery表达式是严格区分大小写的，所以书写时一定注意大小写问题

　　2.使用exist时sql:column() 中的列名须使用双引号，如sql:column("UserID")，若非要使用单引号需要连续输入两个单引号 sql:column(‘‘UserID‘‘)

　　3.不管是where in或是其他情况下使用xml查询时能用exist(看清楚了不是sql里的exists)方法就用exist方法，我们不去刻意追求性能的优化，但能顺手为之的话何乐而不为呢。

方案6 使用表值参数(Table-Valued Parameters 简称TVP Sql Server2008开始支持)

按照msdn描述TVP参数在数据量小于1000时有着很出色的性能，关于TVP可以参考

这里主要介绍如何使用TVP实现DataTable集合传参实现where in

1.使用表值参数，首先在数据库创建表值函数

create type IntCollectionTVP as Table(ID int)

2.表值函数创建好后进行c#调用，

注意点：

　　1.需要SqlParameter中的SqlDbType设置为SqlDbType.Structured然后需要设置TypeName为在数据库中创建的表值函数名，本示例中为IntCollectionTVP

　　2.构造的DataTabel列数必须和表值函数定义的一样，具体列名随意，无需和表值函数定义的列名一致，数据类型可以随意，但还是建议和表值类型定义的保持一致，一来省去隐式类型转换，二来可以在初始化DataTabel时就将不合法的参数过滤掉

　　3.建议定义tvp的时候最好查询条件里的类型和tvp对应字段类型保持一致，这样可以避免隐式类型转换带来的性能损失

DataTable resultDt = new DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
    SqlCommand comm = conn.CreateCommand();
    comm.CommandText = @"select  * from Users(nolock)
            where exists
            (
                select 1 from @MyTvp tvp  
                where tvp.ID=Users.UserID
            )";

    //构造需要传参的TVP DataTable
    DataTable tvpDt = new DataTable();
    //为表添加列，列数需要和表值函数IntCollectionTVP保值一致，列名可以不一样
    tvpDt.Columns.Add("myid", typeof(int));
    //添加数据
    tvpDt.Rows.Add(1);
    tvpDt.Rows.Add(2);
    tvpDt.Rows.Add(3);
    tvpDt.Rows.Add(4);     
    //这里的TypeName对应我们定义的表值函数名
    comm.Parameters.Add(new SqlParameter("@MyTvp", SqlDbType.Structured) { Value = tvpDt, TypeName = "IntCollectionTVP" });
    using (SqlDataAdapter adapter = new SqlDataAdapter(comm))
    {
    adapter.SelectCommand = comm;
    adapter.Fill(resultDt);
    }
}

总结：

至此，一共总结了6六种where参数化实现，分别如下

1.使用CHARINDEX或like实现where in 参数化

2.使用exec动态执行SQl实现where in 参数化

3.为每一个参数生成一个参数实现where in 参数化

4.使用临时表实现where in 参数化

5.使用xml参数实现where in 参数化

6.使用表值参数(TVP)实现where in 参数化

其中前4种在一文中进行了列举和示例

6种方法，6种思路，

其中方法1 等于完全弃用了索引，若无特殊需要不建议采用，

方法2 本质上合拼SQL没啥区别与其用方法2自欺其人还不如直接拼接SQL来的实惠