• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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.
0
Francis_B
Asked:
Francis_B
  • 3
  • 2
  • 2
1 Solution
 
dstanley9Commented:
Have the stored proc that inserts the order return the value of SCOPE_IDENTITY() and use that value in your orderdetails table

http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
0
 
kalliopiCommented:
Are you using a dataset?
0
 
Francis_BAuthor Commented:
Yep, using a dataset...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
kalliopiCommented:
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...";

ordersDT.Rows.Add(row);

// Post the order (master row) to the database
ordersTA.Update(ordersDT);

// 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;
detailsDT.Rows.Add(detailRow);

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

// Then, post the details abck to the DB as well
detailsTA.Update(detailsDT);

That's about it. pretty simple... right?
0
 
Francis_BAuthor Commented:
sounds good...I'm trying it now ...
0
 
dstanley9Commented:
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.
0
 
dstanley9Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now