ADO.NET / 用 SqlBulkCopy 类，海量批写入数据库

SqlBulkCopy 类，使您可以用其他源的数据有效批量加载 SQL Server 表。

Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable instance 或可使用 DataReader 读取数据。

----------------------------------------------------------------
示例一:
同上方 msdn 连接的写法，不透过 DataTable 暂存所有要批写入的数据，而是直接用
DataReader + SqlBulkCopy 类，即时海量地批写入 :

简易示例

using(SqlConnection Conn = new SqlConnection(strConnString)) 
{ 
    Conn.Open(); 
    SqlCommand Comm = new SqlCommand("SELECT EmployeeID, LastName, FirstName FROM Employees", Conn); 
SqlDataReader dr; 
dr = Comm.ExecuteReader(); 
    using (SqlConnection bcConn = new SqlConnection(strConnString)) 
    { 
        bcConn.Open(); 
        using (SqlBulkCopy bc = new SqlBulkCopy(bcConn)) 
        { 
          //要存入的数据库
            bc.DestinationTableName = "dbo.TestEmployees"; 
//字段对应
           bc.ColumnMappings.Add("EmployeeID", "EmployeeID"); 
          bc.ColumnMappings.Add("LastName", "LastName"); 
          bc.ColumnMappings.Add("FirstName", "FirstName"); 
          try 
          { 
          　  for (int i = 0; i < 1000; i++) 
              　 bc.WriteToServer(dr); 
          } 
          catch (Exception ex) 
          { 
              Console.WriteLine(ex.Message); 
          } 
          if (dr != null) 
              dr.Close(); 
        } 
    }
}

 

在發現了 SqlBulkCopy 以後，發現它更是威力強大，現在就來介紹 SqlBulkCopy 的猛，如下例 :
示例二 :
(此例为先将要批写入的所有数据，暂存在一个 DataTable 里)

进阶示例

  //一开始我们先产生一个 DataTable，用来装我们要写入的数据
 DataTable dt = new DataTable();
 dt.Columns.Add("id", typeof(int));
 dt.Columns.Add("name", typeof(string));
//因为 SqlBulkCopy 的猛，就是海量地快速一次性写入能力，我们來跑 10 万条吧
 int i;
 for (i = 0; i < 100000; i++)
 {
     DataRow dr = dt.NewRow();
     dr["name"] = i.ToString();
     dt.Rows.Add(dr);
 }
//声明连接字符串
 SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString);
conn.Open();
//声明 SqlBulkCopy
 using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
 {
       //設置一个批，写入多少条记录
       sqlBC.BatchSize = 1000;
//設置逾時的秒数
       sqlBC.BulkCopyTimeout = 60;
//設置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表後，呼叫事件处理函数
       sqlBC.NotifyAfter = 10000;
       sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
//設置要写入的数据库
       sqlBC.DestinationTableName = "dbo.Table1";
//对应数据行
       sqlBC.ColumnMappings.Add("id", "id");
       sqlBC.ColumnMappings.Add("name", "name");
//开始写入
       sqlBC.WriteToServer(dt);
 }
conn.Dispose();
void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
 {
     Response.Write("---<br/>");
 } 

 

測試環境：SQL2005 Express
測試資料量：10 万条
測試次數：10 次
平均秒數：2.3532 秒

结论：太可怕啦，之前的写法，如果真的要写入 10 万条這种海量的数据，都需花费一分钟左右，但使用了 SqlBulkCopy 卻只要短短的兩秒钟。下表看的出來，如果记录条数很少，就沒必要使用 SqlBulkCopy 了。

 

寫入十万条记录 10 次的平均秒數
使用SqlBulkCopy：2.2051
使用AddWithValue：63.418
寫入一万条记录 10 次的平均秒數
使用SqlBulkCopy：0.2188
使用AddWithValue：6.3856
寫入一千条记录 10 次的平均秒數
使用SqlBulkCopy：0.0187
使用AddWithValue：0.5805
寫入一百条记录 10 次的平均秒數
使用SqlBulkCopy：0.0062
使用AddWithValue：0.0353
寫入十条记录 10 次的平均秒數
使用SqlBulkCopy：0.004
使用AddWithValue：0.004

 

使用事务和SqlBulkCopy导入大批量数据
个人测试所用。调用请修改。

 

