The following 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.
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.
