SqlBulkCopy 建構函式

參考

定義

命名空間:: System.Data.SqlClient

組件:: System.Data.SqlClient.dll

組件:: System.Data.dll

組件:: netstandard.dll, System.Data.SqlClient.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` 參數中提供的選項進行運作。如果提供了非 null 的 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 則會擲回。

另請參閱

在 SQL Server 中執行大量複製作業
ADO.NET 概觀 \(部分機器翻譯\)

適用於

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 傳遞使用者識別碼和密碼，這種方式比在連接字串中以文字指定使用者識別碼和密碼來得更加安全。

範例

下列主控台應用程式示範如何使用指定為字串的連線來大量載入資料。關閉實例時，會自動關閉連線 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 則會擲回。

另請參閱

在 SQL Server 中執行大量複製作業
ADO.NET 概觀 \(部分機器翻譯\)

適用於

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 所有大量複製選項的詳細資訊。

另請參閱

在 SQL Server 中執行大量複製作業
ADO.NET 概觀 \(部分機器翻譯\)

適用於

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

使用已提供之現有開啟的 SqlBulkCopy 執行個體，初始化 SqlConnection 的新執行個體。 SqlBulkCopy 執行個體會根據 copyOptions 參數中提供的選項進行運作。如果提供了非 null 的 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 執行個體，其下會發生大量複製。

備註

如果 options include UseInternalTransaction 和自 externalTransaction 變數不是 Null，則會擲回 InvalidArgumentException 。

如需示範如何在 SqlBulkCopy 交易中使用的範例，請參閱交易和大量複製作業。

另請參閱

在 SQL Server 中執行大量複製作業
ADO.NET 概觀 \(部分機器翻譯\)

適用於

共用方式為

SqlBulkCopy 建構函式

定義

多載

SqlBulkCopy(SqlConnection)

參數

範例

備註

另請參閱

適用於

SqlBulkCopy(String)

參數

範例

備註

另請參閱

適用於

SqlBulkCopy(String, SqlBulkCopyOptions)

參數

範例

備註

另請參閱

適用於

SqlBulkCopy(SqlConnection, SqlBulkCopyOptions, SqlTransaction)

參數

備註

另請參閱

適用於

其他資源