"Chris" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
|
Similar Threads
