.NET中的SqlBulkCopy类用法

在开发过程中，经常会遇到向数据库插入大量数据的情况，那么如果是百万条数据，一条条的插入实在是太慢了，所以就有了SqlBulkCopy类。

本文中我将示范SqlBulkCopy类的不同应用。

以下面三张表为例：

 

先讲相同的表结构直接复制数据

从Products_Archive表 复制数据到 Products_Latest表：

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

 

private static void PerformBulkCopy()
{
  //创建数据库连接
 string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

//源数据表
  using (SqlConnection sourceConnection = new SqlConnection(connectionString))
 {
   SqlCommand myCommand = new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);
             sourceConnection.Open();
        SqlDataReader reader = myCommand.ExecuteReader(); 
 
  //目的表
   using (SqlConnection destinationConnection = new SqlConnection(connectionString))
   {
 // 打开连接
     destinationConnection.Open();
  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);           



     }
   }
  reader.Close();  

 }

}

 

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

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

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

 

 

接下来讲下不同结构表之间复制数据：

从Products_Archive表中把所有的产品名称及其数量复制到Products_TopSelling表里

 

private static void PerformBulkCopyDifferentSchema()

{

 string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

 DataTable sourceData = new DataTable();

// 源

 using (SqlConnection sourceConnection = new SqlConnection(connectionString))

 {

  SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);

   sourceConnection.Open();

   SqlDataReader reader = myCommand.ExecuteReader();

   // 目的

   using (SqlConnection destinationConnection = new SqlConnection(connectionString))

   {

 // 打开连接

     destinationConnection.Open();

 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))

     {

       bulkCopy.ColumnMappings.Add("ProductID", "ProductID");

       bulkCopy.ColumnMappings.Add("ProductName", "Name");

       bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");

       bulkCopy.DestinationTableName = "Products_TopSelling";

       bulkCopy.WriteToServer(reader);

     }


   }

 reader.Close();

 }

}

ColumnMappings集合用于映射源表和目的表之间的列。

 

 

从XML文件复制数据到数据库的表中

 

数据源并不局限于数据库的表，你也可以使用XML文件做数据源。 这里有一个非常简单的使用XML文件做数据源进行批量复制操作的例子：

 

 

(Products.xml)

 

<?xml version="1.0" encoding="utf-8" ?>

<Products>

 <Product productID="1" productName="Chai" />

 <Product productID="2" productName="Football" />

 <Product productID="3" productName="Soap" />

 <Product productID="4" productName="Green Tea" />

</Products>

private static void PerformBulkCopyXMLDataSource()

{

 string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";

 DataSet ds = new DataSet();

 DataTable sourceData = new DataTable();

 ds.ReadXml(@"C:Products.xml");

 sourceData = ds.Tables[0];

 // 目的

 using (SqlConnection destinationConnection = new SqlConnection(connectionString))

 {

   // 打开连接

   destinationConnection.Open();

   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))

   {

     // 列映射

     bulkCopy.ColumnMappings.Add("productID", "ProductID");

     bulkCopy.ColumnMappings.Add("productName", "Name");

                   

     bulkCopy.DestinationTableName = "Products_TopSelling";

     bulkCopy.WriteToServer(sourceData);

   }

 }

}

首先把XML文件读进DataTable，然后再使用SqlBulkCopy类的WriteToServer方法。 因为目的表示是Products_TopSelling，所以我们必须执行列映射。