TableAdapters and Transactions

In my last post I showed you how to save related parent-child DataTables in proper update, insert and delete order by controlling how the TableAdapters saved the rows. In this next post I'll demonstrate how we can do this inside a database transaction. We use transactions when we want all rows updated consistently. Meaning if one row fails to update in the database, we want the whole batch to roll back like nothing happened. This can be useful in many scenarios.

Say you are saving an order and it's details into two tables, but you want to make sure that if any of the detail rows fail to save to the database, that the entire order is not saved -- keeping the order and its details together at all times. In the previous example if one of the detail rows failed to update, an exception would be thrown but part of the order would be saved into the database. It would be up to you to retry the save again on the rest of the rows on the client. This could cause potential inconsistencies in our data.

So what we want to do is to begin a transaction, submit all the rows for save, then commit the transaction if no exceptions were thrown, otherwise we want to roll back the entire batch like nothing happened. It's important to note that transactions are a costly database operation and you should only use them if you need to use them. For instance, don't use them if you are only updating one row at a time. It's best to use them when you are editing related rows on a single entity that needs to save consistently. For instance, if you are editing one single order and its details this is a good set of rows that should probably participate in a database transaction.

When we want our TableAdapters to work inside a transaction there are a few things we need to do in addition to setting up the DataRelation properly like I showed in the previous post. First we need to make sure that the DataAdapter behind our TableAdapter is set up properly. DataAdapters are the objects that bridge between DataSets and the database, executing the commands we define. Remember that a TableAdapter is essentially a nice wrapper around all the select, insert, update and delete ADO.NET command objects. We need to make sure that the DataAdapter exposed by the Adapter property on the TableAdapter is set up to not accept changes on the DataRows during an update. This is because if the transaction fails, we need all the RowStates preserved because we're going to roll back the database transaction. So set Adapter.AcceptChangesDuringUpdate = False. The other property we want to set on the adapter will tell it to stop processing the rows if an error occurs, Adapter.ContinueUpdateOnError = False.

Next we need to make sure that all the TableAdapters involved in the transaction use the same database connection. By default, TableAdapters open and close their own connections so we will need to write some code to be able to manually set the TableAdapter's connection object. The TableAdapter exposes a Connection property that we can set. What actually happens when we set this property is that all command objects are set with this connection.

Finally we need to set up the transaction object itself. There are a couple ways to set up database transactions, you can use the ADO.NET SqlTransaction object or you can use the .NET 2.0 TransactionScope object in the System.Transactions namespace. Let's take a look at both of these techniques using an application I built over the Northwind database.

Using the ADO.NET SqlTransaction object requires you to also set the transaction object manually onto the commands.  The command objects must have a reference to the same connection as well as the same transaction object. When you use a SqlTransaction, you have to set this up manually. Luckily you can easily extend your TableAdapter classes using Partial Classes. Here I created a method on each of my TableAdapters called AssignConnection that allows us to pass in a SqlConnection and a SqlTransaction object and sets them up:

Namespace OrdersDataSetTableAdapters

    Partial Class OrdersTableAdapter

        Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _

Optional ByVal trans As SqlClient.SqlTransaction = Nothing)

            Me.Connection = conn

            If trans IsNot Nothing Then

                Me.Adapter.InsertCommand.Transaction = trans

                Me.Adapter.DeleteCommand.Transaction = trans

                Me.Adapter.UpdateCommand.Transaction = trans

            End If

            Me.Adapter.AcceptChangesDuringUpdate = False

            Me.Adapter.ContinueUpdateOnError = False

        End Sub

    End Class

    Partial Class Order_DetailsTableAdapter

        Public Sub AssignConnection(ByVal conn As SqlClient.SqlConnection, _

Optional ByVal trans As SqlClient.SqlTransaction = Nothing)

            Me.Connection = conn

            If trans IsNot Nothing Then

                Me.Adapter.InsertCommand.Transaction = trans

                Me.Adapter.DeleteCommand.Transaction = trans

                Me.Adapter.UpdateCommand.Transaction = trans

            End If

            Me.Adapter.AcceptChangesDuringUpdate = False

            Me.Adapter.ContinueUpdateOnError = False

        End Sub

    End Class

End Namespace

Now that we have this set up we need to create the SqlConnection, begin the transaction and assign it to all the TableAdapters, then we can start our ordered save. However, what we're going to save is not the OrdersDataset that is directly on our form, we need to create a copy of just the changes and try to save that. Then if all goes well we will merge those changes with those on our main OrdersDataset. This technique avoids any concurrency errors that may occur if your transaction failed and you tried to resubmit the save. To make this work smoothly with inserted rows, it is recommended that we set the AutoIncrementSeed and AutoIncrementStep on the OrderID primary key in the OrdersDataTable to -1, -1. (You would do this on any AutoIncrementing primary key that was involved in the transaction.)

This allows us to remove the added rows on the client dataset easily before we merge the saved rows. This is needed because the DataSet.Merge() method looks at the primary keys of rows to determine row identity. If we left the inserted rows in our OrdersDataSet and tried to merge the saved rows we may end up with duplicates because the inserted database key may not match the client DataSet.

