Close connection in finalizer

[wakeup]

In a class of my asp.net application, I open connection in constructor and I want close connection in finalizer method but .close fails.

 

An unhandled exception of type 'System.InvalidOperationException' occurred in mscorlib.dll

Additional information: Handle is not initialized.

 

I have read this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclassclosetopic.asp

CAUTION Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, you should only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Programming for Garbage Collection.

 

 

How can I close this connection?

Thankssss

 

breakdance

[stustarz]

Just call the connections .close method after you have used the connection to retrieve your data. there is not really a need to leave it open for the life of a class if it isnt needed. I would also only open it when required rather than opening it in the constructor, unless of cause you are executing a datacommand in the constructor aswell.

 

I usually call .close directly after i have populated the controls that require the data.

[wakeup]

but open an close connection is most expensive in time in my application.

Do you know how i can finalize it automaticaly?

thankss

[stustarz]

What are you using to pull the data, a dataset or a datareader?

[michael_hk]

but open an close connection is most expensive in time in my application.

Do you know how i can finalize it automaticaly?

thankss

 

There is something call "Connection Pooling" that alleviates this problem, google to get more info.

[PlausiblyDamp]

If you must keep the connection open (although as michael_hk pointed out connection pooling may alleviate the performance issues) then relying on a finalizer is a Bad Thing™. By design the time till a finalizer being called is non-deterministic, about the only thing you can say for sure is that it will get called sometime between your application freeing up the reference to the variable and the application shutting down...

You are probably better of giving your class a close method (or similar) and requiring users of your class to call the methods when they have finished with the object.

Even better I would suggest you look at implementing the IDisposable interface in your class and do your clean up there.

Edited February 25, 2005 by PlausiblyDamp

[wakeup]

stustarz : I'm usin datareader de methods get only one value.

 

michael_hk: I have read access don't have "connection pools" :( and i'm using access

 

PlausiblyDamp: The programers can forget call the closing connection method. I want that it run automatic.

 

Thanks to everyone

[stustarz]

Well in that case when you call .executereader use this:

 

'Will close the connection once the data has been read into the datareader
'object
.ExecuteReader(CommandBehavior.CloseConnection)

[stustarz]

Or, if your not executing the reader and using executenonquery or executescalar (to retrieve the one value) then all you need to is call the connection.close() directly after the execution of the reader.

[PlausiblyDamp]

In that case tough. If a programmer cannot remember to close a resource after he has used it then he can expect problems; giving a Dispose / Close method provides the developer with a means to manage the resources if the desire to do so, but they can still rely on automatic clean-up some time in the future - their choice. Out of interest are you coding in VB or C#? If C# then it even has a keyword (using) to simplify using a Dispose methods.

 

Just out of completeness - the reason you cannot close a connection in a finalizer is because there is no guarenteed order in which finalizers will be executed. If your class is being finalized then the runtime may have already closed and finalized the connection anyway. As a developer you really should provide a manual way to close expensive resources (DB connections, file handles etc.)

Edited February 25, 2005 by PlausiblyDamp

[wakeup]

stustarz: I don't want open and close connection for each method, i want open in the class instance and close in the finalizer.

 

PlausiblyDamp: Thanks, I use c#, I will have a connecion close metod

[PlausiblyDamp]

If you are doing this from ASP.Net your objects are going to be created / destroyed every page refresh anyway (unless you are storing them in a session or similar); keeping a connection open for the duration may be counter productive.