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.
Hi Beth, I fixed the problem, thanks anyways.
You can't use the TableAdapterManager to automatically do that. Instead just call the TableAdapter.Update method passing it just the deleted rows first then you can update the rest.
Me.MyTableAdapter.Update(Me.MyDataSet.MyTable.Select("", "", DataViewRowState.Deleted))
in regards to Serge's post in January of 2008, I'm having similar issues trying to get the FK for a child table with the end result being -- cant add records for a child table. Interestingly, I cant even manually add the primary key / foreign key in the child table and let it update that way. Any ideas?
If you aren't using SQL Server (or more specifically a database that can run batch SQL statements like Access) then you will have to add a handler to the RowUpdated event and retrieve the key maunally. If you look at the attached sample you will see that the Insert statements on the TableAdapters are actually batch statements that return the keys. I'm not an Access expert but here's an article that may help you handle this situation if it applies to you:
This all makes sense, however once you get the PrimaryKey -- do you have to manually code the entering of the the PK as the Foreign Key in the child tables AND handle adding new records? It wont be bad if the child table was represented as a datagridview -- but if they were all textboxes, it would be pretty tedious if you have many child objects you need to check on.
If you set up the DataSet relations and the related BindingSources on the controls properly this will happen automatically for you. See this video:
Even if your child table is represented as text boxes instead of a datagridview? I can get it to work for the latter without any modification... Just not as a detail view. Fantastic videos by the way. Nice, Clean and organized.
Yes that should work as long as you set up the relations and bindingsources correctly as shown in that video. You also need to make sure you call EndEdit on the BindingSources to push the changes into the DataSet before calling UpdateAll on the TableAdapter. Perhaps that is the problem?
I saw your video-series...it's great!
I have a question: in my previous application I use a MS Acces databse with password. Because of the "identity-problem" of the access database, now I'm using an mdf database and I want to set its password. How do I set the password?
Another question: I don't understand what is the SQL Server version installed by VB 2008 (component of the VS 2008 iso image)...I have only two directories: Microsoft SQL Server and Microsoft SQL Server Compact Edition.....what version is it?
I believe SQL Server 2005 Express is installed with Visual Studio. You probably want to install SQL Server Management Studio though. It's easier to add users and manage security from here:
Here's a set of tutorials on how to use it:
For SQL Server 2008 Express with SSMS see: http://www.microsoft.com/downloads/details.aspx?FamilyId=7522A683-4CB2-454E-B908-E805E9BD4E28&displaylang=en
Are there any microsoft whitepapers that explain how to create a multi-user database applications with vb.net?
Thanks for the video tutorials on VB. They are the wonderful practical demo tutorials , I have ever seen.
I have been following the video on how to update related tables in VB 2008. As mentioned in the video I have modified the relationships between a parent and child table to Cascaed for Update and Delete rule.But here I am facing a new problem.(I have the parnet table added to the form as 'Details' and related child table entries under datagridview.)
The problem : Whenever I delete a child row from datagridview, the entire Parent table corresponding to that and all other childs entries get deleted!. Why does this happen?
Here what I am trying to do is, I created a SProc, for delete, which sets a bDeleted column to True in the table. I have changed the datatableadater property for delete command to this SProc and set the parameter properly. Also in the Delete rule (under edit relationship - datatables) I tried setting it to None. But this time it throws an error saying "Cannot delete this row because constraints are enforced on relation FK_Tests_Test_GroupTests, and deleting this row will strand child rows." . Could you please guide me on this.
Hi Beth, I'm just starting to work with DataSet, DataBinding, DataSource and TableAdapter on VB2008.
Now, after a lot of trying and error tests, I have already a connection to a MS Access base using a DataSet, TableAdapter, DataGridView and a DataBinding.
Now I have a barely idea about how its work, but I want to know exactly what is the purpose of a DataBinding, DataSource and Dataset.
All the information I can find is about how to use it, but did not explain what is it purpose.
So I get mess and I don't know if I must to make the queries to the DataSet, to the DataSource, to the DataGridView or DataBinding.
Thanks in advance, Roberto.
(Excuse me if my English is no to good, I'm trying to get a better one).
I believe, it would be really helpful to you to go through the video tutorials given by Beth. Please find the link given below.
I managed to solve the previous problem, by removing the "enforce constraint" for the dataset. and by changing the binding source of the binding navigator between child and parent datatables on datagridview enter and leave events.
I am not sure if there is a better way to do it. if yes , please suggest.
Thanks for all the great videos!
Regarding the "new" Table Adapter Manager, I can't get the example to work with the SQL Compact version. Perhaps it is not ment to be used with it or am I missing out on something?