How to fill objects master - datail collections c#/SQL2005

Question

I'm using Business Objects Collections.
(Not using datasets. Generics collections only.)
Collections of Business objects are filled using SQL Reader

I'd like to know your opinion what is best approach to fill master details (or parent-child) collections

Assume I have 2 objects: Invoice and Invoice_Details
Invoice Object has generic collection "Details" (type of Invoice Details)

What would be best approach to work / fill both collections?
(Eg I'd like to read all invoices from 2008 yr and present on GUI)

• Do you read all invoices for selected date range, than all children and populate proper Invoice's Details
• Or read invoice one by one and related details? (eg using multiply result sets)
• I've also noticed approach based on Binding Source -> read children only when changing current record position...

I'm very interested your opinion what would be best / fastest scenario?

Answer 1

This depends on what you're trying to do.

If you need all of the invoice details whenever you need the invoice, then issue a query for the details when you first access the invoice (unless you know you'll always need both, in which case issue a single batch with two result sets).

Similar patterns apply to other cases.

Answer 2

To minimize the impact, I'd load the Invoice records in one go, and then lazy load the Invoice_Details only when they were needed. This has the advantage of being the quickest way to load all of the invoice data in, while presenting the lowest amount of memory usage.