SqlServer參數(shù)化查詢之where in和like實現(xiàn)之xml和DataTable傳參介紹

更新時間：2012年05月21日 14:03:46 作者：

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

方案5 使用xml參數(shù)

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

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

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

D.使用 exist() 方法而不使用 value() 方法
由于性能原因，不在謂詞中使用 value() 方法與關(guān)系值進行比較，而改用具有 sql:column() 的 exist()。
http://msdn.microsoft.com/zh-cn/library/ms178030.aspx

使用xml的value方法實現(xiàn)(不推薦)

復制代碼代碼如下:

 
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方法實現(xiàn)，性能相對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 
)"; 

//也可以這樣寫，結(jié)果是一樣的 
//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方法實現(xiàn)(推薦)

復制代碼代碼如下:

 
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方法實現(xiàn)這樣能夠獲得較高的性能 
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結(jié)構(gòu)的查詢方法示例，在實際使用中經(jīng)常因為不同的xml結(jié)構(gòu)經(jīng)常傷透了腦筋

復制代碼代碼如下:

 
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方法實現(xiàn)，性能相對exist要低 
comm.CommandText = @"select * from Users 
where UserID in 
( 
select T.c.value('UserID[1]','int') from @xml.nodes('/root/User') as T(c) 
)"; 
//也可以這樣寫，結(jié)果是一樣的 
//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方法實現(xiàn)這樣能夠獲得較高的性能 
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參數(shù)時需要注意點:

　　1.不同于SQL語句默認不區(qū)分大小寫，xml的XQuery表達式是嚴格區(qū)分大小寫的，所以書寫時一定注意大小寫問題

　　2.使用exist時sql:column() 中的列名須使用雙引號，如sql:column("UserID")，若非要使用單引號需要連續(xù)輸入兩個單引號 sql:column(''UserID'')

　　3.不管是where in或是其他情況下使用xml查詢時能用exist(看清楚了不是sql里的exists)方法就用exist方法，我們不去刻意追求性能的優(yōu)化，但能順手為之的話何樂而不為呢。

方案6 使用表值參數(shù)(Table-Valued Parameters 簡稱TVP Sql Server2008開始支持)
按照msdn描述TVP參數(shù)在數(shù)據(jù)量小于1000時有著很出色的性能，關(guān)于TVP可以參考 http://msdn.microsoft.com/en-us/library/bb510489.aspx

這里主要介紹如何使用TVP實現(xiàn)DataTable集合傳參實現(xiàn)where in
1.使用表值參數(shù)，首先在數(shù)據(jù)庫創(chuàng)建表值函數(shù)
create type IntCollectionTVP as Table(ID int)
2.表值函數(shù)創(chuàng)建好后進行c#調(diào)用，
注意點：
　　1.需要SqlParameter中的SqlDbType設(shè)置為SqlDbType.Structured然后需要設(shè)置TypeName為在數(shù)據(jù)庫中創(chuàng)建的表值函數(shù)名，本示例中為IntCollectionTVP
　　2.構(gòu)造的DataTabel列數(shù)必須和表值函數(shù)定義的一樣，具體列名隨意，無需和表值函數(shù)定義的列名一致，數(shù)據(jù)類型可以隨意，但還是建議和表值類型定義的保持一致，一來省去隱式類型轉(zhuǎn)換，二來可以在初始化DataTabel時就將不合法的參數(shù)過濾掉
　　3.建議定義tvp的時候最好查詢條件里的類型和tvp對應(yīng)字段類型保持一致，這樣可以避免隱式類型轉(zhuǎn)換帶來的性能損失

復制代碼代碼如下:

 
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 
)"; 
//構(gòu)造需要傳參的TVP DataTable 
DataTable tvpDt = new DataTable(); 
//為表添加列，列數(shù)需要和表值函數(shù)IntCollectionTVP保值一致，列名可以不一樣 
tvpDt.Columns.Add("myid", typeof(int)); 
//添加數(shù)據(jù) 
tvpDt.Rows.Add(1); 
tvpDt.Rows.Add(2); 
tvpDt.Rows.Add(3); 
tvpDt.Rows.Add(4); 
//這里的TypeName對應(yīng)我們定義的表值函數(shù)名 
comm.Parameters.Add(new SqlParameter("@MyTvp", SqlDbType.Structured) { Value = tvpDt, TypeName = "IntCollectionTVP" }); 
using (SqlDataAdapter adapter = new SqlDataAdapter(comm)) 
{ 
adapter.SelectCommand = comm; 
adapter.Fill(resultDt); 
} 
} 

總結(jié)：
至此，一共總結(jié)了6六種where參數(shù)化實現(xiàn)，分別如下
1.使用CHARINDEX或like實現(xiàn)where in 參數(shù)化
2.使用exec動態(tài)執(zhí)行SQl實現(xiàn)where in 參數(shù)化
3.為每一個參數(shù)生成一個參數(shù)實現(xiàn)where in 參數(shù)化
4.使用臨時表實現(xiàn)where in 參數(shù)化
5.使用xml參數(shù)實現(xiàn)where in 參數(shù)化
6.使用表值參數(shù)(TVP)實現(xiàn)where in 參數(shù)化
其中前4種在Sql Server參數(shù)化查詢之where in和like實現(xiàn)詳解一文中進行了列舉和示例
6種方法，6種思路，
其中方法1 等于完全棄用了索引，若無特殊需要不建議采用，
方法2 本質(zhì)上合拼SQL沒啥區(qū)別與其用方法2自欺其人還不如直接拼接SQL來的實惠
方法3 受參數(shù)個數(shù)(做多2100個參數(shù))限制，而且若傳的參數(shù)過多性能如何有待驗證，可以酌情使用
方法4 示例中采用的臨時表，其實可以換成表變量性能也許會更好些，不過寫法上有些繁瑣，可以具體的封裝成一個函數(shù)會好些(推薦)
方法5 使用xml傳參，既然有這種類型說明性能上應(yīng)該還不錯，其它會比拼接SQL好很多，使用上也還比較方便，不過需要開發(fā)人員對xml查詢有一定了解才行(推薦)
方法6 tvp方式sql server2008以后才可以使用，很好很強大，若只為where in 的話可以定義幾個tvp where in問題就很容易解決了，而且是強類型也更容易理解(推薦)
不好去評論具體那種方法最好，還是那句老話合適的最好。

此文章屬懶惰的肥兔原創(chuàng)

您可能感興趣的文章: