Using External Transactions with SqlBulkCopy

Performing Atomic SqlBulkCopy Imports

To perform atomic SqlBulkCopy imports that span across all batches (and, optionally, across
other database statements) we need to use transactions. The following steps outline the
process of using a transaction with SqlBulkCopy:

1. Create a SqlConnection to the destination database server.
2. Open the connection.
3. Create a SqlTransaction object.
4. Create the SqlBulkCopy object passing in the SqlTransaction object into the constructor.
5. Perform the import - the call to WriteToServer - within a Try...Catch block.
6. If the operation completes, commit the transaction; if it fails, roll it back.

' STEP 1 - Create a SqlConnection to the destination database server Using destinationConnection As New SqlConnection(ConfigurationManager .ConnectionStrings("InventoryDBConnectionString").ConnectionString)    ' STEP 2 - Open the connection.    destinationConnection.Open()    ' STEP 3 - Create a SqlTransaction object.    Dim myTransaction As SqlTransaction =                                 destinationConnection.BeginTransaction()    ' STEP 4 - Create the SqlBulkCopy object passing in the                               SqlTransaction object into the constructor.    Using bulkCopy As New SqlBulkCopy(destinationConnection,                                SqlBulkCopyOptions.Default, myTransaction)       bulkCopy.DestinationTableName = "InventoryItems"       'Define column mappings       For Each col As DataColumn In excelData.Columns          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName)       Next       'With external transactions, the entire operation occurs under         the umbrella of one transaction,       'regardless of the BatchSize value       bulkCopy.BatchSize = 100       ' STEP 5 - Perform the import - the call to WriteToServer - within                   a Try...Catch block.       Try          bulkCopy.WriteToServer(excelData)          ' STEP 6a - Success! Commit transaction          myTransaction.Commit()          Display some sort of success message...       Catch ex As Exception          ' STEP 6b - Failure - rollback the transaction          myTransaction.Rollback()          Display some sort of error message...       End Try    End Using    destinationConnection.Close() End Using

If you had other statements that needed to be performed under the umbrella of this
transaction you would execute them within the Try...Catch block.