Saving two DataTables, with Parent - Child relation

Hi experts,

simple scenario : I have two tables, tblOrders and tblOrdersDetails. tblOrders hold generic order info, and tblOrdersDetails hold all items related to this order.

columnare as follow:  tblOrders :  OrderID (identity, autonumber)  and column Customer (nvarchar)

in the tblOrdersDetails, the columns are : ItemID(identity, autonumber) and column OrderID (int) wich I want to be equal to the OrderID form tblOrders.

Now when dealing with DataTables, how do I get an order to save to the tblOrders, and then all records that are saved to the tblOrdersDetails, I want them to have the previously generated Identity filed in their column OrderID ?

Any help please, very urgent to complete this.
Who is Participating?
Have the stored proc that inserts the order return the value of SCOPE_IDENTITY() and use that value in your orderdetails table
Are you using a dataset?
Francis_BAuthor Commented:
Yep, using a dataset...
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

I would do it like this:

Let's say that your dataset is called DataSet1.  You will need to figure out the specific names from your dataset, but in general.

DataSet1TableAdapters.tblOrdersTableAdapter ordersTA = new DataSet1TableAdapters.tblOrdersTableAdapter();
DataSet1.tblOrdersDataTable ordersDT = ordersTA.GetData();

DataSet1.tblOrdersRow row = ordersDT.NewtblOrdersRow();

row.OrderDate = DateTime.Now;
row.Name = "James Smith";
row.Address = "address...";
row.Etc = "and so on...";


// Post the order (master row) to the database

// Now you can insert the order detail rows the same way
DataSet1TableAdapters.tblOrderDetailsTableAdapter detailsTA = new DataSet1TableAdapters.tblOrderDetailsTableAdapter();
DataSet1.tblOrderDetailsDataTable detailsDT = detailsTA .GetData();

DataSet1.tblOrderDetailsRow detailRow = detailsDT.NewtblOrderDetailsRow();
detailRow.OrderId = row.OrderId;  // This will take the primary key from the orders row and assign it to the details row
detailRow.Quantity = 5;
detailRow.Price = 25;

detailRow = detailsDT.NewtblOrderDetailsRow();
detailRow.OrderId = row.OrderId;

// Then, post the details abck to the DB as well

That's about it. pretty simple... right?
Francis_BAuthor Commented:
sounds good...I'm trying it now ...
You might have problems with the dataset suggestion as you're making the dataset generate the ID when the database should generate it.  If you had two apps adding rows at the same time, they could get the same ID and you'd have a conflict with saving.  You should save the parent row first, get the database-generated ID and use that to save the child rows.
Sorry, just looked closer and saw that you're getting the data again (including the ID) after updating the parent.  This may work, but it's an extra trip to the DB to get all of the original data back again when all you need is the generated ID.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.