SQL Server 参数化查询之where in和like实现之xml和DataTable传参介绍

这篇文章主要为大家详细介绍了SQL Server 参数化查询之where in和like实现之xml和DataTable传参介绍，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

方案5 使用xml参数对sql server xml类型参数不熟悉的童鞋需要先了解下XQuery概念，这里简单提下XQuery 是用来从 XML 文档查找和提取元素及属性的语言，简单说就是用于查询xml的语言说到这就会牵着到XPath，其实XPath是XQuery的一个子集，XQuery 1.0 和 XPath 2.0 共享相同的数据模型，并支持相同的函数和运算符，XPath的方法均适用于XQuery，假如您已经学习了 XPath，那么学习 XQuery 也不会有问题。详见//www.512pic.com/w3school/xquery/xquery_intro.htmXQuery概念了解后需要进一步了解下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); 
} 
} 

代码如下:

 
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 in1.使用表值参数，首先在数据库创建表值函数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); 
} 
} 

注：关于SQL Server 参数化查询之where in和like实现之xml和DataTable传参介绍的内容就先介绍到这里，更多相关文章的可以留意