Architecture question

[Charlie Brown]

When filling class data from a relational database, what are some good
ways to minimize the data load to only the data you need? I will
provide the following example to illustrate (simplified):

Class Ingredient
Public Property Ingredents As List(Of Ingredient)

Class Recipe
Public Property RecipeName as String
Public Property Ingredients As List(Of Ingredient)

Class MenuItem
Public Property MenuItemName as String
Public Property MenuRecipe As Recipe


In one form, I might be trying to look at a list of all the recipes in
our database so I would only pull the RecipeName out of the database
since recursively gathering the ingredient list would slow it down
quite a bit. In another form I may need to gather all the recipes and
their main ingredients (without the recursive call to get the
Ingredient.Ingredients() ), at other times I want all of the
information.

How can I design my classes, or data classes in such a way that I
achieve this outcome without adding a ton of overloaded methods and
calls to my database?

One of my thoughts was to place the database call in the property of
the classes as such:

Private _ingredientList as List(Of Ingredient)
Public Property IngredientList As List(Of Ingredient)
Get
If _ingredientList Is Nothing
_ingredientList = GetIngredientsFromDatabase()
End If

Return _ingredientList
End Get
.....

The problem I have so far with this method is that it makes
multithread applications a little more complex than they need to be,
and it forces many calls to the database over and over again.

A better method for loading all the recipes and their ingredients
would be to collect all of the recipes and ingredients from the
database, then loop through the data and "fill" the classes, that
would reduce the database calls from a potential of hundreds down to
2, certainly preferable.

A mix of the two options seems to be a way to go, but I wanted to prod
the minds of the good folks on the usenet for ideas (as a solo
developer, i can only bounce ideas off the wall, which is fun, but
gets me nowhere most of the time)

Thanks in advance,
Charlie

 

[sloan]

I use Controller/Manager classes to handle the different needs.

See
http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!139.entry

I return IDataReader(s) with a single ResultSet OR multiple ResultSets
depending on what I need.

I write my TSQL so that it brings the data I need, in the fewest ResultSets
as necessary.

...

Keep the business objects / collections seperate from the code that creates
them.

...

 

[sloan]

Find the CustomerController class, and find these lines:


