Re: garbage collection

From: Kumar Reddi (KumarReddi_at_REMOVETHIS.gmail.com)
Date: 12/14/04 

Date: Tue, 14 Dec 2004 15:27:25 -0500

You can not return reader after the connection is closed. Yes, Close()
method does close all the readers associated with the connections. Though
microsoft documentation does not say anything about it.

But to return a dataReader you need to keep the connection open. So, you
might be wondering how you are going to close the connection in the another
method. For this, you should create the dataReader using

ExecuteReader(CommandBehavior.CloseConnection);

so your reader should be created as
sql.ExecuteReader(CommandBehavior.CloseConnection);
 Creating reader this way, would close the database connection associated
with the reader, when the reader itself is closed.

So, in your calling function, simply close the reader after you are done
with it. That would close the connection as well 

-- 
Kumar Reddi
http://kumarreddi.blogspot.com
"Bijoy Naick" <b_naick@yahoo.ca> wrote in message
news:uw1Gmmh4EHA.3708@TK2MSFTNGP14.phx.gbl...
> Understood.. so what happens in the following case..
>
> Assume that I have a custom class - call it Events. One of the methods of
> this class is GetEvents defined/implemented as follows:
>
> public function GetEvents(startDate, endDate) as sqlDataReader
>     Dim myReader As SqlDataReader
>     Dim oConn As SqlConnection
>
>     oConn.Open()
>     sql = "select * from events in date range"
>     myReader = oConn.ExecuteReader(sql)
>     oConn.Close()
>     return myReader
> end function
>
> If an aspx page creates an instance of the Events class and calls the
> GetEvents method, it will get a reader back.. How can I close the reader
> defined in the GetEvents method?
>
> BTW: I took a look at the code in the Application Block provided by Msft
> called SqlHelper. Their code doesnt close the reader either..
>
> Bijoy
>
> "Kumar Reddi" <KumarReddi@REMOVETHIS.gmail.com> wrote in message
> news:ORFESfh4EHA.4028@TK2MSFTNGP15.phx.gbl...
> >
> > First of all, with datareaders, if you do not close the reader after the
> > usage, you can not reuse the connection on which it opened. This is not
a
> > garbage collection issue, its a logical error. Garbage collection cleans
> the
> > resources for you, by doing automatic memory deallocation, but it doesnt
> > close the datareader for you. Thats your job. Also, If your application
> > doesnt allow database connection pooling, you need to dispose your
> database
> > connection. But, your application seems to be using the connection
> pooling,
> > so you do not have to worry. By the way, the reason you need to call
> dispose
> > on database connection is, it is unmanaged object. GC can not clean the
> > unmanaged resources for you
> > -- 
> > Kumar Reddi
> > http://kumarreddi.blogspot.com
> >
> > "Bijoy Naick" <b_naick@yahoo.ca> wrote in message
> > news:uTRytYh4EHA.1564@TK2MSFTNGP09.phx.gbl...
> > > Just wondering if anyone has experienced any issues with garbage
> > collection
> > > in .net. We developed an application using VB .NET; many of the pages
> made
> > > db calls.
> > >
> > > One of the developers forgot to close the connection(s) and DataReader
> > > objects. We found that once the site recieved a large number of hits,
> the
> > > server died on us and memory usage was ridiculously high. Since that,
> > we've
> > > closed all the connections and datareaders and the app works great..
> > >
> > > Should the .NET GC not have managed all of this for us?
> > >
> > > BTW: I know its good coding practice to close everything you open..
but
> > just
> > > wondering IF GC does this or not..
> > >
> > > Bijoy
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: DataView vs DataReader
    ... This is because the reader maintains an open ... connection to the database - a valuable resource. ... Now, a dataview, has nothing to do with database access itself. ... I definitely recommend you go ahead and eliminate the datareader. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Need More ADO Suggestions
    ... You would capture the data you need and store it and return that to your calling code, not the DataReader. ... You are correct that the reader must stay open as long as you are iterating through the data, so you want to get in and get out as quick as you can. ... the opening and closing of your connection (so you won't have to worry about using "using" on the connection obejct or loop through your DataReader data as soon as you get it and copy it over to a DataTable. ... I understand the benefits of automatically disposed objects, but I find the lack of deterministic finalization a step away from encapsulation, the main point of OOP. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Connections not closing and display slow.
    ... But when I run a stored procedure from my dropdown, ... You must close your DataReader (which you can't do with your code ... will cause the connection to close. ... CommandBehavior.CloseConnection but it does close the reader. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: garbage collection
    ... it will get a reader back.. ... you can not reuse the connection on which it opened. ... > garbage collection issue, its a logical error. ... > on database connection is, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: connection close problem
    ... > cant really do that as the function returns a datareader and cannot ... > access the datareader once the connection is closed. ... Reader is a 'connected' object and as such, ... Query the Reader is based on. ...
    (microsoft.public.dotnet.general)
Quantcast