本篇，我将来讲解一下在sqlserver中批量插入数据。
先创建一个用来测试的数据库和表，为了让插入数据更快，表中主键采用的是guid，表中没有创建任何索引。guid必然是比自增长要快的，因为你生成一个guid算法所花的时间肯定比你从数据表中重新查询上一条记录的id的值然后再进行加1运算要少。而如果存在索引的情况下，每次插入记录都会进行索引重建，这是非常耗性能的。如果表中无可避免的存在索引，我们可以通过先删除索引，然后批量插入，最后再重建索引的方式来提高效率。
create database carsys;
go
use carsys;
go
create table product(
id uniqueidentifier primary key ,
name varchar (50) not null ,
price decimal (18,2) not null
)
我们通过sql脚本来插入数据，常见如下三种方式。
方式一， 一条一条插入，性能最差，不建议使用。
insert into product(id, name ,price) values (newid(), '牛栏1段' ,160);
insert into product(id, name ,price) values (newid(), '牛栏2段' ,260);
......
方式二
insert into product(id, name ,price) values
(newid(), '牛栏1段' ,160)
,(newid(), '牛栏2段' ,260)
......
方式三
insert into product(id, name ,price)
select newid(), '牛栏1段' ,160
union all
select newid(), '牛栏2段' ,180
union all
......
在c#中通过ado.net来实现批量操作同样也存在三种方式。
方式一： 逐条插入
static void insertone()
{
console.writeline( "采用一条一条插入的方式实现" );
stopwatch sw = new stopwatch();
long totalrow = 1000000;
using (sqlconnection conn = new sqlconnection(strconnmsg)) //using中会自动open和close 连接。
{
string sql = "insert into product(id,name,price) values(newid(),@p,@d)" ;
conn.open();
for ( int i = 0; i < 1000000; i++)
{
using (sqlcommand cmd = new sqlcommand(sql, conn))
{
cmd.parameters.addwithvalue( "@p" , "商品" + i);
cmd.parameters.addwithvalue( "@d" , i);
sw.start();
cmd.executenonquery();
console.writeline( string .format( "插入1条记录，时间：{0}" , sw.elapsedmilliseconds));
}
if (i == 1000)
{
sw.stop();
break ;
}
}
}
console.writeline( string .format( "插入{0}条记录，每1000条的插入时间是{1}毫秒,预估总得插入时间是{2}毫秒，{3}分钟" , totalrow, sw.elapsedmilliseconds,
((sw.elapsedmilliseconds / 1000) * totalrow), getminute((sw.elapsedmilliseconds / 1000 * totalrow))));
}
运行结果如下：
我们会发现插入100w条记录，预计需要50分钟时间，每插入一条记录大概需要3毫秒左右。
方式二： 使用sqlbulk
#region 方式二
static void inserttwo()
{
console.writeline( "使用bulk插入的实现方式" );
stopwatch sw = new stopwatch();
datatable dt = gettableschema();
using (sqlconnection conn = new sqlconnection(strconnmsg))
{
sqlbulkcopy bulkcopy = new sqlbulkcopy(conn);
bulkcopy.destinationtablename = "product" ;
bulkcopy.batchsize = dt.rows.count;
conn.open();
sw.start();
for ( int i = 0; i < totalrow;i++ )
{
datarow dr = dt.newrow();
dr[0] = guid.newguid();
dr[1] = string .format( "商品" , i);
dr[2] = ( decimal )i;
dt.rows.add(dr);
}
if (dt != null && dt.rows.count != 0)
{
bulkcopy.writetoserver(dt);
sw.stop();
}
console.writeline( string .format( "插入{0}条记录共花费{1}毫秒，{2}分钟" , totalrow, sw.elapsedmilliseconds, getminute(sw.elapsedmilliseconds)));
}
}
static datatable gettableschema()
{
datatable dt = new datatable();
dt.columns.addrange( new datacolumn[] {
new datacolumn( "id" , typeof (guid)),
new datacolumn( "name" , typeof ( string )),
new datacolumn( "price" , typeof ( decimal ))});
return dt;
}
#endregion
运行结果如下：
插入100w条记录才8s多，是不是很溜。
方式三： 使用tvps(表值参数）插入数据
从sqlserver 2008起开始支持tvps。创建缓存表producttemp ，执行如下sql。
create type producttemp as table (
id uniqueidentifier primary key ,
name varchar (50) not null ,
price decimal (18,2) not null
)
执行完成之后，会发现在数据库carsys下面多了一张缓存表producttemp
可见插入100w条记录共花费了11秒多。
总结： 大数据批量插入方式一尽量避免使用，而方式二和方式三都是非常高效的批量插入数据方式。其都是通过构建datatable的方式插入的，而我们知道datatable是存在内存中的，所以当数据量特别特别大，大到内存中无法一次性存储的时候，可以分段插入。比如需要插入9千万条数据，可以分成9段进行插入，一次插入1千万条。而在for循环中直接进行数据库操作，我们是应该尽量避免的。每一次数据库的连接、打开和关闭都是比较耗时的，虽然在c#中存在数据库连接池，也就是当我们使用using或者conn.close()，进行释放连接时，其实并没有真正关闭数据库连接，它只是让连接以类似于休眠的方式存在，当再次操作的时候，会从连接池中找一个休眠状态的连接，唤醒它，这样可以有效的提高并发能力，减少连接损耗。而连接池中的连接数，我们都是可以配置的。
源码下载：https://pan.baidu测试数据/s/1slkrrlr
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：http://HdhCmsTestcnblogs测试数据/jiekzou/p/6145550.html
dy("nrwz");
查看更多关于C#批量插入数据到Sqlserver中的三种方式的详细内容...