TableAdapters and Transactions

TableAdapters and Transactions

Rate This
  • Comments 27

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.

       '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)

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!

Visual Studio 2008 Update: Use the new TableAdapterManager to update related tables in a transaction. See this post for details.

Attachment: NorthwindTransaction.zip
Leave a Comment
  • Please add 1 and 8 and type the answer here:
  • Post
  • Hello,

    if I insert (in a new order) more than one product the line below starts a infinite loop and raises an overflow exception.

    "For Each detail As Order_DetailsRow In details"

    (method Order_DetailsDataTable_ColumnChanged, file OrdersDataSet.vb)

    What could be wrong?

    I am using VS2005 and SqlServer2005 Express.

    Regards

  • Hi Beth,

    First of all, I would said that this article is excellent and thank god that I found this website.

    I seems not to be able to solve the dbconcurrency or  the delete and update - "update command affected 0 of the expected 1 record."  problems when working with related Tables.

    I followed your previous(first part) of this article and my problems seems to go away at least for now but that problem seems to come back occasionally. I will received that error occasionally when start working only with new inserted records.

    I would like to implement this second part of tutorial but I'm working with MS Access Database. Maybe you could post an article similar to this but intended for MS Access Database.

    Thanks

    Sam

  • Hi Beth:

    Wow, you certainly understand dataadapters!  I too am having issues with "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" and only occurs when I add a new row to my dataset and attempt to use the Update method on my dataadapter.  I have read about 20 different resolutions to this problem but none of them seem to apply to my situation.  I did add a partial class to trap the RowUpdated event on the data adapter so that I could insert the Primary Key value into my dataset (using a Stored Procedure on SQL Server 2005) but that didn't solve my problem either.  I am running out of solutions on resolving this problem and it's probably something benign and not obvious.

  • Hi Ed,

    If the parent fails to insert correctly then the child will also fail. It's important to do this inside a transaction like I explain above so that the integrity of your data stays intact. To troubleshoot the exact problem use SQL Profiler to see if there are failing triggers or other messages being returned from your database.

    HTH,

    -B

  • In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

  • In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

  • May i know is that any way to do this in vb.net 2003, the concept and the purpose is totally what i want to do but i using the visual studio 2003. Please help up... Your help are highly appreciated.

  • content is fantastic. it help me a lot , thank you Beth Missi

  • Thanks Beth,

    I have been trying for ages to implement batch transactions but none of the MS examples use strongly typed datasets and I could never work out how to set the transaction object.  I never would have thought of partial classes.

    Now my users won't keep getting upset because the data is inconsistent.

    Ben

  • Thanks for this piece of Code.  All other options I have seen are written in C#.  I had to modify it for oracle which was easy.   I did run into one problem which had me stumped for a few hours.  I am using custom insert commands and it kept giving me an obscure error.  I finally adapted the code for the partial class to following:

               Dim CC_CNT As Integer

               Me.Connection = conn

               If trans IsNot Nothing Then

                   Me.Adapter.InsertCommand.Transaction = trans

                   Me.Adapter.DeleteCommand.Transaction = trans

                   Me.Adapter.UpdateCommand.Transaction = trans

                   For CC_CNT = 0 To Me.CommandCollection.Length - 1

                       Me.CommandCollection(CC_CNT).Transaction = trans

                   Next

               End If

    This put transaction on all the commands in the command collections as well as the generic update/insert and delete.  Hope this helps others

  • I found this (blogs.msdn.com/.../tableadapters-and-transactions.aspx) helpful.

    I want a video regarding this project. I want to know about more on  LineTotal and OrderTotal fields and their counterparts in the database and related settings to successfully handle such scenarios.

  • Hey,

    Thanks for your great posts,

    I'am using VS Express 2013 and using bindingsources and tableadapters not sql queries or conn strings.

    How can i adapt this article to my program.

    Currently i'am inserting a new row with tableadapter.insert just to see the line i added to dgv. so much usage of insert, update and delete queries. And i'm not happy with that.

    I fact i'm working on using datatable.addnewrow to insert row to dgv first and then insert them when i click save button.

    Is it possible by just using tableapadters and bindingsources?

    P.S. If you know any detailed article about table adapters and bindingsources which explains usage of their properties with examples would you mind sharing it with me?

    Thank for your help in advance.

Page 2 of 2 (27 items) 12