Sql Server參數化查詢之where in和like實現之xml和DataTable傳參

在上一篇Sql Server參數化查詢之where in和like實現詳解中介紹了在Sql Server使用參數化查詢where in的幾種實現方案，遺漏了xml和表值參數，這里做一個補充文章導讀方案5使用xml參數方案6 使用表值參數TVP 6種實現方案總結 ?方案5 使用xml參數對sql server xml類型參數不熟悉的童鞋需要先了解下XQuery概念，這里簡單提下XQuery 是用來從 XML 文檔查找和提取元素及屬性的語言，簡單說就是用于查詢xml的語言說到這就會牽著到XPath，其實XPath是XQuery的一個子集，XQuery 1.0 和 XPath 2.0 共享相同的數據模型，并支持相同的函數和運算符，XPath的方法均適用于XQuery，假如您已經學習了 XPath，那么學習 XQuery 也不會有問題。詳見http://www.w3school.com.cn/xquery/xquery_intro.asp XQuery概念了解后需要進一步了解下Sql Server對xml的支持函數，主要為query()

在上一篇Sql Server參數化查詢之where in和like實現詳解中介紹了在Sql Server使用參數化查詢where in的幾種實現方案，遺漏了xml和表值參數，這里做一個補充

文章導讀

方案5使用xml參數

方案6 使用表值參數TVP，DataTable傳參

6種實現方案總結

方案5 使用xml參數

對sql server xml類型參數不熟悉的童鞋需要先了解下XQuery概念，這里簡單提下XQuery 是用來從 XML 文檔查找和提取元素及屬性的語言，簡單說就是用于查詢xml的語言說到這就會牽著到XPath，其實XPath是XQuery的一個子集，XQuery 1.0 和 XPath 2.0 共享相同的數據模型，并支持相同的函數和運算符，XPath的方法均適用于XQuery，假如您已經學習了 XPath，那么學習 XQuery 也不會有問題。詳見http://www.w3school.com.cn/xquery/xquery_intro.asp

XQuery概念了解后需要進一步了解下Sql Server對xml的支持函數，主要為query()、nodes()、exist()、value()、modify() ，詳見http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

使用xml方式實現where in時有兩種實現方式，使用value和exist，在這里推薦使用exist方法，msdn是這樣描述的：

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

由于性能原因，不在謂詞中使用 value() 方法與關系值進行比較，而改用具有 sql:column() 的 exist()。

http://msdn.microsoft.com/zh-cn/library/ms178030.aspx

使用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可以參考 http://msdn.microsoft.com/en-us/library/bb510489.aspx

這里主要介紹如何使用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種在Sql Server參數化查詢之where in和like實現詳解一文中進行了列舉和示例

6種方法，6種思路，

其中方法1 等于完全棄用了索引，若無特殊需要不建議采用，

方法2 本質上合拼SQL沒啥區別與其用方法2自欺其人還不如直接拼接SQL來的實惠

方法3 受參數個數(做多2100個參數)限制，而且若傳的參數過多性能如何有待驗證，可以酌情使用

方法4 示例中采用的臨時表，其實可以換成表變量性能也許會更好些，不過寫法上有些繁瑣，可以具體的封裝成一個函數會好些(推薦)

方法5 使用xml傳參，既然有這種類型說明性能上應該還不錯，其它會比拼接SQL好很多，使用上也還比較方便，不過需要開發人員對xml查詢有一定了解才行(推薦)

方法6 tvp方式sql server2008以后才可以使用，很好很強大，若只為where in 的話可以定義幾個tvp where in問題就很容易解決了，而且是強類型也更容易理解(推薦)

不好去評論具體那種方法最好，還是那句老話合適的最好。

posted @ 2012-04-29 09:59 懶惰的肥兔閱讀(11479) 評論(6) 收藏舉報

刷新頁面返回頂部

Sql Server參數化查詢之where in和like實現之xml和DataTable傳參

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

公告