asp.net 使用SqlBulkCopy极速插入数据到 SQL Server
按这个来算，我们那个发水票的时间就会由 10分钟-->20秒，这可太神奇了。
于是乎，下demo，测试，改成自己一般使用的方法测试，NND，还真可以说是极速。
在此贴上我的Demo:SqlBulkCopy.rar
<div class="codetitle"><a style="CURSOR: pointer" data="49390" class="copybut" id="copybut49390" onclick="doCopy('code49390')"> 代码如下:
<div class="codebody" id="code49390">
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();
}
///
/// 使用普通插入数据
/// ///
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;
}
///
/// 使用sqlBulkCopy方式插入数据
/// ///
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,"select * from Passport where 1=2").Tables[0];
}
}
}
