数据库教程:asp.net 使用SqlBulkCopy极速插入数据到 SQL Server

复制代码 代码如下:

using system;
using system.diagnostics;
using system.data;
using system.data.sqlclient;
using microsoft.applicationblocks.data;
namespace consoleappinserttest
{
class program
{
static int count = 1000000; //插入的条数
static void main(string[] args)
{
long sqlbulkcopyinsertruntime = sqlbulkcopyinsert();
console.writeline(string.format(“使用sqlbulkcopy插入{1}条数据所用的时间是{0}毫秒”, sqlbulkcopyinsertruntime, count));
long commoninsertruntime = commoninsert();
console.writeline(string.format(“普通方式插入{1}条数据所用的时间是{0}毫秒”, commoninsertruntime, count));
console.readkey();
}
/// <summary>
/// 使用普通插入数据
/// </summary>
/// <returns></returns>
private static long commoninsert()
{
stopwatch stopwatch = new stopwatch();
stopwatch.start();
for (int i = 0; i < count; i++)
{
sqlhelper.executenonquery(sqlhelper.sqlconnection, commandtype.text, “insert into passport(passportkey) values(‘” + guid.newguid() + “‘)”);
}
stopwatch.stop();
return stopwatch.elapsedmilliseconds;
}
/// <summary>
/// 使用sqlbulkcopy方式插入数据
/// </summary>
/// <returns></returns>
private static long sqlbulkcopyinsert()
{
stopwatch stopwatch = new stopwatch();
stopwatch.start();
datatable datatable = gettableschema();
for (int i = 0; i < count; i++)
{
datarow datarow = datatable.newrow();
datarow[2] = guid.newguid();
datatable.rows.add(datarow);
}
//console.writeline(stopwatch.elapsedmilliseconds);//初始化数据时间
sqlbulkcopy sqlbulkcopy = new sqlbulkcopy(sqlhelper.sqlconnection);
sqlbulkcopy.destinationtablename = “passport”;
if (datatable != null && datatable.rows.count != 0)
{
sqlbulkcopy.writetoserver(datatable);
}
sqlbulkcopy.close();
stopwatch.stop();
return stopwatch.elapsedmilliseconds;
}
private static datatable gettableschema()
{
return sqlhelper.executedataset(sqlhelper.sqlconnection, commandtype.text, “select * from passport where 1=2”).tables[0];
}
}
}