connection close problem

[sam]

Hello group,
I have a function which is used to initiate sqlDataReader
object.
I was trying to invoke the close method on the DataReader object but
cant really do that as the function returns a datareader and cannot
access the datareader once the connection is closed.

Here is what I do:

public function getDataReader() as datareader
oCmd.Connection.Open() ( oCmd - command object)
Dim oDr As SqlDataReader =
oCmd.ExecuteReader(CommandBehavior.CloseConnection )
' cant add a odr.close()
Return oDr
End Function

How do I close a datareader explicitly and access it outside the
function.

Thanks,
Chris.

[William Ryan eMVP]

Hi Sam:
"sam" <sr*****@mailcity.com> wrote in message
news:5c**************************@posting.google.c om...

Hello group,
I have a function which is used to initiate sqlDataReader
object.
I was trying to invoke the close method on the DataReader object but
cant really do that as the function returns a datareader and cannot
access the datareader once the connection is closed.

Here is what I do:

public function getDataReader() as datareader
oCmd.Connection.Open() ( oCmd - command object)
Dim oDr As SqlDataReader =
oCmd.ExecuteReader(CommandBehavior.CloseConnection )
' cant add a odr.close()
Return oDr
End Function
Returning datareaders is generally something you probably want to avoid. A
Reader is a 'connected' object and as such, is useless without a persistent
connection. As such, what you are asking to do , if I understand you
correctly, is imposssible. Remember also that ExecuteReader simply fires
the query but the data transfer hasn't happened yet. So if you close the
connection an/or the reader, that's pretty much it as far as that goes.

Also, Kathleen Dollard http://www.gendotnet.com/blog/ has really come out
against, for instance, passing data readers between layers. IN this case
you aren't exactly doing what she warns against but it's similar enough.
You'll notice that MS left out a method to return a reader from the Data
Access Application block.. and there's a good reason for it. To get
anything like this to work, you'd need to pass around a connection and a
reader and the whole thing is going to be kind of klunky (at best).

I'd recommend building a business object that mirrors the fields in the
Query the Reader is based on . Then build a collection of these objects
(strongly typed would be best) and fill the collection with the reader.
Then you can close the reader and the connection and just return the
Collection. This will give you all the functionality you need and provide a
much more safe and clean framework to work in.

HTH,

Bill
How do I close a datareader explicitly and access it outside the
function.

Thanks,
Chris.

W.G. Ryan, eMVP
http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html

[Marina]

That's right.

You don't close the connection - keep the connection open and return the
datareader.

When you call Close on the datareader, that will automatically close
connection, since you are using CommandBehavior.CloseConnection.

If you don't close the reader, the connection will remain open until the GC
cleans it up, which may be a while, and more then likely you will run out of
connections in your connection pool.

"sam" <sr*****@mailcity.com> wrote in message
news:5c**************************@posting.google.c om...

Hello group,
I have a function which is used to initiate sqlDataReader
object.
I was trying to invoke the close method on the DataReader object but
cant really do that as the function returns a datareader and cannot
access the datareader once the connection is closed.

Here is what I do:

public function getDataReader() as datareader
oCmd.Connection.Open() ( oCmd - command object)
Dim oDr As SqlDataReader =
oCmd.ExecuteReader(CommandBehavior.CloseConnection )
' cant add a odr.close()
Return oDr
End Function

How do I close a datareader explicitly and access it outside the
function.

Thanks,
Chris.

[chris]

yah.. Thats exactly whats happening. But coz I use this function in a loop
( pre-existing code), as a result the connection pool is running out of
connections.. So, I had found a hack which works fine as of now..I return
the datareader to the caller function where a datareader object is created.
Invoking a close on that datareader solves the problem :)
Ryan's method could be used but its too late for that..

Thanks for the reply..

"Marina" <so*****@nospam.com> wrote in message
news:e8**************@TK2MSFTNGP09.phx.gbl...

That's right.

You don't close the connection - keep the connection open and return the
datareader.

When you call Close on the datareader, that will automatically close
connection, since you are using CommandBehavior.CloseConnection.

If you don't close the reader, the connection will remain open until the GC cleans it up, which may be a while, and more then likely you will run out of connections in your connection pool.

"sam" <sr*****@mailcity.com> wrote in message
news:5c**************************@posting.google.c om...

Hello group,
I have a function which is used to initiate sqlDataReader
object.
I was trying to invoke the close method on the DataReader object but
cant really do that as the function returns a datareader and cannot
access the datareader once the connection is closed.

Here is what I do:

public function getDataReader() as datareader
oCmd.Connection.Open() ( oCmd - command object)
Dim oDr As SqlDataReader =
oCmd.ExecuteReader(CommandBehavior.CloseConnection )
' cant add a odr.close()
Return oDr
End Function

How do I close a datareader explicitly and access it outside the
function.

Thanks,
Chris.