DataReader and DAL

[Diffident]

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is not a
good practice...righ t?

Thnks for all your suggestions!!

[sloan]

Use the IDataReader object.
I prefer to ~consume IDataReader's in the Biz Layer, but to NOT send them
"up" to the Presentation layer.

See
http://sholliday.spaces.live.com/blog/
5/24/2006
Custom Objects/Collections and Tiered Development
for a complete downloadable example.

Also .. at the bottom of my blog entry, there is a MS article (which I say
"read top to bottom" or something like that)
It will give you a good birds eye view.
"Diffident" <Di*******@disc ussions.microso ft.comwrote in message
news:FB******** *************** ***********@mic rosoft.com...

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is not

a

good practice...righ t?

Thnks for all your suggestions!!

[EagleRed]

I would not say that returning a DataReader is not a good practice in
general. If fact the best approach depends upon the problem and issues at
hand.

The DataReader is a read-only, forward-only object that is good for quick
retrieval of data while maintaining an open database connection. You should
close the DataReader BEFORE closing the connection or it will be orphaned and
there will be problems in performing subsequent operations in the same
database. You can use the DataReader directly as a datasource on list
oriented controls. Also, you can iterate through the DataReader and capture
the data to business objects.

The DataTable does not apply to a DataReader. It is usually used in
connection with the DataSet object and the DataAdapter. It is used to
contain a snapshot of the underlying data while DISCONNECTED from the
datasource. You can, however, repeatedly access the data going forward or
backward in the result set and perform updates, insertions and deletions in a
disconnected fashion and then reconnect to the datasource to apply the
changes.

I hope this helps. You should consult the documentation or any of a number
of excellent books on the subject. A good current suggestion is "Programmin g
Microsoft ADO.NET 2.0 - Core Reference, 2005 Edition" by David Sceppa
(Microsoft Press), ISBN 0-7356-2206-X.

Good luck,
Eagle
"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is not a
good practice...righ t?

Thnks for all your suggestions!!

[Diffident]

Let's consider that I have a generic method in my DAL which returns
IDataReader object to my BLL. When will I close my connection. Should I close
it in BLL? It does seem to be a good practice to close the connection in
BLL.....

"Ea******@HighF lyingBirds.com" wrote:

I would not say that returning a DataReader is not a good practice in
general. If fact the best approach depends upon the problem and issues at
hand.

The DataReader is a read-only, forward-only object that is good for quick
retrieval of data while maintaining an open database connection. You should
close the DataReader BEFORE closing the connection or it will be orphaned and
there will be problems in performing subsequent operations in the same
database. You can use the DataReader directly as a datasource on list
oriented controls. Also, you can iterate through the DataReader and capture
the data to business objects.

The DataTable does not apply to a DataReader. It is usually used in
connection with the DataSet object and the DataAdapter. It is used to
contain a snapshot of the underlying data while DISCONNECTED from the
datasource. You can, however, repeatedly access the data going forward or
backward in the result set and perform updates, insertions and deletions in a
disconnected fashion and then reconnect to the datasource to apply the
changes.

I hope this helps. You should consult the documentation or any of a number
of excellent books on the subject. A good current suggestion is "Programmin g
Microsoft ADO.NET 2.0 - Core Reference, 2005 Edition" by David Sceppa
(Microsoft Press), ISBN 0-7356-2206-X.

Good luck,
Eagle
"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is not a
good practice...righ t?

Thnks for all your suggestions!!

[bruce barker \(sqlwork.com\)]

datareaders use unmanaged memory, so best practice is open, and close them
in the same routine, with a using or try/finally statement.

-- bruce (sqlwork.com)

"Diffident" <Di*******@disc ussions.microso ft.comwrote in message
news:F4******** *************** ***********@mic rosoft.com...

Let's consider that I have a generic method in my DAL which returns
IDataReader object to my BLL. When will I close my connection. Should I
close
it in BLL? It does seem to be a good practice to close the connection in
BLL.....

"Ea******@HighF lyingBirds.com" wrote:

>I would not say that returning a DataReader is not a good practice in
general. If fact the best approach depends upon the problem and issues
at
hand.

The DataReader is a read-only, forward-only object that is good for quick
retrieval of data while maintaining an open database connection. You
should
close the DataReader BEFORE closing the connection or it will be orphaned
and
there will be problems in performing subsequent operations in the same
database. You can use the DataReader directly as a datasource on list
oriented controls. Also, you can iterate through the DataReader and
capture
the data to business objects.

The DataTable does not apply to a DataReader. It is usually used in
connection with the DataSet object and the DataAdapter. It is used to
contain a snapshot of the underlying data while DISCONNECTED from the
datasource. You can, however, repeatedly access the data going forward
or
backward in the result set and perform updates, insertions and deletions
in a
disconnected fashion and then reconnect to the datasource to apply the
changes.

I hope this helps. You should consult the documentation or any of a
number
of excellent books on the subject. A good current suggestion is
"Programmin g
Microsoft ADO.NET 2.0 - Core Reference, 2005 Edition" by David Sceppa
(Microsoft Press), ISBN 0-7356-2206-X.

Good luck,
Eagle
"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a
DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is
not a
good practice...righ t?

Thnks for all your suggestions!!

[Marina Levit [MVP]]

You would have to close it there. The DAL would have no idea when the
consumer is done with the datareader. This is why using datareaders is more
risky - you are relying on the class consuming the DAL to close the
connection. If the programmer of that class forgets, you end up spending
time tracking connection leaks.

"Diffident" <Di*******@disc ussions.microso ft.comwrote in message
news:F4******** *************** ***********@mic rosoft.com...

Let's consider that I have a generic method in my DAL which returns
IDataReader object to my BLL. When will I close my connection. Should I
close
it in BLL? It does seem to be a good practice to close the connection in
BLL.....

"Ea******@HighF lyingBirds.com" wrote:

>I would not say that returning a DataReader is not a good practice in
general. If fact the best approach depends upon the problem and issues
at
hand.

The DataReader is a read-only, forward-only object that is good for quick
retrieval of data while maintaining an open database connection. You
should
close the DataReader BEFORE closing the connection or it will be orphaned
and
there will be problems in performing subsequent operations in the same
database. You can use the DataReader directly as a datasource on list
oriented controls. Also, you can iterate through the DataReader and
capture
the data to business objects.

The DataTable does not apply to a DataReader. It is usually used in
connection with the DataSet object and the DataAdapter. It is used to
contain a snapshot of the underlying data while DISCONNECTED from the
datasource. You can, however, repeatedly access the data going forward
or
backward in the result set and perform updates, insertions and deletions
in a
disconnected fashion and then reconnect to the datasource to apply the
changes.

I hope this helps. You should consult the documentation or any of a
number
of excellent books on the subject. A good current suggestion is
"Programmin g
Microsoft ADO.NET 2.0 - Core Reference, 2005 Edition" by David Sceppa
(Microsoft Press), ISBN 0-7356-2206-X.

Good luck,
Eagle
"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a
DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is
not a
good practice...righ t?

Thnks for all your suggestions!!

[Peter Bromberg [C# MVP]]

I wish I had a dollar for every post I've read where developers use
DataReaders and don't seem to understand that they are holding connections
open, and then they start getting exceptions because the pool has no more
connections available.

There is a bit more overhead in using a SqlDataAdapter to return a Dataset,
but the advantage is that the Adapter handles all the connection business
automatically and now you've got disconnected data that you can do whatever
you want with in your DAL without worrying about blowup up your application.

Word to the wise.

Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a DAL
method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is not a
good practice...righ t?

Thnks for all your suggestions!!

[sloan]

That's the question on the table ... isn't it?

Do the developers know how to use the IDataReader's correctly?

If so, then the biz layer can consume/use and CLOSE the datareader
correctly.
Aka, you can trust your biz layer developers to do this.

If not, then return DataTables or DataSets, and not IDataReaders.
IDataReader's offer the best performance, but there isn't any magic fairy
dust, if you want use them, you need to know how to use them , and how to
close them.

Where I am, I can trust the bizlayer developers to close the datareaders.
However, presentation developers could be anybody, thus I do NOT (where I
am ) send IDataReaders to people who code the presentation layers.

........
You'll have to decide what is the appropriate solution where you work.

"Peter Bromberg [C# MVP]" <pb*******@yaho o.nospammin.com wrote in message
news:80******** *************** ***********@mic rosoft.com...

I wish I had a dollar for every post I've read where developers use
DataReaders and don't seem to understand that they are holding connections
open, and then they start getting exceptions because the pool has no more
connections available.

There is a bit more overhead in using a SqlDataAdapter to return a

Dataset,

but the advantage is that the Adapter handles all the connection business
automatically and now you've got disconnected data that you can do

whatever

you want with in your DAL without worrying about blowup up your

application.

>
Word to the wise.

Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com


"Diffident" wrote:

Hello All,

I would like to use DataReader based accessing in my Data Access Layer
(DAL). What is considered to be a best practice while returning from a

DAL

method that executes a query and returns N rows. DataReader object?
Collection object? DataTable object? Returning a DataReader object is

not a

good practice...righ t?

Thnks for all your suggestions!!