none
Fill child datables of dataset automatically RRS feed

  • Question

  • I have a dataset with heirarchal collection of datatables.  I want to fill the entire dataset from a database based on specified parent table criteria.

    From reading MSDN, it appears I can fill my parent datatable by creating a "Fillby" query that takes a specified field entry as input.  But I would like all child datatables to be filled in likewise, based off what was added to the parent datatable  (and these child datatables have their own child datatables, and so on).  What would be the simplest set up to accomplish this task?
    Wednesday, July 8, 2009 4:05 PM

Answers

  • Write separate queries and load each datatable member by name. Execute each one of these as the DataAdapter.SelectCommand

    da.SelectCommand = "SELECT * from parent"
    da.Fill(dataSet, "parent" );

    da.SelectCommand = "SELECT child1.* from child1 inner join parent on parent.id = child1.foreignkey"
    da.Fill(dataSet, "child1" );

    da.SelectCommand = "SELECT child2.* from child2 inner join parent on parent.id = child2.foreignkey"
    da.Fill(dataSet, "child2" );

    da.SelectCommand = "SELECT child3.* from child3 inner join child1 on child1.id = child2.foreignkey inner join parent on parent.id = child1.foreignkey"
    da.Fill(dataSet, "child3" );

    etc...

    This is a brute force method that you can easily abstract.


    • Marked as answer by Tekito Wednesday, July 8, 2009 6:50 PM
    Wednesday, July 8, 2009 6:39 PM

All replies

  • My first post sounds a little confusing, so in other words, basically after downloading the parent record I want, I would like all the child datatables to be filled in accordingly.
    Wednesday, July 8, 2009 6:04 PM
  • Write separate queries and load each datatable member by name. Execute each one of these as the DataAdapter.SelectCommand

    da.SelectCommand = "SELECT * from parent"
    da.Fill(dataSet, "parent" );

    da.SelectCommand = "SELECT child1.* from child1 inner join parent on parent.id = child1.foreignkey"
    da.Fill(dataSet, "child1" );

    da.SelectCommand = "SELECT child2.* from child2 inner join parent on parent.id = child2.foreignkey"
    da.Fill(dataSet, "child2" );

    da.SelectCommand = "SELECT child3.* from child3 inner join child1 on child1.id = child2.foreignkey inner join parent on parent.id = child1.foreignkey"
    da.Fill(dataSet, "child3" );

    etc...

    This is a brute force method that you can easily abstract.


    • Marked as answer by Tekito Wednesday, July 8, 2009 6:50 PM
    Wednesday, July 8, 2009 6:39 PM
  • Thanks.  I'm not entirely aware of all of dataset's capabilities and I thought that it could somehow do this automatically.  But this should work fine. 
    Wednesday, July 8, 2009 6:50 PM