closing DataReader in another layer

[DS]

Is there a way to automatically close the data reader connection? I'm using
the MS Data Access Application block to substantially {entirely} separate
the data access layer (DAL) from the business layer (BL) and this is great
with DataSets since they can be closed off in the DAL, but it doesn't seem
possible with the DataReader since it would need to be .close() in the BL
once I'm done with it.

I've read on the web that there is a bit of a debate about using something
like this:
using (DataReader dr = ExecuteReader(...))
{
return dr;
}
-- supposedly this automatically calls the dispose and closes the connection
once you're done with it in the BL, but some say its no good.

How should I go about making use of it, or should I just stick to DataSets?

Thanks in advance.

[Michael Giagnocavo [MVP]]

Try this:

theReader = theConnection.ExecuteReader(CommandBehavior.CloseC onnection);
return theReader;

CommandBehaviour.CloseConnection makes the reader so that when the reader is
closed, the underlying connection gets closed too. This allows you to call
Close() on the reader somewhere else, and not leak a connection.

However, you might consider DataSets for a different reason: They actually
can go across physical layers, not just logical ones.

--
Michael Giagnocavo
MVP
www.atrevido.net
"DS" <no****************@rogers.com> wrote in message
news:_d********************@rogers.com...

Is there a way to automatically close the data reader connection? I'm
using the MS Data Access Application block to substantially {entirely}
separate the data access layer (DAL) from the business layer (BL) and this
is great with DataSets since they can be closed off in the DAL, but it
doesn't seem possible with the DataReader since it would need to be
.close() in the BL once I'm done with it.

I've read on the web that there is a bit of a debate about using something
like this:
using (DataReader dr = ExecuteReader(...))
{
return dr;
}
-- supposedly this automatically calls the dispose and closes the
connection once you're done with it in the BL, but some say its no good.

How should I go about making use of it, or should I just stick to
DataSets?

Thanks in advance.

[Joyjit Mukherjee]

Hi,

I think you should use the CommandBehavior Enumeration in the parameter to
Executereader method. Set it to CloseConnection so that when ur BL closed
the reader, the connection is automatically closed.

Regards
Joyjit

"DS" <no****************@rogers.com> wrote in message
news:_d********************@rogers.com...

Is there a way to automatically close the data reader connection? I'm using the MS Data Access Application block to substantially {entirely} separate
the data access layer (DAL) from the business layer (BL) and this is great
with DataSets since they can be closed off in the DAL, but it doesn't seem
possible with the DataReader since it would need to be .close() in the BL
once I'm done with it.

I've read on the web that there is a bit of a debate about using something
like this:
using (DataReader dr = ExecuteReader(...))
{
return dr;
}
-- supposedly this automatically calls the dispose and closes the connection once you're done with it in the BL, but some say its no good.

How should I go about making use of it, or should I just stick to DataSets?
Thanks in advance.

[DS]

"Michael Giagnocavo [MVP]" <mg*******@atrevido.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl...

Try this:

theReader = theConnection.ExecuteReader(CommandBehavior.CloseC onnection);
return theReader;

CommandBehaviour.CloseConnection makes the reader so that when the reader
is closed, the underlying connection gets closed too. This allows you to
call Close() on the reader somewhere else, and not leak a connection.

However, you might consider DataSets for a different reason: They actually
can go across physical layers, not just logical ones.

--
Michael Giagnocavo
MVP
www.atrevido.net

Thanks for the reply Michael,
I'd like to not even have to call the .Close on the reader in the business
tier as it is easy to forget to do, especially down the road. With a dataset
its not a big deal since I can close the connection in the DAL before the
dataset even gets returned and never have to worry about it in the buiness
layer. With a dataset if I forget to dispose it, at least that gets taken
care of by the garbage collection, whilst I believe a connection close does
not happen correctly on garbage collection of the DataReader.

Cheers,
Dan

[Nick Malik]

> >

However, you might consider DataSets for a different reason: They actually can go across physical layers, not just logical ones.

I'd like to not even have to call the .Close on the reader in the business
tier as it is easy to forget to do, especially down the road. With a

dataset its not a big deal since I can close the connection in the DAL before the
dataset even gets returned and never have to worry about it in the buiness
layer.

Hi Dan,

If you have truly seperated the data layer from the business layer, then you
really should use the dataset. A lot of folks pan the dataset because the
data is read into an object that gets passed around and it takes time to
load up the object. The same folks then encapsulate their data into an
object and, guess what, they pass it around, rarely adding much more
functionality to it than the dataset already has.

One thing that I have done, in my code, is to create an object that contains
a dataset. I pass my object into my DAL, which fills the contained dataset
with data. I then pass my object around, which as some additional
functionality for validating and enforcing business rules. When it comes
time to update the database, I pass my object back to the DAL, which then
uses the internal dataset to update the database. That way, I get the
functionality of the Dataset, plus the business rules enforcement of my
application, with a minimum of code to debug.

