【学习笔记】SQL Server 中的批量复制操作 (ADO.NET)

注：本学习笔记资料来源于MSDN帮助文档

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

使用 SqlBulkCopy 类可以执行下列操作：

1. 单次批量复制操作
2. 多次批量复制操作
3. 事务中的批量复制操作
   
   提供 SqlBulkCopy 代码示例是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一个 SQL Server 实例中，则使用 Transact-SQL INSERT … SELECT 语句复制数据会更加容易、更加迅速。
    
   1. 连接到源服务器上并获取要复制的数据。 如果可以从 IDataReader 或 DataTable 对象检索数据，则这些数据还可能来自其他源。
   2. 连接到目标服务器（除非您希望 SqlBulkCopy 为您建立连接）。
   3. 创建一个 SqlBulkCopy 对象，设置任何必要的属性。
   4. 设置 DestinationTableName 属性以指示执行批量插入操作的目标表。
   5. 调用一个 WriteToServer 方法。
   6. 可以选择更新属性并根据需要再次调用 WriteToServer。
   7. 调用 Close，或将批量复制操作包装在 Using 语句中。

执行批量复制操作的一般步骤如下所示：

警告：

我们建议源列和目标列的数据类型匹配。 如果数据类型不匹配，则 SqlBulkCopy 会尝试使用由 Value 部署的规则将每个源值转换为目标数据类型。 转换可能会影响性能，还可能会导致意外的错误。 例如，大多数情况下，Double 数据类型可以转换为 Decimal 数据类型，但是有时就不能。

SqlBulkCopy 四种 构造函数

SqlBulkCopy(string )	SqlBulkCopy(String, SqlBulkCopyOptions)
SqlBulkCopy(SqlConnection)	SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

 

(1)SqlBulkCopy单次批量复制

（1）SqlBulkCopy 单次批量复制操作【示例】

     
using System.Data.SqlClient;

class Program
{
    staticvoid Main()
    {
        string connectionString = GetConnectionString();
        // Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();

            // Perform an initial count on the destination table.
            SqlCommand commandRowCount =new SqlCommand(
                "SELECT COUNT(*) FROM "+
                "dbo.BulkCopyDemoMatchingColumns;",
                sourceConnection);
            long countStart = System.Convert.ToInt32(
                commandRowCount.ExecuteScalar());
            Console.WriteLine("Starting row count = {0}", countStart);

            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData =new SqlCommand(
                "SELECT ProductID, Name, "+
                "ProductNumber "+
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            // Open the destination connection. In the real world you would 
            // not use SqlBulkCopy to move data from one table to the other 
            // in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();

                // Set up the bulk copy object. 
                // Note that the column positions in the source
                // data reader match the column positions in 
                // the destination table so there is no need to
                // map columns.
using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        // Write from the source to the destination.
                        bulkCopy.WriteToServer(reader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        // Close the SqlDataReader. The SqlBulkCopy
                        // object is automatically closed at the end
                        // of the using block.
                        reader.Close();
                    }
                }
                // Perform a final count on the destination 
                // table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
                    commandRowCount.ExecuteScalar());
                Console.WriteLine("Ending row count = {0}", countEnd);
                Console.WriteLine("{0} rows were added.", countEnd - countStart);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
    }
    privatestaticstring GetConnectionString()
        // To avoid storing the sourceConnection string in your code, 
        // you can retrieve it from a configuration file. 
    {
        return"Data Source=(local); "+
            " Integrated Security=true;"+
            "Initial Catalog=AdventureWorks;";
    }
}
          

 

 

（2）T-SQL 单次批量复制操作

using (SqlConnection connection = New SqlConnection(connectionString))

{

string queryString ="BULK INSERT Northwind.dbo.[Order Details] "+

    "FROM 'f:\mydata\data.tbl' "+

    "WITH ( FORMATFILE='f:\mydata\data.fmt' )";

connection.Open();

SqlCommand command =new SqlCommand(queryString, connection);



command.ExecuteNonQuery();

}

 

 

SqlBulkCopy 支持多次批量赋值操作注意：

使用 SqlBulkCopy 的相同实例执行多次批量复制操作通常比每个操作使用独立的实例更加有效。

如果使用相同的 SqlBulkCopy 对象执行多次批量复制操作，不会限制每个操作中的源信息或目标信息相同还是不同。 但是，必须确保每次写入服务器时正确设置了列关联信息。

可以使用 SqlBulkCopy 类的单个实例执行多次批量复制操作。 如果在两次复制之间更改了操作参数（例如目标表的名称），必须先更新这些参数，然后再进行对任何 WriteToServer 方法的后续调用，如下例中所示。 除非显式更改，否则，所有属性值都将与给定实例的上一次批量复制操作相同。

 

批量复制操作可以作为独立的操作执行，也可以作为多步事务的一部分执行。 后一种方式使您可以在同一事务中执行多个批量复制操作并执行其他数据库操作（例如插入、更新和删除），同时仍能够提交或回滚整个事务。

默认情况下，批量复制操作作为独立的操作执行。 批量复制操作以非事务性方式发生，不可能使其回滚。如果需要在出错时回滚全部批量复制或它的一部分，可以使用 SqlBulkCopy 托管的事务，在现有事务中执行批量复制操作，或者在 System.TransactionsTransaction 中登记它。

(3)在代码中执行专有事务的复制操作。	using (SqlBulkCopy bulkCopy =new SqlBulkCopy( connectionString, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.UseInternalTransaction))
（4）使用现有事务	using (SqlTransaction transaction = destinationConnection.BeginTransaction()) using (SqlBulkCopy bulkCopy =new SqlBulkCopy( destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction))