Saving two DataTables, with Parent - Child relation

Posted on 2006-06-05
Last Modified: 2010-04-16
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.
Question by:Francis_B
    LVL 25

    Accepted Solution

    Have the stored proc that inserts the order return the value of SCOPE_IDENTITY() and use that value in your orderdetails table
    LVL 6

    Expert Comment

    Are you using a dataset?

    Author Comment

    Yep, using a dataset...
    LVL 6

    Expert Comment

    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?

    Author Comment

    sounds good...I'm trying it now ...
    LVL 25

    Expert Comment

    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.
    LVL 25

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Hire Top Freelancers to Complete C# Projects

    Source the talented Expert Exchange community
    for top quality work on your C# projects.

    Hire the best. Collaborate easily. Get quality work.

    Article by: Ivo
    C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now