SqlBulkCopyColumnMappingCollection Class

Definition

CollectionBase 继承的 SqlBulkCopyColumnMapping 对象的集合。Collection of SqlBulkCopyColumnMapping objects that inherits from CollectionBase.

public ref class SqlBulkCopyColumnMappingCollection sealed : System::Collections::CollectionBase
public ref class SqlBulkCopyColumnMappingCollection sealed : System::Collections::IList
public sealed class SqlBulkCopyColumnMappingCollection : System.Collections.CollectionBase
public sealed class SqlBulkCopyColumnMappingCollection : System.Collections.IList
type SqlBulkCopyColumnMappingCollection = class
    inherit CollectionBase
type SqlBulkCopyColumnMappingCollection = class
    interface ICollection
    interface IEnumerable
    interface IList
Public NotInheritable Class SqlBulkCopyColumnMappingCollection
Inherits CollectionBase
Public NotInheritable Class SqlBulkCopyColumnMappingCollection
Implements IList
Inheritance
SqlBulkCopyColumnMappingCollection
Inheritance
SqlBulkCopyColumnMappingCollection

Examples

下面的示例将数据从AdventureWorks示例数据库中的源表大容量复制到同一个数据库中的目标表。The following example bulk copies data from a source table in the AdventureWorks sample database to a destination table in the same database. 尽管目标中的列数与源中的列数匹配,但列名称和序号位置不匹配。Although the number of columns in the destination matches the number of columns in the source, the column names and ordinal positions do not match. SqlBulkCopyColumnMapping 将添加到 SqlBulkCopy 对象的 SqlBulkCopyColumnMappingCollection 中,以创建大容量复制的列映射。SqlBulkCopyColumnMapping are added to the SqlBulkCopyColumnMappingCollection for the SqlBulkCopy object to create a column map for the bulk copy.

Important

除非已按照大容量复制示例设置中所述创建了工作表,否则此示例将不会运行。This sample will not run unless you have created the work tables as described in Bulk Copy Example Setup. 提供此代码是为了演示仅使用 SqlBulkCopy 时的语法。This code is provided to demonstrate the syntax for using SqlBulkCopy only. 如果源表和目标表位于同一个 SQL Server 实例中,则使用 Transact-sql INSERT … SELECT 语句复制数据会更加简单快捷。If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

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

Remarks

列映射定义数据源和目标表之间的映射。Column mappings define the mapping between data source and the target table.

如果未定义映射(即,ColumnMappings 集合为空),则根据序号位置隐式映射列。If mappings are not defined - that is, the ColumnMappings collection is empty - the columns are mapped implicitly based on ordinal position. 对于这种方式,源架构和目标架构必须匹配。For this to work, source and target schemas must match. 否则,会引发 InvalidOperationExceptionIf they do not, an InvalidOperationException is thrown.

如果 ColumnMappings 集合不为空,则必须指定数据源中存在的每一列。If the ColumnMappings collection is not empty, not every column present in the data source has to be specified. 被忽略的不是由集合映射的。Those not mapped by the collection are ignored.

可以按名称或序号引用源列和目标列。You can refer to source and target columns by either name or ordinal. 可以在同一个映射集合中混合按名称和按顺序的列引用。You can mix by-name and by-ordinal column references in the same mappings collection.

Properties

Capacity

获取或设置 CollectionBase 可包含的元素数。Gets or sets the number of elements that the CollectionBase can contain.

(Inherited from CollectionBase)
Count
Count

获取 CollectionBase 实例中包含的元素数。Gets the number of elements contained in the CollectionBase instance. 不能重写此属性。This property cannot be overridden.

(Inherited from CollectionBase)
InnerList

获取一个 ArrayList,它包含 CollectionBase 实例中元素的列表。Gets an ArrayList containing the list of elements in the CollectionBase instance.

(Inherited from CollectionBase)
Item[Int32]

获取位于指定索引位置的 SqlBulkCopyColumnMapping 对象。Gets the SqlBulkCopyColumnMapping object at the specified index.

List

获取一个 IList,它包含 CollectionBase 实例中元素的列表。Gets an IList containing the list of elements in the CollectionBase instance.

(Inherited from CollectionBase)

Methods