BusinessObjects.Customer custMatch =
coll.Contains(currentOrder.CustomerID);
if (null!= custMatch)
{
custMatch.AllOrders.Add(currentOrder);
}
else
{
throw new ApplicationException("There is an orphaned Order with
Order.OrderID = " + Convert.ToString(currentOrder.OrderID) + " and
Order.CustomerID = " + currentOrder.CustomerID);
}

This is a key spot on how I populate multiple Customer(s) where each
Customer has multiple Order(s), but I only use 1 stored procedures and only
2 result sets.

...

Code is in C#, but you'll get the ideas.


A complete sample code/zipfile is downloadable. Read the README.txt.

 

[Charlie Brown]

Find the CustomerController class, and find these lines:

       BusinessObjects.Customer custMatch =
coll.Contains(currentOrder.CustomerID);
       if (null!= custMatch)
       {
        custMatch.AllOrders.Add(currentOrder);
       }
       else
       {
        throw new ApplicationException("There is an orphaned Order with
Order.OrderID = " + Convert.ToString(currentOrder.OrderID) + " and
Order.CustomerID = " + currentOrder.CustomerID);
       }

This is a key spot on how I populate multiple Customer(s) where each
Customer has multiple Order(s), but I only use 1 stored procedures and only
2 result sets.

..

Code is in C#, but you'll get the ideas.

A complete sample code/zipfile is downloadable.  Read the README.txt.









- Show quoted text -

Thanks for the informative post.

Do you use the same method when filling an object that you are unsure
if you may need the nested objects later? For example gathering all
the customers for a datagrid or list, unsure of which customer the
user may want to drill down into, in the case of orders. I imagine
not loading all of the order data if a user would only select a
customer from a list to see the details of. Are you then just hitting
the database one more time to get the order info?

 

[sloan]

That's what you have to decide.

Is it cheaper to "pre populate" or populate "on demand".

...

Usually, I get them on-demand.

But it still depends. If the drill down was "Emails For This Person", then
I might prepopulate because a subcollection of 1-5 strings (per person)
isn't so bad.
(Assuming a person has N number of emails and usually has 1, sometimes 2,
sometimes N)

But for 100 Order(s) objects (per Customer), I'd get them on demand.


And it depends if I'm making a website for 10 users or 1000 users.

.......

In your scenario, I'd cache the list of Customers. If they drilled down, I
fetch a OrderCollection(BySingleCustomer) and then add them to the child
collection of the Customer(single object). Then recache it.
Then I'd add some logic to not refetch (by hitting the db) if the
Customer(single object)/OrderCollection was populated.
Then I'd add some kind of "bool forceRefresh" if the person wanted to
requery for the db for "fresh" data.


The above would be if I were showing details ON THE CUSTOMER LIST Page.

...

Alot of times, I'd redirect them to a new page, throw the CustomerID in the
querystring, and then get a single customer/ with Order details object, and
reshow it.
This would be the "Show Single Customer With Orders" aspx page.

I don't do alot of "inline" pages with hidden divs. But that's me.


The important thing is to just hit the db "just enough". And different
methods on the Customer(Controller) class give me the options I need.

I don't know dude, you gotta pick something, but hopefully the example will
help out and show one approach.

I HATE business objects with DAL calls in the constructor.
I HATE calling a property, and have the DAL call in the property if the
property is null/nothing (or not yet populated).

I see this too-clever-for-my-own-good poo-poo way too much.

I could label this as "Seperation of Concerns" or something I guess.
My code is more reusable and less buggy and less "hitting the db when I
don't need to" than other approaches I've seen.

Good luck.

...

PS
Find and read that "bird's eye view" article from MS I reference at the
blog. Bookmark it, and keep it around. It gives the pros/cons of
approaches.
I wish a 2.0 version existed.

Find the CustomerController class, and find these lines:

BusinessObjects.Customer custMatch =
coll.Contains(currentOrder.CustomerID);
if (null!= custMatch)
{
custMatch.AllOrders.Add(currentOrder);
}
else
{
throw new ApplicationException("There is an orphaned Order with
Order.OrderID = " + Convert.ToString(currentOrder.OrderID) + " and
Order.CustomerID = " + currentOrder.CustomerID);
}

This is a key spot on how I populate multiple Customer(s) where each
Customer has multiple Order(s), but I only use 1 stored procedures and
only
2 result sets.

..

Code is in C#, but you'll get the ideas.

A complete sample code/zipfile is downloadable. Read the README.txt.









- Show quoted text -

Thanks for the informative post.

Do you use the same method when filling an object that you are unsure
if you may need the nested objects later? For example gathering all
the customers for a datagrid or list, unsure of which customer the
user may want to drill down into, in the case of orders. I imagine
not loading all of the order data if a user would only select a
customer from a list to see the details of. Are you then just hitting
the database one more time to get the order info?

 

[Charlie Brown]

That's what you have to decide.

Is it cheaper to "pre populate" or populate "on demand".

..

Usually, I get them on-demand.

But it still depends.  If the drill down was "Emails For This Person", then
I might prepopulate because a subcollection of 1-5 strings (per person)
isn't so bad.
(Assuming a person has N number of emails and usually has 1, sometimes 2,
sometimes N)

But for 100 Order(s) objects (per Customer), I'd get them on demand.

And it depends if I'm making a website for 10 users or 1000 users.

......

In your scenario, I'd cache the list of Customers.  If they drilled down, I
fetch a OrderCollection(BySingleCustomer) and then add them to the child
collection of the Customer(single object).  Then recache it.
Then I'd add some logic to not refetch (by hitting the db) if the
Customer(single object)/OrderCollection was populated.
Then I'd add some kind of "bool forceRefresh" if the person wanted to
requery for the db for "fresh" data.

The above would be if I were showing details ON THE CUSTOMER LIST Page.

..

Alot of times, I'd redirect them to a new page, throw the CustomerID in the
querystring, and then get a single customer/ with Order details object, and
reshow it.
This would be the "Show Single Customer With Orders" aspx page.

I don't do alot of "inline" pages with hidden divs.  But that's me.

The important thing is to just hit the db "just enough".  And different
methods on the Customer(Controller) class give me the options I need.

I don't know dude, you gotta pick something, but hopefully the example will
help out and show one approach.

I HATE business objects with DAL calls in the constructor.
I HATE calling a property, and have the DAL call in the property if the
property is null/nothing (or not yet populated).

I see this too-clever-for-my-own-good poo-poo way too much.

I could label this as "Seperation of Concerns" or something I guess.
My code is more reusable and less buggy and less "hitting the db when I
don't need to" than other approaches I've seen.

Good luck.

..

PS
Find and read that "bird's eye view" article from MS I reference at the
blog.  Bookmark it, and keep it around.  It gives the pros/cons of
approaches.
I wish a 2.0 version existed.









Thanks for the informative post.

Do you use the same method when filling an object that you are unsure
if you may need the nested objects later?  For example gathering all
the customers for a datagrid or list, unsure of which customer the
user may want to drill down into, in the case of orders.  I imagine
not loading all of the order data if a user would only select a
customer from a list to see the details of.  Are you then just hitting
the database one more time to get the order info?- Hide quoted text -

- Show quoted text -

Agreed. I like the ideas you presented, I think I will go with
something hybrid that suits the use case. I like the forceReload
option on caching, but didn't know if that sounded too goofy or not.
I think I will go with something along the lines of prefetching tables
that rarely change on a background thread, then using the cached
information when I need it.

I often see a lot of entity frameworks (good or bad) that do
everything on-demand, but I just can't see doing "everything" on
demand.

I read through the MS article again last night, I looked through it
previously but I wasn't using this much database code until now, so it
was a good refresher. I am going to stick with passing datareaders
and datatables around instead of creating business entities since my
data classes will be consumed by several different projects and I
foten just need the data not a business object.

Thanks for your insight.