SqlBulkCopy Class

Definition

允许你使用其他源的数据有效地批量加载 SQL Server 表。Lets you efficiently bulk load a SQL Server table with data from another source.

public ref class SqlBulkCopy sealed : IDisposable
public sealed class SqlBulkCopy : IDisposable
type SqlBulkCopy = class
    interface IDisposable
Public NotInheritable Class SqlBulkCopy
Implements IDisposable
Inheritance
SqlBulkCopy
Implements

Examples

下面的控制台应用程序演示了如何使用 SqlBulkCopy 类加载数据。The following console application demonstrates how to load data using the SqlBulkCopy class. 在此示例中,SqlDataReader 用于将数据从 SQL Server AdventureWorks 数据库的 Production.Product 表复制到相同数据库的一个类似的表中。In this example, a SqlDataReader is used to copy data from the Production.Product table in the SQL Server AdventureWorks database to a similar table in the same database.

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.BulkCopyDemoMatchingColumns;",
                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();

            // Open the destination connection. In the real world you would
            // not use SqlBulkCopy to move data from one table to the other
            // in the same database. This is for demonstration purposes only.
            using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();

                // Set up the bulk copy object.
                // Note that the column positions in the source
                // data reader match the column positions in
                // the destination table so there is no need to
                // map columns.
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        // Write from the source to the destination.
                        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.BulkCopyDemoMatchingColumns;", _
                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 New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            ' Open the destination connection. In the real world you would 
            ' not use SqlBulkCopy to move data from one table to the other   
            ' in the same database. This is for demonstration purposes only.
            Using destinationConnection As SqlConnection = _
                New SqlConnection(connectionString)
                destinationConnection.Open()

                ' Set up the bulk copy object. 
                ' The column positions in the source data reader 
                ' match the column positions in the destination table, 
                ' so there is no need to map columns.
                Using bulkCopy As SqlBulkCopy = _
                  New SqlBulkCopy(destinationConnection)
                    bulkCopy.DestinationTableName = _
                    "dbo.BulkCopyDemoMatchingColumns"

                    Try
                        ' Write from the source to the destination.
                        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 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

Microsoft SQL Server 包含一个用于将数据从一个表移到另一个表的常用命令提示实用工具,无论是在单个服务器上还是在服务器之间。Microsoft SQL Server includes a popular command-prompt utility named bcp for moving data from one table to another, whether on a single server or between servers. 利用 SqlBulkCopy 类,您可以编写提供类似功能的托管代码解决方案。The SqlBulkCopy class lets you write managed code solutions that provide similar functionality. 还可以通过其他方式将数据加载到 SQL Server 表中(例如 INSERT 语句),但 SqlBulkCopy 对它们提供显著的性能优势。There are other ways to load data into a SQL Server table (INSERT statements, for example), but SqlBulkCopy offers a significant performance advantage over them.

SqlBulkCopy 类可用于只将数据写入 SQL Server 表。The SqlBulkCopy class can be used to write data only to SQL Server tables. 但是,数据源不限于 SQL Server;可以使用任何数据源,只要数据可以加载到 DataTable 实例或使用 IDataReader 实例进行读取即可。However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

在将类型 SqlDateTimeDataTable 列大容量加载到类型为 SQL Server 2008 中添加的日期/时间类型之一的 SQL Server 列时,SqlBulkCopy 将会失败。SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008.

Constructors

SqlBulkCopy(SqlConnection)

使用 SqlConnection 的指定打开实例初始化 SqlBulkCopy 类的新实例。Initializes a new instance of the SqlBulkCopy class using the specified open instance of SqlConnection.

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用提供的 SqlConnection 的现有打开实例初始化 SqlBulkCopy 类的新实例。Initializes a new instance of the SqlBulkCopy class using the supplied existing open instance of SqlConnection. SqlBulkCopy 实例的行为取决于 copyOptions 参数中提供的选项。The SqlBulkCopy instance behaves according to options supplied in the copyOptions parameter. 如果提供非空 SqlTransaction,则复制操作将在该事务中执行。If a non-null SqlTransaction is supplied, the copy operations will be performed within that transaction.

SqlBulkCopy(String)

基于提供的 connectionString 初始化并打开 SqlConnection 的新实例。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 该构造函数使用 SqlConnection 来初始化 SqlBulkCopy 类的新实例。The constructor uses the SqlConnection to initialize a new instance of the SqlBulkCopy class.

SqlBulkCopy(String, SqlBulkCopyOptions)

基于提供的 connectionString 初始化并打开 SqlConnection 的新实例。Initializes and opens a new instance of SqlConnection based on the supplied connectionString. 构造函数使用该 SqlConnection 来初始化 SqlBulkCopy 类的新实例。The constructor uses that SqlConnection to initialize a new instance of the SqlBulkCopy class. SqlConnection 实例的行为取决于 copyOptions 参数中提供的选项。The SqlConnection instance behaves according to options supplied in the copyOptions parameter.

Properties

BatchSize

