SqlBulkCopyColumnMappingCollection.Clear Méthode

public:
 virtual void Clear();

public:
 void Clear();

public void Clear ();

abstract member Clear : unit -> unit
override this.Clear : unit -> unit

override this.Clear : unit -> unit

Public Sub Clear ()

Implémente

Exemples

L’exemple suivant effectue deux opérations de copie en bloc. La première opération copie les informations d’en-tête de commande client, et la seconde copie les détails de la commande client. Bien que cela ne soit pas strictement nécessaire dans cet exemple (car les positions ordinales des colonnes source et de destination correspondent), l’exemple définit des mappages de colonnes pour chaque opération de copie en bloc. La Clear méthode doit être utilisée après la première copie en bloc et avant la définition des mappages de colonnes de la copie en bloc suivante.

using System.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;";
    }
}

Imports System.Data.SqlClient

Module Module1
    Sub Main()
        Dim connectionString As String = GetConnectionString()

        ' Open a connection to the AdventureWorks database.
        Using connection As SqlConnection = New SqlConnection(connectionString)
            connection.Open()

            ' Empty the destination tables.
            Dim deleteHeader As New SqlCommand( _
              "DELETE FROM dbo.BulkCopyDemoOrderHeader;", connection)
            deleteHeader.ExecuteNonQuery()
            deleteHeader.Dispose()
            Dim deleteDetail As New SqlCommand( _
              "DELETE FROM dbo.BulkCopyDemoOrderDetail;", connection)
            deleteDetail.ExecuteNonQuery()

            ' Perform an initial count on the destination table 
            ' with matching columns.
            Dim countRowHeader As New SqlCommand( _
               "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderHeader;", _
                connection)
            Dim countStartHeader As Long = 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.
            Dim countRowDetail As New SqlCommand( _
                "SELECT COUNT(*) FROM dbo.BulkCopyDemoOrderDetail;", _
                connection)
            Dim countStartDetail As Long = System.Convert.ToInt32( _
                countRowDetail.ExecuteScalar())
            Console.WriteLine("Starting row count for Detail table = " & _
               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.
            Dim headerData As SqlCommand = New SqlCommand( _
             "SELECT [SalesOrderID], [OrderDate], " & _
             "[AccountNumber] FROM [Sales].[SalesOrderHeader] " & _
             "WHERE [AccountNumber] = @accountNumber;", _
             connection)

            Dim parameterAccount As SqlParameter = New SqlParameter()
            parameterAccount.ParameterName = "@accountNumber"
            parameterAccount.SqlDbType = SqlDbType.NVarChar
            parameterAccount.Direction = ParameterDirection.Input
            parameterAccount.Value = "10-4020-000034"
            headerData.Parameters.Add(parameterAccount)

            Dim readerHeader As SqlDataReader = _
             headerData.ExecuteReader()

            ' Get the Detail data in a separate connection.
            Using connection2 As SqlConnection = New SqlConnection(connectionString)
                connection2.Open()

                Dim sourceDetailData As SqlCommand = 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)

                Dim accountDetail As SqlParameter = New SqlParameter()
                accountDetail.ParameterName = "@accountNumber"
                accountDetail.SqlDbType = SqlDbType.NVarChar
                accountDetail.Direction = ParameterDirection.Input
                accountDetail.Value = "10-4020-000034"
                sourceDetailData.Parameters.Add( _
                 accountDetail)

                Dim readerDetail As SqlDataReader = _
                 sourceDetailData.ExecuteReader()

                ' Create the SqlBulkCopy object.
                Using bulkCopy As SqlBulkCopy = _
                  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 ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        readerHeader.Close()
                    End Try

                    ' 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 ex As Exception
                        Console.WriteLine(ex.Message)
                    Finally
                        readerDetail.Close()
                    End Try
                End Using

                ' Perform a final count on the destination tables
                ' to see how many rows were added.
                Dim countEndHeader As Long = System.Convert.ToInt32( _
                  countRowHeader.ExecuteScalar())
                Console.WriteLine("{0} rows were added to the Header table.", _
                  countEndHeader - countStartHeader)
                Dim countEndDetail As Long = 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()
            End Using
        End Using
    End Sub

    Private Function GetConnectionString() As String
        ' 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;"
    End Function
End Module

Remarques

La Clear méthode est généralement utilisée lorsque vous utilisez une seule SqlBulkCopy instance pour traiter plusieurs opérations de copie en bloc. Si vous créez des mappages de colonnes pour une opération de copie en bloc, vous devez effacer après SqlBulkCopyColumnMappingCollection la WriteToServer méthode et avant de traiter la copie en bloc suivante.

L’exécution de plusieurs copies en bloc à l’aide du même SqlBulkCopy instance est généralement plus efficace du point de vue des performances que l’utilisation d’une copie distincte SqlBulkCopy pour chaque opération.

S’applique à

Voir aussi

• Opérations de copie en bloc dans SQL Server
• Vue d'ensemble d’ADO.NET