SqlServer參數化查詢之where in和like實現之xml和DataTable傳參介紹

方案5 使用xml參數

對sql server xml型別參數不熟悉的童鞋需要先瞭解下XQuery概念，這裡簡單提下XQuery 是用來從 XML 文檔尋找和提取元素及屬性的語言，簡單說就是用於查詢xml的語言說到這就會牽著到XPath，其實XPath是XQuery的一個子集，XQuery 1.0 和 XPath 2.0 共用相同的資料模型，並支援相同的函數和運算子，XPath的方法均適用於XQuery，假如您已經學習了 XPath，那麼學習 XQuery 也不會有問題。詳見http://www.jb51.net/w3school/xquery/xquery_intro.htm

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問題就很容易解決了，而且是強型別也更容易理解(推薦)
不好去評論具體那種方法最好，還是那句老話合適的最好。

此文章屬懶惰的肥兔原創