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 4 and 5 and type the answer here:
  • Post
  • Hi Beth,

    Can you guide me on how to get  a child form to save along with the record that I parsed from a parent form..

    the parent form is registration form, from the registration record to open a case form.

    Please look at the codes below:

    Private Sub OpenCaseFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OpenCaseFile.Click

           Dim c5 As New Cases

           c.Reg_IDLabel1.Text = Me.Reg_IDLabel1.Text

           c.Complainant_IDLabel1.Text = Me.Complainant_IDComboBox.Text

           c.Offender_IDLabel1.Text = Me.Offender_IDComboBox.Text

           c.Commission_IDLabel1.Text = Me.Commission_IDLabel1.Text

           c.ShowDialog()

       End Sub

    -----------------------------

    this is the save from function for the child form:

    Private Function Save() As Boolean

           Dim saved As Boolean = False

           If Me.RegisCaseFileDataSet.HasChanges Then

               Try

                   'Send Adds/Updates in Parent-Child order

                   Dim RegisUpdates() As DataRow = _

                       Me.RegisCaseFileDataSet.Registration.Select("", "", _

                       DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                   Dim CasesUpdates() As DataRow = _

                       Me.RegisCaseFileDataSet.Case_file.Select("", "", _

                       DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)

                   Me.RegistrationBindingSource.EndEdit()

                   Me.RegistrationTableAdapter.Update(RegisUpdates)

                   Me.ComplainantsBindingSource.EndEdit()

                   Me.ComplainantsTableAdapter.Update(CasesUpdates)

                   Me.OffendersBindingSource.EndEdit()

                   Me.OffendersTableAdapter.Update(CasesUpdates)

                   Me.CasefileBindingSource.EndEdit()

                   Me.Case_fileTableAdapter.Update(CasesUpdates)

                   'Send Deletes in Child-Parent order

                   Dim CasesDeletes() As DataRow = _

                       Me.RegisCaseFileDataSet.Case_file.Select("", "", _

                       DataViewRowState.Deleted)

                   Dim RegisDeletes() As DataRow = _

                       Me.RegisCaseFileDataSet.Registration.Select("", "", _

                       DataViewRowState.Deleted)

                   'Child

                   Me.Case_fileTableAdapter.Update(CasesDeletes)

                   Me.CasefileBindingSource.EndEdit()

                   'Parent

                   Me.RegistrationBindingSource.EndEdit()

                   Me.RegistrationTableAdapter.Update(RegisDeletes)

                   Me.ComplainantsBindingSource.EndEdit()

                   Me.ComplainantsTableAdapter.Update(CasesDeletes)

                   Me.OffendersBindingSource.EndEdit()

                   Me.OffendersTableAdapter.Update(CasesDeletes)

                   saved = True

               Catch ex As Exception

                   MsgBox(ex.ToString)

               End Try

           End If

           Return saved

       End Function

    ------------------

    I have set the relationship correctly, but I cannot get Reg_ID, Complainant_id, Offender_ID to be pushed into the case_file table..

    thanks,

    Best Regard

  • I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships

  • I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationships

  • I cannot figure out how to use the TableAdaptors to update Parent-child records when the Parent has been filtered i.e. with employees my Table Adaptor Selects employees that were not terminated.

    When I have a child table say EmployeeAvailability, when setting its table adaptor I have to apply the parent filter to ensure I only select child records that have a parent record selected.

    Now the child table adaptor will not automatically generate the Insert, Update or delete within the adaptor.

    Can anyone point me to some examples of how this real world scenario is handled.

    cheers

    Kieran

  • Hi rpms,

    The designer cannot generate commands automatically if you are using multiple tables in your select statement. In essence you are creating a "view" of your data and because there could be many different ways to update the backend tables, the designer wouldn't know what to generate.

    That said, because you aren't selecting anything different in the results than columns from the child table there is a trick that you can do.

    Use the dataset designer to generate all the commands as you normally would when you don't specify a filter on the child table. Then go into the Properties window and select the SelectCommand.CommandText property and write your join and filter condition to the parent table. As long as you do not modify the fields that are being selected this should work. When the designer asks if you want to re-generate the update commands answer NO.

    HTH,

    -B

  • Hi rpms,

    I wrote up a blog post on how a couple different ways you can do this real world scenario:

    http://blogs.msdn.com/bethmassi/archive/2009/07/22/filtering-child-datatables-using-tableadapters.aspx

    I hope this answers your question.

    Cheers,

    -B

  • I'm trying to make a master-detail insert in a local SQL CE database, updating through a TableAdapterManager.

    But I'm getting huge problems.

    Please help me looking here:

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/e66a081c-f622-4fe7-bf56-2828f04f9f48/

    Thanx

  • Hi Italian Cousin,

    You're going to need to do something similar as Access in this case since SQL CE doesn't support batch statements. Take a look at this post for more info:

    http://blogs.msdn.com/bethmassi/archive/2009/05/14/using-tableadapters-to-insert-related-data-into-an-ms-access-database.aspx

    HTH,

    -B

  • Thanks you my friend, you saved me.. i was trying all day to make this happen, and i done it when i read your article.

  • hi,

    thanks a lot, before i watch your video i have to write a long code to do this.

    how i wonder i can do all this in just a couple of rows...

    thanks again

    a how can i reject any change i have make before updating the database something like refresh the Form

  • Hi Swanto xu,

    Glad you liked the post. Make sure if you are using Visual Studio 2008 that you use the updated TableAdapterManager instead: http://blogs.msdn.com/bethmassi/archive/2008/01/07/the-new-tableadaptermanager-in-visual-studio-2008.aspx

    You can call RejectChanges method on the DataRow, DataTable or DataSet level. See this topic for more information: http://msdn.microsoft.com/en-us/library/system.data.dataset.rejectchanges.aspx

    HTH,

    -B

  • Hi,

    I am using table adapter in my winforms app that hold a record (pessamistic lock) style as long as the form is open (to be lock compatable with an existing COBOL app my winforms app will eventualy replace (now it works in parrallel). The problem is while a pending lock trasaction is going in one form, if the user opens any other form (or another instance of the same form, the lock is dropped in the exisitng instance as soon as the first table adapter in the new form runs a query (either custom or visually created fill queries.  Anyone have any idea why?

  • Hello,

    How do you retrieve the ID field from the parent table on an insert using the TableAdapterManager?  

    I need the (auto incremented) value to use elsewhere down the code.

    Example:

    PaymentTransactions tamPayTransaction = new PaymentTransactions();

    dsPaymentTransactions dsPayTransaction = new dsPaymentTransactions();

    tamPayTransaction.Fill(dsPayTransaction);

    dsPaymentTransactions.PaymentTransactionsRow drPayTransaction = dsPayTransaction.PaymentTransactions.NewPaymentTransactionsRow();

    drPayTransaction.Name = this.ctrlCreditCardInfo1.CC_NameOnCard.Text.Trim();

    drPayTransaction.Company = this.ctrlCreditCardInfo1.CC_Company.Text.Trim();

    drPayTransaction.CardNumber = strCardNumber;

    drPayTransaction.MerchantResult = int.Parse(pfp.ResponseResult);

    drPayTransaction.MerchantAuthCode = pfp.ResponseAuthCode;

    drPayTransaction.MerchantPNRef = pfp.ResponsePNRef;

    drPayTransaction.SessionID = Session.SessionID;

    drPayTransaction.Amount = this.m_dTotalAmount;

    drPayTransaction.EntryDateTime = DateTime.Now;

    dsPayTransaction.PaymentTransactions.AddPaymentTransactionsRow(drPayTransaction);

    // HERE IT RETURNS a -1, I need the SCOPE_IDENTITY() in this value...

    int nPaymentTransactionsID = drPayTransaction.ID;

    Thanks for any help you can provide.

  • Hi Norm,

    You will need to save the dataset to the server in order to get the real ID. You should be able to handle the RowUpdated event on the DataRow to obtain the value while the Save is happening. Keep in mind that if you have set up the relationships properly between your datatables then using the temporary key on the client will work. The keys will be refreshed automatically after the save.

    HTH,

    -B

  • well, i found your blog 2 hours ago. i was searching for more infos using typedDataset in VB, by the way, i love it both!

    your articles are so good, so clear, and i thank you very very AND very much.

    have a full happy life

    from brasil!

Page 3 of 3 (45 items) 123