/// <summary>
/// 使用事务和SqlBulkCopy批量导入数据 示例
/// By Conan304 2009年8月29日21:51:02
/// </summary>
/// <param name="phone"></param>
/// <param name="smsInfo"></param>
private static void SqlBulkCopyData(string[] phone, string smsInfo)
{

if (phone == null) return;

//生成DataTable
DataTable dataTable = new DataTable();

DataColumn column = new DataColumn();

//qy_id
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "QY_ID";
dataTable.Columns.Add(column);

//Info_ID
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.MaxLength = 32;
column.ColumnName = "Info_ID";
dataTable.Columns.Add(column);

//SP_Port
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.MaxLength = 24;
column.ColumnName = "SP_Port";
dataTable.Columns.Add(column);

//Phone
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.MaxLength = 11;
column.ColumnName = "Phone";
dataTable.Columns.Add(column);

//Content
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.MaxLength = 300;
column.ColumnName = "Content";
dataTable.Columns.Add(column);

//SendTime
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.ColumnName = "SendTime";
column.DefaultValue = DateTime.Now;
dataTable.Columns.Add(column);

//SendLevel
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "SendLevel";
column.DefaultValue = 4;
dataTable.Columns.Add(column);

//IsLong
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "IsLong";
dataTable.Columns.Add(column);

//AdminID
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "AdminID";
column.DefaultValue = 0;
dataTable.Columns.Add(column);

string[] result = new string[phone.Length];

for (int i = 0; i < phone.Length; i++)
{
//生成GUID
string Guid = System.Guid.NewGuid().ToString("N").ToUpper();
string phoneNew = string.Empty;
int phoneType = GetMobileType(phone[i], out phoneNew);
if (phoneType == 0)
{
result[i] = phoneNew + "-0-0";
}
else
{
DataRow dataRow = dataTable.NewRow();
dataRow["qy_id"] = 16;
dataRow["Info_ID"] = Guid;
dataRow["SP_Port"] = "10657027014211";
dataRow["Phone"] = phoneNew;
dataRow["Content"] = smsInfo;
dataRow["IsLong"] = 0;
dataTable.Rows.Add(dataRow);
result[i] = phoneNew + "-1-" + Guid;
}
}

//BCP copy
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS";
conn.Open();

SqlTransaction sqlbulkTransaction = conn.BeginTransaction();

//请在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);


copy.DestinationTableName = "T_SMS_SendInfo";
foreach (DataColumn dc in dataTable.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

}
try
{
copy.WriteToServer(dataTable);
sqlbulkTransaction.Commit();
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
Console.WriteLine(ex.ToString());
}
finally
{
copy.Close();
conn.Close();
}


//for (int x = 0; x < dataTable.Rows.Count; x++)
//{
// for (int i = 0; i < dataTable.Columns.Count; i++)
// {
// Console.WriteLine("Column Name:{0},and value is:{1}",dataTable.Columns[i].ColumnName.ToString(),dataTable.Rows[x][i].ToString());
// }
// Console.WriteLine();
//}

//foreach (string str in result)
//{
// Console.WriteLine(str.ToString());
//}
}

 

使用SqlBulkCopyOptions.UseInternalTransaction：

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  connectionString, SqlBulkCopyOptions.KeepIdentity |
  SqlBulkCopyOptions.UseInternalTransaction))
  {
  bulkCopy.BatchSize = 10;
  bulkCopy.DestinationTableName =
  "dbo.BulkCopyDemoMatchingColumns";

  // Write from the source to the destination.
  // This should fail with a duplicate key error
  // after some of the batches have been copied.
  try
  {
  bulkCopy.WriteToServer(XXX);
  }
  catch (Exception ex)
  {
  Console.WriteLine(ex.Message);
  }
  finally
  {
  //some code;
  }
  }

使用SqlTransaction ：

using (SqlConnection destinationConnection =
  new SqlConnection(connectionString))
  {
  destinationConnection.Open();

  using (SqlTransaction transaction =
  destinationConnection.BeginTransaction())
  {
  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
  destinationConnection, SqlBulkCopyOptions.KeepIdentity,
  transaction))
  {
  bulkCopy.BatchSize = 10;
  bulkCopy.DestinationTableName =
  "dbo.BulkCopyDemoMatchingColumns";

  // Write from the source to the destination.
  // This should fail with a duplicate key error.
  try
  {
  bulkCopy.WriteToServer(XXX);
  transaction.Commit();
  }
  catch (Exception ex)
  {
  Console.WriteLine(ex.Message);
  transaction.Rollback();
  }
  finally
  {
  //code;
  }
  }
  }
  }

 

 

SqlBulkCopyOptions

	成员名称	说明
	Default	对所有选项使用默认值。
	KeepIdentity	保留源标识值。如果未指定，则由目标分配标识值。
	CheckConstraints	请在插入数据的同时检查约束。默认情况下，不检查约束。
	TableLock	在批量复制操作期间获取批量更新锁。如果未指定，则使用行锁。
	KeepNulls	保留目标表中的空值，而不管默认值的设置如何。如果未指定，则空值将由默认值替换（如果适用）。
	FireTriggers	指定后，会导致服务器为插入到数据库中的行激发插入触发器。
	UseInternalTransaction	如果已指定，则每一批批量复制操作将在事务中发生。如果指示了此选项，并且为构造函数提供了 SqlTransaction 对象，则发生 ArgumentException。