Know Dot Net Article

Use ADO.NET Transactions to Perserve Data Integrity

ADO.NET Transactions

by Les Smith

Preserve the integrity of your data through the use of Database Transactions in ADO.NET. It's easy and the payoff is win-win.

More times than any of us want to admit, we don't (think we) have time to do something right the first time, yet we always find time to do it over.

I wrote an Equipment Rental Database Application some time back. Many tables were involved and when it is time to check out equipment and write an invoice, several of these tables have to have concurrent updates and they obviously must to be kept in sync.  The same thing occurs when the equipment is checked in and the invoice is closed out.

For example, at least four tables are involved; Customer, Equipment, Invoice Header, and Invoice Detail.  Depending on how many items were involved, multiple inserts may be made to the Invoice Detail.  If you attempt to make updates to multiple tables and hope to keep them in sync, you must use database transactions.

The following set of code will show the code for wrapping multiple database updates into a single transaction to ensure the integrity of the database.  If any one of the updates fails, then no changes will occur in the database.  This illustration is over simplified and not really complete, with respect to the SQL statements, but you will get the jist of the transaction logic.

In the following code, I will do several key things to effect the transaction process.

Create and open a Connection Object to the database.
Add a Transaction object to the Connection. The Transaction object is tied to the Connection Object until the transaction is Committed or Rolled Back.
Create a Command Object and add the Connection Object to it.
Create an SQL statement for each update. For this article I am using Dynamic SQL, but in real life I would want to use a Stored Procedure. For that, I would change the CommandText type to StoredProcedure.
For each database action, I will use the same Command Object; just change the SQL statement for each new update.
Call the ExecuteNonQuery method of the Command Object to do the update.
If I do not get at least one row updated or inserted by each call to ExecuteNonQuery, I will throw an exception and terminate the processing, which will cause all of the database activity to be Rolled Back.

   Private Function CheckOut(ByVal EquipID As String, _
      ByVal CustomerID As Integer) As Integer
      ' wrap all database activity in a transaction
      ' so that all records are updated or none are.
      Dim conn As New SqlConnection("RealConnectString")
      Dim cmd As New SqlCommand
      Dim transact As SqlTransaction
      Dim Sql As String
      Dim rows As Integer

      Try
         conn.Open()
      Catch ex As Exception
         Return -1 ' can't open connection
      End Try

      transact = conn.BeginTransaction()
      cmd.Connection = conn
      Try
         ' now update the equipment table to indicate on rent
         Sql = "Update Equipment set Status = 'On Rent', "
         Sql &= "Renting_CustomerID = " & CustomerID.ToString & " "
         Sql &= "where EquipID = '" & EquipID & "'"

         cmd.CommandText = Sql
         rows = cmd.ExecuteNonQuery()
         If rows <> 1 Then
            Throw New Exception( _
             "Update of equipment record failed in checkout")
         End If

         ' Insert Invoice header record, use same command object
         Sql = "insert into Invoices(....) values(....)"
         cmd.CommandText = Sql
         rows = cmd.ExecuteNonQuery()
         If rows <> 1 Then
            Throw New Exception( _
             "insert of invoice record failed in checkout")
         End If

         ' insert invoice details here
         ' use same type of code as shown above

         ' Update customer record balance due here
         ' use same type of code as shown above

         ' if we make it here, all updates will be good
         ' commit them to the database
         transact.Commit()
         Return 1 ' check out was successful
      Catch ex As Exception
         ' if we get here some database action failed
         ' we don't want any of them to be record
         transact.Rollback()
         Return -2 ' database failed
      End Try
   End Function

If you are updating multiple tables and hope to keep them synchrionized, you should be using two methodologies. First, set up relationships in your database. In other words, make Invoice Details a child of the Invoice Header table. The database will then ensure that you cannot create a Detail record without first creating a Header record, etc. Secondly, use database transactions to guarantee that you don't update any tables in a situation like the one described in this article, without all activity being successful.