SqlBulkCopy 构造函数

参考

定义

命名空间:: System.Data.SqlClient

程序集:: System.Data.SqlClient.dll

程序集:: System.Data.dll

包:: System.Data.SqlClient v4.8.6

重要

一些信息与预发行产品相关，相应产品在发行之前可能会进行重大修改。对于此处提供的信息，Microsoft 不作任何明示或暗示的担保。

初始化 SqlBulkCopy 类的新实例。

重载

SqlBulkCopy(SqlConnection)	使用 SqlConnection 的打开的指定实例初始化 SqlBulkCopy 类的新实例。
SqlBulkCopy(String)	基于提供的 `connectionString` 初始化并打开 SqlConnection 的新实例。该构造函数使用 SqlConnection 来初始化 SqlBulkCopy 类的新实例。
SqlBulkCopy(String, SqlBulkCopyOptions)	基于提供的 `connectionString` 初始化和打开 SqlConnection 的新实例。此构造函数使用此 SqlConnection 来初始化 SqlBulkCopy 类的新实例。 SqlConnection 实例的行为取决于 `copyOptions` 参数中提供的选项。
SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)	使用提供的 SqlBulkCopy 的现有打开实例初始化 SqlConnection 类的新实例。 SqlBulkCopy 实例的行为取决于 `copyOptions` 参数中提供的选项。如果提供非空 SqlTransaction，则复制操作将在该事务中执行。

SqlBulkCopy(SqlConnection)

使用 SqlConnection 的打开的指定实例初始化 SqlBulkCopy 类的新实例。

public:
 SqlBulkCopy(System::Data::SqlClient::SqlConnection ^ connection);

public SqlBulkCopy (System.Data.SqlClient.SqlConnection connection);

new System.Data.SqlClient.SqlBulkCopy : System.Data.SqlClient.SqlConnection -> System.Data.SqlClient.SqlBulkCopy

Public Sub New (connection As SqlConnection)

参数

connection: SqlConnection

将用于执行批量复制操作的已经打开的 SqlConnection 实例。如果连接字符串不使用 Integrated Security = true，则可以使用 SqlCredential 来传递用户 ID 和密码，这比将用户 ID 和密码指定为连接字符串中的文本更安全。

示例

以下控制台应用程序演示如何使用已打开的连接大容量加载数据。在此示例中，SqlDataReader 用于将数据从 SQL Server AdventureWorks 数据库的 Production.Product 表复制到相同数据库的一个类似的表中。此示例只为了方便演示。在生产应用程序中，不会使用 SqlBulkCopy 将数据从一个表移到同一数据库中的另一个表。请注意，源数据不必位于 SQL Server;可以使用可读取到或加载到 IDataReader DataTable的任何数据源。

重要

除非已按批量复制示例设置中所述创建了工作表，否则此示例不会运行。提供此代码是为了演示仅使用 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.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

注解

由于在初始化实例时 SqlBulkCopy 连接已打开，因此该连接在实例关闭后 SqlBulkCopy 保持打开状态。

connection如果参数为 null，ArgumentNullException则引发。

另请参阅

适用于

SqlBulkCopy(String)

基于提供的 connectionString 初始化并打开 SqlConnection 的新实例。该构造函数使用 SqlConnection 来初始化 SqlBulkCopy 类的新实例。

public:
 SqlBulkCopy(System::String ^ connectionString);

public SqlBulkCopy (string connectionString);

new System.Data.SqlClient.SqlBulkCopy : string -> System.Data.SqlClient.SqlBulkCopy

Public Sub New (connectionString As String)

参数

connectionString: String

定义将打开的且由 SqlBulkCopy 实例使用的连接的字符串。如果该连接字符串不使用 Integrated Security = true，则可以使用 SqlBulkCopy(SqlConnection) 或 SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction) 和 SqlCredential 来传递用户 ID 和密码，这比将用户 ID 和密码指定为连接字符串中的文本更安全。

示例

以下控制台应用程序演示如何使用指定为字符串的连接批量加载数据。当实例关闭时， SqlBulkCopy 连接会自动关闭。

在此示例中，首先将源数据从SQL Server表读取到 SqlDataReader 实例。源数据不必位于 SQL Server;可以使用可读取到或加载到 IDataReader DataTable的任何数据源。

