asp.net使用SqlBulkCopy极速插入数据到SQLServer

今天早上reader 上收到cnblogs的订阅里看到一个关于SQL语句快速插入的文章，提到SqlBulkCopy，感觉不错，按他的测试SqlBulkCopy要比普通插入快近30倍，

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, CommandType.Text, "select * from Passport where 1=2").Tables[0]; 
} 
} 
} 
 
转自cnblogs的文章