SqlBulkCopyColumnMappingCollection 类
定义
重要
一些信息与预发行产品相关，相应产品在发行之前可能会进行重大修改。 对于此处提供的信息，Microsoft 不作任何明示或暗示的担保。
从 CollectionBase 继承的 SqlBulkCopyColumnMapping 对象的集合。
public ref class SqlBulkCopyColumnMappingCollection sealed : System::Collections::IListpublic ref class SqlBulkCopyColumnMappingCollection sealed : System::Collections::CollectionBasepublic sealed class SqlBulkCopyColumnMappingCollection : System.Collections.IListpublic sealed class SqlBulkCopyColumnMappingCollection : System.Collections.CollectionBasetype SqlBulkCopyColumnMappingCollection = class
interface ICollection
interface IEnumerable
interface IListtype SqlBulkCopyColumnMappingCollection = class
inherit CollectionBasePublic NotInheritable Class SqlBulkCopyColumnMappingCollection
Implements IListPublic NotInheritable Class SqlBulkCopyColumnMappingCollection
Inherits CollectionBase- 继承
-
SqlBulkCopyColumnMappingCollection
- 继承
- 实现
示例
以下示例将数据从“AdventureWorks”示例数据库中的源表大容量复制到同一个数据库中的目标表。 尽管目标中的列数与源中的列数匹配，但列名称和序号位置不匹配。 SqlBulkCopyColumnMapping将 添加到 对象的 中SqlBulkCopyColumnMappingCollectionSqlBulkCopy，以便为大容量复制创建列映射。
重要
除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。 如果源表和目标表位于同一SQL Server实例中，则使用 Transact-SQL INSERT ... SELECT 语句复制数据会更轻松、更快。
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoDifferentColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();
// Set up the bulk copy object.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(connectionString))
{
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoDifferentColumns";
// The column order in the source doesn't match the order
// in the destination, so ColumnMappings must be defined.
bulkCopy.ColumnMappings.Add("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add("Name", "ProdName");
bulkCopy.ColumnMappings.Add("ProductNumber", "ProdNum");
// Write from the source to the destination.
try
{
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
reader.Close();
}
}
// Perform a final count on the destination
// table to see how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart);
Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection 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 sourceConnection As SqlConnection = _
New SqlConnection(connectionString)
sourceConnection.Open()
' Perform an initial count on the destination table.
Dim commandRowCount As New SqlCommand( _
"SELECT COUNT(*) FROM dbo.BulkCopyDemoDifferentColumns;", _
sourceConnection)
Dim countStart As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Starting row count = {0}", countStart)
' Get data from the source table as a SqlDataReader.
Dim commandSourceData As SqlCommand = New SqlCommand( _
"SELECT ProductID, Name, ProductNumber " & _
"FROM Production.Product;", sourceConnection)
Dim reader As SqlDataReader = commandSourceData.ExecuteReader
' Set up the bulk copy object.
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
bulkCopy.DestinationTableName = _
"dbo.BulkCopyDemoDifferentColumns"
' The column order in the source doesn't match the order
' in the destination, so ColumnMappings must be defined.
bulkCopy.ColumnMappings.Add("ProductID", "ProdID")
bulkCopy.ColumnMappings.Add("Name", "ProdName")
bulkCopy.ColumnMappings.Add("ProductNumber", "ProdNum")
' Write from the source to the destination.
Try
bulkCopy.WriteToServer(reader)
Catch ex As Exception
Console.WriteLine(ex.Message)
Finally
' Close the SqlDataReader. The SqlBulkCopy
' object is automatically closed at the end
' of the Using block.
reader.Close()
End Try
End Using
' Perform a final count on the destination table
' to see how many rows were added.
Dim countEnd As Long = _
System.Convert.ToInt32(commandRowCount.ExecuteScalar())
Console.WriteLine("Ending row count = {0}", countEnd)
Console.WriteLine("{0} rows were added.", countEnd - countStart)
Console.WriteLine("Press Enter to finish.")
Console.ReadLine()
End Using
End Sub
Private Function GetConnectionString() As String
' To avoid storing the sourceConnection 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
注解
列映射定义数据源和目标表之间的映射。
如果未定义映射（即 ColumnMappings 集合为空），则根据序号位置隐式映射列。 对于这种方式，源架构和目标架构必须匹配。 否则， InvalidOperationException 将引发 。
ColumnMappings如果集合不为空，则不必指定数据源中存在的每一列。 未由集合映射的那些将被忽略。
可以按名称或序号引用源列和目标列。 可以在同一映射集合中混合按名称和按序号列引用。
属性
| Capacity |
获取或设置 CollectionBase 可包含的元素数。 (继承自 CollectionBase) |
| Count |
获取 SqlBulkCopyColumnMappingCollection 中包含的元素数。 |
| Count |
获取 CollectionBase 实例中包含的元素数。 不能重写此属性。 (继承自 CollectionBase) |
| InnerList |
获取一个 ArrayList，它包含 CollectionBase 实例中元素的列表。 (继承自 CollectionBase) |
| Item[Int32] |
获取位于指定索引位置的 SqlBulkCopyColumnMapping 对象。 |
| List |
获取一个 IList，它包含 CollectionBase 实例中元素的列表。 (继承自 CollectionBase) |
方法
显式接口实现
扩展方法
| Cast<TResult>(IEnumerable) |
将 IEnumerable 的元素强制转换为指定的类型。 |
| OfType<TResult>(IEnumerable) |
根据指定类型筛选 IEnumerable 的元素。 |
| AsParallel(IEnumerable) |
启用查询的并行化。 |
| AsQueryable(IEnumerable) |
将 IEnumerable 转换为 IQueryable。 |
