SqlBulkCopyColumnMappingCollection.RemoveAt(Int32) 方法
定义
重要
一些信息与预发行产品相关，相应产品在发行之前可能会进行重大修改。 对于此处提供的信息，Microsoft 不作任何明示或暗示的担保。
从集合中移除指定索引处的映射。
public:
void RemoveAt(int index);public:
virtual void RemoveAt(int index);public void RemoveAt (int index);override this.RemoveAt : int -> unitabstract member RemoveAt : int -> unit
override this.RemoveAt : int -> unitPublic 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 副本更高效。
