学步园

最近，正和团队做集团内A公司X平台向B公司移植，众所周知，软件移植项目必然少不了数据转移——数据从一个地方复制到另外一个地方是再平常不过的需求了。然而B公司老X平台已经上线5,6年，单表上百万的数据量真是家常便饭，如果仅说让这些数据能妥善的“安置”到新的平台中，那也很easy，可是让人头痛的是海量数据的转移需要多久？几个小时？几个工作日？——因为所有数据转移的时间的总和等于最终新老平台切换时我们的服务需要停止的时间。抛开项目其它工程不说，如果整个数据转移需求3,5个工作日，我想别说客户，产品和业务部门的同事都不可能同意。

我很幸运，也是我孤陋寡闻，上周一个新来的同事建议说ASP.NET 2.0有一个SqlBulkCopy类，它可以帮助我们很快速的复制数据。

起初查到一篇这样的博文：《DataTable的数据批量写入数据库》。原文如下：

最近在将excel中的文件导入到数据库中，用程序进行编写，由于数据量较大所以速度很慢，后来采用了SqlBulkCopy类，解决了速度的问题，我就insert语句,sqldataadapter.update(dataset,tablename);sqlbulkcopy.WriteToServer(datatable);三个方法的性能进行比较：

1、生成测试的datatable表，表结构如下：

UniqueID(主键，自动增长)  |  CompanyName  |  CompanyCode  |  Address  |  Owner  |  Memo

共6个字段。

 SqlConnection sqlconnection = new SqlConnection(connectionString);
 SqlDataAdapter sqldataadapter = new SqlDataAdapter("select * from Table_1 where 1=2", sqlconnection);
 DataSet dataset = new DataSet();
 sqldataadapter.Fill(dataset, "Table_1");
 DataTable datatable = dataset.Tables[0];
  //生成20000条记录

  for (int i = 0; i < 20000; i++)
  {
          DataRow datarow = datatable.NewRow();
          datarow["CompanyName"] = "companyname"+string.Format("{0:0000}",i);
          datarow["CompanyCode"] = "companycode" + string.Format("{0:0000}", i);
          datarow["Address"] = "address" + string.Format("{0:0000}", i);
          datarow["Owner"] = "owner" + string.Format("{0:0000}", i);
          datarow["Memo"] = "memo" + string.Format("{0:0000}", i);

          datatable.Rows.Add(datarow);
  }

2、使用sqlcommand.executenonquery()方法插入

foreach (DataRow datarow in datatable.Rows)
{
string sql = "INSERT INTO [Table_1]

([CompanyName],[CompanyCode],[Address],[Owner],[Memo])" +
"VALUES('" + datarow["CompanyName"].ToString() + "'" +
",'" + datarow["CompanyCode"].ToString() + "'" +
",'" + datarow["Address"].ToString() + "'" +
",'" + datarow["Owner"].ToString() + "'" +
",'" + datarow["Memo"].ToString() + "')";
  using (SqlConnection sqlconn = new SqlConnection(connectionString))
  {
      sqlconn.Open();

      SqlCommand sqlcommand = new SqlCommand(sql, sqlconn);
      sqlcommand.ExecuteNonQuery();
      sqlconn.Close();
  }
}

插入20000条记录时间:00:00:29.7336000

3、使用sqldataadapter.update(dataset,tablename);

SqlCommand insertcommand = new SqlCommand("INSERT INTO [Table_1]([CompanyName],[CompanyCode],[Address],[Owner],[Memo])" +
 "VALUES(@CompanyName, @CompanyCode,@Address,@Owner,@Memo)",new SqlConnection(connectionString));
 insertcommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 50, "CompanyName");
 insertcommand.Parameters.Add("@CompanyCode", SqlDbType.NChar, 25, "CompanyCode");
insertcommand.Parameters.Add("@Address", SqlDbType.NChar, 255, "Address");
insertcommand.Parameters.Add("@Owner", SqlDbType.NChar, 25, "Owner");
insertcommand.Parameters.Add("@Memo", SqlDbType.NChar, 255, "Memo");
sqldataadapter.InsertCommand = insertcommand;

sqldataadapter.Update(dataset, "Table_1");  

插入20000条记录时间:00:00:22.8938000

4、使用sqlbulkcopy.writetoserver(datatable)

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
sqlbulkcopy.DestinationTableName = "Table_1";//数据库中的表名

sqlbulkcopy.WriteToServer(dataset.Tables[0]);

插入20000条记录时间:00:00:00.3276000

