connection object

T

Tamer_Ahmed

Centurion
Joined
Dec 20, 2003
Messages
159
Location
Egypt
hi i heard that if i didn't close the connection with connection.close
it's not a problem coz after closing the page all objects will be destroyed in the memory is it right or wrong
 
Hi

Have found that it was always necessary to close the connection with the database at the end of each segement of db code and as an added precaution, i also throw in a connection.close in the try catch statement prior to throwing the exception.

Hope this helps.

Mike55
 
You might want to consider implementing a disposed event for your class. The remarks in MSDN for the disposed event read:

Resources that require significant processor time, such as database connections, should be released with this event.
Click to expand...

So it'd seem that MS thinks you should explicitly close database connections from code.

In C# you might setup a dispose method for a page like this:

Code: 
//  Setup an event handler (in Page_Load or InitializeComponent).
this.Disposed += new EventHandler(classname_Disposed);

//  The event code:
private void pagename_Disposed(object sender, EventArgs e)
{
	//  Custom dispose code goes here...
	//  like closing class-level db connections.

	//  If the class inherits from another class (as asp.net pages do...)
	//  Call the base classes dispose method to let it do its thing too.
	base.Dispose();
}

Paul
 
You should be closing the connection as soon as possible-- not when the page is disposed, not when the GC feels like getting to it, not somewhere three methods up the stack. Close it in the method that it's opened, as soon as the data adapter(s) and data reader(s) are done with it. If you don't close and dispose, the connection won't be placed back in the connection pool, and you'll wind up with errors that look like this:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Click to expand...
 
Derek is right. I have one more thing to add. Close all your DataReader if you use some. Waiting before they are cleared will cause error like "Can't create a DataReader because there's already one ...... " (don't remember all the message).

The moral in all that is... as soon you are done using a connection or a DataReader or anything that can be closed... CLOSE IT !!! Don't wait the end of the world or that the GC wish to clear it.

Ever did some Unmanaged programming ? Hummm... the good ol days... We weren't asking "should I free the memory or not ?" ... we were simply doing it...

Damn... I'm only 20... I gotta stop before getting too old :p lol
 
If you're talking about an ASP.Net page where the page is going to "live" for an extremely brief time period of time I'd think it'd be exceptionally unlikely that you'd ever have timeout issues due to a lingering connection object.

I personally write tight database code (in extremely complicated ASP.Net web apps) and close/dismiss my connections as soon as I'm done with them but theoretically I don't see how it could really be "bad" to use a page-level connection object that's closed upon dispose when the life of the page class is going to be less than one second of real time. I personally think using a connection this way isn't the "proper" or ideal solution but I also really don't think it's that big a deal in this type of setting.

The question in this thread wasn't "tell me how to do everything perfectly" or "tell me how to write my code" - it was a simple question about the lifetime of connections and resources.

Paul
 
If you fail to explicitly close / dispose of the connection object then you have no control over when it will actually close, even though the page may only exist for less than one second any variables that require garbage collection may be around for a lot longer. If you fail to close DB connections yourself then these no longer needed but not yet garbage collected connections will still be in memory and still be holding on to a physical database connection.
 
Last edited:
PlausiblyDamp said:
If you fail to explicitly close / dispose of the connection object then you have no control over when it will actually close, even though the page may only exist for less than one second any variables that require garbage collection may be around for a lot longer. If you fail to close DB connections yourself then these no longer needed but not yet garbage collected connections will still be in memory and still be holding on to a physical database connection.
Click to expand...

Agreed. But, would it be so bad if you had a class-level connection object for whatever reason and closed in the dispose event (say, rather than having a specific load routine where the connection was created and destroyed in a more isolated setting)?

I'm not saying don't close the connection. I'm saying (in theory since this isn't how I do it) that in an ASP.Net page I don't think it'd be the end of the world if you closed a connection in the dispose event since the life of the app is minimal.

*shrug*

Paul
 
Back
Top