方案5 使用xml參數(shù)
對(duì)sql server xml類型參數(shù)不熟悉的童鞋需要先了解下XQuery概念，這里簡(jiǎn)單提下XQuery 是用來(lái)從 XML 文檔查找和提取元素及屬性的語(yǔ)言，簡(jiǎn)單說(shuō)就是用于查詢xml的語(yǔ)言說(shuō)到這就會(huì)牽著到XPath，其實(shí)XPath是XQuery的一個(gè)子集，XQuery 1.0 和 XPath 2.0 共享相同的數(shù)據(jù)模型，并支持相同的函數(shù)和運(yùn)算符，XPath的方法均適用于XQuery，假如您已經(jīng)學(xué)習(xí)了 XPath，那么學(xué)習(xí) XQuery 也不會(huì)有問(wèn)題。
XQuery概念了解后需要進(jìn)一步了解下Sql Server對(duì)xml的支持函數(shù)，主要為query()、nodes()、exist()、value()、modify() ，詳見(jiàn)http://msdn.microsoft.com/zh-cn/library/ms190798.aspx
使用xml方式實(shí)現(xiàn)where in時(shí)有兩種實(shí)現(xiàn)方式，使用value和exist，在這里推薦使用exist方法，msdn是這樣描述的：
D.使用 exist() 方法而不使用 value() 方法
由于性能原因，不在謂詞中使用 value() 方法與關(guān)系值進(jìn)行比較，而改用具有 sql:column() 的 exist()。
http://msdn.microsoft.com/zh-cn/library/ms178030.aspx
使用xml的value方法實(shí)現(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方法實(shí)現(xiàn)，性能相對(duì)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方法實(shí)現(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方法實(shí)現(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)的查詢方法示例，在實(shí)際使用中經(jīng)常因?yàn)椴煌膞ml結(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方法實(shí)現(xiàn)，性能相對(duì)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方法實(shí)現(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ù)時(shí)需要注意點(diǎn):
1.不同于SQL語(yǔ)句默認(rèn)不區(qū)分大小寫，xml的XQuery表達(dá)式是嚴(yán)格區(qū)分大小寫的，所以書寫時(shí)一定注意大小寫問(wèn)題
2.使用exist時(shí)sql:column() 中的列名須使用雙引號(hào)，如sql:column("UserID")，若非要使用單引號(hào)需要連續(xù)輸入兩個(gè)單引號(hào) sql:column(''UserID'')
3.不管是where in或是其他情況下使用xml查詢時(shí)能用exist(看清楚了不是sql里的exists)方法就用exist方法，我們不去刻意追求性能的優(yōu)化，但能順手為之的話何樂(lè)而不為呢。
方案6 使用表值參數(shù)(Table-Valued Parameters 簡(jiǎn)稱TVP Sql Server2008開(kāi)始支持)
按照msdn描述TVP參數(shù)在數(shù)據(jù)量小于1000時(shí)有著很出色的性能，關(guān)于TVP可以參考 http://msdn.microsoft.com/en-us/library/bb510489.aspx
這里主要介紹如何使用TVP實(shí)現(xiàn)DataTable集合傳參實(shí)現(xiàn)where in
1.使用表值參數(shù)，首先在數(shù)據(jù)庫(kù)創(chuàng)建表值函數(shù)
create type IntCollectionTVP as Table(ID int)
2.表值函數(shù)創(chuàng)建好后進(jìn)行c#調(diào)用，
注意點(diǎn)：
1.需要SqlParameter中的SqlDbType設(shè)置為SqlDbType.Structured然后需要設(shè)置TypeName為在數(shù)據(jù)庫(kù)中創(chuàng)建的表值函數(shù)名，本示例中為IntCollectionTVP
2.構(gòu)造的DataTabel列數(shù)必須和表值函數(shù)定義的一樣，具體列名隨意，無(wú)需和表值函數(shù)定義的列名一致，數(shù)據(jù)類型可以隨意，但還是建議和表值類型定義的保持一致，一來(lái)省去隱式類型轉(zhuǎn)換，二來(lái)可以在初始化DataTabel時(shí)就將不合法的參數(shù)過(guò)濾掉
3.建議定義tvp的時(shí)候最好查詢條件里的類型和tvp對(duì)應(yīng)字段類型保持一致，這樣可以避免隱式類型轉(zhuǎn)換帶來(lái)的性能損失
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對(duì)應(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ù)化實(shí)現(xiàn)，分別如下
1.使用CHARINDEX或like實(shí)現(xiàn)where in 參數(shù)化
2.使用exec動(dòng)態(tài)執(zhí)行SQl實(shí)現(xiàn)where in 參數(shù)化
3.為每一個(gè)參數(shù)生成一個(gè)參數(shù)實(shí)現(xiàn)where in 參數(shù)化
4.使用臨時(shí)表實(shí)現(xiàn)where in 參數(shù)化
5.使用xml參數(shù)實(shí)現(xiàn)where in 參數(shù)化
6.使用表值參數(shù)(TVP)實(shí)現(xiàn)where in 參數(shù)化
其中前4種在Sql Server參數(shù)化查詢之where in和like實(shí)現(xiàn)詳解 一文中進(jìn)行了列舉和示例
6種方法，6種思路，
其中方法1 等于完全棄用了索引，若無(wú)特殊需要不建議采用，
方法2 本質(zhì)上合拼SQL沒(méi)啥區(qū)別與其用方法2自欺其人還不如直接拼接SQL來(lái)的實(shí)惠
方法3 受參數(shù)個(gè)數(shù)(做多2100個(gè)參數(shù))限制，而且若傳的參數(shù)過(guò)多性能如何有待驗(yàn)證，可以酌情使用
方法4 示例中采用的臨時(shí)表，其實(shí)可以換成表變量性能也許會(huì)更好些，不過(guò)寫法上有些繁瑣，可以具體的封裝成一個(gè)函數(shù)會(huì)好些(推薦)
方法5 使用xml傳參，既然有這種類型說(shuō)明性能上應(yīng)該還不錯(cuò)，其它會(huì)比拼接SQL好很多，使用上也還比較方便，不過(guò)需要開(kāi)發(fā)人員對(duì)xml查詢有一定了解才行(推薦)
方法6 tvp方式sql server2008以后才可以使用，很好很強(qiáng)大，若只為where in 的話可以定義幾個(gè)tvp where in問(wèn)題就很容易解決了，而且是強(qiáng)類型也更容易理解(推薦)
不好去評(píng)論具體那種方法最好，還是那句老話合適的最好。
此文章屬懶惰的肥兔原創(chuàng)
