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.
PingBack from http://geeklectures.info/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008/
We saw the new TableAdapterManager and thought it would be great. Unfortunately, due to a logic bug in the designer code, it fails to work with already open connections and therefore cannot perform an transactional UpdateAll across multiple DataSets without involving MSDTC. For the time being, we have a PowerShell script to fix the bad code everytime the designer file gets regenerated.
While I think your scenario should be supported in the designer, I'd be careful updating too many rows in a transaction when they really don't need to be. Even in my example above it's not really necessary for me to have the transaction wrapped around the entire batch. The risk of concurrency errors will increase in a multi-user system the more rows you're trying to update, and a rollback of all the data may not be desired if a single row cannot be updated. For instance, in the example above if the update worked but the insert failed, I probably would still want the update to succeed because the orders are independent of eachother. In other types of scenarios, however, you'd want the entire set to rollback so what I'm doing would make sense.
BTW, for now you can still roll your own manually like we did in VS 2005 if you do want to support multiple datasets.
I noticed that TableAdapterManager explicitly creates and commits transaction. How to make it participate in the ambient (TransactionScope controlled) transaction?
Just like Jason mentions, the generated code doesn't allow use of a shared connection thus not allowing for the this scenario with the TableAdapterManager. It will be fixed in VS2008 SP1. For now you can do it the same as you did in VS 2005. Here's the code for that http://blogs.msdn.com/bethmassi/archive/2007/07/11/tableadapters-and-transactions.aspx
Dear Beth, i'm a beginner in .NET developing and i'm writing a database management software like northwind. I like a lot the new data components you demo in "how do i" video series and the new enhancement such as TableAdapterManager and LINQ. I have only a problem and i would ask you the better solution. For example: if i load a datasset from database using tableadapter's fill method to use that data in a form and the same data are loaded and modified in another form or externally by another user of the database, how can i refresh the data loaded in the first dataset without refill the dataset?
Can you help me please?
There's a couple ways to do it. You can use the Merge method on the dataset to merge new records into a dataset you already have filled or you can set the .ClearBeforeFill property on the TableAdapter to False so that when you call the Fill method, it will merge the records returned from the query instead of clearing the DataSet first.
I have a problem with the code you suggested above. I have a project which is using an access (mdb) database. First of all, can I use the code above for this ?
Second when I try to use the tablemanager.updateall I always get an error saying there has to be a relation the parent table.
I've tried setting the cascade rule on the database relation level, on the dataset level (as you suggested), on both but I always get an error.
What am I doing wrong and what kind relation do you have to set and where ?
Do I have to set other properties (like incrementseed etc.) ?
I followed your code very thoroughly but still no succes.
Thank you for your reply
in regards to the bug in VS2008 that Jason found in the designer code - Does this effect only people who want to use TransactionScope controlled transactions ?
if i would like to use regular ado.net sql transactions, am i affected by this issue ?
I have just entered the code for the N-Tier DataSets by following the video for the “How Do I” series.
I have found a significant problem after the first save on the DataGridView form. When I try to save a second time I am getting an exception in the DataManager class in the DAL project.
In the line saved = (manager.UpdateAll(products) > 0) (orders in your code) it reports a ‘Concurrency violation’. I think you need to add new code as it thinks to record I have is out of sinc with the one at the server.
I have added a timestamp column to my table as I asumed it was a good way to keep track of changes on the table. I see there is no code at all to handel concurrent access to records so the user can choose to over write or drop and update the record without getting all the records again and reseting the whole DataGridView.
Maybe its time for another video building on the first solution!
If you have answer the problem somewhere can you direct me to it?
Regarding Access, you need to add code to the DataAdapter's RowUpdated event in order to retrieve the @@IDENTITY after a row is inserted. This is because Access doesn't support multiple statements in one command so the generated Insert code on you TableAdapter cannot include the retrieval of the primary key. I'll put it on the list to work on a sample for Access.
Hi May East,
Yes this will affect any type of transaction you want to perfrom accross *DataSets*. SQL-Transactions are automatic accross DataTables inside a single DataSet. In order to perform a transaction accross DataSets you will need to write the same code you did in VS 2005 for now. This will be fixed in SP1 of VS 2008.
I'm working on getting the CodeGallery project rolled out for this sample, I'm hoping it will be up this week. If it isn't, I'll put it up here. Feel free to use the contact form to email me and I'll send you the finished code.
Couple things to check, you need to make sure you pass the DataSet ByRef on the Save and that you merge the records properly on the client afterwards (as shown in the video). The TableAdapters also need to be set up properly so that they can retrieve the Identity keys on inserts. This should happen automatically if you have an identity PK in the database. By default, the concurrency check does so by checking all the original values against current values in the columns in the WHERE clause of the UpdateCommand, but if you set up a timestamp it will just use that (I recommend that approach if you don't need the finer concurrency check because this performs better.)
Well thanks for getting back to me as you must be very busy. I will do some checks and get back to you if I don’t get it sorted. Maybe I missed something in the video.
On another subject. I have a problem with the ‘smallmoney’ column type in the SQL server table. It always shows 4 past the decimal like ‘0.0000’ and the same format is appearing on the client forms. Can I change this on the server or on the BindingSource or where?
p.s. I see you play guitar. I help out with a band called ‘Kamagra’
You can see them here. http://www.systonsocial.co.uk/
Thanks for your time
You were right! I missed out…
After the save and I also added....
If changes.HasErrors Then
MsgBox("Data on the form is invalid and can't be saved", MsgBoxStyle.Exclamation)
...before the save to stop invalid data being sent.