Applying transactions to multi-table updates
The SqlTransaction object provides clients with the ability to commit or roll back updates to SQL Server base tables. Here are steps to execute ADO.NET transacted 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.
ADO.NET Member | ADODB Member | ADO.NET IsolationLevel Description |
Chaos | adXactChaos | Prevents pending changes from more highly isolated transactions from being overwritten |
ReadCommitted | AdXactReadCommitted adXactCursorStability |
Avoids dirty reads but permits non-repeatable reads and phantom data (default) |
ReadUncommitted | AdXactReadUncommitted adXactBrowse |
Allows dirty reads, non-repeatable rows, and phantom rows |
RepeatableRead | adXactRepeatableRead | Prevents non-repeatable reads but allows phantom rows |
Serializable | AdXactSerializable , adXactIsolated |
Prevents dirty reads, non-repeatable reads and phantom rows by placing a range lock on the data being updated. |
Snapshot | None | Stores a version of SQL Server 2005 data that clients can read while another client modifies the same data |
Unspecified | adXactUnspecified | Indicates that the provider is using a different and unknown isolation level |
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.