(I don't believe in re-inventing the wheel).

Hope this helps,
--- Nick

[Michael Giagnocavo [MVP]]

Well, if you don't want to call Close, i.e., manage resources, then don't
use unmanaged resources (like a DB connection). A datareader is a directly
connected reader to get data out of the DB. If you find that it's hard to
keep track of in other layers and so on, then you should not be using it.

--
Michael Giagnocavo
MVP
www.atrevido.net
"DS" <no****************@rogers.com> wrote in message
news:Yf********************@rogers.com...

"Michael Giagnocavo [MVP]" <mg*******@atrevido.net> wrote in message
news:O9**************@TK2MSFTNGP09.phx.gbl...

Try this:

theReader = theConnection.ExecuteReader(CommandBehavior.CloseC onnection);
return theReader;

CommandBehaviour.CloseConnection makes the reader so that when the reader
is closed, the underlying connection gets closed too. This allows you to
call Close() on the reader somewhere else, and not leak a connection.

However, you might consider DataSets for a different reason: They
actually can go across physical layers, not just logical ones.

--
Michael Giagnocavo
MVP
www.atrevido.net

Thanks for the reply Michael,
I'd like to not even have to call the .Close on the reader in the business
tier as it is easy to forget to do, especially down the road. With a
dataset its not a big deal since I can close the connection in the DAL
before the dataset even gets returned and never have to worry about it in
the buiness layer. With a dataset if I forget to dispose it, at least that
gets taken care of by the garbage collection, whilst I believe a
connection close does not happen correctly on garbage collection of the
DataReader.

Cheers,
Dan

[DS]

"Nick Malik" <ni*******@hotmail.nospam.com> wrote in message
news:akBpd.567042$mD.385239@attbi_s02...

>
> However, you might consider DataSets for a different reason: They actually > can go across physical layers, not just logical ones.
>

I'd like to not even have to call the .Close on the reader in the
business
tier as it is easy to forget to do, especially down the road. With a

dataset

its not a big deal since I can close the connection in the DAL before the
dataset even gets returned and never have to worry about it in the
buiness
layer.

Hi Dan,

If you have truly seperated the data layer from the business layer, then
you
really should use the dataset. A lot of folks pan the dataset because the
data is read into an object that gets passed around and it takes time to
load up the object. The same folks then encapsulate their data into an
object and, guess what, they pass it around, rarely adding much more
functionality to it than the dataset already has.

One thing that I have done, in my code, is to create an object that
contains
a dataset. I pass my object into my DAL, which fills the contained
dataset
with data. I then pass my object around, which as some additional
functionality for validating and enforcing business rules. When it comes
time to update the database, I pass my object back to the DAL, which then
uses the internal dataset to update the database. That way, I get the
functionality of the Dataset, plus the business rules enforcement of my
application, with a minimum of code to debug.

(I don't believe in re-inventing the wheel).

Hope this helps,
--- Nick

Hi Nick,
Thanks for the response, and yes it does help. I'm coming from an
ASP/VBScript/ADO background, and for scaleable website the best bet was to
always dump the recordset into an array, that way you could cut the
connection to the database right away (important for web development), and
you wouldn't have the overhead of having the ADO component floating around.
I know DataSets resolve both issues, but since in some cases I'm dealing
with such simple data I thought it may be better to just toss it into an
array and get it in there with the firehose (which I think is what the
DataReader uses anyway). In this case the data is just a single column of
primary key (int) numbers that can be dumped into an integer 1D array. What
do you think?

The second reason I'm doing it is because the result (be it a DataSet that
contains, just 1 table, and that table contains just 1 column, OR just a
simple array) is being returned from a static method (the method is used to
find/search for a given string and return all the recordIds that contain
it). So it seems from a program simplicity/design/maintainability/logic side
of things it makes sense to return the simple int array because it is fairly
self explanitory (just a list of Ids), rather than a DataSet which is a bit
more ambigious.

I'd appreciate any input you have with this approach since as I said, I'm
kind'a new to this :-)

Thanks again,
Dan

[Anders Borum]

Hello!

If you have truly seperated the data layer from the business layer, then you really should use the dataset.
If speed is of high importance, you can't beat a datareader populating your
own custom models. The dataset is great, but don't forget that the joy of
filling a dataset comes with a performance degradation.
The same folks then encapsulate their data into an
object and, guess what, they pass it around, rarely adding much more
functionality to it than the dataset already has.

There's nothing wrong with that, if you can justify writing your own models.
I agree that there are many situations where you'd want to wrap / use typed
datarows (and the other objects from the dataset), and it's a timesaver when
combined with code generation.

--
venlig hilsen / with regards
anders borum
--