使用类SqlBulkCopy转存数据

原博文地址：http://gridviewguy.com/ArticleDetails.aspx?articleID=233 

前言

由于本人单薄的英语水平，可能在文章中有很多翻译错误，请好心人斧正。由于没有和原创进行商量我也不知道这样算不算是侵犯作者的版权。不过相信这位外国老兄不会怪我，我是学习用的。呵呵。

 

Transferring Data Using SqlBulkCopyClass

By AzamSharp Views: 1242

Introduction:     Transferring data from one source to another is a common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to SQL SERVER database. In this article I will demonstrate the different aspects of the SqlBulkCopy class.   序言：  在软件开发过程从一个数据源到另一数据源转存数据是一种最常见的操作 。有几种不同的情形如从旧系统到系统的迁移，备份数据，从不同的发布者收集数据。ASP.Net 2.0 包括了SqlBulkCopy这个类，可以帮助从不同的数据源复制数据到SqlServer数据库。这篇文章我将展示SqlBulkCopy类的不同应用。   Database Design:   The database design is pretty simple as it is based on the Products table in the Northwind database. I have created three more tables in the Northwind database. Check out the database diagram below to have better idea.   数据库设计   下面这个数据库设计十分简单，因为是建在的Northwind 数据库Products表基础上的。我在northwind这个数据库上多建立了三个表格 。 看看下面的数据库表格你会有一个更清楚地理解。     The Products_Archive and Products_Latest have the same schema as the Products table while the Products_TopSelling table is different. I will explain the purpose of Products_TopSelling table later in this article.   表 Products_Archive 和 Products_Latest 与 Products 表有一样的结构，同时 表Products_TopSelling 有所不同。在文章的后面我将介绍建立表 Products_TopSelling 的目的．   The Products_Archive table contains 770,000 rows. You don’t have to worry about how the rows got there; you just need to think how to move all those rows in the Products_Latest table. 表Products_Archive拥有770，000行，你不必关心这些数据是怎样生成的，你只需要想怎样把这些数据转移到表Products_Latest中。   Transferring Data from Products_Archive to Products_Latest:   转存数据从Products_Archive 到Products_Latest   SqlBulkCopy contains an instance method WriteToServer which is used to transfer the data from the source to the destination. WriteToServer method can perform action of DataRow[] array, DataTable and DataReader. Depending on the situation you can choose the container you like but in most cases choosing DataReader is a good idea. This is because DataReader is a forward-only, read-only stream. It does not hold the data and thus is much faster then DataTable and DataRows[]. The code below is used to transfer the data from the source table to the destination table.   SqlBulkCopy包含一个用来从源到目标源转存数据实例方法WriteToServer 。WriteToServer 方法可以执行 DataRow[] array, DataTable 和 DataReader的行为。你可以选择你自己喜欢的容器。但是大部分情形选择DataReader 是一个非常不错的主意。 因为DataReader是一种只能向前，只读的类型。它不持有数据所以比DataTable 和DataRows[]更快。下面的代码是用来从源数据到目标数据源转存数据的。 private static void PerformBulkCopy()         {             string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";                                                 // get the source data             using (SqlConnection sourceConnection = new SqlConnection(connectionString))             {                   SqlCommand myCommand = new SqlCommand("SELECT * FROM Products_Archive", sourceConnection);                 sourceConnection.Open();                 SqlDataReader reader = myCommand.ExecuteReader();                                      // open the destination data             using (SqlConnection destinationConnection = new SqlConnection(connectionString))             {                 // open the connection                 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();                                             }                      }   There are couple of points to mention here. First I am using the DataReader to fetch to the rows from the database table. SqlBulkCopy class object “bulkCopy” sets the DestinationTableName property to the destination table which in this case is “Products_Latest”. Products_Latest is the destination table since the data is transferred from the Products_Archive table to the Products_Latest table. The bulkCopy object also exposes the SqlRowsCopied event which is fired after the rows identified by the NotifyAfter property has reached. This means the event will be fired after every 1000 rows since NotifyAfter is set to 1000.  这有几点我要提到。首先我使用了DataReader从数据库中获取数据行，类SqlBulkCopy的实例“bulkCopy” 使用属性DestinationTableName来设置目标表格，这个实例中我们使用“Products_Latest””，Products_Latest是目标表格，因为数据从表Products_Archive转存到表Products_Latest表。bulkCopy对象公开了SqlRowsCopied事件，将在NotifyAfter属性后面执行。意味着每1000行事件都会执行一次，应为NotifyAfter属性被设置成1000了 The BatchSize property is very important as most of the performance depends on it. The BatchSize means that how many rows will be send to the database at one time to initiate the data transfer. I have set the BatchSize to 500 which means that once, the reader has read 500 rows they will be sent to the database to perform the bulk copy operation. By default the BatchSize is “1” which means that each row is sent to the database as a single batch.   BatchSize 属性非常重要，因为大部分的执行。因为它代表着一次将有多少行数据转存到数据库中。 我设置BatchSize=500 那就意味着如果reader已经读取了500行。数据将被送到数据库进行大容量复制 默认的BatchSize是等于1意味着每读取一行就转存到数据库。 Different BatchSize will give you different results. You should test that which batch size suits your needs.   不同的BatchSize 将给你不同的结果，你应该测试一下多大的批量数据是最适合你的需求的。 Transferring Data Between Tables of Different Mappings: In the above example both the tables had the same schema. Sometimes, you need to transfer the data between tables whose schema is different. Suppose you want to transfer all the product name and quantity from the Products_Archive table to the Products_TopSelling table. The schema in the two tables is different as they have different column names. This is also visible in the image above under the database design section. 在上面的例子中我们是在两个有同样结构的表格中转存数据，有时候我们需要在有不同结构的表格中传递数据。假设你将从表Products_Archive中转存所有的产品名称和数量到表Products_Archive两个表格的模式不同因为它们有不同的列名称。这可以从上面数据库设计图的下面看出   private static void PerformBulkCopyDifferentSchema()         {               string connectionString = @"Server=localhost;Database=Northwind;Trusted_Connection=true";               DataTable sourceData = new DataTable();               // get the source data             using (SqlConnection sourceConnection = new SqlConnection(connectionString))             {                   SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);                 sourceConnection.Open();                 SqlDataReader reader = myCommand.ExecuteReader();                   // open the destination data                 using (SqlConnection destinationConnection = new SqlConnection(connectionString))                 {                     // open the connection                     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();               }                    }   The ColumnMappings collection is used to map the column between the source table and the destination table. 集合ColumnMappings 被用来做原数据和目标数据的集合。   Transferring Data from XML File to Database Table: 从xml文件向数据表格转存数据 The data source is not only limited to database tables but you can also use XML files. Here is a very simple XML file which is used as a source for the bulk copy operation.   数据源不是仅仅限制在数据库表格，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];               // open the destination data             using (SqlConnection destinationConnection = new SqlConnection(connectionString))             {                 // open the connection                 destinationConnection.Open();                   using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))                 {                     // column mappings                     bulkCopy.ColumnMappings.Add("productID", "ProductID");                     bulkCopy.ColumnMappings.Add("productName", "Name");                                         bulkCopy.DestinationTableName = "Products_TopSelling";                     bulkCopy.WriteToServer(sourceData);                 }             }           }   The file is first read into the DataTable and then fed to the WriteToServer method of the SqlBulkCopy class. Since, the destination table is Products_TopSelling we had to perform the column mapping.   Conclusion:   In this article I demonstrated how to use the SqlBulkCopy class which is introduced in .NET 2.0. SqlBulkCopy class makes it easier to transfer the data from a source to the SQL SERVER database.   I hope you liked the article, happy coding!