所以说速度是sqlbulkcopy最快，sqldataadapter.update()次之，sqlcommand.ExecuteNonQuery()最慢。

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/humozhi/archive/2009/03/31/4039868.aspx

我有个习惯，就是新的事物总抱有怀疑的态度，于是发动几个同事一起测试，结果确实很让我吃惊。平均导入速度要比之前传统的方式快10倍左右。

查了些资料，微软是这样定义的：

SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

补充：SqlBulkCopy是.NET Framework 2.0新增的类，位于命名空间System.Data.SqlClient下，主要提供把其他数据源的数据有效批量的加载到SQL Server表中的功能。类似与 Microsoft SQL Server 包中名为 bcp 的命令行应用程序。但是使用 SqlBulkCopy 类可以编写托管代码解决方案，性能上优于bcp命令行应用程序，更优于如Insert方式向SQL Server表加载大量数据。SqlBulkCopy可以应用到大批量数据的转移上，而不管数据源是什么。

另外，《使用asp.net 2.0中的SqlBulkCopy类批量复制数据》说：

­­SqlBulkCopy 包含一个方法 WriteToServer，它用来从数据的源复制数据到数据的目的地。 WriteToServer方法可以处理的数据类型有DataRow[]数组，DataTable 和 DataReader。 你可以根据不同的情形使用不同的数据类型，但是更多时候选择DataReader是一个比较好的主意。 这是因为DataReader是一个只向前的、只读的数据流，它不会保存数据，所以要比DataTable 和 DataRows[]都要快。

下面的代码的作用是把数据从源表复制到目的表。
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
      {
        bulkCopy.BatchSize = 500;

        bulkCopy.NotifyAfter = 1000;
        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
        bulkCopy.DestinationTableName = "Products_Latest";
        bulkCopy.WriteToServer(reader);                   
      }

这里有一对需要提及的知识点。 首先，我使用DataReader来从数据库的表中读取数据。 Products_Latest是目的表，因为数据要从Products_Archive表复制到Products_Latest表。 bulkCopy对象提供了一个SqlRowCopied事件，在每次处理完NotifyAfter属性指定的行数时发生。 本例中的意思就是每处理完1000行就触发一次该事件，因为NotifyAfter被设置成了1000

BatchSize属性是非常重要的，程序性能如何主要就依靠着它。 BatchSize的意思就是同每一批次中的行数，在每一批次结束时，就将该批次中的行发送到数据库。 我将BatchSize设置成了500，其意思就是reader每读出500行就将他们发送到数据库从而执行批量复制的操作。 BatchSize的默认值是“1”，其意思就是把每一行作为一个批次发送到数据库。

设置不同的BatchSize在性能上将给你带来不同的结果。 你应该根据你的需求进行测试，来决定BatchSize的大小。

查了下原理：

SqlBulkCopy让 DataSet 内大量的数据或是 DataReader通过数据流（Stream）直接读取大量的记录，可以快速将这些记录添加到目的数据库的数据表中。但要注意的是它并非如我们一般用的 bcp.exe工具程序，可以从某个符号分隔文件读取大量数据，选择性地搭配格式文件（Format File）将记录装载到数据库中，或是将数据库内的数据导出成为一个文件。但由于DataSet 能集成 XML 数据，因此依然可以采用 SqlBulkCopy 类型，轻松地通过 DataSet 将 XML 文件数据大量转入到数据库。

总结

在了解了SqlBulkCopy之后，我们基本上所有大数据都是用它实现的，有人说SqlBulkCopy不是走ADO.NET的而是直接操作MDB文件，我也没有证实。但我想暂时不影响我实用它。当然，SqlBulkCopy使用时要注意以下几点：

1．确认确实需要大容量更新在执行此操作，(几十行的数据请尽量使用别的渠道把).

2．确认数据一致性，与检查机制，以免遇到主键冲突，数据不符格式等意外。

3．SqlBulkCopy操作可能会导致对目标表元数据的更改（例如，禁用约束检查时）。如果出现这种情况，访问大容量插入表的并发快照隔离事务将失败。

4. SqlBulkCopy将向数据库下大容量更新锁，请注意并发性，以免其他连接因长时间等待而超时。

延伸

SqlBulkCopy的快速数据复制，不得不让我想到数据库备份及数据库读写分离的多机数据同步。刚才在找资料时，也看到了一篇SqlBulkcopy的应用《利用SqlBulkCopy实现网上答题系统》。当然，数据库读写分离后数据同步是一门很大的学问，SqlBulkcopy我想只能是初级的解决方案。