每个批处理中的行数。Number of rows in each batch. 在每个批处理结束时,批处理中的行将发送到服务器。At the end of each batch, the rows in the batch are sent to the server.

BulkCopyTimeout

超时之前要完成操作的秒数。Number of seconds for the operation to complete before it times out.

ColumnMappings

返回 SqlBulkCopyColumnMapping 项的集合。Returns a collection of SqlBulkCopyColumnMapping items. 列映射定义数据源中的列与目标中的列之间的关系。Column mappings define the relationships between columns in the data source and columns in the destination.

DestinationTableName

服务器上的目标表的名称。Name of the destination table on the server.

EnableStreaming

启用或禁用 SqlBulkCopy 对象,以流式传输 IDataReader 对象中的数据。Enables or disables a SqlBulkCopy object to stream data from an IDataReader object.

NotifyAfter

定义生成通知事件之前要处理的行数。Defines the number of rows to be processed before generating a notification event.

Methods

Close()

关闭 SqlBulkCopy 实例。Closes the SqlBulkCopy instance.

Equals(Object)

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

(Inherited from Object)
GetHashCode()

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

(Inherited from Object)
GetType()

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

(Inherited from Object)
MemberwiseClone()

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

(Inherited from Object)
ToString()

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

(Inherited from Object)
WriteToServer(DataRow[])

将所有行从提供的 DataRow 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。Copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServer(DataTable)

将所有行从提供的 DataTable 复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。Copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServer(DataTable, DataRowState)

仅将与提供的 DataTable 中提供的行状态匹配的行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。Copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServer(DbDataReader)

将所有行从提供的 DbDataReader 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。Copies all rows from the supplied DbDataReader array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServer(IDataReader)

将所有行从提供的 IDataReader 复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。Copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(DataRow[])

WriteToServer(DataRow[]) 的异步版本,它将所有行从提供的 DataRow 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DataRow[]), which copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(DataRow[], CancellationToken)

WriteToServer(DataRow[]) 的异步版本,它将所有行从提供的 DataRow 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DataRow[]), which copies all rows from the supplied DataRow array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

取消标记可用于请求在命令超时超过前放弃操作。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 将通过返回的任务对象报告异常。Exceptions will be reported via the returned Task object.

WriteToServerAsync(DataTable)

WriteToServer(DataTable) 的异步版本,它将提供的 DataTable 中的所有行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DataTable), which copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(DataTable, CancellationToken)

WriteToServer(DataTable) 的异步版本,它将提供的 DataTable 中的所有行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DataTable), which copies all rows in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

取消标记可用于请求在命令超时超过前放弃操作。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 将通过返回的任务对象报告异常。Exceptions will be reported via the returned Task object.

WriteToServerAsync(DataTable, DataRowState)

WriteToServer(DataTable, DataRowState) 的异步版本,它只将匹配所提供 DataTable 中所提供行状态的行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。The asynchronous version of WriteToServer(DataTable, DataRowState), which copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(DataTable, DataRowState, CancellationToken)

WriteToServer(DataTable, DataRowState) 的异步版本,它只将匹配所提供 DataTable 中所提供行状态的行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表中。The asynchronous version of WriteToServer(DataTable, DataRowState), which copies only rows that match the supplied row state in the supplied DataTable to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

取消标记可用于请求在命令超时超过前放弃操作。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 将通过返回的任务对象报告异常。Exceptions will be reported via the returned Task object.

WriteToServerAsync(DbDataReader)

WriteToServer(DbDataReader) 的异步版本,它将所有行从提供的 DbDataReader 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DbDataReader), which copies all rows from the supplied DbDataReader array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(DbDataReader, CancellationToken)

WriteToServer(DbDataReader) 的异步版本,它将所有行从提供的 DbDataReader 数组复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(DbDataReader), which copies all rows from the supplied DbDataReader array to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(IDataReader)

WriteToServer(IDataReader) 的异步版本,它将提供的 IDataReader 中的所有行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(IDataReader), which copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

WriteToServerAsync(IDataReader, CancellationToken)

WriteToServer(IDataReader) 的异步版本,它将提供的 IDataReader 中的所有行复制到 SqlBulkCopy 对象的 DestinationTableName 属性指定的目标表。The asynchronous version of WriteToServer(IDataReader), which copies all rows in the supplied IDataReader to a destination table specified by the DestinationTableName property of the SqlBulkCopy object.

取消标记可用于请求在命令超时超过前放弃操作。The cancellation token can be used to request that the operation be abandoned before the command timeout elapses. 将通过返回的任务对象报告异常。Exceptions will be reported via the returned Task object.

Events

SqlRowsCopied

每当 NotifyAfter 属性指定的行数被处理后会发生。Occurs every time that the number of rows specified by the NotifyAfter property have been processed.

Explicit Interface Implementations

IDisposable.Dispose()

释放由 SqlBulkCopy 类的当前实例占用的所有资源。Releases all resources used by the current instance of the SqlBulkCopy class.

Applies to

See also