異動和大量複製作業Transaction and Bulk Copy Operations

大量複製作業可做為隔離作業或多步驟交易的其中一個執行步驟。Bulk copy operations can be performed as isolated operations or as part of a multiple step transaction. 第二種選項可讓您在同一異動內執行多個大量複製作業,並執行其他資料庫作業 (例如插入、更新及刪除),同時仍然能夠認可或回復整個異動。This latter option enables you to perform more than one bulk copy operation within the same transaction, as well as perform other database operations (such as inserts, updates, and deletes) while still being able to commit or roll back the entire transaction.

根據預設,大量複製作業會做為隔離作業執行。By default, a bulk copy operation is performed as an isolated operation. 大量複製作業會以非異動性方式進行,而且無法回復。The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back. 如果您需要在發生錯誤時復原全部或部分大量複製,您可以使用SqlBulkCopy管理的交易、在現有的交易內執行大量複製作業,或在 Transaction系統中登記。If you need to roll back all or part of the bulk copy when an error occurs, you can use a SqlBulkCopy-managed transaction, perform the bulk copy operation within an existing transaction, or be enlisted in a System.TransactionsTransaction.

執行非交易大量複製作業Performing a Non-transacted Bulk Copy Operation

下列主控台應用程式 (Console Application) 會顯示非交易大量複製作業在作業過程中發生錯誤時的狀況。The following Console application shows what happens when a non-transacted bulk copy operation encounters an error partway through the operation.

在此範例中,來源資料表和目的地資料表各自包含一個Identity名為ProductID的資料行。In the example, the source table and destination table each include an Identity column named ProductID. 程式碼會先刪除所有資料列,然後插入其ProductID已知存在於來源資料表中的單一資料列,以準備目的地資料表。The code first prepares the destination table by deleting all rows and then inserting a single row whose ProductID is known to exist in the source table. 依預設會在目標資料表中,為每個加入之資料列的 Identity 資料行產生新的值。By default, a new value for the Identity column is generated in the destination table for each row added. 在此範例中,開啟連接時會設定選項,以強制大量載入處理序使用來源資料表中的 Identity 值。In this example, an option is set when the connection is opened that forces the bulk load process to use the Identity values from the source table instead.

執行大量複製作業時,其 BatchSize 屬性會設定為 10。The bulk copy operation is executed with the BatchSize property set to 10. 當作業遇到無效的資料列時,系統會擲回例外狀況 (Exception)。When the operation encounters the invalid row, an exception is thrown. 在第一個範例中,大量複製作業是非交易的。In this first example, the bulk copy operation is non-transacted. 發生錯誤前複製的所有批次作業都會得到認可;處理任何其他批次作業之前,會復原包含重複索引鍵的批次作業,並中止大量複製作業。All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.

注意

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。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-sqlINSERT … SELECT語句來複製資料會更輕鬆且更快速。If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQLINSERT … 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();

            //  Delete all from the destination table.
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key
            //  when all rows from source are bulk copied.
            //  Note that this technique will only be successful in
            //  illustrating the point if a row with ProductID = 446
            //  exists in the AdventureWorks Production.Products table.
            //  If you have made changes to the data in this table, change
            //  the SQL statement in the code to add a ProductID that
            //  does exist in your version of the Production.Products
            //  table. Choose any ProductID in the middle of the table
            //  (not first or last row) to best illustrate the result.
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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 the KeepIdentity option.
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    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 sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As New SqlCommand
            commandDelete.Connection = sourceConnection
            commandDelete.CommandText = _
               "DELETE FROM dbo.BulkCopyDemoMatchingColumns"
            commandDelete.ExecuteNonQuery()

            ' Add a single row that will result in duplicate key
            ' when all rows from source are bulk copied.
            ' Note that this technique will only be successful in 
            ' illustrating the point if a row with ProductID = 446  
            ' exists in the AdventureWorks Production.Products table. 
            ' If you have made changes to the data in this table, change
            ' the SQL statement in the code to add a ProductID that
            ' does exist in your version of the Production.Products
            ' table. Choose any ProductID in the middle of the table
            ' (not first or last row) to best illustrate the result.
            Dim commandInsert As New SqlCommand
            commandInsert.Connection = sourceConnection
            commandInsert.CommandText = _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" & _
               "INSERT INTO dbo.BulkCopyDemoMatchingColumns " & _
               "([ProductID], [Name] ,[ProductNumber]) " & _
               "VALUES(446, 'Lock Nut 23','LN-3416');" & _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF"
            commandInsert.ExecuteNonQuery()

            ' 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 the KeepIdentity option.
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString, _
              SqlBulkCopyOptions.KeepIdentity)
                bulkCopy.BatchSize = 10
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"

                ' Write from the source to the destination.
                ' This should fail with a duplicate key error
                ' after some of the batches have already been copied.
                Try
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                Finally
                    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

