garbage collection

[Bijoy Naick]

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

 

[Kumar Reddi]

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

 

[Bijoy Naick]

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]

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

 

[Scott Allen]

In this case you are handing ownership of the SqlDataReader over to
the caller of the method. After all, only the caller knows when they
are done with the reader and are ready to close it.

There is no ExecuteReader method on SqlConnection in 1.1. Is this 2.0
or a custom library?

The best practice when returning a SqlDataReader from a method is to
use:

= command.ExecuteReader(CommandBehavior.CloseConnection)

The above ensures that the underlying database connection is closed
when the SqlDataReader is closed.

 

[Bijoy Naick]

hmm.. interesting..

so if i close the reader in the aspx page, the reader in the class will also
get closed?

 

[Kumar Reddi]

Yeah I noticed that Bijoy Naick was creating a datareader on the connection
object. I thought its a mistake while typing.

Hmm, so there is a ExecuteReader in .net 2.0 on connection object.
Interesting..

 

[Scott Allen]

Well, I checked and it doesn't appear to be in 2.0 either:
http://msdn2.microsoft.com/library/492zc4xe.aspx

Perhaps the OP had a cut and paste error.

 

[Bijoy Naick]

my code was "pseudocodish".. not real code

Well, I checked and it doesn't appear to be in 2.0 either:
http://msdn2.microsoft.com/library/492zc4xe.aspx

Perhaps the OP had a cut and paste error.