The New TableAdapterManager in Visual Studio 2008

The New TableAdapterManager in Visual Studio 2008

  • Comments 76

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.

Update: Also check out this "How Do I" video on how to use the TableAdapterManager to update related DataTables.

Enjoy!

Attachment: NorthwindTransaction2008.zip
Leave a Comment
  • Please add 1 and 4 and type the answer here:
  • Post
  • Great! Thanks for your time and thanks for the link...

  • Hi Beth,

    Great tutorial. In my form I only need to create a master \ detail record (no need to load existing entries). Can you give me an idea of how to go about this?

    Like in the tutorial it should allow for multiple detail records to be added but as a detail view of the DataGridView as there will be quite a number of detail columns and its better to lay them out individually.

    Any help much appreciated.

    Declan

  • Hi Declan,

    When you drag the related order details from the Data Sources window just select "Details" instead of DataGrid. The toolbar above the gid in that example works against the OrderDetailBindingSource so it will allow you to navigate throwgh the detail rows no matter if the details are displayed in a grid or layed out individually.

    HTH,

    -B

  • Sorry the issue is more to do with the saving of the record. I was able to layout the data with a binding navigator for the detail as you described.

    When the form is opened the ( + ) symbol to add detail entries is enabled. When the specific data is entered and the save icon is clicked on the parent navigator, the primary key (identity seed) is not being saved on child record. I have followed the tutorial to ensure the EndEdit() is called on each binding source as well as the AddingNew event on the child binding source.

  • I managed to solve the issue...now onto validating the data on save :-)

    Thanks for the help and keep up the excellent work

  • Hi Beth

    have a problem with my datasase.In my form i have three related tables.The problem is that i can't update all three tables but only the first two with hierarchical update ( I used the article from Microsoft MSDN site).

    All datagrids are drag-and-drop from data-source all the code is auto-generated. I use VS 2008 and Northwind db.

    Thanks

  • Hi Kostas,

    You need to make sure you set up both relations between the three tables as a Foreign Key and Constraint and set Update and Delete rules to cascade. Then you need to make sure you call EndEdit on the parent BindingSources as you navigate to the children.

    Take a look at this video and make sure to repeat the process for your third table:

    http://msdn.microsoft.com/en-us/vbasic/cc138241.aspx

    HTH,

    -B

  • Hi Beth

    I followed your instructions from the video. I set up the commands for the third table , set the relations to cascade but nothing. I put three ENDEDITS . more help would be apreciated

    thanks

  • Hi Beth,

    I know that your post is quite old now, but as I read your blog since some months, I thought that you could share your expertise on TableAdapterManager and bound controls.

    Consider the simplest example where a datagridview bound to an access database is dropped on a form (VS2008/.Net3.5 SP1).

    The designer creates the usual objects (adaptermanager, bindingsource, dataset and so on).

    My problem is the following :

    - A user is editing a cell value in the datagridview

    - the network becomes unavailable

    - when the user clicks the NAvigator save button, an exception is thrown during TableAdapterManager.UpdateAll(Me.theDataSet) method. This exception states that the database.mdb file is unreachable.

    Until now, that's OK.

    What I would like to do is to display in the datagridview, the previous value, that is to say the original value, not the modified one, in order to let the user choose to retry the editing or quit the application.

    I've set the TableAdapterManager.BackupDatasetBeforeUpdate to true, with no result. I've tried to call bindingsource.resetbindings or resetcurrentitem, but can not find any solution.

    Is it possible to achieve this behavior using a DataGridView, its binding, the dataset, or tableadapter, or should I backup myself the initial values of the edited row in order to retrieve them when exception occurs ?

    Thanks in advance.

    Manu

  • The solution is to simply call DataSet.RejectChanges().

    I didn't understand  this method.

    Thanks anyway.

  • Hi Beth Massi,

    I'm relatively new to VB2005/ado etc. (Old linear guy). Anyway, I'm trying to use the VB2005 tools to create and manipulate 2 DBs, read only, but they are connected. Should I be using the tools or just do it manually. All the references I see are to forms, where as I don;t have any forms, as this app is a service under windows. Just having a hard time trying to use the tools.

    BTW I like the videos.

    Thanks

    Matt.

    PS: my personal boating speed record WILL BE 110mph in a cigarette.

  • How do you fill the dataset with TableAdapterManager ?

  • Hi Beth:

    I have a problem. I am new in vb.net 3.5 and I have to update a program that was done by another person. My problem in that the project has a TableAdapterManager but not all the TableAdapters are included in this TableAdapterManager. I would like to know how can I include this others TableAdapters in the TableAdapterManager and specifically a QryTableAdapter.

    I think that these other TableAdapters were created after the TableAdapterManager was generated and I don't know how to update the TableAdapterManager.

    Thanks

  • Hi Beth,

    Awesome blog by the way. I have a question about datatablemanagers. I want to be able to use the dataset designer in my software but I'm having an issue where I need to be able to change the connection string at runtime. Currently, I get my connection string from my app.config where I have multiple connection string. I retreive the User setting of the current database I want to use and get the string to a variable. Now my issue is how do I "extend" of overide the strings in each table adapters without writting a partial class for each of my table adapters. I already have over 30 different ones and they change daily.

    I have read many forums and post that seem to fix the issue but only by creating a partial class for each tables adapters. I don't want to have to retype all the time whenever I change or add something to my dataset.

    I hope you can guide me in the right direction!

    Patrick Miron

  • Hi, I've seen the videos you've posted and I like very much the way you explain everything, now I'm having a problem with Updating Related Tables with this method you posted, I was trying with a simple 2 related tables DataBase, Clients and Addresses, I modifyed the RelationShip Update/Delete Rules of the DataSet, but still doesn't update the child, only the parent, I didn't drag it to the Form as DataGrid, I did it as Details, both Parent and Child, I didn't thought that could make any difference, but I changed the design to DataGridView and only this way worked fine, now I would like to know if this technique doesn't work having both Parent and Child(s) as Details? I don't always like to make it as DataGridView, please could you answer me this, please.

    Best Regards

Page 5 of 6 (76 items) «23456