SqlServer参数化查询之where in和like实现之xml和DataTable传参介绍[MSSQL防范]

筹划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问题就很简单办理了,并且是强范例也更简单理解(举荐)
不好去评论具体那种办法最好,还是那句老话符合的最好.

此文章属怠惰的肥兔原创