Add(Int32, Int32)

通过使用序号指定源列和目标列,创建一个新的 SqlBulkCopyColumnMapping 并将其添加到集合中。Creates a new SqlBulkCopyColumnMapping and adds it to the collection, using ordinals to specify both source and destination columns.

Add(Int32, String)

通过对源列使用序号和对目标列使用字符串,创建一个新的 SqlBulkCopyColumnMapping 并将其添加到集合中。Creates a new SqlBulkCopyColumnMapping and adds it to the collection, using an ordinal for the source column and a string for the destination column.

Add(SqlBulkCopyColumnMapping)

将指定的映射添加到 SqlBulkCopyColumnMappingCollection 中。Adds the specified mapping to the SqlBulkCopyColumnMappingCollection.

Add(String, Int32)

通过使用列名称描述源列,同时使用序号指定目标列,从而创建一个新的 SqlBulkCopyColumnMapping 并将其添加到集合中。Creates a new SqlBulkCopyColumnMapping and adds it to the collection, using a column name to describe the source column and an ordinal to specify the destination column.

Add(String, String)

通过使用列名称指定源列和目标列,创建一个新的 SqlBulkCopyColumnMapping 并将其添加到集合中。Creates a new SqlBulkCopyColumnMapping and adds it to the collection, using column names to specify both source and destination columns.

Clear()

清除集合的内容。Clears the contents of the collection.

Contains(SqlBulkCopyColumnMapping)

获取一个值,该值指示集合中是否存在指定的 SqlBulkCopyColumnMapping 对象。Gets a value indicating whether a specified SqlBulkCopyColumnMapping object exists in the collection.

CopyTo(SqlBulkCopyColumnMapping[], Int32)

从特定索引处开始,将 SqlBulkCopyColumnMappingCollection 的元素复制到 SqlBulkCopyColumnMapping 项的数组。Copies the elements of the SqlBulkCopyColumnMappingCollection to an array of SqlBulkCopyColumnMapping items, starting at a particular index.

Equals(Object)

确定指定的对象是否等于当前对象。Determines whether the specified object is equal to the current object.

(Inherited from Object)
GetEnumerator()
GetEnumerator()

返回循环访问 CollectionBase 实例的枚举器。Returns an enumerator that iterates through the CollectionBase instance.

(Inherited from CollectionBase)
GetHashCode()

作为默认哈希函数。Serves as the default hash function.

(Inherited from Object)
GetType()

获取当前实例的 TypeGets the Type of the current instance.

(Inherited from Object)
IndexOf(SqlBulkCopyColumnMapping)

获取指定 SqlBulkCopyColumnMapping 对象的索引。Gets the index of the specified SqlBulkCopyColumnMapping object.

Insert(Int32, SqlBulkCopyColumnMapping)

在指定索引处插入一个新 SqlBulkCopyColumnMappingInsert a new SqlBulkCopyColumnMapping at the index specified.

MemberwiseClone()

创建当前 Object 的浅表副本。Creates a shallow copy of the current Object.

(Inherited from Object)
OnClear()

清除 CollectionBase 实例的内容时执行其他自定义进程。Performs additional custom processes when clearing the contents of the CollectionBase instance.

(Inherited from CollectionBase)
OnClearComplete()

在清除 CollectionBase 实例的内容之后执行其他自定义进程。Performs additional custom processes after clearing the contents of the CollectionBase instance.

(Inherited from CollectionBase)
OnInsert(Int32, Object)

在向 CollectionBase 实例中插入新元素之前执行其他自定义进程。Performs additional custom processes before inserting a new element into the CollectionBase instance.

(Inherited from CollectionBase)
OnInsertComplete(Int32, Object)

在向 CollectionBase 实例中插入新元素之后执行其他自定义进程。Performs additional custom processes after inserting a new element into the CollectionBase instance.

(Inherited from CollectionBase)
OnRemove(Int32, Object)

当从 CollectionBase 实例移除元素时执行其他自定义进程。Performs additional custom processes when removing an element from the CollectionBase instance.

(Inherited from CollectionBase)
OnRemoveComplete(Int32, Object)

在从 CollectionBase 实例中移除元素之后执行其他自定义进程。Performs additional custom processes after removing an element from the CollectionBase instance.