重要

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();

            // Set up the bulk copy object using a connection string.
            // In the real world you would not use SqlBulkCopy to move
            // data from one table to the other in the same database.
            using (SqlBulkCopy bulkCopy =
                       new SqlBulkCopy(connectionString))
            {
                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 SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            ' Set up the bulk copy object using a connection string. 
            ' In the real world you would not use SqlBulkCopy to move
            ' data from one table to the other in the same database.
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
                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 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

注解

在大容量复制操作结束时，连接会自动关闭。

如果 connectionString 为 null， ArgumentNullException 则引发。如果 connectionString 是空字符串， ArgumentException 则引发。

另请参阅

适用于

SqlBulkCopy(String, SqlBulkCopyOptions)

基于提供的 connectionString 初始化和打开 SqlConnection 的新实例。此构造函数使用此 SqlConnection 来初始化 SqlBulkCopy 类的新实例。 SqlConnection 实例的行为取决于 copyOptions 参数中提供的选项。

public:
 SqlBulkCopy(System::String ^ connectionString, System::Data::SqlClient::SqlBulkCopyOptions copyOptions);

public SqlBulkCopy (string connectionString, System.Data.SqlClient.SqlBulkCopyOptions copyOptions);

new System.Data.SqlClient.SqlBulkCopy : string * System.Data.SqlClient.SqlBulkCopyOptions -> System.Data.SqlClient.SqlBulkCopy

Public Sub New (connectionString As String, copyOptions As SqlBulkCopyOptions)

参数

connectionString: String

copyOptions: SqlBulkCopyOptions

SqlBulkCopyOptions 枚举中的值的组合，可确定将哪些数据源行复制到目标表中。

示例

以下控制台应用程序演示如何使用指定为字符串的连接执行大容量加载。选项设置为在加载目标表时使用源表的标识列中的值。在此示例中，首先将源数据从SQL Server表读取到 SqlDataReader 实例。源表和目标表各包含一个 Identity 列。默认情况下，在目标表中为添加的每一行生成 “标识” 列的新值。在此示例中，当连接打开时，将设置一个选项，该选项强制大容量加载进程改用源表中的 Identity 值。若要查看选项如何更改大容量加载的工作方式，请使用 dbo 运行示例。BulkCopyDemoMatchingColumns 表为空。从源加载所有行。然后再次运行示例，而不清空表。引发异常，代码会向控制台写入一条消息，通知你由于违反主键约束而未添加行。

重要

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();

            // Create the SqlBulkCopy object using a connection string
            // and the KeepIdentity option.
            // In the real world you would not use SqlBulkCopy to move
            // data from one table to the other in the same database.
            using (SqlBulkCopy bulkCopy =
                new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                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 SqlCommand = New SqlCommand( _
               "SELECT ProductID, Name, ProductNumber " & _
               "FROM Production.Product;", sourceConnection)
            Dim reader As SqlDataReader = commandSourceData.ExecuteReader

            ' Create the SqlBulkCopy object using a connection string 
            ' and the KeepIdentity option. 
            ' In the real world you would not use SqlBulkCopy to move
            ' data from one table to the other in the same database.
            Using bulkCopy As SqlBulkCopy = _
              New SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity)
                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 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

注解

可以在主题中 SqlBulkCopyOptions 获取有关所有大容量复制选项的详细信息。

另请参阅

适用于

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用提供的 SqlBulkCopy 的现有打开实例初始化 SqlConnection 类的新实例。 SqlBulkCopy 实例的行为取决于 copyOptions 参数中提供的选项。如果提供非空 SqlTransaction，则复制操作将在该事务中执行。

public:
 SqlBulkCopy(System::Data::SqlClient::SqlConnection ^ connection, System::Data::SqlClient::SqlBulkCopyOptions copyOptions, System::Data::SqlClient::SqlTransaction ^ externalTransaction);

public SqlBulkCopy (System.Data.SqlClient.SqlConnection connection, System.Data.SqlClient.SqlBulkCopyOptions copyOptions, System.Data.SqlClient.SqlTransaction externalTransaction);

new System.Data.SqlClient.SqlBulkCopy : System.Data.SqlClient.SqlConnection * System.Data.SqlClient.SqlBulkCopyOptions * System.Data.SqlClient.SqlTransaction -> System.Data.SqlClient.SqlBulkCopy

Public Sub New (connection As SqlConnection, copyOptions As SqlBulkCopyOptions, externalTransaction As SqlTransaction)

参数

connection: SqlConnection

copyOptions: SqlBulkCopyOptions

SqlBulkCopyOptions 枚举中的值的组合，可确定将哪些数据源行复制到目标表中。

externalTransaction: SqlTransaction

现有的 SqlTransaction 实例，批量复制操作将在该实例下进行。

注解

如果选项包括 UseInternalTransaction 并且 externalTransaction 参数不为 null，则会引发 InvalidArgumentException 。

有关演示如何在事务中使用 SqlBulkCopy 的示例，请参阅事务和大容量复制操作。

另请参阅

适用于

SqlBulkCopy 构造函数

定义

重载

SqlBulkCopy(SqlConnection)

参数

示例

注解

另请参阅

适用于

SqlBulkCopy(String)

参数

示例

注解

另请参阅

适用于

SqlBulkCopy(String, SqlBulkCopyOptions)

参数

示例

注解

另请参阅

适用于

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

参数

注解

另请参阅

适用于

反馈

其他资源