Hierarchical object retrieval

bgilbert posted on Tuesday, January 30, 2007

Forgive me if this is an old topic or is on page 1 of the book.

I have an object pattern similar to Project/ProjectResources/ProjectResource in PTracker with one exception. I'm starting with a Projects editable root collection class. My UI will have two DataGridViews: the top will be filled with Project objects. When one is selected, the related ProjectResource child collection will fill the second grid. Since both collections will never get too large, I want to fill the child collections when each Project is instantiated. Similar to PTracker, I'm returning two result sets in my sproc.

In the ExecuteFetch method of the Projects class I have something like this:

            Using dr As SafeDataReader = New SafeDataReader(cm.ExecuteReader())
                While dr.Read()
                    Me.Add(Project.GetProject(dr))
                End While
                dr.NextResult()
                For Each proj As Project In Me
                    proj.LoadResources(dr)
                Next
            End Using

In the LoadResources method I loop through the second result set and find the records where the key matches, like this:
        With dr
            While .Read
                If .GetInt16("ProjectId") = _id Then
                   ' Fill local collection
                    _projectResources.Add(ProjectResource.GetProjectResource(dr))
                End If
            End While
        End With

The trouble I'm having is with the DataReader (forward-only). After I've run this routine on the first parent object, I'm done reading. I see no methods on the reader that allow me to start over.

I'm guessing there's a better way to do this.

Thanks,
Barry

SonOfPirate replied on Tuesday, January 30, 2007

I'm in the process of setting this exact thing up.  Here's how I'm doing it:

Using dr As SafeDataReader = New SafeDataReader(cm.ExecuteReader())
    While dr.Read()
        Me.Add(Project.GetProject(dr))
    EndWhile
    dr.NextResult()
    While dr.Read()
        Me.Items(dr.GetInt16("ProjectId")).Resources.Add(ProjectResource.GetProjectResouce(dr))
    End While
End Using

The difference is that the parent is responsible for loading the child object's collection using the ProjectId value to identify which item in its own collection the record pertains to.  This allows you to retrieve the record in a sequential (forward only) manner and eliminates the problems you've encountered).

HTH

 

bgilbert replied on Tuesday, January 30, 2007

I don't see how this will work. The ProjectId is not the same as the Item's index in the collection.

William replied on Tuesday, January 30, 2007

SonOfPirate replied on Wednesday, January 31, 2007

bgilbert:

I don't see how this will work. The ProjectId is not the same as the Item's index in the collection.

Yea, if you're not using a keyed collection with the ID value as the key (or some other field that you can return in the grandchild's resultset), then this won't work and you are pretty much limited to using a DataSet that you can reliably manipulate forward and backward.

 

bgilbert replied on Wednesday, January 31, 2007

Thank you gentlemen. I found Brian Criswell's SafeDataRowReader.

Given this little wrinkle, I'm re-evaluating the decision to pre-load. This app has a relatively small performance requirement so simplicity usually wins over performance. I'll keep this in mind for when it's time to optimize.