SqlBulkCopyColumnMappingCollection.RemoveAt(Int32) 方法

定义

从集合中移除指定索引处的映射。

public:
 void RemoveAt(int index);
public:
 virtual void RemoveAt(int index);
public void RemoveAt (int index);
override this.RemoveAt : int -> unit
abstract member RemoveAt : int -> unit
override this.RemoveAt : int -> unit
Public Sub RemoveAt (index As Integer)

参数

index
Int32

要从集合中移除的 SqlBulkCopyColumnMapping 对象的从零开始的索引。

实现

示例

以下示例执行两个大容量复制操作。 第一个操作复制销售订单标头信息,第二个操作复制销售订单详细信息。 虽然在本示例中, (因为源列和目标列的序号位置与) 匹配,但此示例定义每个大容量复制操作的列映射。 这两个大容量复制都包含 SalesOrderID 的映射,因此本示例不会在大容量复制操作之间清除整个集合,而是删除 除 SalesOrderID 映射之外的所有映射,然后为第二个大容量复制操作添加相应的映射。

重要

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

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

                    // Rather than clearing mappings that are not necessary
                    // for the next bulk copyo peration, the unneeded mappings
                    //  are removed with the RemoveAt method.
                    bulkCopy.ColumnMappings.RemoveAt(2);
                    bulkCopy.ColumnMappings.RemoveAt(1);

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

                    // 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"

                    ' Rather than clearing mappings that are not necessary
                    ' for the next bulk copy operation, the unneeded         
                    ' mappings are removed with the RemoveAt method.
                    bulkCopy.ColumnMappings.RemoveAt(2)
                    bulkCopy.ColumnMappings.RemoveAt(1)

                    ' Add order detail column mappings.
                    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

注解

RemoveAt使用单个SqlBulkCopy实例处理多个大容量复制操作时,最常使用 方法。 如果为一个大容量复制操作创建列映射,则必须在调用 方法后 WriteToServer 以及定义下一个大容量复制的映射之前删除不再应用的映射。 可以使用 方法清除整个集合Clear,或使用 方法RemoveAt单独Remove删除映射。

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

适用于

另请参阅