SqlServer參數化查詢之where in和like完成之xml和DataTable傳參引見。本站提示廣大學習愛好者：（SqlServer參數化查詢之where in和like完成之xml和DataTable傳參引見）文章只能為提供參考，不一定能成為您想要的結果。以下是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成績就很輕易處理了，並且是強類型也更輕易懂得(推舉)
欠好去評論詳細那種辦法最好，照樣那句老話適合的最好。
此文章屬懶散的肥兔原創
