OdbcDataReader - Problem closing connection

[Steven Blair]

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

[Nicholas Paldino [.NET/C# MVP]]

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

[Steven Blair]

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

[Nicholas Paldino [.NET/C# MVP]]

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

[William Ryan eMVP]

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

[William Ryan eMVP]

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

[Steven Blair]

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!