Exception closing a database connection in destructor

[Leonardo Nu]

Please, can somebody help?

I have a class with a connection and a transaction.

I want to make sure that my connection will be closed by
the end of the application. I tried to close the
connection in the destructor but it throws the following
exception:

Unhandled Exception: System.InvalidOperationException:
Handle is not initialized
at System.WeakReference.get_Target()
at System.Data.Common.WeakReferenceCollection.Close
(Boolean flag)
at System.Data.OleDb.OleDbConnection.CloseReferences
(Boolean canceling)
at System.Data.OleDb.OleDbConnection.DisposeManaged()
at System.Data.OleDb.OleDbConnection.Close()
at testeOleDb.sql.FtDbTransaction.Finalize()

When i do not close my connection or end the transaction,
the table stay locked and i can't start another
transaction.

I'm using an Oracle DB but i had the same problem with
SqlServer.

[Angel Saenz-Badillos[MS]]

Leonardo,
This is a very common error, in fact it is so common that I added a fairly
large CAUTION paragraph to the docs for SqlConnection Close
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.
This is specially bad for the SqlConnection, but make sure that you do not
touch any managed objects in your destructor. Use destructors only for
releasing native resources.

Yes I know that it is confusing, but you really cannot use the destructors
that way.
Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Leonardo Nu" <msdnv...@bol.com.br> wrote in message
news:0ebd01c32c2f$81fd44d0$a301...@phx.gbl...

[Leonardo N.]

Angel,

Thanks for answer, but I still have some doubts.
How can I make sure that my transaction will be closed by
the end of the application? I'm using a WebApplication and
if the transaction isn't commited or if the connection
isn't closed, the table stay locked and i can't access it
again.

Thanks

Leonardo.

>.
>

[Angel Saenz-Badillos[MS]]

The correct way to make sure that the connection is being closed properly is
with the Try finally construct, or in c# the "using" construct.

So it would look something like:
using(SqlConnection connection = new SqlConnection(connectionstring)) {
//do your work here
}

or
SqlConnection connection;
try{
connection = new SqlConnection(connectionstring);
//do your work here
}
finally{
connection.Dispose(); //or connection.Close();
}

These two structs are equivalent and the finally guarantees the closing of
your connection.

One important thing to remember is pooling, if you start a transaction
through our api and you close the connection without calling commit or
rollback we will rollback the transaction inmediatelly with the SqlClient.
If you start a transaction manually with a "Begin Transaction" SQL call you
have to remember to call Commit Transaction or Rollback Transaction before
closing the connection, otherwise you will lock the transaction for as long
as that connection remains unused on the pool.
Hope this helped

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Leonardo N." <msdnv...@bol.com.br> wrote in message
news:04c001c32c65$f4a578f0$a101...@phx.gbl...

[Leonardo N.]

Thank you Angel.
I think my problem was solved with the using construct.

Thanks
Leonardo.

>.
>