SqlBulkCopyColumnMappingCollection.Clear Metode

Definisi

Ruang nama:

Microsoft.Data.SqlClient

Rakitan:

Microsoft.Data.SqlClient.dll

Paket:

Microsoft.Data.SqlClient v5.2.0

Menghapus isi koleksi.

public:
 void Clear();

public void Clear ();

override this.Clear : unit -> unit

Public Sub Clear ()

Contoh

Contoh berikut melakukan dua operasi penyalinan massal. Operasi pertama menyalin informasi header pesanan penjualan, dan yang kedua menyalin detail pesanan penjualan. Meskipun tidak benar-benar diperlukan dalam contoh ini (karena posisi ordinal kolom sumber dan tujuan cocok), contoh menentukan pemetaan kolom untuk setiap operasi penyalinan massal. Metode Clear harus digunakan setelah salinan massal pertama dilakukan dan sebelum pemetaan kolom salinan massal berikutnya ditentukan.

Penting

Sampel ini tidak akan berjalan kecuali Anda telah membuat tabel kerja seperti yang dijelaskan dalam Penyiapan Contoh Salinan Massal. Kode ini disediakan untuk mendemonstrasikan sintaks untuk menggunakan SqlBulkCopy saja. Jika tabel sumber dan tujuan berada dalam instans SQL Server yang sama, lebih mudah dan lebih cepat untuk menggunakan pernyataan Transact-SQL INSERT … SELECT untuk menyalin data.

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;";
    }
}

Keterangan

Metode Clear ini paling umum digunakan ketika Anda menggunakan satu SqlBulkCopy instans untuk memproses lebih dari satu operasi penyalinan massal. Jika Anda membuat pemetaan kolom untuk satu operasi penyalinan massal, Anda harus menghapus SqlBulkCopyColumnMappingCollection setelah WriteToServer metode dan sebelum memproses salinan massal berikutnya.

Melakukan beberapa salinan massal menggunakan instans yang sama SqlBulkCopy biasanya akan lebih efisien dari sudut pandang performa daripada menggunakan terpisah SqlBulkCopy untuk setiap operasi.