.NET中的SqlBulkCopy类用法

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

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

以下面三张表为例：

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

从Products_Archive表复制数据到 Products_Latest表：

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

 1 private static void PerformBulkCopy()
 2 {
 3   //创建数据库连接
 4  string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
 5 
 6 //源数据表
 7   using (SqlConnection sourceConnection = new SqlConnection(connectionString))
 8  {
 9    SqlCommand myCommand = new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);
10              sourceConnection.Open();
11         SqlDataReader reader = myCommand.ExecuteReader(); 
12  
13   //目的表
14    using (SqlConnection destinationConnection = new SqlConnection(connectionString))
15    {
16  // 打开连接
17      destinationConnection.Open();
18   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
19      {
20        bulkCopy.BatchSize = 500;
21 
22    bulkCopy.NotifyAfter = 1000;
23 
24        bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
25 
26        bulkCopy.DestinationTableName = "Products_Latest";
27 
28        bulkCopy.WriteToServer(reader);           
29 
30 
31 
32      }
33    }
34   reader.Close();  
35 
36  }
37 
38 }

这里有一对需要提及的知识点。首先，我使用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表里

 1 private static void PerformBulkCopyDifferentSchema()
 2 
 3 {
 4 
 5  string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
 6 
 7  DataTable sourceData = new DataTable();
 8 
 9 // 源
10 
11  using (SqlConnection sourceConnection = new SqlConnection(connectionString))
12 
13  {
14 
15   SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);
16 
17    sourceConnection.Open();
18 
19    SqlDataReader reader = myCommand.ExecuteReader();
20 
21    // 目的
22 
23    using (SqlConnection destinationConnection = new SqlConnection(connectionString))
24 
25    {
26 
27  // 打开连接
28 
29      destinationConnection.Open();
30 
31  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
32 
33      {
34 
35        bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
36 
37        bulkCopy.ColumnMappings.Add("ProductName", "Name");
38 
39        bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");
40 
41        bulkCopy.DestinationTableName = "Products_TopSelling";
42 
43        bulkCopy.WriteToServer(reader);
44 
45      }
46 
47 
48    }
49 
50  reader.Close();
51 
52  }
53 
54 }

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>

 1 private static void PerformBulkCopyXMLDataSource()
 2 
 3 {
 4 
 5  string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";
 6 
 7  DataSet ds = new DataSet();
 8 
 9  DataTable sourceData = new DataTable();
10 
11  ds.ReadXml(@"C:Products.xml");
12 
13  sourceData = ds.Tables[0];
14 
15  // 目的
16 
17  using (SqlConnection destinationConnection = new SqlConnection(connectionString))
18 
19  {
20 
21    // 打开连接
22 
23    destinationConnection.Open();
24 
25    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
26 
27    {
28 
29      // 列映射
30 
31      bulkCopy.ColumnMappings.Add("productID", "ProductID");
32 
33      bulkCopy.ColumnMappings.Add("productName", "Name");
34 
35                    
36 
37      bulkCopy.DestinationTableName = "Products_TopSelling";
38 
39      bulkCopy.WriteToServer(sourceData);
40 
41    }
42 
43  }
44 
45 }

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

.NET中的SqlBulkCopy类用法

推荐阅读