OdbcDataReader - Problem closing connection

  • Thread starter Thread starter Steven Blair
  • Start date Start date
S

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
 
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,

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 said:
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]
- (e-mail address removed)

Steven Blair said:
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
Click to expand...
Click to expand...
 
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]
- (e-mail address removed)

Steven Blair said:
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

message news:[email protected]...
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]
- (e-mail address removed)

Steven Blair said:
Hi,

I am writing an application using a 3 tier model (Client, Business
Click to expand...
Layer
and
DB)
The DB layer creates a OdbcDataReader object back up to the Client
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
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
 
Back
Top