By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,165 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,165 IT Pros & Developers. It's quick & easy.

OdbcDataReader - Problem closing connection

P: n/a
Hi,

I am writing an application using a 3 tier model (Client, Business Layer and
DB)
The DB layer creates a OdbcDataReader object back up to the Client where the
data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed this
closed the connection to the Database, but apparently not (using processlist
in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client layer
without having to pass the original Connection object to the Client layer?

Regards,

Steven
Nov 16 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Steven,

The OdbcConnection is using connection pooling in order to optimise
multiple accesses to the connection pool. Opening and closing a database
connection (for real, instead of fetching it from a pool), can be a very,
very expensive operation.

If you truly need to do this, then you need to set the connection
properties such that connection pooling is disabled.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Hi,

I am writing an application using a 3 tier model (Client, Business Layer and DB)
The DB layer creates a OdbcDataReader object back up to the Client where the data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed this
closed the connection to the Database, but apparently not (using processlist in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client layer
without having to pass the original Connection object to the Client layer?

Regards,

Steven

Nov 16 '05 #2

P: n/a
Nicholas,

Not sure I understand.

I have returned a DataReader from my Database layer:

GetData( ref dr );

Take the data I need from it, then:

dr.Close();

I had originally thought this closed the underlying DB connection.

Is there anyway to close the connection from the Client Layer, since the
Client layer does not have access to Connection objects?

I find it hard to believe that a DataReader could only be closed at the same
level as it was created at.

Regards,

Steven

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:uF**************@tk2msftngp13.phx.gbl...
Steven,

The OdbcConnection is using connection pooling in order to optimise
multiple accesses to the connection pool. Opening and closing a database
connection (for real, instead of fetching it from a pool), can be a very,
very expensive operation.

If you truly need to do this, then you need to set the connection
properties such that connection pooling is disabled.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Hi,

I am writing an application using a 3 tier model (Client, Business Layer

and
DB)
The DB layer creates a OdbcDataReader object back up to the Client where

the
data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed this
closed the connection to the Database, but apparently not (using

processlist
in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client layer without having to pass the original Connection object to the Client layer?
Regards,

Steven


Nov 16 '05 #3

P: n/a
Steven,

It's not a matter of the object being closed at the level it is being
created at.

When you call close on the data reader, it does not close the connection
(it shouldn't). The data reader should only close the reader. The state of
the connection should be unchanged.

Now, the other thing to consider is that when you call close on the
connection itself, if connection pooling is being used (which it most likely
is), then you will have to shut that off (however, this is unadvised).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Nicholas,

Not sure I understand.

I have returned a DataReader from my Database layer:

GetData( ref dr );

Take the data I need from it, then:

dr.Close();

I had originally thought this closed the underlying DB connection.

Is there anyway to close the connection from the Client Layer, since the
Client layer does not have access to Connection objects?

I find it hard to believe that a DataReader could only be closed at the same level as it was created at.

Regards,

Steven

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in message news:uF**************@tk2msftngp13.phx.gbl...
Steven,

The OdbcConnection is using connection pooling in order to optimise
multiple accesses to the connection pool. Opening and closing a database
connection (for real, instead of fetching it from a pool), can be a very, very expensive operation.

If you truly need to do this, then you need to set the connection
properties such that connection pooling is disabled.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Hi,

I am writing an application using a 3 tier model (Client, Business Layer
and
DB)
The DB layer creates a OdbcDataReader object back up to the Client
where the
data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed

this closed the connection to the Database, but apparently not (using

processlist
in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client

layer without having to pass the original Connection object to the Client layer?
Regards,

Steven



Nov 16 '05 #4

P: n/a
Steven:

There are a LOT of potential problems with passing datareaders between
layers. I think Kathleen Dollard's words on the subject were "suicide" and
"disaster waiting to happen" You'll notice MS Left the datareader out of
the data access application block. Since they are worthless without
connections, you have to pass a bunch of stuff a lot of places to get this
implementation to work, and for what it's worth, I'd make really sure I
wanted to use this methodology before going live.
"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Hi,

I am writing an application using a 3 tier model (Client, Business Layer and DB)
The DB layer creates a OdbcDataReader object back up to the Client where the data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed this
closed the connection to the Database, but apparently not (using processlist in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client layer
without having to pass the original Connection object to the Client layer?

Regards,

Steven

Nov 16 '05 #5

P: n/a
You may find this helpful..
http://weblogs.asp.net/ssmith/archiv...4/07/5029.aspx
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:u3**************@tk2msftngp13.phx.gbl...
Steven:

There are a LOT of potential problems with passing datareaders between
layers. I think Kathleen Dollard's words on the subject were "suicide" and
"disaster waiting to happen" You'll notice MS Left the datareader out of
the data access application block. Since they are worthless without
connections, you have to pass a bunch of stuff a lot of places to get this
implementation to work, and for what it's worth, I'd make really sure I
wanted to use this methodology before going live.
"Steven Blair" <st**********@btinternet.com> wrote in message
news:c7*******************@news.demon.co.uk...
Hi,

I am writing an application using a 3 tier model (Client, Business Layer

and
DB)
The DB layer creates a OdbcDataReader object back up to the Client where

the
data is read and displayed on the screen.
I then call the Close method of this OdbcDataReader. I had assumed this
closed the connection to the Database, but apparently not (using

processlist
in MySQL confirmed the connections were not being closed).
How can I successfully close the Database connection from the Client layer without having to pass the original Connection object to the Client layer?
Regards,

Steven


Nov 16 '05 #6

P: n/a
Thanx everyone for the comments. I think I am going to avoid using
DataReader's from now. I should have done a little more homework before
over running my application with them (I have spent most of today
changing over to use DataSets)

Maybe MSDN should have a warning about using DataReaders ;)

Steven
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.