Applying transactions to multi-table updates

All updates within a single procedure to more than one table should run under the control of a transaction. The SqlTransaction object provides clients with the ability to commit or, in the event of an exception, roll back updates to SQL Server base tables. Managing transactions in ADO.NET is similar to that for ADODB.Connection objects, which have BeginTrans, CommitTrans, and RollbackTrans methods.

SqlTransaction objects have corresponding BeginTransaction, CommitTransaction, and RollbackTransaction methods. Unlike ADODB connections, ADO.NET lets you selectively enlist commands in an active transaction.

The following are steps to execute ADO.NET transacted updates:

• Define a local transaction as an SqlTransaction, OleDbTransaction, or OdbcTransaction object.
• Invoke the transaction's BeginTransaction method with an optional IsolationLevel enumeration argument. The default IsolationLevel property value is ReadCommitted.
• Enlist commands in the transaction by their Transaction property.
• Invoke the ExecuteNonQuery method for each command.
• Invoke the transaction's Commit method.
• If an exception occurs, invoke the transaction's Rollback method.

ADO.NET's IsolationLevel and ADODB's IsolationLevelEnum enumerations share many common members, as shown in the following table.

Snapshot is a new ADO.NET 2.0 isolation level for SQL Server 2005 only. Snapshot isolation eliminates read locks by providing other clients a copy (snapshot) of the unmodified data until the transaction commits. You must enable Snapshot isolation in SQL Server Management Studio (SSMS) or by issuing a T-SQL ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON command to take advantage of the transaction scalability improvement that this new isolation level offers.

The following RunInsertTransaction listing illustrates reuse of a single SqlTransaction and SqlCommand object for sets of update transactions on the Northwind Customers and Orders tables. Running this transaction makes non-reversible changes to the OrderID column of the Orders table, so it's a good idea to back up the Northwind database before running this type of code. Notice that you must re-enlist the SqlCommand object in the SqlTransaction after a previous transaction commits.

     Public Sub RunInsertTransaction()
           'Add and delete new Customers and Orders records
          Dim strConn As String = "Server=localhost;Database=Northwind;" + _
          "Integrated Security=SSPI"
          Dim cnnNwind As SqlConnection = New SqlConnection(strConn)

          'Specify a local transaction object
          Dim trnCustOrder As SqlTransaction
          Dim intRecordsAffected As Integer
          Dim strTitle As String
          Try
               cnnNwind.Open()
               Try
                    trnCustOrder = cnnNwind.BeginTransaction(IsolationLevel.RepeatableRead)
                    'Define and execute the INSERT SqlCommand for a new customer
                    strTitle = "INSERT "
                    Dim strSQL As String = "INSERT Customers (CustomerID, CompanyName) " + _
                       "VALUES ('BOGUS', 'Bogus Company')"
                    Dim cmdTrans As SqlCommand = New SqlCommand(strSQL, cnnNwind)
                    cmdTrans.CommandType = CommandType.Text

                    'Enlist the command in the transaction
                    cmdTrans.Transaction = trnCustOrder
                    intRecordsAffected = cmdTrans.ExecuteNonQuery

                    'INSERT an Order record for the new customer
                    strSQL = "INSERT Orders (CustomerID, EmployeeID, OrderDate, ShipVia) " + _
                      "VALUES ('BOGUS', 1, '" + Today.ToShortDateString + "', 1)"
                    cmdTrans.CommandText = strSQL
                    intRecordsAffected += cmdTrans.ExecuteNonQuery
                    'Commit the INSERT transaction
                    trnCustOrder.Commit()

                    'Delete the Orders and Customers records
                    strTitle = "DELETE "
                    trnCustOrder = cnnNwind.BeginTransaction(IsolationLevel.RepeatableRead)
                    strSQL = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'"
                    cmdTrans.CommandText = strSQL

                    'The previous transaction has terminated, so re-enlist
                    cmdTrans.Transaction = trnCustOrder
                    intRecordsAffected += cmdTrans.ExecuteNonQuery

                    strSQL = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'"
                    cmdTrans.CommandText = strSQL
                    intRecordsAffected += cmdTrans.ExecuteNonQuery

                    'Commit the DELETE transaction
                    trnCustOrder.Commit()

              Catch excTrans As SqlException
                   MsgBox(excTrans.Message + excTrans.StackTrace, , _
                      strTitle + "Transaction Failed")
                   Try
                        trnCustOrder.Rollback()
                   Catch excRollback As SqlException
                       MsgBox(excTrans.Message + excTrans.StackTrace, , _
                          strTitle + "Rollback Failed")
                   End Try
             End Try
        Catch exc As Exception
             MsgBox(exc.Message + exc.StackTrace)
        Finally
             'Close the SqlConnection
             cnnNwind.Close()
             Dim strMsg As String
             If intRecordsAffected = 4 Then
                strMsg = "INSERT and DELETE transactions succeeded."
             Else
                   strMsg = "INSERT, DELETE, or both transactions failed. " + _
                   "Check your Customers and Orders tables."
             End If
             MsgBox(strMsg, , "RunInsertTransaction")
       End Try
End Sub

This is another example of client operations that most DBAs won't permit. In production applications, stored procedures with T-SQL BEGIN TRAN[SACTION], COMMIT TRAN[SACTION], and ROLLBACK TRAN[SACTION] statements handle multi-table updates.

The above tip was excerpted from Chapter 1, 'Migrating from ADO to ADO.NET,' of the book Expert One-on-one Visual Basic 2005 Database Programming by Roger Jennings, courtesy of Wiley Publishers. Click here for the complete collection of book excerpts.