connection object

[Tamer_Ahmed]

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

[mike55]

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

[PWNettle]

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.

 

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:

 

//  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

[Derek Stone]

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.

[Arch4ngel]

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

[PWNettle]

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

[PlausiblyDamp]

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. Edited March 30, 2007 by PlausiblyDamp

[PWNettle]

If you fail to explicitly close / dispose of the connection object then you have no control over when it will actually close' date=' 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.[/quote']

 

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

[Derek Stone]

You're assuming that the class will be disposed when the page finishes executing. That's not how the garbage collector in .NET works.