SqlServer參數(shù)化查詢之where in和like實現(xiàn)之xml和DataTable傳參介紹
方案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，假如您已經學習了 XPath，那么學習 XQuery 也不會有問題。詳見https://www.jb51.net/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() 方法與關系值進行比較，而改用具有 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
)"; //也可以這樣寫，結果是一樣的
//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結構的查詢方法示例，在實際使用中經常因為不同的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方法實現(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)
)";
//也可以這樣寫，結果是一樣的
//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時有著很出色的性能，關于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#調用，
注意點：
1.需要SqlParameter中的SqlDbType設置為SqlDbType.Structured然后需要設置TypeName為在數(shù)據(jù)庫中創(chuàng)建的表值函數(shù)名，本示例中為IntCollectionTVP
2.構造的DataTabel列數(shù)必須和表值函數(shù)定義的一樣，具體列名隨意，無需和表值函數(shù)定義的列名一致，數(shù)據(jù)類型可以隨意，但還是建議和表值類型定義的保持一致，一來省去隱式類型轉換，二來可以在初始化DataTabel時就將不合法的參數(shù)過濾掉
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();
//為表添加列，列數(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對應我們定義的表值函數(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);
}
}
總結：
至此，一共總結了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 本質上合拼SQL沒啥區(qū)別與其用方法2自欺其人還不如直接拼接SQL來的實惠
方法3 受參數(shù)個數(shù)(做多2100個參數(shù))限制，而且若傳的參數(shù)過多性能如何有待驗證，可以酌情使用
方法4 示例中采用的臨時表，其實可以換成表變量性能也許會更好些，不過寫法上有些繁瑣，可以具體的封裝成一個函數(shù)會好些(推薦)
方法5 使用xml傳參，既然有這種類型說明性能上應該還不錯，其它會比拼接SQL好很多，使用上也還比較方便，不過需要開發(fā)人員對xml查詢有一定了解才行(推薦)
方法6 tvp方式sql server2008以后才可以使用，很好很強大，若只為where in 的話可以定義幾個tvp where in問題就很容易解決了，而且是強類型也更容易理解(推薦)
不好去評論具體那種方法最好，還是那句老話合適的最好。 此文章屬懶惰的肥兔原創(chuàng)
版權聲明：本站文章來源標注為YINGSOO的內容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內容來源于網友推薦、互聯(lián)網收集整理而來，僅供學習參考，不代表本站立場，如有內容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信