So let's write some code! First, get the changes and then create a new SqlConnection, open it, and then call BeginTransaction. That returns a SqlTransaction which we use to assign to our TableAdapters as you saw in the partial class code above.

Dim changes As DataSet = Me.OrdersDataSet.GetChanges()

If changes IsNot Nothing Then

    Dim conn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)

    Dim trans As SqlClient.SqlTransaction = Nothing

    Try

        conn.Open()

        trans = conn.BeginTransaction

        Me.OrdersTableAdapter.AssignConnection(conn, trans)

        Me.Order_DetailsTableAdapter.AssignConnection(conn, trans)

Next we process the ordered save, similar to what we did in the previous post, but this time we're working with a copy of the rows from the changes DataSet.

Next we commit the transaction and merge the changes or roll the transaction back if there was an exception thrown:

      trans.Commit()

      'Merges the changes and accepts them when the transaction succeeds.

      Me.MergeAfterSave(changes)

      Catch ex As Exception

          MsgBox(ex.ToString)

          'Rollback the transaction if there is an error

          If trans IsNot Nothing Then trans.Rollback()

      Finally

          conn.Close()

      End Try

  End If

Merging changes is very simple, you just remove all the rows with -1 keys, call Merge, then AcceptChanges:

Private Sub MergeAfterSave(ByVal dsChanges As DataSet)

    Dim row As DataRow

    Dim tblTarget As DataTable

    Dim i As Integer

    With Me.OrdersDataSet

        'First remove all the new rows that were added on the client.

        For i = 0 To .Tables.Count - 1

            tblTarget = .Tables(i)

            For Each row In tblTarget.Select("", "", DataViewRowState.Added)

                tblTarget.Rows.Remove(row)

            Next

        Next

        'Now merge and accept the changes

        .Merge(dsChanges)

        .AcceptChanges()

    End With

End Sub 

So that's the technique using SqlTransaction. Next I want to show you how our code gets a bit simpler if we use the TransactionScope object. This class is new to .NET 2.0 and it allows all kinds of flexibility when working with transactions and not just database transactions. Use this class if you need distributed transaction support like if you are saving to multiple database or other DTC enabled resources. You can read more about the TransactionScope object here and here. Keep in mind that the TransactionScope object will automatically promote a database transaction to a distributed transaction if you have different connection references and its a bit tricky to get it right, so you may opt for the "more code but safer" implementation above. Distributed transactions are very expensive compared to database transactions so use them wisely!

When we use TransactionScope we don't have to manually set the transaction object on our TableAdapter commands anymore, we just need to set up the shared connection. Also, we don't have to call Rollback explicitly -- this is done automatically if an exception is thrown. Here's the syntax of the entire save method using this technique:

Private Function SaveInTransactionScopeTransaction() As Boolean

    Dim saved As Boolean = False

    Dim changes As DataSet = Me.OrdersDataSet.GetChanges()

    If changes IsNot Nothing Then

        Dim conn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)

        Me.OrdersTableAdapter.AssignConnection(conn)

        Me.Order_DetailsTableAdapter.AssignConnection(conn)

        Try

            Using ts As New System.Transactions.TransactionScope

                conn.Open()

                'Send Adds/Updates in Parent-Child order

                Dim orderUpdates() As DataRow = _

changes.Tables("Orders").Select("", "", _

DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                Dim detailUpdates() As DataRow = _

changes.Tables("Order Details").Select("", "", _

DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                Me.OrdersTableAdapter.Update(orderUpdates)

                Me.Order_DetailsTableAdapter.Update(detailUpdates)

                'Send Deletes in Child-Parent order

                Dim orderDeletes() As DataRow = _

changes.Tables("Orders").Select("", "", _

DataViewRowState.Deleted)

                Dim detailDeletes() As DataRow = _

changes.Tables("Order Details").Select("", "", _

DataViewRowState.Deleted)

                Me.Order_DetailsTableAdapter.Update(detailDeletes)

                Me.OrdersTableAdapter.Update(orderDeletes)

                'The TransactionScope object will automatically perform a

                ' rollback if an exception is raised.

                ts.Complete()

            End Using

            saved = True

            'Merge the changes and accept them when the transaction succeeds.

            Me.MergeAfterSave(changes)

        Catch ex As Exception

            MsgBox(ex.ToString)

        Finally

            conn.Close()

        End Try

    End If

    Return saved

End Function

As you can see this is a bit cleaner code, but as I said, be careful using it. You can open up SQL-Profiler and have a look at the transactions that are being sent (you may have to add those events to your trace template). I've attached a complete sample application that shows these various techniques on different sets of Order data. As always, use this as a guide in your applications but make sure your scenarios call for transactions in the first place before diving in here. The sample application shows various sets of data from all orders, to just one customer's orders, to just a single order. This last scenario is where transactions fit the best. You can control if transactions should be used in each of these scenarios and which technique by selecting the appropriate value in the dropdown in the toolstrip at the top of the order form.

May all your transactions be short and succeed!