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 .
Comments
Anonymous
July 11, 2007
In my last post I showed you how to save related parent-child DataTables in proper update, insert andAnonymous
July 11, 2007
Announcing the LunchTimerCoder.com Initiative [Via: Chris Pietschmann ] Building a PC, Part I [Via:...Anonymous
August 01, 2007
I just released two more videos ( #15 , #16 ) in the Forms Over Data video series . One is on understandingAnonymous
August 07, 2007
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!Anonymous
August 10, 2007
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, -BAnonymous
October 01, 2007
Recently I've had more than a few questions about how to handle working with data across multiple forms.Anonymous
November 29, 2007
The comment has been removedAnonymous
November 29, 2007
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, -BAnonymous
November 29, 2007
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.Anonymous
December 10, 2007
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, -BAnonymous
December 17, 2007
Please I need A form with the code ro sendd emial anywhereAnonymous
January 07, 2008
In my previous post on TableAdapters and Transactions I showed a couple techniques on how to performAnonymous
January 07, 2008
In my previous post on TableAdapters and Transactions I showed a couple techniques on how to performAnonymous
February 10, 2008
The comment has been removedAnonymous
February 11, 2008
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, -BAnonymous
April 06, 2008
testing... last post didn't appear...Anonymous
April 06, 2008
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.Anonymous
April 06, 2008
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?Anonymous
April 06, 2008
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!!Anonymous
April 21, 2008
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!Anonymous
May 31, 2008
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 tablesAnonymous
June 05, 2008
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 tablesAnonymous
June 24, 2008
Great article! I have not succueded to find a good explenation on this confusing subject until found your one. thanks a lotAnonymous
July 07, 2008
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 FransAnonymous
July 21, 2008
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, -BethAnonymous
January 24, 2009
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? thanxAnonymous
February 13, 2009
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, -BAnonymous
February 18, 2009
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 SubAnonymous
March 19, 2009
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!Anonymous
May 08, 2009
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?Anonymous
May 14, 2009
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
Anonymous
May 14, 2009
I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationshipsAnonymous
May 15, 2009
I’ve posted before about how to use TableAdapters to update parent-child (master-detail) relationshipsAnonymous
July 21, 2009
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 KieranAnonymous
July 22, 2009
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, -BAnonymous
July 22, 2009
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, -BAnonymous
September 09, 2009
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/ ThanxAnonymous
September 09, 2009
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, -BAnonymous
January 06, 2010
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.Anonymous
January 12, 2010
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 FormAnonymous
January 13, 2010
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, -BAnonymous
July 19, 2010
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?Anonymous
September 26, 2010
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.Anonymous
September 27, 2010
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, -BAnonymous
October 26, 2010
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!Anonymous
November 10, 2014
How would this work across multiple forms. Say I have a parent table on form1 and a child table on form2, how would cascade updates, inserts and deletes work.