Working with TableAdapters and Related DataTables
This past couple weeks I've been asked to help out a few people in the community work through a couple snags they were having with respect to the TableAdapters and how they should work in a multi-user application when updating related parent-child tables. The basic idea is very simple but there are a few tricks you need to do in the designer and in your update code that give people the hiccups sometimes.
What we want to do is control the order of update, insert and deletes so that multi-user batch edits will work inside a single parent-child form. There are many books and articles written on this but most describe how to use the DataAdapter directly to do it. Instead I want to use the TableAdapters (since they are easy to design). One of the many things the Visual Basic team tries to balance when they build designers is how much functionality should they expose against how easy it is to build something and get it to work immediately. A TableAdapter is meant to surface the basic functions in order to select, add, edit and delete against a single table in your database. TableAdapters are components that are generated by the DataSet designer and behind the scenes they use all the standard ADO.NET objects, Connections, Commands, and DataAdapters. And with partial classes you can extend these components with your own methods easily if you need more advanced usage. I think that's a pretty fair trade off.
There are a few key things that need to happen in each insert, update and delete situation that can get kind of tricky when you start having to deal with multiple related tables. For instance, when we insert a parent row to the database, the database primary keys are backfilled into the DataRow based on your insert statement defined on the TableAdapter. However the foreign key field in the child also needs to be automatically filled in the DataRow BEFORE it is inserted into the database, so that the database referential integrity will work. By default, the insert statement is generated for you and it selects the primary key field, but if you use database stored procs then you need to make sure you return the primary keys as well (in SQL-Server you can use SCOPE_IDENTITY()). I show an example of creating stored procs in this video and mapping them to TableAdapter commands in this video.
By default, the DataSet designer sets the DataRelations between parent-child tables as "Relation Only". This means that the DataSet will not enforce the foreign key constraints on the client, just your database -- this makes it a bit easier to work with the data on the client. But it also means that you may have a problem inserting your records that are involved in a parent-child relationship. This is because the key field isn't cascaded to the child DataTable in the DataSet before it is sent to the database and you may end up with a foreign key constraint violation. To fix this we need to set the DataRelation in the DataSet designer to "Both Relation and Foreign Key Constraint" and then you need to set the Update and Delete rules to Cascade. Just right-click on the DataRelation and select "Edit Relation" in the DataSet designer:
Once we change this, we need to be careful of the order we fill our DataSet using the TableAdapters. When you use drag-and-drop data binding, the designer writes some code for you in the Load handler of your form that calls the Fill methods on your TableAdapters. You need to make sure you fill them in parent-child order or the constraint will fail.
Me.ParentTableAdapter.Fill(Me.MyDataSet.Parent)
Me.ChildTableAdapter.Fill(Me.MyDataSet.Child)
Now in order to save the rows properly in this scenario we need to send inserts and updates on the parent table first, then the child. Then we can send deletes on the child and then the parent. The TableAdapters only know about one table in your DataSet so this isn't built in automatically when you call .Update() so we have a bit of work to do. (NOTE: There is a new object generated for you in Visual Studio 2008 called the TableAdapterManager that WILL handle this scenario for you! Stay tuned, we'll be converting this application once Beta2 is released.)
Here's an example from the Northwind database, saving Orders and OrderDetails. When the call to Me.Order_DetailsTableAdapter.Update(detailUpdates)is made, the OrderDetail DataRows have already been populated with the key values from the inserted Order DataRow. This is because we set the Update Rule on the DataRelation to Cascade.
Private Function Save() As Boolean
Dim saved As Boolean = False
If Me.OrdersDataSet.HasChanges Then
Try
'Send Adds/Updates in Parent-Child order
Dim orderUpdates() As DataRow = _
Me.OrdersDataSet.Orders.Select("", "", _
DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
Dim detailUpdates() As DataRow = _
Me.OrdersDataSet.Order_Details.Select("", "", _
DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
Me.OrdersTableAdapter.Update(orderUpdates)
Me.Order_DetailsTableAdapter.Update(detailUpdates)
'Send Deletes in Child-Parent order
Dim orderDeletes() As DataRow = _
Me.OrdersDataSet.Orders.Select("", "", _
DataViewRowState.Deleted)
Dim detailDeletes() As DataRow = _
Me.OrdersDataSet.Order_Details.Select("", "", _
DataViewRowState.Deleted)
Me.Order_DetailsTableAdapter.Update(detailDeletes)
Me.OrdersTableAdapter.Update(orderDeletes)
saved = True
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
Return saved
End Function
In the next post I'll follow up with how we can perform this kind of update inside a database transaction, taking a look at a couple ways we can do it, and I'll post a complete application that works against the Northwind database. UPDATE: Here's the follow up.
Visual Studio 2008 Update: Use the new TableAdapterManager to update related tables in a transaction. See this post for details.
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
