SqlBulkCopyColumnMappingCollection.Clear 方法

定义

命名空间:

Microsoft.Data.SqlClient

程序集:

Microsoft.Data.SqlClient.dll

包:

Microsoft.Data.SqlClient v5.2.0

清除集合的内容。

public:
 void Clear();

public void Clear ();

override this.Clear : unit -> unit

Public Sub Clear ()

示例

以下示例执行两个大容量复制操作。 第一个操作复制销售订单标头信息，第二个操作复制销售订单详细信息。 虽然此示例中不是绝对必需的 (因为源列和目标列的序号位置与) 匹配，但该示例为每个大容量复制操作定义列映射。 Clear在执行第一个大容量复制之后和定义下一个大容量复制的列映射之前，必须使用 方法。

重要

除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一SQL Server实例中，则使用 Transact-SQL INSERT … SELECT 语句复制数据会更轻松、更快。

using Microsoft.Data.SqlClient;

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

            // Empty the destination tables. 
            SqlCommand deleteHeader = new SqlCommand(
                "DELETE FROM dbo.BulkCopyDemoOrderHeader;",
                connection);
            deleteHeader.ExecuteNonQuery();
            SqlCommand deleteDetail = new SqlCommand(
                "DELETE FROM dbo.BulkCopyDemoOrderDetail;",
                connection);
            deleteDetail.ExecuteNonQuery();

            // Perform an initial count on the destination
            //  table with matching columns. 
            SqlCommand countRowHeader = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;",
                connection);
            long countStartHeader = System.Convert.ToInt32(
                countRowHeader.ExecuteScalar());
            Console.WriteLine(
                "Starting row count for Header table = {0}",
                countStartHeader);

            // Perform an initial count on the destination
            // table with different column positions. 
            SqlCommand countRowDetail = new SqlCommand(
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;",
                connection);
            long countStartDetail = System.Convert.ToInt32(
                countRowDetail.ExecuteScalar());
            Console.WriteLine(
                "Starting row count for Detail table = {0}",
                countStartDetail);

            // Get data from the source table as a SqlDataReader.
            // The Sales.SalesOrderHeader and Sales.SalesOrderDetail
            // tables are quite large and could easily cause a timeout
            // if all data from the tables is added to the destination. 
            // To keep the example simple and quick, a parameter is  
            // used to select only orders for a particular account 
            // as the source for the bulk insert. 
            SqlCommand headerData = new SqlCommand(
                "SELECT [SalesOrderID], [OrderDate], " +
                "[AccountNumber] FROM [Sales].[SalesOrderHeader] " +
                "WHERE [AccountNumber] = @accountNumber;",
                connection);
            SqlParameter parameterAccount = new SqlParameter();
            parameterAccount.ParameterName = "@accountNumber";
            parameterAccount.SqlDbType = SqlDbType.NVarChar;
            parameterAccount.Direction = ParameterDirection.Input;
            parameterAccount.Value = "10-4020-000034";
            headerData.Parameters.Add(parameterAccount);
            SqlDataReader readerHeader = headerData.ExecuteReader();

            // Get the Detail data in a separate connection.
            using (SqlConnection connection2 = new SqlConnection(connectionString))
            {
                connection2.Open();
                SqlCommand sourceDetailData = new SqlCommand(
                    "SELECT [Sales].[SalesOrderDetail].[SalesOrderID], [SalesOrderDetailID], " +
                    "[OrderQty], [ProductID], [UnitPrice] FROM [Sales].[SalesOrderDetail] " +
                    "INNER JOIN [Sales].[SalesOrderHeader] ON [Sales].[SalesOrderDetail]." +
                    "[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID] " +
                    "WHERE [AccountNumber] = @accountNumber;", connection2);

                SqlParameter accountDetail = new SqlParameter();
                accountDetail.ParameterName = "@accountNumber";
                accountDetail.SqlDbType = SqlDbType.NVarChar;
                accountDetail.Direction = ParameterDirection.Input;
                accountDetail.Value = "10-4020-000034";
                sourceDetailData.Parameters.Add(accountDetail);
                SqlDataReader readerDetail = sourceDetailData.ExecuteReader();

                // Create the SqlBulkCopy object. 
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(connectionString))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoOrderHeader";

                    // Guarantee that columns are mapped correctly by
                    // defining the column mappings for the order.
                    bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
                    bulkCopy.ColumnMappings.Add("OrderDate", "OrderDate");
                    bulkCopy.ColumnMappings.Add("AccountNumber", "AccountNumber");

                    // Write readerHeader to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(readerHeader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        readerHeader.Close();
                    }

                    // Set up the order details destination. 
                    bulkCopy.DestinationTableName = "dbo.BulkCopyDemoOrderDetail";

                    // Clear the ColumnMappingCollection.
                    bulkCopy.ColumnMappings.Clear();

                    // Add order detail column mappings.
                    bulkCopy.ColumnMappings.Add("SalesOrderID", "SalesOrderID");
                    bulkCopy.ColumnMappings.Add("SalesOrderDetailID", "SalesOrderDetailID");
                    bulkCopy.ColumnMappings.Add("OrderQty", "OrderQty");
                    bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
                    bulkCopy.ColumnMappings.Add("UnitPrice", "UnitPrice");

                    // Write readerDetail to the destination.
                    try
                    {
                        bulkCopy.WriteToServer(readerDetail);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        readerDetail.Close();
                    }
                }

                // Perform a final count on the destination
                // tables to see how many rows were added. 
                long countEndHeader = System.Convert.ToInt32(
                    countRowHeader.ExecuteScalar());
                Console.WriteLine("{0} rows were added to the Header table.",
                    countEndHeader - countStartHeader);
                long countEndDetail = System.Convert.ToInt32(
                    countRowDetail.ExecuteScalar());
                Console.WriteLine("{0} rows were added to the Detail table.",
                    countEndDetail - countStartDetail);
                Console.WriteLine("Press Enter to finish.");
                Console.ReadLine();
            }
        }
    }

    private static string GetConnectionString()
    // To avoid storing the connection string in your code, 
    // you can retrieve it from a configuration file. 
    {
        return "Data Source=(local); " +
            " Integrated Security=true;" +
            "Initial Catalog=AdventureWorks;";
    }
}

注解

Clear使用单个SqlBulkCopy实例处理多个大容量复制操作时，最常使用 方法。 如果为一个大容量复制操作创建列映射，则必须在 处理下一个大容量复制之前清除 SqlBulkCopyColumnMappingCollection 方法后面的 WriteToServer 。

从性能的角度来看，使用同 SqlBulkCopy 一实例执行多个大容量复制通常比对每个操作使用单独 SqlBulkCopy 复制更有效。