方案5 使用xml參數

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

XQuery概念了解后需要進一步了解下Sql Server對xml的支持函數，主要為query()、nodes()、exist()、value()、modify() ，詳見

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

D.使用 exist() 方法而不使用 value() 方法
由于性能原因，不在謂詞中使用 value() 方法與關系值進行比較，而改用具有 sql:column() 的 exist()。


使用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);
}
}

復制代碼 代碼如下:

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);
}
}

復制代碼 代碼如下:

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);
}
}

這里主要介紹如何使用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對應字段類型保持一致，這樣可以避免隱式類型轉換帶來的性能損失

復制代碼 代碼如下: