Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application 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.
When you use clickonce to deploy a database program, where are the database and application files installed?
I have written a database program and now I need to write a utility program to backup and restore the database. How would I do this in visual basic. What white papers can I read to help me get started? A few code snippets would be great..
Does the TableAdapterManager have an issue with cascading Foreign Keys on a Datatable with a self join?
I'm getting the error "ForeignKeyConstraint FK_ABC_DEF requires the child key value (xxxx) to exist in the Parent Table."
The self join relation has the Update Rule set to Cascade...
Hi Beth, I resolved that issue! The error was unrelated to the Self-join relation - the tableadapter manager updates the FK of the child records perfectly, Brilliant feature!
Hi Beth massi I found everwhere The hirachical prpperty of the Table adabter manager but it could not be see any where plz give the metode by which i can update the property Set it to True for the Insert update Delete Metode .
Plz Help me you can post replay on my mail id email@example.com
Hi Beth, thanks for all this great videos
I have a question about the tableadaptermanager
I need to merge the results of the saved dataset in the client side, but i wouldnt like to use the logic you used in your video (merge the returned dataset, delete added rows and then acceptchanges)
In VS2005, i used to set acceptchangesduringupdate=false for the tableadapter, but now with the tableadaptermanager, it doesnt work, even if i set it like this:
Dim taMngr As New DataSet1TableAdapters.TableAdapterManager
Dim ta As New DataSet1TableAdapters.CitiesTableAdapter
ta.Adapter.AcceptChangesDuringUpdate = False
taMngr.CiudadesTableAdapter = ta
Any ideas of how t prevent the tableadaptermanager from calling accept changes on the saved data so i can merge it back in the client without getting duplicated rows??
Thanks in advance
The technique I showed is necessary with n-tier datasets that have database-assigned primary keys because there's no way to know what the key will be on the client. If you do a straight merge without deleting the added rows first then you will end up with duplicate rows. You can create a more generic method that also checks and merges any errors in the tables as well. Call this after your middle-tier dataset comes back from an attempted save regardless if it returns true or false:
Private Sub MergeChanges(ByVal dsSource As DataSet, ByVal dsTarget As DataSet)
Dim tblTarget As DataTable
Dim i As Integer
For i = 0 To .Tables.Count - 1
tblTarget = .Tables(i)
'Remove added rows on the client first so we don't end up with duplicates
Dim addedrows = (From row In tblTarget _
Where row.RowState = DataRowState.Added).ToArray()
For Each row In addedrows
'Watch your constraints here, this will fail if the dataset constraints are invalid
' This will merge errors, new keys and any other middle-tier changes
If dsSource.Tables.Contains(tblTarget.TableName) Then
'Only AcceptChanges on the client's tables if the middle-tier returned no changes.
If dsSource.Tables(tblTarget.TableName).GetChanges Is Nothing Then
You spoke about RowUpdated event several times to resolve problem with Access databases, but I could not found that event in controls that designer generated for my form (xxxTableAdapter). So I noticed that event handler belongs to DataAdapter instead of TableAdapter class. So why should I do now? Change all TableAdapters with DataAdapter?
I also suggest you to add a field for email adresses in comments, so one can be notified automatically when others reply to his/her comment.
Please see this post: http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx
Also, you can subscribe to the comment RSS in the comment notification area above.
I am having the same problem as Frans(above), except that I am using a local Access database. My TableAdapter uses a join and displays fields from two tables. I am able to follow your solution for updating the table (stored procedures) using an sqlserver database, but can't get it to work for my Access database. All the store procedure & parameter options are not activated.
Access doesn't support stored procedures so these options are not available for this database. However you may be able to gleen a solution from these two posts I have done on Access and DataSets:
You can write an Update statement that updates only the main table and ignores the joined fields but if you need to update both tables you will need to have two separate TableAdapters against Access. Is there any chance you can use SQL Express edition, it's free.
Thanks so much for your help. I finally got it to work. I do indeed only need to update the main table.
The solution was fairly simple.
1) Create a TableAdapter for the main table only and copy the TableAdapter UPDATE procedure (stored in the TableAdapter properties window)
2) Configure the old SELECT query in the “TableAdapter Configuration Wizard Query Builder” to include fields from both tables (main table needs to contain a primary key field; tables were already joined in Access)
3) Paste the old procedure into the now blank TableAdapter UPDATE procedure
4) After creating a DataGridView, I was able to display fields from both tables and update the main table.
I have a winform named 'BillForm' with bounded textboxes (CustomerIDTextBox, BillNoTextBox and TotalTextBox - its datasource is typed dataset 'BillDataset'). I want to update Balance column in CustomerDataTable(it is also typed dataset) and its primarykey column CustomerID with equivalent CustomerIDTextBox & TotalTextBox value in 'BillForm'.
I'm not load or fill CustomerDataset in 'BillForm' formload event. Can you please provide me a sample program?
I tried this code, the datatable dt is updated but my database not update.
Private Sub BillingBindingNavigatorSaveItem_Click _
(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Dim da As New CustomerDataSetTableAdapters.CustomerTableAdapter
Dim dt As New CustomerDataSet.CustomerDataTable
dt.Rows(0)("Balance") += CDec (NetTotalTextBox.Text)
Don't call AcceptChanges until AFTER you successfully call Update. When you call AcceptChanges it marks all the rows in the DataSet with "no change" so if you call Update afterwards there will be no changes to send to the backend database.
Take a look at this topic in the library for more information: http://msdn.microsoft.com/en-us/library/ss7fbaez.aspx