(Inherited from CollectionBase)
OnSet(Int32, Object, Object)

当在 CollectionBase 实例中设置值之前执行其他自定义进程。Performs additional custom processes before setting a value in the CollectionBase instance.

(Inherited from CollectionBase)
OnSetComplete(Int32, Object, Object)

当在 CollectionBase 实例中设置值后执行其他自定义进程。Performs additional custom processes after setting a value in the CollectionBase instance.

(Inherited from CollectionBase)
OnValidate(Object)

当验证值时执行其他自定义进程。Performs additional custom processes when validating a value.

(Inherited from CollectionBase)
Remove(SqlBulkCopyColumnMapping)

SqlBulkCopyColumnMapping 中移除指定的 SqlBulkCopyColumnMappingCollection 元素。Removes the specified SqlBulkCopyColumnMapping element from the SqlBulkCopyColumnMappingCollection.

RemoveAt(Int32)

从集合中移除指定索引处的映射。Removes the mapping at the specified index from the collection.

ToString()

返回表示当前对象的字符串。Returns a string that represents the current object.

(Inherited from Object)

Explicit Interface Implementations

ICollection.CopyTo(Array, Int32)
ICollection.CopyTo(Array, Int32)

从目标数组的指定索引处开始将整个 CollectionBase 复制到兼容的一维 ArrayCopies the entire CollectionBase to a compatible one-dimensional Array, starting at the specified index of the target array.

(Inherited from CollectionBase)
ICollection.IsSynchronized
ICollection.IsSynchronized

获取一个值,该值指示是否同步对 CollectionBase 的访问(确保线程安全)。Gets a value indicating whether access to the CollectionBase is synchronized (thread safe).

(Inherited from CollectionBase)
ICollection.SyncRoot
ICollection.SyncRoot

获取用于同步对 CollectionBase 的访问的对象。Gets an object that can be used to synchronize access to the CollectionBase.

(Inherited from CollectionBase)
IList.Add(Object)
IList.Add(Object)

将一个对象添加到 CollectionBase 的末尾。Adds an object to the end of the CollectionBase.

(Inherited from CollectionBase)
IList.Contains(Object)
IList.Contains(Object)

确定 CollectionBase 是否包含特定元素。Determines whether the CollectionBase contains a specific element.

(Inherited from CollectionBase)
IList.IndexOf(Object)
IList.IndexOf(Object)

搜索指定的 Object,并返回整个 CollectionBase 中第一个匹配项的从零开始的索引。Searches for the specified Object and returns the zero-based index of the first occurrence within the entire CollectionBase.

(Inherited from CollectionBase)
IList.Insert(Int32, Object)
IList.Insert(Int32, Object)

将元素插入到 CollectionBase 中的指定索引处。Inserts an element into the CollectionBase at the specified index.

(Inherited from CollectionBase)
IList.IsFixedSize
IList.IsFixedSize

获取一个值,该值指示 CollectionBase 是否具有固定大小。Gets a value indicating whether the CollectionBase has a fixed size.

(Inherited from CollectionBase)
IList.IsReadOnly
IList.IsReadOnly

获取一个值,用于指示 CollectionBase 是否为只读。Gets a value indicating whether the CollectionBase is read-only.

(Inherited from CollectionBase)
IList.Item[Int32]
IList.Item[Int32]

获取或设置指定索引处的元素。Gets or sets the element at the specified index.

(Inherited from CollectionBase)
IList.Remove(Object)
IList.Remove(Object)

CollectionBase 中删除特定对象的第一个匹配项。Removes the first occurrence of a specific object from the CollectionBase.

(Inherited from CollectionBase)

Extension Methods

Cast<TResult>(IEnumerable)

IEnumerable 的元素强制转换为指定的类型。Casts the elements of an IEnumerable to the specified type.

OfType<TResult>(IEnumerable)

根据指定类型筛选 IEnumerable 的元素。Filters the elements of an IEnumerable based on a specified type.

AsParallel(IEnumerable)

启用查询的并行化。Enables parallelization of a query.

AsQueryable(IEnumerable)

IEnumerable 转换为 IQueryableConverts an IEnumerable to an IQueryable.

Applies to

See also