Working with TableAdapters and Related DataTables

Working with TableAdapters and Related DataTables

Rate This
  • Comments 46

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 4 and 5 and type the answer here:
  • Post
  • In my last post I showed you how to save related parent-child DataTables in proper update, insert and

  • Announcing the LunchTimerCoder.com Initiative [Via: Chris Pietschmann ] Building a PC, Part I [Via:...

  • I just released two more videos ( #15 , #16 ) in the Forms Over Data video series . One is on understanding

  • Hi Beth,

    I've been enjoying your series and trying to follow using my own database.

    I'm getting an error about trying to convert a 1-dimensional array of system.data.datarow to a system.data.datarow -- AND I can't get past it.

    Do you have any idea where I might be goofing up?

    In the Meantime, I'll redo the example using Northwind.  

    Thanks for the series, it's terrific!

  • Katiebird,

    I can't tell what's wrong unless you post the code. Sounds like you have some erroneous parentheses after your DataRow variable. You might want to ask this question on the forums: http://forums.microsoft.com/msdn/showforum.aspx?forumid=32&siteid=1

    Cheers,

    -B

  • Recently I've had more than a few questions about how to handle working with data across multiple forms.

  • Hi Beth,

    I'm working through your FormsOverData series as an intro to SQL Server. I love it - I wish something like this had been around when I was cutting my teeth with Access!

    I thought I had my head around Parent-Child Relationships, then I ran into this problem with referential integrity which I can reproduce very easily using your OMS application.

    If I take Form2 from your OrderManager3 project, and enable referential integrity, it still all works fine. However, if I comment out the Fill calls in Form2_Load, then add a Button which does this:-

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

           Me.CustomerTableAdapter.FillByGetOne(Me.CustomerOrdersDataSet.Customer)

           Me.OrdersTableAdapter.Fill(Me.CustomerOrdersDataSet.Orders)

       End Sub

    [FillByGetOne just gets the Customer record where CustomerID = 1]

    So, I'm just pulling one Cust record down, but the Fill for the OrdersTableAdapter fails with an integrity violation. I'm really puzzled as to why. I'm sure its something very simple but I can't figure it out, even from your excellent series of videos.

    Thanks for reading this.

  • Hi Larson,

    When you enable referential integrity on the dataset that means the for every child there MUST be a parent. You need to make sure to only pull the records down for that same one customer on the orders table as well. Take a look at this video on how to handle saving as well: http://msdn2.microsoft.com/en-us/vbasic/bb725826.aspx

    HTH,

    -B

  • Of course, I see that now. My mistake was that just because I only *see* the Orders belonging to a single Customer, I'd assumed that's all that was being brought down. But of course it's not. All the Order records come down, and what I see is controlled by the BindingAdapter.  Correct?

    Thanks for the invaluable pointers as always.

  • Hi Larson,

    You can control what records are returned by adding a paramertized query on the TableAdapter. I show how to do that here: http://blogs.msdn.com/bethmassi/archive/2007/05/25/creating-a-parameterized-query.aspx

    Cheers,

    -B

  • Please I need A form with the code ro sendd emial anywhere

  • In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

  • In my previous post on TableAdapters and Transactions I showed a couple techniques on how to perform

  • I am banging my head against the wall trying to correctly get VB 2008 to update a related table.  My form/data are setup almost identical to how they were in both 'updating related tables' vidoes.  My problem is that when I add a record, the primary key of the parent stays -1 and perpetuates to the child.  So when the tableadaptermanager updates with the dataset, the parent is put in and assigned a new (autonumber) id, but the child table rows are put in with -1 as the id.  I am using Access 2003 and don't know if that is the problem.

  • Hi Don,

    I'm not an Access developer but I do know that it's a bit of work inserting rows since Access does not support batch SQL statements. What the TableAdapters do for you when you create your DataSets against SQL Server is they generate an additional statement after the INSERT command that is used to retrieve the SCOPE_IDENTITY().

    In Access (using the Jet Provider 4.0) you will need to issue a separate command (SELECT @@IDENTITY) after each row is inserted in order to retrieve the identity. You can do this by adding a handler in each TableAdapter to the _adapter.RowUpdated.

    Here's some information that may help explain: http://msdn2.microsoft.com/en-us/library/ms971502.aspx

    HTH,

    -B

Page 1 of 4 (46 items) 1234