Can I load children and grandchildren with one datareader?

mking posted on Monday, October 30, 2006

Is it possible to have an EditableRootList (a collection of EditableChild objects) where each EditableChild contains an EditableChildList, and populate it all with one call to the database?

Like this:   EditableRootList (a) -- EditableChild (b) -- EditableChildList (c) -- EditableChild (d)

I'm afraid if I called DataReader.NextResult() to load (d), then I would not be able to get back to the first resultset to load the 2nd (b) object.

So I'm thinking (a) should pass a datareader to (b), then (b) should pass its ID value to (c) and (c) should make its own call to the database and pass a second datareader to (d).

I was curious if there's a way to load it all with just one call to the database and a single datareader that gets passed around. 

Thanks,

Mike

JoeFallon1 replied on Monday, October 30, 2006

This question has been asked before - it is a tricky one. If you search this forum (and the old one) you can find longer discussions on this topic.

Basically, there are 2 main approaches to this problem.

1. Use more than one datareader.

In the child object:

Protected Friend Overridable Sub Fetch(ByVal dr As SafeDataReader)
  'fetch normal child data here using the passed in dr.

  FetchChildren()
End Sub

Protected Overridable Sub FetchChildren()
  'load grandchild objects here if there are any using a second datareader
  Dim dr2 As SafeDataReader
  Try
    dr2 = New SafeDataReader(DAL.ExecuteReader(SQL.SelectByLineKey(mLineKey)))
    mGrandchildColl = GrandchildColl.GetGrandchildColl(dr2)
  Finally
    dr2.Close()
  End Try
End Sub

The code above causes each grandchild collection to hit the DB on its own.
I use this style of coding.

2. Other people use DataSets and tables with Relations to grab all of the data in one huge shot and then parse the DataSet to load their BOs.

Joe

 

 

jhw replied on Tuesday, January 16, 2007

Here is the load grandchildren solution I prefer. I am using this when loading collections of objects which have both children and grandchildren.

Start by changing the stored procedure from:

select id, parentAttrib from parantTable

select id, childAttrib from childTable where id= parent.childId

select id, grandchildAttrib from gandchildTable id= parent.childId

TO:

 

select p.id, parentAttrib, c.id, childAttrib, g.Id, grandchildAttrib from parentTable p

join childTable c on p.childId = c.Id

join grandchildTable g on g.Id = c.childId

 

So instead of separate results, I now get a single result, each row of which contains all the info I neeed to load a single parent, child, grandchild object of my collection. But now I need to modify the objects to read from a single result instead of muliple result sets from the db.

Instead of Fetch(dr) I now use another  Fetch(dr, index). Instead of the children using the next result, they just keep reading from the same result, moving along with the index. Now my collection can load using the while safedataread.read method that is standard in csla.

rdrunner replied on Thursday, April 19, 2007

Hello...

If you use this method, you will waste a lot of resources to transfer redundant data. So you need to be very cautios about using it. If an object only has VERY FEW(<5) Children/Grandchildren, then it is "acceptable". But if you have a "huge" parent and then add lots of children and each child has lots of children itself, then the suggested aproach will transfer the Parent row "lots*lots" times. and this could use up a considerable amount of bandwith...

Just imagine a BLOB/Picture/File in the parent object thats transfered a whole bunch of times for no reason at all.

I think the "best" solution is to use a dataset and fill it with the data, and then create the real relations in the set to use the getchildrows. I understood from the other posts, that there is a "Save Datarow reader" (or something like that) that will give you a functionality like that, but so far i have only used datasets for my needs (Mental note to self to try it out sometimes)

Brian Criswell replied on Monday, October 30, 2006

You could also search for SafeDataRowReader, which uses a DataSet with DataRelations but behaves like a SafeDataReader to deal with null values.

xAvailx replied on Monday, October 30, 2006

You can return the data in XML. Use XML as the data source instead of data reader and pass down your class hierarchy to load the child objects. We've succesfully used this approach with a hierarchy like you describe.

HTH

mking replied on Monday, October 30, 2006

Thanks everyone.  I did some searching and found these related threads.

http://forums.lhotka.net/forums/thread/5327.aspx

http://forums.lhotka.net/forums/thread/3702.aspx

http://forums.lhotka.net/forums/thread/7532.aspx

hurcane replied on Monday, October 30, 2006

I've used another way to do this that uses a single datareader without datasets. I deal with an Order/Line/Comment hierarchy in which the user edits all three entities. The basic loading goes like this:

Load all three sets of data with a single call to the database.
Load the Order with the first result set.
Loop the second result set to load the lines, just like in the book.
Loop the third result set to load the comments. With each row, identify the line that the comment belongs to and pass the datareader to an AddExistingComment sub on the Line. This is passed to an AddExistingComment sub on the CommentsList, which finally adds the comment to it's own list.

Just another technique that I haven't really seen mentioned before.

RockfordLhotka replied on Thursday, April 19, 2007

You can look at the DeepData sample I used at VS Live (and will use again in Orlando in May) to see a solution that doesn't use a DataSet.

I also discussed this code on a recent DNR TV show.