在異動中執行專用大量複製作業Performing a Dedicated Bulk Copy Operation in a Transaction

根據預設,大量複製作業是自己的異動。By default, a bulk copy operation is its own transaction. 當您要執行專用大量複製作業時,請建立具有連接字串的新 SqlBulkCopy 執行個體,或使用不含作用中交易的現有 SqlConnection 物件。When you want to perform a dedicated bulk copy operation, create a new instance of SqlBulkCopy with a connection string, or use an existing SqlConnection object without an active transaction. 在每個案例中,會先建立大量複製作業,然後認可或復原交易。In each scenario, the bulk copy operation creates, and then commits or rolls back the transaction.

您可以在 UseInternalTransaction 類別建構函式 (Constructor) 中明確指定 SqlBulkCopy 選項,以便明確地讓大量複製作業在自己的異動中執行,進而讓每個大量複製作業的批次在個別的異動內執行。You can explicitly specify the UseInternalTransaction option in the SqlBulkCopy class constructor to explicitly cause a bulk copy operation to execute in its own transaction, causing each batch of the bulk copy operation to execute within a separate transaction.

注意

由於是在不同的異動中執行不同的批次作業,因此如果在大量複製作業期間發生錯誤,則將復原目前批次作業中的所有資料列,但是先前批次作業的資料列將保留在資料庫中。Since different batches are executed in different transactions, if an error occurs during the bulk copy operation, all the rows in the current batch will be rolled back, but rows from previous batches will remain in the database.

下列主控台應用程式類似於先前的範例,但有一個例外狀況:在此範例中,大量複製作業會管理其本身的交易。The following console application is similar to the previous example, with one exception: In this example, the bulk copy operation manages its own transactions. 發生錯誤前複製的所有批次作業都會得到認可;處理任何其他批次作業之前,會復原包含重複索引鍵的批次作業,並中止大量複製作業。All batches copied up to the point of the error are committed; the batch containing the duplicate key is rolled back, and the bulk copy operation is halted before processing any other batches.

