SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested Xml Using Dataset

    Hi,

    I am using the Northwind database to try and learn how to use DATASET RELATIONS to write a nested xml but am struggling to get my code to work with a one to many relationship between three tables.

    I am using four tables from the Northwind database as follows:

    Customer
    --------------
    CustomerID PK

    +++++++++++++++++++

    Orders
    ----------
    OrderID PK

    ++++++++++++++++++++

    Order Detail
    -----------------
    OrderID PK
    ProductID PK

    +++++++++++++++++++++

    Products
    --------------
    ProductID PK

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

    And this is how they are related in the database with primary and foreign keys:

    Customer ----> Order

    Order -----> Order Detail <---- Products

    As you can see the Order Detail tables has a compound primary key consisting of OrderID and ProductID.

    This is the code I am suing to set up the relationships:

    DataRelation custOrderRel = thisDataSet.Relations.Add("CustOrders",
    thisDataSet.Tables["Customers"].Columns["CustomerID"],
    thisDataSet.Tables["Orders"].Columns["CustomerID"]);

    DataRelation orderDetailRel = thisDataSet.Relations.Add("OrderDetail",
    thisDataSet.Tables["Orders"].Columns["OrderID"],
    thisDataSet.Tables["Order Details"].Columns["OrderID"]);

    DataRelation orderProductRel = thisDataSet.Relations.Add(
    "OrderProducts", thisDataSet.Tables["Products"].Columns["ProductID"],
    thisDataSet.Tables["Order Details"].Columns["ProductID"]);

    thisDataSet.WriteXmlSchema(Server.MapPath("~/xml/Northwind.xsd"));
    thisDataSet.WriteXml(Server.MapPath("~/xml/Northwind.xml"));

    thisDataSet.Relations["CustOrders"].Nested = true;
    thisDataSet.Relations["OrderDetail"].Nested = true;
    thisDataSet.Relations["OrderProducts"].Nested = true;

    thisDataSet.WriteXml(Server.MapPath("~/xml/nestedNorthwind.xml"));

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

    When I try to write the NESTED xml to disc, I get the following error:

    "Cannot proceed with serializing DataTable 'Order Details'. It contains a DataRow which has multiple parent rows on the same Foreign Key."

    As you can see there is a problem with the Order Detail table.

    I would appreciate if you could shed some light on how to fix it.

    Cheers

    CP

  2. #2
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Since I have received no reply for my post so far, I would like to try and explain again in a few sentences what I am trying to do.

    Basically, I have a many-to-many relationship between Order and Product via a bridge table called OrderDetail.

    The bridge table's primary key consists of two foreign keys coming from each of the corresponding tables.

    I need to find a way to set the relations between the tables in the right way so that I can call the WriteXml method of the dataset and get it nested based on the relationship of the tables.

    Has anyone achieved this before? is it possible to do that with a dataset? Any tutorial?


    Cheers

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2001
    Location
    RI, USA
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried some of the other params on the WriteXml method? I can't remember exactly what they are right now.. but I'm thinking, InferSchema, IngoreSchema etc..

    Also, to make sure your dataset has the right relations set, have you just tried iterating the rows outputting it?
    in1.com | Bootply - Bootstrap playground

  4. #4
    SitePoint Member
    Join Date
    Oct 2007
    Posts
    9
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have tried all that but nothing works. I think I cannot navigate from the Order table to the Products table using the bridge table using a dataset.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •