ChildObject Contains a collection ?

JoOfMetL posted on Thursday, August 31, 2006

Hello, I have a little problem :

---------------

public class Project : BusinessBase
{
      private ProjectResources _resources
...
}

pulibc class ProjectResources :  BusinessListBase<ProjectResources, ProjectResource>

public class ProjectResource :  BusinessBase<ProjectResource>

-----------------

Here, no problem. But If my objet ProjectResource contains a collection, so :

-----------------
 
public class ProjectResource :  BusinessBase<ProjectResource>
{
      private TestCollections _testCollection
...
}

pulibc class TestCollections BusinessListBase<TestCollections , TestCollection>

public class TestCollection :  BusinessBase<TestCollection >

-----------------

Which is the best solution ? Because it is not possible to charge all my objects starting from one only SELECT .

Thank you

RockfordLhotka replied on Thursday, August 31, 2006

The data access in this case is challenging. Many people use a DataSet to simplify the process, though other techniques exist, and have been discussed in previous threads dealing with data access for grandchild objects.

A strongly typed DataSet will reconcile the parent-child-grandchild relationships between your data automatically, making it relatively easy to load your objects with data. The root object simply creates the whole DataSet and passes it to the child collections rather than a datareader. Obviously there's some performance hit due to the overhead of the DataSet, but it is a simple coding model, and keeps you to one database hit per generation (root, child, grandchild).

Another approach is to have each child hit the database to get its grandchildren. In that case you'd have n+1 database hits, where n is the number of child objects. That can get expensive, but is also relatively easy to implement.

Another approach is to return all three generations from a single stored procedure, much like I show in the book. This requires the most coding, because your child collection needs to look at the grandchild data in the datareader and find the right child to which it should pass the datareader for those grandchildren. I don't find this code overwhelmingly difficult, but it certainly is more work than the previous options.

JoOfMetL replied on Friday, September 01, 2006

Thank you.

I understand easily the first two approaches

I don't find in the book your third approach

Brian Criswell replied on Friday, September 01, 2006

You may also want to search the forums for SafeDataRowReader.

JoOfMetL replied on Saturday, September 02, 2006

Thank you Brian Criswell.

Yes I researched for GrandChild.

And I found SafeDataRowReader. If I don't mistake it's the solution based on the DataSet. For the moment, I use the SafeDataRowReader, and I think that it's a good solution.

But I am interested by the solution exposed in the book but I don't find it.

Crestline replied on Wednesday, October 11, 2006

JoOfMetL:

Thank you Brian Criswell.

Yes I researched for GrandChild.

And I found SafeDataRowReader. If I don't mistake it's the solution based on the DataSet. For the moment, I use the SafeDataRowReader, and I think that it's a good solution.

But I am interested by the solution exposed in the book but I don't find it.

Which page in the book refers to the stored procedure approach?  The CodeSmith templates all use the datareader to fill the child objects so we would be interested in this approach.  In another project, I have coded it so that each child fills it's child by passing the related id for the stored procedure.  It wasn't to hard to do but if I can use the CodeSmith templates for the object code and only have to spend time with a more complex stored procedure, that is something we'd like to try.

Thanks for any references....

Crestline replied on Wednesday, October 11, 2006

Is this the chuck of code from the ProjectTracker PTData stored procedures that you would be refering to....

EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE getProject

    (

        @id uniqueidentifier

    )

AS

    SELECT Id,Name,Started,Ended,Description,LastChanged

    FROM Projects

    WHERE Id=@id


    SELECT ResourceId,LastName,FirstName,Assigned,Role,Assignments.LastChanged AS LastChanged

    FROM Resources,Assignments

    WHERE ProjectId=@id AND ResourceId=Id

    RETURN'

END

JoOfMetL replied on Thursday, October 12, 2006

Hi,

I Don't think that is this code.

For the grand child, I Use the SafeDataRowReader.