Data marshalling from Sql server into business objects

Hi there,
I am having real problems finding the optimum way of retrieving data
from a Sql server and marshalling that into my c# business objects. I
have a fairly complex object hierarchy which I want to populate all in
a single shot (rather than relying on lazy loading which would cause
many roundtrips when the event's subordinates are enumerated). In my
object model...
an event has 0...n comments
a comment has 0...n visibility groups
an event has 1...n links
a link has 0...n link descriptors
an event has 1...n messages
In the relational world, this necessitates seven tables within my
schema :
tb_events
tb_events_comments
tb_events_comments_groups
tb_groups (pre-fetched)
tb_events_links
tb_links
tb_events_messages
tb_messages (pre-fetched)
The query I am using to retrieve this lot uses temporary tables and
goes like this...
/* -- START -- */
/* select all the events into a temporary table
this first statement can be modified if any queries are required
against
any of the subordinate
*/
select *
into #events
from tb_events
where is_deleted = 0
/* now select all the comments into a temporary table which
are subordinates of the events identified in query #1
*/
select *
into #events_comments
from tb_events_comments
where event_uid in (select uid from #events)
/* and the same for all the links
*/
select *
into #events_links
FROM tb_events_links
WHERE event_uid in (select uid from #events)
/* and now all the output queries which actually perform the retrieval
*/
select * from #events
select * from #events_comments
select * from tb_events_comments_groups where comment_uid in (select
uid from #events_comments)
select * from #events_links where event_uid in (select uid from
#events)
select * from tb_links where uid in (select link_uid from
#events_links)
select * from tb_link_descriptors where link_uid in (select link_uid
from #events_links)
select * from tb_events_messages where event_uid in (select uid from
#events)
/* and tidy up all the temp tables
*/
drop table #events
drop table #events_comments
drop table #events_links
/* -- END -- */
My question really is : is the above a valid way of retreiving all the
data required for population of the object hierarchy or are there any
nicer / faster / more elegant solutions? I've run it through Profiler
as both an ad-hoc script and a stored procedure (the sp is naturally
faster) and it doesn't seem to impose an unreasonable burden on the db.
The only other way I can think of is using a proxy object (EventProxy)
which contains "top line" details and if the full event is required
then it can be separately retrieved.
Surely there's a nicer way of doing it than this!?
Hope you can help,
MattI think your approach is suitable not knowing anything about your
situation. Volume is the biggest concern I would have. Are you pulling
one event, 10 events, all events? How many events in the table when you
query it? What kind of indexes? etc, etc.
Personally, I would not use the temp tables and would query the tables
directly. I might consider moving some of the subqueries to Table-value
functions for clarity and reuse.
Your biggest pain is going to be in your DAC code, where you have to
rebuild the object by looping through all these rows.
However, if you really want to be clever, you can make this one massive
query complete with all the joins, return it as XML, and serialize it
right into your object. Wouldn't that be ? Not easy at all, but
very .
--
700cb Development, Inc.
http://www.700cb.net
.NET utilities, developer tools,
and enterprise solutions
"Matt" <matt.jones@.studio10.co.uk> wrote in news:1132060815.255026.14610
@.g14g2000cwa.googlegroups.com:

> Hi there,
> I am having real problems finding the optimum way of retrieving data
> from a Sql server and marshalling that into my c# business objects. I
> have a fairly complex object hierarchy which I want to populate all in
> a single shot (rather than relying on lazy loading which would cause
> many roundtrips when the event's subordinates are enumerated). In my
> object model...
> an event has 0...n comments
> a comment has 0...n visibility groups
> an event has 1...n links
> a link has 0...n link descriptors
> an event has 1...n messages
> In the relational world, this necessitates seven tables within my
> schema :
> tb_events
> tb_events_comments
> tb_events_comments_groups
> tb_groups (pre-fetched)
> tb_events_links
> tb_links
> tb_events_messages
> tb_messages (pre-fetched)
> The query I am using to retrieve this lot uses temporary tables and
> goes like this...
> /* -- START -- */
> /* select all the events into a temporary table
> this first statement can be modified if any queries are required
> against
> any of the subordinate
> */
> select *
> into #events
> from tb_events
> where is_deleted = 0
> /* now select all the comments into a temporary table which
> are subordinates of the events identified in query #1
> */
> select *
> into #events_comments
> from tb_events_comments
> where event_uid in (select uid from #events)
> /* and the same for all the links
> */
> select *
> into #events_links
> FROM tb_events_links
> WHERE event_uid in (select uid from #events)
> /* and now all the output queries which actually perform the retrieval
> */
> select * from #events
> select * from #events_comments
> select * from tb_events_comments_groups where comment_uid in (select
> uid from #events_comments)
> select * from #events_links where event_uid in (select uid from
> #events)
> select * from tb_links where uid in (select link_uid from
> #events_links)
> select * from tb_link_descriptors where link_uid in (select link_uid
> from #events_links)
> select * from tb_events_messages where event_uid in (select uid from
> #events)
> /* and tidy up all the temp tables
> */
> drop table #events
> drop table #events_comments
> drop table #events_links
> /* -- END -- */
> My question really is : is the above a valid way of retreiving all the
> data required for population of the object hierarchy or are there any
> nicer / faster / more elegant solutions? I've run it through Profiler
> as both an ad-hoc script and a stored procedure (the sp is naturally
> faster) and it doesn't seem to impose an unreasonable burden on the db.
> The only other way I can think of is using a proxy object (EventProxy)
> which contains "top line" details and if the full event is required
> then it can be separately retrieved.
> Surely there's a nicer way of doing it than this!?
> Hope you can help,
> Matt
>