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.ExecuteRea der(CommandBeha vior.CloseConne ction)
' 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]

Re: connection close problem

Hi Sam:
"sam" <sri_san@mailci ty.com> wrote in message
news:5ca7db4a.0 405190746.53ba0 aba@posting.goo gle.com...[color=blue]
> 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.ExecuteRea der(CommandBeha vior.CloseConne ction)
> ' cant add a odr.close()
> Return oDr
> End Function[/color]

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[color=blue]
>
> How do I close a datareader explicitly and access it outside the
> function.
>
> Thanks,
> Chris.[/color]


W.G. Ryan, eMVP

http://forums.devbuzz.com/

http://www.knowdotnet.com/williamryan.html

[Marina]

Re: connection close problem

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 .CloseConnectio n.

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" <sri_san@mailci ty.com> wrote in message
news:5ca7db4a.0 405190746.53ba0 aba@posting.goo gle.com...[color=blue]
> 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.ExecuteRea der(CommandBeha vior.CloseConne ction)
> ' cant add a odr.close()
> Return oDr
> End Function
>
> How do I close a datareader explicitly and access it outside the
> function.
>
> Thanks,
> Chris.[/color]

[chris]

Re: connection close problem

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" <someone@nospam .com> wrote in message
news:e8$sGbdPEH A.3052@TK2MSFTN GP09.phx.gbl...[color=blue]
> 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 .CloseConnectio n.
>
> If you don't close the reader, the connection will remain open until the[/color]
GC[color=blue]
> cleans it up, which may be a while, and more then likely you will run out[/color]
of[color=blue]
> connections in your connection pool.
>
> "sam" <sri_san@mailci ty.com> wrote in message
> news:5ca7db4a.0 405190746.53ba0 aba@posting.goo gle.com...[color=green]
> > 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.ExecuteRea der(CommandBeha vior.CloseConne ction)
> > ' cant add a odr.close()
> > Return oDr
> > End Function
> >
> > How do I close a datareader explicitly and access it outside the
> > function.
> >
> > Thanks,
> > Chris.[/color]
>
>[/color]