Wednesday, May 25, 2011

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.

No comments :

Post a Comment