Sqlserver 2005使用XML一次更新多条记录的方法

我想很多人都知道，在oracle里面，存储过程里面可以传入数组（如int[]），也就是说，可以传多条记录到数据，从而一起更新。减少数据库的请求次数。
但sqlserver呢？bulk insert这个很多人都知道，我也知道，但可惜，我从来没用过，只有导数据的时候才会考虑，但导数据dts不是更方便吗？
手头的一个项目，有几个功能，每次需要更新n（n<1000）条记录，记录不多，但如果每次只更新一条，循环insert，那每个功能需要n次请求数据库，如果有1000个并发，那数据库除了做你这个事情，其他的活不用干了。所以，需要尽量减少数据库请求，做到一次更新所有的记录。
幸好，sqlserver给我们提供了一个新功能，利用xml（2000好像是没有这个功能的）。
先来假定一个这样的需求：用户更新一个book，同时需要更新n个章节。
一般的思路是这样，先更新book，然后循环章节数，n次更新数据的章节表。大家可以看下这个性能。

那我们用xml试试

利用xml更新的存储过程

create procedure up_book_insert
(
@bookid int,
@chapterxml xml
)
as
begin
create table #table
(
chapterid int,
chaptername varchar(255),
price int
);
insert #table
select *
from (
select x.c.value('id[1]', 'int') as chapterid,
x.c.value('name[1]', 'varchar(255)') as chaptername,
x.c.value('price[1]','int') as price
from @chapterxml.nodes('chapter') as x(c) --注意：这里的x(c)命名空间是需要的
) t;
insert into tbchapter(bookid,chapterid,chaptername,price)
select @bookid,chapterid,chaptername,price from #table;
end

其实，在存储过程里面可以把临时表去掉的。


然后我们执行下看看

执行存储过程

exec up_book_insert 10000,'<chapter><id>268</id><name>第268章</name><price>100</price></chapter><chapter><id>273</id><name>第273章</name><price>100</price></chapter><chapter><id>275</id><name>第275章</name><price>100</price></chapter>'

怎么样？不错吧。只需要在存储过程里面对xml格式进行解析。


而在c#里面，xml格式可以传入dbtype.string类型就可以了。

再写一个函数来生成xml格式的字符串


生成xml格式的函数

public static string formatxmlinfo(list<chapterinfo> list)
{
if (list==null||list.count<=0)
{
return string.empty;
}
stringbuilder sb = new stringbuilder();
foreach (chapterinfo info in list)
{
sb.appendformat("<chapter><id>{0}</id><name>{1}</name><price>{2}</price></chapter>", info.chapterid, info.chaptername, info.price);
}
return sb.tostring();
}

好了，完成了。

性能具体怎么样，还没进行测试，但肯定的一点是，比多次请求数据库，或者在存储过程里面循环分割字符串效率要高。