stored procedures and typed datasets

[Chris]

I have the following setup:

1. 5 tables in a SQL database:
- Protocols
- Readers
- Participants
- Items
- Comments

2. The relationships between the tables are as follows:
- Protocols can contain multiple Readers, Participants and Items
- Items can contain multiple Comments

I am using these objects in an ASP.NET application which explains why I
don't want to retrieve more data than necessary in each of the following
questions. A DataSet containing all the data would be too big for the
Cache, Session or Application objects. My questions are as follows:

1. Should I use a single typed DataSet containing all the tables? I can
see how this might be a good solution with good relation maintenance
etc. but what about if I want to return a list of the Name field from
each Protocol row? I don't want to retrieve each Protocols row's
Readers, Participants and Items rows and each Items row's Comments rows
when all I'm interested is the Name field from the Protocols table. I
might often want to retrieve a subset of Protocols aswell. Using a
DataView would mean that the whole table would get retrieved and then
get filtered, causing excess network traffic and processing.

2. How would I retrieve a single Protocols row and put it into the typed
DataSet with its Readers and Participants loaded into DataTables in the
typed DataSet?

3. How do I set the SourceColumn property for my SqlCommand.Parameter
objects? In untyped datasets this is as simple as setting it to the
late-bound Column.Name property. Is it the same for typed DataSets? This
would, as far as I know, take away the advantage of early-binding in
typed DataSets.

Am I simply using the wrong tools at the wrong time here? One example of
a function in my ASP.NET application is my "Protocol Wizard" which spans
over 4 pages. 1 page for Protocols row information, 1 for Participants
and 1 for Readers. My solution was to create a class that could contain
the data in a couple of strings and ArrayLists and store an instance of
the class in the Session object for the duration of the wizard. When the
user views the 4th page which is a summary of the first 3 and confirms
the data entered, I manually move the class's data into the SQL
database. This works but I can't help feeling that I shouldn't be making
my own classes for data storage when the System.Data namespace already
has a whole plethora of objects for storage, management and
transportation of data. Doing things typed should make things even
better.

Regards
Chris

 

[David Browne]

I have the following setup:

1. 5 tables in a SQL database:
- Protocols
- Readers
- Participants
- Items
- Comments

2. The relationships between the tables are as follows:
- Protocols can contain multiple Readers, Participants and Items
- Items can contain multiple Comments

I am using these objects in an ASP.NET application which explains why I
don't want to retrieve more data than necessary in each of the following
questions. A DataSet containing all the data would be too big for the
Cache, Session or Application objects. My questions are as follows:

1. Should I use a single typed DataSet containing all the tables?
Yes.

I can
see how this might be a good solution with good relation maintenance
etc. but what about if I want to return a list of the Name field from
each Protocol row?

You either fill the ProtocolDataTable with "select * from protocol", or you
define a seperate DataSet with just the name column, and fill it with
"select name from protocol". If you have only a handful of protocols, you
can just fill the protocol DataTable right off the bat, and load the other
tables as needed.

I don't want to retrieve each Protocols row's
Readers, Participants and Items rows and each Items row's Comments rows
when all I'm interested is the Name field from the Protocols table.

You can always fetch the parent row without fetching the child row. If you
want to fetch the child row without fetching the parent row you must set
DataSet.EnforceConstraints = false.

I
might often want to retrieve a subset of Protocols aswell. Using a
DataView would mean that the whole table would get retrieved and then
get filtered, causing excess network traffic and processing.

You can fill the DataTable with a selective query like "select * from
protocol where ..."

2. How would I retrieve a single Protocols row and put it into the typed
DataSet with its Readers and Participants loaded into DataTables in the
typed DataSet?

Fill each DataTable with a selective query like

"select * from protocol where id = @id" - for protocol DataTable
"select * from Readers where protocol_id = @id"
"select * from Participants where protocol_id = @id"
"select * from Comments where reader_id in (select reader_id from Readers
where protocol_id = @id)"

3. How do I set the SourceColumn property for my SqlCommand.Parameter
objects? In untyped datasets this is as simple as setting it to the
late-bound Column.Name property. Is it the same for typed DataSets?

Same way. The CommandBuilder will do this for you, so you can always use
the CommandBuilder and examine the Commands it creates to figure out what's
going on.

This
would, as far as I know, take away the advantage of early-binding in
typed DataSets.

Huh? Not at all. The Parameter.SourceColumn just tells the DataAdapeter
how map database columns to DataTable columns.

Am I simply using the wrong tools at the wrong time here? One example of
a function in my ASP.NET application is my "Protocol Wizard" which spans
over 4 pages. 1 page for Protocols row information, 1 for Participants
and 1 for Readers. My solution was to create a class that could contain
the data in a couple of strings and ArrayLists and store an instance of
the class in the Session object for the duration of the wizard. When the
user views the 4th page which is a summary of the first 3 and confirms
the data entered, I manually move the class's data into the SQL
database. This works but I can't help feeling that I shouldn't be making
my own classes for data storage when the System.Data namespace already
has a whole plethora of objects for storage, management and
transportation of data. Doing things typed should make things even
better.

I agree. Typed Datasets are the way to go. You can stash the dataset in
the Session and carry it from page to page.

One useful trick is to inherit from your typed dataset. In your inherited
class you can add all sorts of useful business logic, and retain all the
benefits of having a DataSet.

David

 

[Guest]

Thank you for your answer David and sorry I haven't replied sooner. For some reason, most of my posts and the replies they generate disappear once I get a reply. This only happens in my dedicated nntp client (xnews) and not on the web-based version. Can anyone explain this

The answer I got to my questions were fine although I suddenly find myself having to rethink many of the principles I have had regarding ADO.NET, especially in ASP.NET. Does this mean DataReader should be used for all read-only list operations and DataSets for everything else? How about ExecuteNonQuery() for parameter based stored procs with output params etc

I feel many books, sites, tutorials have a very different opinion on what is best. Is there some way of getting a final answer on what is best in specific situations? My understanding is that a DataSet carries more overhead on the in-memory object level but is disconnected so that it all-in-all consumes less resources in the form of connections etc

Regard
Chris

 

[Kevin Yu [MSFT]]

Hi Chris,

Please try to refresh the news client to see the missing posts.

Your understanding to ADO.NET is quite right. However, it is not
recommended to use ExecuteNonQuery and use output params to get the results
of query. ExecuteNonQuery is best for Insert, Update or Delete command.

Whether to use DataSet or DataReader depends on the application you are
working on. The DataReader requires an constantly opened connection, so it
consumes more resource than DataSet does. DataSet is just a dump of data in
the memeory. On an ASP.NET situation, it is better to use DataSet, as there
might be large amount of requests from the client at one time.

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Thank you for your help

Regard
Chris

 

[Kevin Yu [MSFT]]

Hi Chris,

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."