TableAdapters and Transactions

TableAdapters and Transactions

Rate This
  • Comments 26

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 3 and 8 and type the answer here:
  • Post
  • PingBack from http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapters-related-datatables-and-transactions.aspx

  • I am very new to all this data adapter stuff and I think your videos and blogs are great, but I am stuck on one thing to do with related tables and it is driving me crazy. Can you help me? Plllllease????

    I need to simply update an invoice header and invoice item table, but I am getting an error giving me a foreign key violation (HEADERID in item table) even though I have followed your procedures.

    Here is my code can you tell me where i am going wrong? As I said HEADERID in the header is related to HEADERID in the item table.

           Dim o_InvoiceDataService As New DataService.Invoices

           Dim lobjHeader As New DataService.InvoicesTableAdapters.GDax_AuditInvoiceHeaderTableAdapter

           lobjHeader.SetConnectionString(My.Settings.cash_sheetConnectionString)

           lobjHeader.Fill(o_InvoiceDataService.GDax_AuditInvoiceHeader)

           Dim lobjItem As New DataService.InvoicesTableAdapters.GDax_AuditInvoiceItemTableAdapter

           lobjItem.SetConnectionString(My.Settings.cash_sheetConnectionString)

           lobjItem.Fill(o_InvoiceDataService.GDax_AuditInvoiceItem)

           ' Declare a variable named newInvoicesHeaderRow of type HeaderRow.

           Dim newInvoicesHeaderRow As DataService.Invoices.GDax_AuditInvoiceHeaderRow

           ' Assign the new HeaderRow that is returned to the newInvoicesHeaderRow variable.

           newInvoicesHeaderRow = o_InvoiceDataService.GDax_AuditInvoiceHeader.NewGDax_AuditInvoiceHeaderRow

           ' ***** Set the value of the columns in the new HeaderRow. ******

           newInvoicesHeaderRow.ACCOUNT_REF = "PP"

           ' ***** Add the new HeaderRow to the HeaderDataTable ******

           o_InvoiceDataService.GDax_AuditInvoiceHeader.AddGDax_AuditInvoiceHeaderRow(newInvoicesHeaderRow)

           ' Declare a variable named newInvoicesItemRow of type ItemRow.

           Dim newInvoicesItemRow As DataService.Invoices.GDax_AuditInvoiceItemRow

           ' Assign the new ItemRow that is returned to the newInvoicesItemRow variable.

           newInvoicesItemRow = o_InvoiceDataService.GDax_AuditInvoiceItem.NewGDax_AuditInvoiceItemRow

           ' ***** Set the value of the columns in the new ItemRow. ******

           newInvoicesItemRow.NET_AMOUNT = 46

           ' ***** Add the new ItemRow to the ItemDataTable ******

           o_InvoiceDataService.GDax_AuditInvoiceItem.AddGDax_AuditInvoiceItemRow(newInvoicesItemRow)

           lobjHeader.Update(o_InvoiceDataService.GDax_AuditInvoiceHeader)

           lobjItem.Update(o_InvoiceDataService.GDax_AuditInvoiceItem)

  • Hi Graham,

    You need to make sure that the relation is set up properly on your DataSet as I explain here: http://blogs.msdn.com/bethmassi/archive/2007/07/10/working-with-tableadapters-related-datatables-and-transactions.aspx

    This is because when you insert a new parent, the database fills its primary key and that key needs to be filled in the child's foreign key field before the child row is inserted. ADO.NET will do this for you (between the update statements) if you set up your relation properly, indicating that it's a FK constraint and to Cascade updates and deletes.

    HTH,

    -B

  • Thankyou very much. I looked at your videos again and solved all my problems. I have been looking for weeks for a site like yours.

    I think it is fantastic and feel confidence in using all these new features in the right way.

    I just wish I had come across it sooner, would have saved me hours of wasted time trying to get things to work.

  • I just released two more videos ( #15 , #16 ) in the Forms Over Data video series . One is on understanding

  • Great series!! I really love the Forms Over Data videos. Very informative especially for a somelike like me which came from access and vb6.

    I have been making a payroll project and the series has been my guide and im wondering if you can help me with one problem.  I have a parent detail dataset which i have related properly.  And i have created a update stored procedure for both the parent and detail table which has a "Where (PayrollID = @PID) And (EmployeeID = @EID) And (TableRowView = @Original_TableRowView)" clause on the timestamp column.  I followed the above transaction. The problem is i always get a concurrency error when i save the detail table.  If i comment out the timestamp Where clause it updates succesffuly.  Any ideas?

    Thanks

  • The TransactionScope example fails for me.  I'm using Express.  Is that the reason?  Any way around it?  I'll provide specifics, if you like, or is Express hopeless for Transaction processing?

  • Hi Bob,

    SQL- Express should work. Make absolutely sure that the connection reference is the same for all your TableAdapters.

    -B

  • Warren,

    Make sure that your Update statement also contains a ;SELECT after it to reselect the updated timestamp back into the DataSet. This should happen automatically when you use the designer. i.e.

    UPDATE [dbo].[Customer] SET [LastName] = @LastName, [FirstName] = @FirstName, [Address] = @Address, [City] = @City, [State] = @State, [ZIP] = @ZIP WHERE (([CustomerID] = @Original_CustomerID) AND ([Modified] = @Original_Modified));

    SELECT CustomerID, LastName, FirstName, Address, City, State, ZIP, Modified FROM Customer WHERE (CustomerID = @CustomerID)

    HTH,

    -B

  • thanks for the response, unfortunately the detail part is still not updating. the Master record seems to be updating fine but the whole transaction rollback because the detail is not updating.  I tried using the designer generated code but still get a exception saying a concurrency violation, the update command affeced 0 of the expected 1 record.  If i leave out the timestamp field from the where clause on the detail records the updates succeeds.  Any ideas?

    Warren

  • Warren,

    Does it work if you don't use stored procs and instead just use the TableAdapter designer generated insert/update/delete statements?

  • Hi beth,

    Using the table generated adapter designer statements also fails to update, - "concurrency violation, the update command affected 0 of the expected 1 record."  

    Warren

  • Hi Warren,

    Does the sample application attached to this post work for you? Are you using a timestamp field in SQL server or are you using a datetime?

    I would open a trace in SQL profiler and watch the update statement. It sounds like the database row is being updated before your dataset's row is updated. Is it possible you have a trigger firing somewhere?

    You also may want to ask your question on the forums because more people may be able to respond.

    http://forums.microsoft.com/msdn/default.aspx?forumgroupid=10&siteid=1

  • Hi Beth,

    Yes your example works perfectly but your example does not use store proc. to update the tables and yes i'm using timestamp fields.  I haved looked at the SQL profiler and although the update fails the timestamps get updated. Anyway thank you so much for taking time to respond to my questions. I wil check out the forum...

    More power,

    Warren

  • Hi Beth,

    Dang, I finnaly solved it!!! hahaha... stupid rookie mistake!!!.  It was the Cascade update on the primary key that was breaking the transaction.  I have a text based document number which i use as primary key and in my update sp updates this col which then cascade updates the details table and changes thier timestamp col hence the concurrency error. I rewrote the sp to not update the document no. which in the first place should never have been editable after inserting and the update transaction now works!! hehehe.

    Thank you so much beth, would never have done it without you.  Your form over data video series have been really a great help for me coming from vb6.access.  More power to you..

    Warren

Page 1 of 2 (26 items) 12