none
Can DataSet handle self-referential relationship? RRS feed

  • Question

  • Hi,

    I have the following tables:

    * Data
      - ID
      - ...
    * Relationships
      - ParentID
      - ChildID

    If I bring both the DATA and RELATIONSHIPS table into a dataset, is there a way to add the relationships into the model in a way that would add value?  e.g. to enforce the constraints for example?   If so I guess I'm interested in:

    Q1 - Is it possible?
    Q2 - What would the code look like to set up the relationships?

    Thanks
    Monday, October 12, 2009 2:26 PM

Answers

  • Yes, it seems to work just fine. I was skeptical, but I tried it. Your relationship set-up would be like it would be if you were relating different tables:

    DataRelation dr = new DataRelation("MyRelation", this.ds.Tables[0].Columns["ParentID"], this.ds.Tables[0].Columns["ChildID"]);
    this.ds.Relations.Add(dr);
     

    ~~Bonnie Berent [C# MVP]

    (new blog --- not many posts yet --- be patient)
    geek-goddess-bonnie.blogspot.com
    Monday, October 12, 2009 9:19 PM
  • > Can DataSet handle self-referential relationship?

    Do you mean a constraint where the parent table and child table are the same table?  Yes, DataSet supports this.  The difficulty usually comes in actually saving the data to the database because you usually need to take care to submit the rows in the correct order to the DBMS (and DataSet doesn't help you figure this out!).  This isn't a problem if your DBMS uses deferred constraint checking or--in your case--doesn't have constraints.
    Thursday, October 15, 2009 11:29 PM

All replies

  • Yes, it seems to work just fine. I was skeptical, but I tried it. Your relationship set-up would be like it would be if you were relating different tables:

    DataRelation dr = new DataRelation("MyRelation", this.ds.Tables[0].Columns["ParentID"], this.ds.Tables[0].Columns["ChildID"]);
    this.ds.Relations.Add(dr);
     

    ~~Bonnie Berent [C# MVP]

    (new blog --- not many posts yet --- be patient)
    geek-goddess-bonnie.blogspot.com
    Monday, October 12, 2009 9:19 PM
  • thanks Bonnie - I'll try this - just briefly should having this relationship in place mean that my DataSet should be able to enforce the constraints?  i.e. just trying to understand roughly what benefit you get in ADO.net datasets if you put all the associations/relationships in place?

    Tuesday, October 13, 2009 1:17 AM
  • Benefits? Well, I don't know ... personally I don't usually use relationships. They seem to be mostly useful for displaying data in DataGrids or DataGridViews in a master/detail fashion and I've not designed apps in that manner in the past. And having to work around the constraints can be a pain sometimes, depending on how you process stuff (although, you can turn constraints off and back on if necessary).

    If you're asking if it's better to do it all in one table versus having separate Parent & Child tables, I wouldn't think so ... I'd prefer the separate tables. But, again, it depends on what you're attempting to do with the data or how you're attempting to display it.

    Not much help, I know ... but your questions are kind of generic too. ;0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, October 13, 2009 4:30 AM
  • Hi callagga,

     

    Welcome to ADO.NET DataSet forum!

     

    BonnieB, I noticed that you come to ADO.NET DataSet forum these days to help community members.  Thanks a lot!  J

     

    callagga, do you mean a strongly typed DataSets?  I think with the strongly typed relationships inside the DataSet, the data is more like an Object-oriented model, and the data structure would be easier to understand and use.  A newer related product of Microsoft would be Entity Framework.  As BonnieB suggested, your topic can be a really big one.  J 

     

    If you need any further assistance, please feel free to let me know. 

     

    Hope you both have a great day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 13, 2009 10:21 AM
    Moderator
  • BonnieB, I noticed that you come to ADO.NET DataSet forum these days to help community members.  Thanks a lot!  J




    Thanks Lingzhi Sun. DataSets are one of my favorite topics. I used to frequent this forum before the MSDN forums got a facelift a year or two ago. I forgot about it until recently ... I've been posting in Windows Forms (DataBinding, General) and C# (Language, General). I knew there was another one I used to post in and just found the ADO.NET one recently ... again. So, I'm baaaaaack! ;0)
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, October 14, 2009 2:33 PM
  • callagga, do you mean a strongly typed DataSets ?  I think with the strongly typed relationships inside the DataSet, the data is more like an Object-oriented model, and the data structure would be easier to understand and use.  A newer related product of Microsoft would be Entity Framework .  As BonnieB suggested, your topic can be a really big one.

    Hi Lingzh - I'm not creating any XML Schema, so I think that means I'm not talking about stronly typed(?).   I really just have simple needs in an sqlite database with two tables (see original post) so I was wondering what benefit it would have it I tried to model the relationship (which Bonnie replied to).  I haven't actually had a chance to test it yet unfortunately (but I will), however I was hoping it would effectively enforce at the ADO layer a kind of "foreign key" constraint thing when I'm creating data in the database...
    Thursday, October 15, 2009 10:01 PM
  • Doesn't your database enforce foreign key constraints? Putting constraints in the DataSet will give you valid data in your DataSet and, as such, should give you valid data in your database. But, as I mentioned, depending on what you're doing, sometimes having the constraints in the DataSet is a pain.

    Once you get around to playing with it, you'll probably have some more questions. Keep us posted.
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Thursday, October 15, 2009 10:20 PM
  • The issue is sqlite doesn't support foreign key constraints. :(
    Thursday, October 15, 2009 10:43 PM
  • > Can DataSet handle self-referential relationship?

    Do you mean a constraint where the parent table and child table are the same table?  Yes, DataSet supports this.  The difficulty usually comes in actually saving the data to the database because you usually need to take care to submit the rows in the correct order to the DBMS (and DataSet doesn't help you figure this out!).  This isn't a problem if your DBMS uses deferred constraint checking or--in your case--doesn't have constraints.
    Thursday, October 15, 2009 11:29 PM
  • Hey guys,

    I'm getting back to this project.  In fact I now have an ITEMS and a RELATIONSHIPS table.  The key columns are:

    ITEMS
    * ID
    * << other columns>>

    RELATIONSHIPS
    * ID
    * PARENT_ID
    * CHILD_ID

    I'm trying to understand whether the DataSet / DataRelation approach could somehow map these relations.  For example if I basically want a way to implement the request "Give me all children ITEMS in a DataRow[] form, given a parent ITEM DataRow, based on the RELATIONSHIPS table", is there a way to do this using a DataRelation?   Of if not what would be the easiest way to do this using the DataSet approach?


    Thanks




    Tuesday, November 3, 2009 7:26 AM