Home > Applying transactions to multi-table updates
Book Excerpt:
EMAIL THIS LICENSING & REPRINTS

Applying transactions to multi-table updates

15 May 2006 | Wiley

Expert advice on database development
Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google

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.

Digg This!    StumbleUpon Toolbar StumbleUpon    Bookmark with Delicious Del.icio.us    Add to Google


RELATED CONTENT
.NET development for SQL Server
FAQ: How to troubleshoot and grant SQL Server permissions
Secure SQL Server from SQL injection attacks
Code to restore SQL Server databases in VB.NET
Custom VB.Net scripting in SQL Server Integration Services
Connect to SQL Server database with Visual Basics
SQL Server Blog Watch
Top 10 SQL Server development questions
Developing CLR database objects: 10 tips, 10 minutes
CLR architecture
Creating CLR database objects
.NET development for SQL Server Research

SQL/Transact SQL (T-SQL)
Using DATEADD and DATEDIFF to calculate SQL Server datetime values
Manipulate column names in a SQL Server table
SQL Server trigger vs. stored procedure to receive data notification
Physical data storage in SQL Server 2005 and 2008
SQL Server 2008 data types: Datetime, string, user-defined and more
SQL Server and data manipulation in T-SQL
Enforcing data integrity in a SQL Server database
Supertype and subtype tables in SQL Server
Using SQL Server datetime functions GETDATE, DATENAME and DATEPART
Ordering the results of a SQL query
SQL/Transact SQL (T-SQL) Research

SQL Server 2005 (Yukon)
Tips for tuning SQL Server 2005 to improve reporting performance
Using DATEADD and DATEDIFF to calculate SQL Server datetime values
SQL Server consolidation: Why it's an optimization technique
Parent-child dimensions in SQL Server 2005 with Analysis Services MDX
Using SQL Server datetime functions GETDATE, DATENAME and DATEPART
SSIS error message due to installation problem on SQL Server 2005
SQL Server data conversions from date/time values to character types
Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005
How to configure Database Mail in SQL Server 2005 to send mail
How to use rank function in SQL Server 2005
SQL Server 2005 (Yukon) Research

RELATED GLOSSARY TERMS
Terms from Whatis.com − the technology online dictionary
ACID  (SearchSQLServer.com)
commit  (SearchSQLServer.com)
DAO  (SearchSQLServer.com)
fetch  (SearchSQLServer.com)
OLE DB  (SearchSQLServer.com)
query  (SearchSQLServer.com)
SQL  (SearchSQLServer.com)
T-SQL  (SearchSQLServer.com)

RELATED RESOURCES
2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems
Search Bitpipe.com for the latest white papers and business webcasts
Whatis.com, the online computer dictionary


HomeNewsTopicsITKnowledge ExchangeTipsAsk the ExpertsMultimediaWhite PapersIT Downloads
About Us  |  Contact Us  |  For Advertisers  |  For Business Partners  |  Site Index  |  RSS
SEARCH 
TechTarget provides enterprise IT professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective IT purchase decisions and managing their organizations' IT projects - with its network of technology-specific Web sites, events and magazines.

TechTarget Corporate Web Site  |  Media Kits  |  Reprints  |  Site Map




All Rights Reserved, Copyright 2005 - 2008, TechTarget | Read our Privacy Policy
  TechTarget - The IT Media ROI Experts