SqlBulkCopy SqlBulkCopy SqlBulkCopy SqlBulkCopy Class

定义

使您可以用其他源的数据有效批量加载 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
继承
SqlBulkCopySqlBulkCopySqlBulkCopySqlBulkCopy
实现

示例

以下控制台应用程序演示如何使用 SqlBulkCopy 类加载数据。The following console application demonstrates how to load data using the SqlBulkCopy class. 在此示例中, SqlDataReader用于将数据从 SQL Server AdventureWorks数据库中的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.

重要

除非已按照大容量复制示例设置中所述创建了工作表, 否则此示例将不会运行。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

注解

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.

SqlBulkCopy将类型DataTable SqlDateTime的列大容量加载到 SQL Server 列时将失败, 其类型为 SQL Server 2008 中添加的日期/时间类型之一。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.

构造函数

SqlBulkCopy(SqlConnection) SqlBulkCopy(SqlConnection) SqlBulkCopy(SqlConnection) SqlBulkCopy(SqlConnection)

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

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用提供的 SqlBulkCopy 的现有打开实例初始化 SqlConnection 类的新实例。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) SqlBulkCopy(String) SqlBulkCopy(String) 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) SqlBulkCopy(String, SqlBulkCopyOptions) SqlBulkCopy(String, SqlBulkCopyOptions) 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.

属性

BatchSize BatchSize BatchSize BatchSize

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

BulkCopyTimeout BulkCopyTimeout BulkCopyTimeout BulkCopyTimeout

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

ColumnMappings ColumnMappings ColumnMappings 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 DestinationTableName DestinationTableName DestinationTableName

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

EnableStreaming EnableStreaming EnableStreaming EnableStreaming

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

NotifyAfter NotifyAfter NotifyAfter NotifyAfter

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

方法

Close() Close() Close() Close()

关闭 SqlBulkCopy 实例。Closes the SqlBulkCopy instance.

Equals(Object) Equals(Object) Equals(Object) Equals(Object)

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

(Inherited from Object)
GetHashCode() GetHashCode() GetHashCode() GetHashCode()

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

(Inherited from Object)
GetType() GetType() GetType() GetType()

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

(Inherited from Object)
MemberwiseClone() MemberwiseClone() MemberwiseClone() MemberwiseClone()

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

(Inherited from Object)
ToString() ToString() ToString() ToString()

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

(Inherited from Object)
WriteToServer(DataRow[]) WriteToServer(DataRow[]) WriteToServer(DataRow[]) WriteToServer(DataRow[])

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

WriteToServer(DataTable) WriteToServer(DataTable) WriteToServer(DataTable) WriteToServer(DataTable)

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

WriteToServer(DataTable, DataRowState) WriteToServer(DataTable, DataRowState) WriteToServer(DataTable, DataRowState) WriteToServer(DataTable, DataRowState)

只将与所提供 DataTable 中所提供行状态匹配的行复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServer(DbDataReader) WriteToServer(DbDataReader) WriteToServer(DbDataReader)

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

WriteToServer(IDataReader) WriteToServer(IDataReader) WriteToServer(IDataReader) WriteToServer(IDataReader)

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

WriteToServerAsync(DataRow[]) WriteToServerAsync(DataRow[]) WriteToServerAsync(DataRow[]) WriteToServerAsync(DataRow[])

WriteToServer(DataRow[]) 的异步版本,将 DataRow 数组中提供的所有行都复制到由 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(DataRow[], CancellationToken) WriteToServerAsync(DataRow[], CancellationToken) WriteToServerAsync(DataRow[], CancellationToken)

WriteToServer(DataRow[]) 的异步版本,将 DataRow 数组中提供的所有行都复制到由 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(DataTable) WriteToServerAsync(DataTable) WriteToServerAsync(DataTable)

WriteToServer(DataTable) 的异步版本,将 DataTable 中提供的所有行都复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(DataTable, CancellationToken) WriteToServerAsync(DataTable, CancellationToken) WriteToServerAsync(DataTable, CancellationToken)

WriteToServer(DataTable) 的异步版本,将 DataTable 中提供的所有行都复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(DataTable, DataRowState) WriteToServerAsync(DataTable, DataRowState) WriteToServerAsync(DataTable, DataRowState)

WriteToServer(DataTable, DataRowState) 的异步版本,之间与 DataTable 中所提供行状态匹配的行复制到 DestinationTableName 对象的 SqlBulkCopy 属性中指定的目标表中。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) WriteToServerAsync(DataTable, DataRowState, CancellationToken) WriteToServerAsync(DataTable, DataRowState, CancellationToken) WriteToServerAsync(DataTable, DataRowState, CancellationToken)

WriteToServer(DataTable, DataRowState) 的异步版本,之间与 DataTable 中所提供行状态匹配的行复制到 DestinationTableName 对象的 SqlBulkCopy 属性中指定的目标表中。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) WriteToServerAsync(DbDataReader) WriteToServerAsync(DbDataReader) WriteToServerAsync(DbDataReader)

WriteToServer(DbDataReader) 的异步版本,将 DbDataReader 数组中提供的所有行都复制到由 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(DbDataReader, CancellationToken) WriteToServerAsync(DbDataReader, CancellationToken) WriteToServerAsync(DbDataReader, CancellationToken)

WriteToServer(DbDataReader) 的异步版本,将 DbDataReader 数组中提供的所有行都复制到由 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(IDataReader) WriteToServerAsync(IDataReader) WriteToServerAsync(IDataReader)

WriteToServer(IDataReader) 的异步版本,将 IDataReader 中提供的所有行都复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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) WriteToServerAsync(IDataReader, CancellationToken) WriteToServerAsync(IDataReader, CancellationToken) WriteToServerAsync(IDataReader, CancellationToken)

WriteToServer(IDataReader) 的异步版本,将 IDataReader 中提供的所有行都复制到 DestinationTableName 对象的 SqlBulkCopy 属性指定的目标表中。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.

事件

SqlRowsCopied SqlRowsCopied SqlRowsCopied SqlRowsCopied

在每次处理完 NotifyAfter 属性指定的行数时发生。Occurs every time that the number of rows specified by the NotifyAfter property have been processed.

显式界面实现

IDisposable.Dispose() IDisposable.Dispose() IDisposable.Dispose() IDisposable.Dispose()

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

适用于

另请参阅