Working with TableAdapters and Related DataTables

Working with TableAdapters and Related DataTables

Rate This
  • Comments 45

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.

Leave a Comment
  • Please add 3 and 1 and type the answer here:
  • Post
  • testing... last post didn't appear...

  • Ok that's weird.  I posted this message before and it didn't appear (I got redirected to your homepage), but my previous test post did work... Anyway, here goes again...

    Firstly, Beth, great article.  Exactly what I've been looking for, thanks!

    I had not set relations in the dataset designer to cascade integrity, and was hence struggling with parent/child relationships being lost after executing tableadapter.update() on the parent table.

    Now I'm past that, can you help with my next problem please?  I'm guessing I'm doing something silly...

    Basically, after inserting the parent row, I try to insert the child row (which appears to now be correctly referencing its parent).

    However, the transaction commits ok, but the child record is nowhere to be seen in the SQL database (the parent is visible there).  Do you know what I'm doing wrong?

    On the last couple of attempts to post, I included a code summary... but left it out this time in the hope that my message gets accepted.

    Thanks,

    Ben.

  • I've worked out why the second update - childTableTableAdapter.update(ds.childRow) - isn't working.

    It looks as though the first update for the parent row - parentTableTableAdapter.update(ds.parentRow) - causes the rowstate on the child row to change from "added" to "unchanged".

    I've no idea why that's happening though.  Any thoughts?

  • Hmmm.  Rank amateur.

    I had Accept/Reject rule on the relationship set to cascade as well...

    Thanks again for getting me headed in the right direction!!

  • Hi Beth,

    Thanks for this article - it was very helpful in setting up related DataTables in my current project.

    One point that might be helpful for developers - if you're not using drag-and-drop databinding, you need to link the related records in code using <childrow>.SetParentRow(<parentrow>). Took me a while to figure that out!

  • 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

  • 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

  • Great article! I have not succueded to find a good explenation on this confusing subject until found your one.

    thanks a lot

  • Hi beth,

    thanks for all your video's, I've learned a lot from them. I'm currently combining the video's to work with a middle tier and updating related tables.

    (i've created a tool to generate the webservice from the datamanager so they are always in sink) i think it's cool.

    one thing i'm a bit confused about, is when you say you dont want to load the whole table in the dataset when you don't need all of the data.

    I can understand that. So in the query we make a filtering using a @parameter. But wat is the best way to get the related childs loaded in the related child table.

    Do we have to loop through the parent table and load the corresponding childs? perhaps not with a loop but triggert by an event?

    Or is the best way to create a fill method on the childtable wich is based on same filter as we've used to fill the parenttable?

    thanks again

    Frans

  • Hi Frans,

    When developing a distributed application you always need to balance the number of calls you are making to your middle-tier with the amount of data you want to return. In general, it's better to bring down a larger set of data than to make multiple repeated calls to the middle-tier. This is because even the simple act of making the call can be expensive. So looping may not be the best way to go about it.

    However, you definitely don't want to clog the wire with a lot of data especially if you don't really need it on the client.

    So you need to look for the right balance by doing perf and load testing. Try to break down each entity into a manageable set of data and test the performance and scalability. For instance you may have a form that works with a single order and its order details. That would probably be a reasonable set of data to bring down in one call.

    Also take a look at caching lookup tables and other read-only data on the client to help scalability. There's an easy way to add local caching to the client in VS2008. Here's a video on that technology: http://msdn.microsoft.com/en-us/vbasic/cc307991.aspx

    HTH,

    -Beth

  • Hi Beth,

    I am developing an app with Visual c#. But I am stuck at this point:

    System.Data.SqlClient.SqlCommandBuilder cb;

    cb = new System.Data.SqlClient.SqlCommandBuilder(daNI);

               DataRow dRow = dsNI.Tables["TaskNI"].NewRow();

               dRow[1] = taskTitle;

               dRow[2] = taskText;

               dRow[3] = taskDate;

               dsNI.Tables["TaskNI"].Rows.Add(dRow);

               daNI.Update(dsNI, "TaskNI");

    So this is my code. My database table has 5 columns 1st and 2nd tables are primary and foreign keys respectively.  3rd and 4th columns are nullable

    Error says: connection cannot be initiated. But when i tried to insert string where it should be datetime it recognizes that that column has to be datetime. So i think problem is arising from updating primary and foreign keys. How do we update foreign key?

    thanx

  • Hi Nurlan,

    If the error says that the connection cannot be initiated then it's something wrong with the database connection. Check your connection string and make sure you've assigned a connection to the data adapter.

    HTH,

    -B

  • Hi Beth,

    I followed your tutorials fine for updating two related tables (many thanks!) but am running into problems going from two to three related tables – can you help? I’m new to VB2008 and it is probably something obvious! A simple example (using the Northwind database):

    I drag three datagridviews onto one form: Customers (main node), Orders (related node) and Order Details (related node under Orders).

    Orders is a child of Customers, and Order Details is a child of Orders.

    I’ve created Relation and FK Constraints with Cascade Update and Delete rules for both relations. Hierarchical update is set to true.

    I add the following code (below) to the form, but when I run the code attempt to save a new customer with a new order and order details I get the error: ‘You cannot add or change a record because a related record is required in table ‘Orders’.

    Private Sub Form1_Load_(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Me.CustomersTableAdapter.Fill(Me.NwindDataSet.Customers)

    Me.OrdersTableAdapter.Fill(Me.NwindDataSet.Orders)

    Me.Order_DetailsTableAdapter.Fill(Me.NwindDataSet.Order_Details)

    End Sub

    Private Sub OrdersBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles OrdersBindingSource.AddingNew

    Me.CustomersBindingSource.EndEdit()

    End Sub

    Private Sub Order_DetailsBindingSource_AddingNew(ByVal sender As Object, ByVal e As System.ComponentModel.AddingNewEventArgs) Handles Order_DetailsBindingSource.AddingNew

    Me.OrdersBindingSource.EndEdit()

    End Sub

    Private Sub CustomersBindingSourceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomersBindingSourceBindingNavigatorSaveItem.Click

    Me.Validate()

    Me.CustomersBindingSource.EndEdit()

    Me.OrdersBindingSource.EndEdit()

    Me.Order_DetailsBindingSource.EndEdit()

    Me.TableAdapterManager.UpdateAll(Me.NwindDataSet)

    End Sub

  • Hi

    Wondered if you ever managed to resolve this problem?  I found that when working with three or more related tables that i couldn't get the TableAdapterManager to work.

    My only workaround was sending in the updates, changes and deletes in code as you had to in vb2005 (before the TableAdapterManager was created).  

    I used the code from Beth's video here

    http://msdn.microsoft.com/en-us/vbasic/bb725826.aspx

    i came into even more problems trying to do all this across different forms and keeping them all in sync!

  • I too have had trouble using the tableadapter to update multiple related tables all at once! I'm really hoping there is some sort of easy "duhhh" thing going on here because i dont want to have to manually code everything. Althought at this point, after a month of chugging through possible issues...im about ready to do it. Has ANYONE figured out how to get the tableadaptermanager to work using three or more (nested) related tables?

Page 2 of 3 (45 items) 123