Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for .NET developers. Learn more about Beth.
More videos »
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
'Commit all data to the OrdersDataSet
If Not Me.OrdersDataSet.HasErrors Then
If Me.OrdersDataSet.HasChanges Then
If Me.Save() Then
MessageBox.Show("Your changed have been saved.", Me.Text, _
MessageBox.Show("Your changes could not be saved!", Me.Text, _
MessageBox.Show("Please make changes first.", Me.Text, _
MessageBox.Show("Please correct the errors with this data first.", Me.Text, _
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.
''' 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.
Private Function Save() As Boolean
Dim saved As Boolean = False
If Me.OrdersDataSet.HasChanges Then
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
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.
In the N-Tier DataSets solution I have just added a new Query to the dataset in the DAL project. I have added the code in the DataManager.vb file of the same project and also updated IService.vb and Service.vb. When I returned to my form in the Client project I can’t find the new method in the proxy.
With this code…
Dim proxy As New xxxxService.ServiceClient
Is there something else I need to update to get it?
Sorry to be a pain but others must be getting the same problem.
I needed to update the Reference.vb under the Reference.svcmap in the Client Service References.
Is there in easy way to update this automatically?
When you update the service, you need to also update the proxy code on the client. This code is generated for you. Just expand the Service References on the client project in the Solution Explorer, select the service reference and select "Update Service Reference".
I have a ComboBox problem today.
It is bound to one table and the value member is the primay key but the problem comes from the fact the display member is a decimal (fdRate) not a sting and doesn’t like matching up with the user input. Is there a way of converting the display member (fdRate) to as string so that it can match with the users typing then set the value member (fdKeyID). You must have come across this problem before when not working with string values. When converting I need to see it as “#.##” formating.
Regarding the access database @@IDENTITY problem, I think I found a(nother) simple solution by setting the IncrementSeed and IncrementStep to a value on the dataset.
Than I don't get the constraint problems and everything works fine.
Is this solution also valid or is it not the way to do it ?
You're solution will not work in a multi-user application if you allow re-editing of the inserted rows. If you do not handle retrieving the identity generated by the database then you will need to refresh the client dataset (clear the dataset then call Fill again) so you can get back the key.
I thought that would be a problem. Fortunately the project I'm working on is single user (for the moment).
I have another project which is multi-user, so I think it will be "bye bye" Access and "welcome" Sql.
I've generate Typed Dataset using Dataset Designer Wizard in Visual Studio 2005...
Let's say I have 2 Table in the dataset, [CustomerGroups] and [Customers] with 1 to many relation (CustomerGroups 1->MANY Customers).
When I want to show the [CustomerGroups]."Name" in [Customers] Data Table so I configure the Query became like this :
"select t1.*, t2.Name as CustomerGroupsName from Customers t1, CustomerGroups t2 where t1.CustomerGroupsID = t2.id"
After I saved this query the data table is changed with added 1 column (CustomerGroupsName), but the main problem is Te First Generated Insert, Update, And Delete Command is gone and in the InsertCommand, UpdateCommand and DeleteCommand Property is set to (none)
How can I solve this problem? Because I need to work with Joined Query and need to update it and in the other side I know that command builder can't generate multiple table.
Is there any Property in TableAdapter like "MainTable" Property so I can set the main table I want to update? Or The New TableAdapterManager can solve This Problem?
Please Help... Thanks
I have a form that creates and edits Invoices. Everything works when I edit an Invoice using the tableadaptermanager.UpdateAll method. However, when I create an Invoice, I get a concurrency violation. I set up a trace in SQL Profiler and found that if I edit an Invoice by adding a new line item and editing another, an InsertCommand is executed on my InvoiceDetail table and then an UpdateCommand is executed on my InvoiceDetail table. Again everything works. When I create an Invoice with one or more line items, an InsertCommand is correctly executed on my Invoice table, but then an UpdateCommand is INCORRECTLY executed on my InvoiceDetail table. I checked the rowstate for each InvoiceDetail record and it is correctly set as Added and so why is an UpdateCommand executing on the InvoiceDetail table (instead of an InsertCommand) and causing the save to fail? Any ideas? I'll be happy to provide more info if needed. Please help. Thanks.
I got it working after some trial and error. I had the Accept/Reject Rule set to Cascade. Changing it back to None fixed my problem.
I'm running into a problem with the TableAdapterManager. I get the following error when I try to update a record. "Update requires a valid UpdateCommand when passes DataRow collecting with modified rows." I have used the configuration wizard in the DataSet designer to allow it to build the Select, Insert, Update, Delete statements for each of the tableadapters in the DataSet. Although I have all of the settings set the same for each adapter, it doesn't not create the Update and Delete statements for many of the TableAdapters. Can you tell me what I'm doing wrong?
You will need to write the queries yourself if you have an entity that is a join of multiple tables. I would suggest writing them as stored procedures in the database and then specifying those in the command properties of the TableAdapters.
Here's the documentation on TableAdapter configuration that also explains how to connect them to new or existing stored procedures:
The designer can't create update statements for DataTables that originate from more than one table in your database, is that what is happening in your situation? If so, you need to either specify the statement to execute or write a stored procedure to handle that case.
1. table in database have constrain on some fields
2. Open form with datagrid and delete row
3. Enter new row with same data
4. Try to update
5. Exception!!! Becouse it need the first row be deleted from database first then insert command
How can i workaround this problem?
Hi Beth, i have just installed vb2008 sp1 and i can't see the "Hierarchical Update" in the dataset properties (in the existing versions); the solution is properly set to "net framework 3.5", I created a new empty dataset and this property does not appears.