I must understand what the very best practice for inhabiting a company object hierarchy (parent/child/grandchild) structure is from one database call.
I'm able to think about a few methods to do it off the top my mind for example:
left-joining all of the relations during my sql statement then use looping and logic to fill the company objects
use several choose claims and 1 datareader and employ its NextResult() approach to iterate through each result set and fill the related BO's
Just wondering exactly what the best practice with this is
I'm using DAAB and c# for my DAL
There's no universal recipe. It is dependent on database schema, database size and quantity of records the application reads in typical scenario. You've two processes here:
- fetching data from database
- inhabiting business objects
Fetching data from database is several magnitudes reduced than creating objects in memory. Easiest way is always to construct choose claims for quickest data access.
Queries could be built in 3 ways:
- one large query that brings my way through single execution - you will get most complex SQL, and most likely the quickest execution (is dependent on DB schema)
- master/detail approach - simple queries. plenty of visitors to database. This really is acceptable only when you fetch few records, otherwise it's very slow.
- hybrid: one query for every layer of hierarchy. Think about this approach if previous two techniques will be to slow. This method requires more complicated logic for inhabiting business objects.
You need to choose which option would be acceptable. Some key facts to consider:
- which SQL is simpler to produce and keep Body large that brings my way through single read or several more compact.
- if you select on previous point, you need to measure performance making ultimate decision
I did previously use multiple came back datasets, however the overhead, and also the everchanging API's for this, finally allow me to revisit simply using joins to send it back all-in-one gulp.
I keep close track of the resultset dimensions, but poor any application I have encounter, it isn't been an problem. I have not regretted doing this, overall, but YMMV.
Multiple result sets could possibly get especially squirrelly when the parent-level selection clauses involve child-level selection rules.
By doing this handles every case splitting up works sometimes, however, you will finish up requiring single-set queries in some instances and it is nice to possess only one pattern - particularly if you sometimes are tied to refactoring from together.
Finally you finish track of less hits around the database, and transaction management now is easier.
DataReader NextResult is the greatest since the quantity of data groing through the pipe does not grow as rapidly because the join approach can.
Have you thought about utilizing an OR Mapper for example NHibernate? Eager loading can perform that which you request in a single call towards the database.
If the OR Mapper isn't a choice, then I'd throw my election behind datareader.NextResultSet.
if all of the rows come from exactly the same table (or seem to be), you'll be able to pull the information right into a dataset having a unary relationship and ADO.Internet will connect the hierarchy for you personally