The New TableAdapterManager in Visual Studio 2008
In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform hierarchical updates inside a transaction when you have multiple related DataTables in your DataSet. The example I gave here shows how to use database transactions or the TransactionScope to update your data properly using Visual Studio 2005. It required a bit of code to manage the proper update order of rows, the connection and transaction on the TableAdapters, as well as managing a rollback situation where you wanted to preserve the original changes in the DataSet.
Luckily in Visual Studio 2008 the DataSet Generator has been enhanced to generate a new class that will automatically take care of all of these things in only a few lines of code! Enter the TableAdapterManager.
In this post I'm going to take the previous application and "upgrade" it using Visual Studio 2008. To be clear, I'm not going to update the target framework at all because I want the application to still run under the .NET Framework 2.0. (Visual Studio 2008 can be used to develop applications against .NET 2.0, 3.0 and 3.5 Frameworks under a feature called Multi-Targeting. For more information on how you can use Visual Studio 2008 to target multiple frameworks and upgrade projects, read here.)
When you open up the NorthwindTransaction.sln file in Visual Studio 2008 it will run an Upgrade Project Wizard that will convert the .sln file to be compatible with Visual Studio 2008. After that runs, double-click the OrdersDataSet in the Solution Explorer to open the DataSet Designer. In the properties window you will see "Hierarchical Update"; set that to True then click Save. This indicates to the DataSet Generator that it should generate the TableAdapterManager class. Note that when you create new DataSets the default is already True.
Now open up the OrderForm to open the Windows Form Designer. Delete the Dropdown on the ToolStrip at the top (TransactionToolStripDropDownButton) because we're just going to have one simple Save() method now. Right-click on the form and view the code. In the Click eventhandler for the OrdersBindingNavigatorSaveItem remove the Case statement and instead just call Me.Save():
Private Sub OrdersBindingNavigatorSaveItem_Click() Handles OrdersBindingNavigatorSaveItem.Click
Me.Validate()
'Commit all data to the OrdersDataSet
Me.OrdersBindingSource.EndEdit()
Me.Order_DetailsBindingSource.EndEdit()
If Not Me.OrdersDataSet.HasErrors Then
If Me.OrdersDataSet.HasChanges Then
If Me.Save() Then
MessageBox.Show("Your changed have been saved.", Me.Text, _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("Your changes could not be saved!", Me.Text, _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
Else
MessageBox.Show("Please make changes first.", Me.Text, _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
Else
MessageBox.Show("Please correct the errors with this data first.", Me.Text, _
MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End Sub
Now here comes the fun part. We're going to delete all the Savexxx() methods and write a single Save() method that uses the new TableAdapterManager. This code is doing the same thing as the SaveInDatabaseTransaction() method was previously.
''' <summary>
''' Performs an ordered save so that keys are properly updated in
''' the child table and so that deleted child rows are submitted
''' to the database first inside a database transaction automatically.
''' VS 2008 generates code that takes care of the saving in the proper
''' order in a database transaction.
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Private Function Save() As Boolean
Dim saved As Boolean = False
If Me.OrdersDataSet.HasChanges Then
Try
Dim manager As New OrdersDataSetTableAdapters.TableAdapterManager
'Back up the dataset so that if the transaction fails, then the entire
' dataset is restored to it's original state.
manager.BackupDataSetBeforeUpdate = True
manager.Order_DetailsTableAdapter = Me.Order_DetailsTableAdapter
manager.OrdersTableAdapter = Me.OrdersTableAdapter
saved = (manager.UpdateAll(Me.OrdersDataSet) > 0)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
Return saved
End Function
Notice how much simpler the code is now than was previously. Before we were having to manage the update order, the connection and transaction management, and the rollback state behavior ourselves. This is all taken care of for us now. Now that we're not using them anymore, we can also remove the MergeAfterSave method on the form as well as the AssignConnection methods on the TableAdapter partial classes. Note that if we had created a new Form, an instance of the TableAdapterManager would have been created for us that was preset with the TableAdapter instances already.
Run the application and select a customers' orders and add, update and delete parent and child rows on the form. When we click the save button the rows will be updated and inserted in parent-child order and deleted in child-parent order all within a database transaction. If we open up SQL Server Profiler we can have a look:
You can also indicate to the TableAdapterManager that you want updates processed first by setting the UpdateOrder property. Take a look at the TableAdapterManager documentation for more information.
The TableAdapterManager saves us a lot of code but you'll still need to make sure you've set up your DataSet properly to handle foreign-key constraints like I show in this post as well as call the Fill methods on the TableAdapters in the proper order. Take a look at this documentation for more details on enabling foreign-key constraints in your DataSets so that they can be properly updated by the TableAdapterManager. I've attached the updated application for you to play with.
Enjoy!
Beth Massi is a Program Manager on the VS Community Team working with the Visual Basic Team producing developer content on MSDN and her blog (http://blogs.msdn.com/bethmassi). As a VB community champion and a member of the Microsoft community she helps run a .NET user group in the San Francisco Bay Area and is a frequent speaker at various software development events. Before Microsoft she was a Senior Systems Architect at a health care software product company and was a Microsoft Solutions Architect MVP. Over the last
decade she has worked on distributed applications and frameworks using Visual Basic.NET, ASP.NET, SQL-Server, and Visual FoxPro. She has worked on various projects including developing object-oriented middle-tier frameworks, COM, .NET, Web and Windows-based applications using Microsoft development tools for a variety of businesses. She loves teaching, mountain biking, and modifying cars.
