Best way to return SQL query results in multi-tiered web environment?

[Daniel]

Hi, I'm new to this .Net stuff but am learning as well as I can.

It looks as though there are 2 basic ways to return data from the Data
Access Layer (DAL) to the Business Logic Layer (BLL). One is through the
DataSet class as disconnected cached data. And the other way is through the
DataReader class. The MSDN articles and other sources that I have been
reading recommended not using the DataSet method due to its high overhead
and resource requirement, particulary in web projects where data will need
to be reloaded on virtually every page request. So that option looks like
a poor choice. The DataReader method seems to work fine with 1 exception.
The DataReader object needs to keep the database connection open in order to
use its read function to move through the query results. This seems like a
significant draw back because no other actions can be done to that
connection until the DataReader's close method is called. Which means that
the connection cannot be closed and released back into the pool. Is there
any other way to return query results to the BLL?

Also, if a stored procedure returns both query results and an output
parameter, the output parameter is not available until the DataReader's
close method is called. But, there is apparently no way to reopen the
DataReader after it is closed. How should this be accomplished?

I'm starting to miss disconnected recordsets.

Thanks in advance for any help you provide.

 

[Marina]

Datareaders should never be used to return data from a different tier of the
application.

Datareaders are forward only, read-only and need an open connection. If you
need to go back to the beginning or the middle then you should not be using
a datareader.

You also, cannot ensure that the user who receives the datareader closes it
properly. One more reason to not return datareaders to a client.

Datasets are pretty much the equivalent of disconnected datasets. Why were
you so inclined to use the datareader object, and not the dataset? It is
perfect for what you are trying to do. I don't think it's so resource
intensive that it kills the application.

If you don't like the dataset, create your own datastructure, populate it,
and return that to the user of your business object.

 

[Daniel]

Hi, thanks for the response.

What you say makes sense, however from what I read here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/
vbconDecidingOnDataAccessStrategy.asp, it seems the DataReader might make
more sense for web projects. Of course it still has the open connection
issue. Ideally I would like to create my own classes and return those to
the above layers, however I don't know how to do this efficiently. Do I
loop through the DataReader inside the DAL filling my custom objects and
then return those? It seems like that is inefficient because I would
essentially need to loop through the data twice. Once to fill the objects,
and again to work with the custom data structure in the BLL. Any ideas?

But now after looking more into it, you are probably right in saying that
the DataSet is probably the way to go. It's disconnected and easy to pass
back up through the layers. I'll use that for now.

Thanks,
Dan

Datareaders should never be used to return data from a different tier of the
application.

Datareaders are forward only, read-only and need an open connection. If you
need to go back to the beginning or the middle then you should not be using
a datareader.

You also, cannot ensure that the user who receives the datareader closes it
properly. One more reason to not return datareaders to a client.

Datasets are pretty much the equivalent of disconnected datasets. Why were
you so inclined to use the datareader object, and not the dataset? It is
perfect for what you are trying to do. I don't think it's so resource
intensive that it kills the application.

If you don't like the dataset, create your own datastructure, populate it,
and return that to the user of your business object.

Hi, I'm new to this .Net stuff but am learning as well as I can.

It looks as though there are 2 basic ways to return data from the Data
Access Layer (DAL) to the Business Logic Layer (BLL). One is through the
DataSet class as disconnected cached data. And the other way is through the
DataReader class. The MSDN articles and other sources that I have been
reading recommended not using the DataSet method due to its high overhead
and resource requirement, particulary in web projects where data will need
to be reloaded on virtually every page request. So that option looks like
a poor choice. The DataReader method seems to work fine with 1 exception.
The DataReader object needs to keep the database connection open in

order

to
use its read function to move through the query results. This seems

like