重要

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。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-sqlINSERT … SELECT語句來複製資料會更輕鬆且更快速。If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQLINSERT … 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();

            //  Delete all from the destination table.
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key
            //  when all rows from source are bulk copied.
            //  Note that this technique will only be successful in
            //  illustrating the point if a row with ProductID = 446
            //  exists in the AdventureWorks Production.Products table.
            //  If you have made changes to the data in this table, change
            //  the SQL statement in the code to add a ProductID that
            //  does exist in your version of the Production.Products
            //  table. Choose any ProductID in the middle of the table
            //  (not first or last row) to best illustrate the result.
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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.
            // Note that when specifying the UseInternalTransaction
            // option, you cannot also specify an external transaction.
            // Therefore, you must use the SqlBulkCopy construct that
            // requires a string for the connection, rather than an
            // existing SqlConnection object.
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                       connectionString, SqlBulkCopyOptions.KeepIdentity |
                       SqlBulkCopyOptions.UseInternalTransaction))
            {
                bulkCopy.BatchSize = 10;
                bulkCopy.DestinationTableName =
                    "dbo.BulkCopyDemoMatchingColumns";

                // Write from the source to the destination.
                // This should fail with a duplicate key error
                // after some of the batches have been copied.
                try
                {
                    bulkCopy.WriteToServer(reader);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    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 sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As New SqlCommand
            commandDelete.Connection = sourceConnection
            commandDelete.CommandText = _
               "DELETE FROM dbo.BulkCopyDemoMatchingColumns"
            commandDelete.ExecuteNonQuery()

            ' Add a single row that will result in duplicate key
            ' when all rows from source are bulk copied.
            ' Note that this technique will only be successful in 
            ' illustrating the point if a row with ProductID = 446  
            ' exists in the AdventureWorks Production.Products table. 
            ' If you have made changes to the data in this table, change
            ' the SQL statement in the code to add a ProductID that
            ' does exist in your version of the Production.Products
            ' table. Choose any ProductID in the middle of the table
            ' (not first or last row) to best illustrate the result.
            Dim commandInsert As New SqlCommand
            commandInsert.Connection = sourceConnection
            commandInsert.CommandText = _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" & _
               "INSERT INTO dbo.BulkCopyDemoMatchingColumns " & _
               "([ProductID], [Name] ,[ProductNumber]) " & _
               "VALUES(446, 'Lock Nut 23','LN-3416');" & _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF"
            commandInsert.ExecuteNonQuery()

            ' 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.
            ' Note that when specifying the UseInternalTransaction option, 
            ' you cannot also specify an external transaction. Therefore, 
            ' you must use the SqlBulkCopy construct that requires a string 
            ' for the connection, rather than an existing SqlConnection object.
            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString, _
             SqlBulkCopyOptions.UseInternalTransaction Or _
             SqlBulkCopyOptions.KeepIdentity)
                bulkCopy.BatchSize = 10
                bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns"

                ' Write from the source to the destination.
                ' This should fail with a duplicate key error
                ' after some of the batches have already been copied.
                Try
                    bulkCopy.WriteToServer(reader)

                Catch ex As Exception
                    Console.WriteLine(ex.Message)

                Finally
                    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

使用現有交易Using Existing Transactions

您可指定現有 SqlTransaction 物件做為 SqlBulkCopy 建構函式中的參數。You can specify an existing SqlTransaction object as a parameter in a SqlBulkCopy constructor. 在此情況下,系統會在現有異動中執行大量複製作業,而且不會變更異動狀態 (亦即,它尚未認可或中止)。In this situation, the bulk copy operation is performed in an existing transaction, and no change is made to the transaction state (that is, it is neither committed nor aborted). 這可讓應用程式使用其他資料庫作業來將大量複製作業併入交易中。This allows an application to include the bulk copy operation in a transaction with other database operations. 不過,如果您不指定 SqlTransaction 物件及傳遞 Null 參考,而且連接具有作用中的異動,則會擲回例外狀況。However, if you do not specify a SqlTransaction object and pass a null reference, and the connection has an active transaction, an exception is thrown.

如果因為發生錯誤而需要回復整個大量複製作業,或者大量複製作業應做為可回復之較大處理序的一部分執行,則可提供 SqlTransaction 物件給 SqlBulkCopy 建構函式。If you need to roll back the entire bulk copy operation because an error occurs, or if the bulk copy should execute as part of a larger process that can be rolled back, you can provide a SqlTransaction object to the SqlBulkCopy constructor.

下列主控台應用程式類似於第一個 (非異動) 範例,但有一個例外狀況:在此範例中,大量複製作業包含在較大的外部異動中。The following console application is similar to the first (non-transacted) example, with one exception: in this example, the bulk copy operation is included in a larger, external transaction. 當發生主索引鍵違規錯誤時,會復原整個交易,並且不會將任何資料列加入目標資料表中。When the primary key violation error occurs, the entire transaction is rolled back and no rows are added to the destination table.

重要

除非您已如大量複製範例設定中所述建立工作資料表,否則此範例不會執行。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-sqlINSERT … SELECT語句來複製資料會更輕鬆且更快速。If the source and destination tables are located in the same SQL Server instance, it is easier and faster to use a Transact-SQLINSERT … 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();

            //  Delete all from the destination table.
            SqlCommand commandDelete = new SqlCommand();
            commandDelete.Connection = sourceConnection;
            commandDelete.CommandText =
                "DELETE FROM dbo.BulkCopyDemoMatchingColumns";
            commandDelete.ExecuteNonQuery();

            //  Add a single row that will result in duplicate key
            //  when all rows from source are bulk copied.
            //  Note that this technique will only be successful in
            //  illustrating the point if a row with ProductID = 446
            //  exists in the AdventureWorks Production.Products table.
            //  If you have made changes to the data in this table, change
            //  the SQL statement in the code to add a ProductID that
            //  does exist in your version of the Production.Products
            //  table. Choose any ProductID in the middle of the table
            //  (not first or last row) to best illustrate the result.
            SqlCommand commandInsert = new SqlCommand();
            commandInsert.Connection = sourceConnection;
            commandInsert.CommandText =
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" +
                "INSERT INTO " + "dbo.BulkCopyDemoMatchingColumns " +
                "([ProductID], [Name] ,[ProductNumber]) " +
                "VALUES(446, 'Lock Nut 23','LN-3416');" +
                "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF";
            commandInsert.ExecuteNonQuery();

            // 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 inside the transaction.
            using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();

                using (SqlTransaction transaction =
                           destinationConnection.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(
                               destinationConnection, SqlBulkCopyOptions.KeepIdentity,
                               transaction))
                    {
                        bulkCopy.BatchSize = 10;
                        bulkCopy.DestinationTableName =
                            "dbo.BulkCopyDemoMatchingColumns";

                        // Write from the source to the destination.
                        // This should fail with a duplicate key error.
                        try
                        {
                            bulkCopy.WriteToServer(reader);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                            transaction.Rollback();
                        }
                        finally
                        {
                            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 sourceConnection to the AdventureWorks database.
        Using sourceConnection As SqlConnection = _
           New SqlConnection(connectionString)
            sourceConnection.Open()

            ' Delete all from the destination table.
            Dim commandDelete As New SqlCommand
            commandDelete.Connection = sourceConnection
            commandDelete.CommandText = _
               "DELETE FROM dbo.BulkCopyDemoMatchingColumns"
            commandDelete.ExecuteNonQuery()

            ' Add a single row that will result in duplicate key
            ' when all rows from source are bulk copied.
            ' Note that this technique will only be successful in 
            ' illustrating the point if a row with ProductID = 446  
            ' exists in the AdventureWorks Production.Products table. 
            ' If you have made changes to the data in this table, change
            ' the SQL statement in the code to add a ProductID that
            ' does exist in your version of the Production.Products
            ' table. Choose any ProductID in the middle of the table
            ' (not first or last row) to best illustrate the result.
            Dim commandInsert As New SqlCommand
            commandInsert.Connection = sourceConnection
            commandInsert.CommandText = _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns ON;" & _
               "INSERT INTO dbo.BulkCopyDemoMatchingColumns " & _
               "([ProductID], [Name] ,[ProductNumber]) " & _
               "VALUES(446, 'Lock Nut 23','LN-3416');" & _
               "SET IDENTITY_INSERT dbo.BulkCopyDemoMatchingColumns OFF"
            commandInsert.ExecuteNonQuery()

            ' 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 inside the transaction. 
            Using destinationConnection As SqlConnection = _
               New SqlConnection(connectionString)
                destinationConnection.Open()

                Using transaction As SqlTransaction = _
                  destinationConnection.BeginTransaction()

                    Using bulkCopy As SqlBulkCopy = New _
                      SqlBulkCopy(destinationConnection, _
                         SqlBulkCopyOptions.KeepIdentity, transaction)
                        bulkCopy.BatchSize = 10
                        bulkCopy.DestinationTableName = _
                         "dbo.BulkCopyDemoMatchingColumns"

                        ' Write from the source to the destination.
                        ' This should fail with a duplicate key error.
                        Try
                            bulkCopy.WriteToServer(reader)
                            transaction.Commit()

                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
                            transaction.Rollback()

                        Finally
                            reader.Close()
                        End Try
                    End Using
                End Using
            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

另請參閱See also