MS SQL XML - SQL, XML, Nested Datagrids and the like

Author

SQL, XML, Nested Datagrids and the like

David Lozzi

2005-10-27, 9:25 am

Hello,

First, I apologize for the cross group posting.

I have a calendar to create in ASP.Net, using VB. Basically, the calendar
will display each event, and each event has a list of dates. For Example:

Event 1
date1, date2, date3,

Event 2
date1, date2, date3

and so on....

Here's how I would do it:

Create the event datagrid and load it with all of the events. Each event
will have a datagrid within itself and load that with the appropriate dates.
This works, as I've done it elsewhere, and it looks good. But is it the best
way? Here's my problem with it. When a user hits this page, there will be
one request to SQL for the event list, and then individual requests for each
event to pull the dates. This could reach 15 events for a page, so we're
looking at 16 queries to SQL just for a single page, PER USER! It just feels
like a lot of traffic, doesn't it?

I'm wondering if I can pull the entire single query in XML, then parse and
read it out as one object, one request, for the entire page. If I can, can
you provide some sort of guide or tutorial on how to do it. Or some sample
code. I've worked with some XML (rss and web services) but never had to
create a datagrid from it. Any help is GREATLY appreciated!

Thanks a ton!

--
David Lozzi
Web Applications Developer
dlozzi@(remove-this)delphi-ts.com

Frankie

2005-10-27, 9:25 am

The "just parse and read it" referring to the XML file might be a lot of
work... not sure how you'd actually bind your grids to populate them (or if
you'd have to write a bunch of code to get the data into the grids).

A more traditional approach, and one that might be more straight-forward,
would be to load all of your event information into a DataSet (which can be
thought of as more or less a full-blowd database in memory). The DataSet
contains individual DataTable objects - possibly explicitly related to each
other. You can bind your grids to those DataTables as necessary. To get away
from a bunch of DB hits you could populate your dataSet and then stick it in
the Cache object. The next user comes along and you retrieve the data from
the cached DataSet (no hit against the DB). This would be one possible way
that might make sense if the underlying data changes infrequently.

Just remember that the Cache can get blown away by the system as necessary -
so you'd have to have logic that first attempts to find the DataSet in the
Cache... uses it if it's there... otherwise hits the db to populate it (then
sticks it into the Cache for possible future use). You'd also have to have
some way to refresh the data in the Cache when the data in the underlying db
changes.

-HTH

"David Lozzi" <DavidLozzi@nospam.nospam> wrote in message
news:ubX2zI3yFHA.908@tk2msftngp13.phx.gbl...
> Hello,
>
> First, I apologize for the cross group posting.
>
> I have a calendar to create in ASP.Net, using VB. Basically, the calendar
> will display each event, and each event has a list of dates. For Example:
>
> Event 1
> date1, date2, date3,
>
> Event 2
> date1, date2, date3
>
> and so on....
>
> Here's how I would do it:
>
> Create the event datagrid and load it with all of the events. Each event
> will have a datagrid within itself and load that with the appropriate
> dates. This works, as I've done it elsewhere, and it looks good. But is it
> the best way? Here's my problem with it. When a user hits this page, there
> will be one request to SQL for the event list, and then individual
> requests for each event to pull the dates. This could reach 15 events for
> a page, so we're looking at 16 queries to SQL just for a single page, PER
> USER! It just feels like a lot of traffic, doesn't it?
>
> I'm wondering if I can pull the entire single query in XML, then parse and
> read it out as one object, one request, for the entire page. If I can, can
> you provide some sort of guide or tutorial on how to do it. Or some sample
> code. I've worked with some XML (rss and web services) but never had to
> create a datagrid from it. Any help is GREATLY appreciated!
>
> Thanks a ton!
>
> --
> David Lozzi
> Web Applications Developer
> dlozzi@(remove-this)delphi-ts.com
>
>
>
>

Sponsored Links