child objets on the database side

jhw posted on Tuesday, April 24, 2007

Loading child object data from the database:

Standard procedure is for the parent objects fetch sproc to load the data for the child object.

Currently I manually put in the sql for getting the child objects data. Seems like poor re-use and encapsulation.

So I was thinking of using a user defined function(udf) that would get the child data, and call the udf within the parent objects sproc. Can than select * from the table returned by the udf. But using * is not optimized. Is there another way of doing it.

While not optimized, it does give some encapsulation which would be great.

 

Thanks

 

Heath

jhw replied on Tuesday, April 24, 2007

So I used this system to load a collection of child objects, each of which contains a grandchild.

I selected all of the grandchildren 1st, using user defined function(though another sproc would work).

Then I loaded all the children. I only had to select the id of the grandchild. This cleaned up my sproc nicely, as well as giving me re-use for loading the grandchild objects.

Now back in csla, when loading the 1st level children, I pass the collection of grandchildren into the fetch method, allowing the child to pick out the target grandchild.

This works back into an earlier post about loading grandchildren.

It worked very well, but of course it is not optimized int sql server as I need to use a select * from [user defined function].

It sounds like a lot of work, but if an object is a child of several other objects, it is nice to only have 1 sproc or udf to change in the db if the child object changes.

select * from dbo.udf_JobBearingPacks(@Id)
    --get the job motors
    select jm.Id, Abh, Bottom, HasBeenJobOrdered, InspectionRateToOilCompany, JobId,
    Lobe, MinUnits, NqlInspectionChargeAmount, OilCompanyInvoiceId,
    RentalRateToOilCompany, Stage, UnitsUsed, MotorReplacementCostToOilCompany, MotorNeedsReplacement,
    MotorReplacementJobCost,
    --get the info for the nql insp cx invoice obj
    nii.Id, nii.Gst,  nii.InvoiceNumber, cinv.Id, cinv.BusinessName,
    --get the info for the bearing pack. Get rid of for now while trying new system,
    --and replace with just a list of the id's so class can know which one to load
    dm.Id,
    --dm.Id,   dm.SerialNumber, dm.Description,  dm.MotorSize
.....