Data retrieval with multiple-level hierarchy

Vertigo1 posted on Saturday, August 26, 2006

Ok, having read through the book I understand how the data retrieval operates with the SQL stored procedures in the case of parent and child objects.  Basically a single stored procedure retrieves the details for the specified parent object followed by a second resultset containing the details of all the child object.  The parent object's DataPortal_Fetch function populates its own fields and then passes the (Safe)DataReader to the factory method of the child class in order that it may construct the child objects.

My question is how do you organise things in the case of a multi-level hierarchy?  If you have a Parent/Child/Grandchild situation such that the Child object is both a child and a parent, what's the best way to retrieve the data?  You could have a stored procedure to retrieve the details of a specific Parent object which, as described above, would return a second resultset containing a row for each Child object but how would you retrieve the data for each Grandchild object?  You can't simply add a third resultset as this would contain all Grandchildren of the Parent in one big "lump" rather than discrete resultsets of Grandchildren for each Child.

Not sure if I'm making myself clear here but if someone can explain the best way of doing this I'd be eternally grateful :)

Toby.

tetranz replied on Saturday, August 26, 2006

Toby,

The usual way to do this is with a DataSet. I think I've been down to great grand children. I  still use an sproc with multiple queries that I read with a DataReader but I instanciate a DataSet and load the data below the child level into DataTables within the DataSet. Create relationships within the DataSet and use the GetChildren() is whatever the method is called. I think I passed a DataTable to my lower collections in much the same way as we usually pass a DataReader in CSLA. The Fetch method in the collections loop though the DataRows.

Ross

akabak replied on Monday, August 28, 2006

Ross, do you have any sample code for this? I was just logging on to ask the same question ... I've got a root object that has child contacts, and each child contact has children addresses and children phone numbers related to it.


amanda

tetranz replied on Tuesday, August 29, 2006

akabak:

Ross, do you have any sample code for this? I was just logging on to ask the same question ... I've got a root object that has child contacts, and each child contact has children addresses and children phone numbers related to it.
amanda

Sorry, I don't have working code I can easily publish but its basically like this:

In your stored procedure do something like this. Assuming, just for example, all tables have Id and Name.

SELECT Id, Name FROM Parent WHERE Id = @Id

SELECT Id, Name FROM Child WHERE ParentId = @Id

SELECT Id, Name FROM GrandChild INNER JOIN Child ON GrandChild.ParentId = Child.Id
WHERE Child.ParentId = @Id

In the root object's DataPortal_Fetch, call your sproc in the usual way. Now assuming your sproc call has returned a DataReader dr

Read the root's data into its instance variables.

dr.NextResult()

Now you could do the children in the normal CSLA way by passing it the dr but I found that if there were grandchildren then it was cleaner (although not as efficient) to leap into the DataSet. I'm dealing with small collections. At the very least you need to put the Id of the children into a DataTable so you can create the relationship with the grandchildren. So ... continuing how I did it.

DataSet ds = new DataSet();
DataRow row;

// Children
DataTable dtChildren = ds.Tables.Add("Children");
DataColumnCollection cols = dtChildren.Columns;
cols.Add("Id", typeof(Int32));
cols.Add("Name", typeof(String));
// plus other fields

while (dr.Read())
{
    row = dtChildren.NewRow();
    row["Id"] = dr.GetInt32("Id");
    row["Name"] = dr.GetString("Name");
    // plus other fields
    dtChildren.Rows.Add(row);
}

dr.NextResult()

// Grandchildren
DataTable dtGrandChildren = ds.Tables.Add("GrandChildren");
DataColumnCollection cols = dtGrandChildren.Columns;
cols.Add("Id", typeof(Int32));
cols.Add("Name", typeof(String));
// plus other fields

while (dr.Read())
{
    row = dtGrandChildren.NewRow();
    row["Id"] = dr.GetInt32("Id");
    row["Name"] = dr.GetString("Name");
    // plus other fields
    dtGrandChildren.Rows.Add(row);
}

// create the relationship
ds.Relations.Add("CtoG", dtChildren.Columns["Id"], dtGrandChildren.Columns["ParentId"]);

Now to load the data into the children and grandchild. Looking at it now, the way I did it was slightly untidy because of some other issues. I think the following is how it should be done: Its really just a matter of substituting the DataReader in the normal CSLA templates with a DataTable and its rows.

ChildList is an EditableChildList. Its static "Get" method accepts a DataRow. The root object's Fetch() has something like:

_childList = ChildList.GetChildList(dtChildren);

In ChildList you have a Fetch() method (called from the constructor) that takes a DataTable something like this:

private void Fetch(DataTable dt)
{
    RaiseListChangedEvents = false;
    foreach (DataRow row in dt.Rows)
    {
        this.Add(Child.GetChild(row));
    }
    RaiseListChangedEvents = true;
}

In the Child class, again you pretty much just substitute a DataRow for the DataReader but at the end of its Fetch() you'll have something like:

_grandChildList = GrandChildList.GetGrandChildList(row.GetChildRows("CToG"));

Note that row.GetChildRows("CToG") returns an array of DataRows (not a filtered DataTable) so therefore GrandChildList is different in that it needs to accept an array of DataRows instead of a DataTable like ChildList does but whether you're loading the collection from repeatedly dr.Read(), or rows in a DataTable or an array of DataRows, its all much the same.

Well ...  hope that helps.

Cheers
Ross

david.wendelken replied on Sunday, August 27, 2006

I think it would depend. :)

If you have thousands of parent records in a collection, and you are unlikely to want to look at all of the grandchildren, grabbing all the grandchildren for each parent all at once would be wasteful.

But, if you *are* going to be rooting thru most of the grandchildren in your object, then asking for the data one child at a time would be wasteful.

Hope that helps!