Automatic Dispose

[jhgonzales]

I've created a class where the New constructor opens a database connection. I
can't figure out the necessary code to "automatically" close and dispose of
the database connection when the class I've created goes out of
scope...without needing to call a method to specifically to do that. I would
like to ensure that the database connection is closed when the class is used
and no explicit close is called by the caller.

 

[Herfried K. Wagner [MVP]]

I've created a class where the New constructor opens a database
connection. I
can't figure out the necessary code to "automatically" close and dispose
of
the database connection when the class I've created goes out of
scope...without needing to call a method to specifically to do that. I
would
like to ensure that the database connection is closed when the class is
used
and no explicit close is called by the caller.

Your class could implement 'IDisposable' and the finalizer can be used to
dispose the connection when getting finalized. However, there is no way to
automatically finalize an object if the only variable referencing it goes
out of scope.

 

[jhgonzales]

Thank you for the reply. Can you show me a sample of how I would do that? I
used this article as a sample
http://msdn.microsoft.com/en-us/library/s9bwddyx(VS.80).aspx
but i keep getting .net provider error when sqlconn.close is called. Where
does the call the close belong?

Public Class ResourceClass
Implements IDisposable

Private sqlConn as SQLConnection
Private managedResource As System.ComponentModel.Component
Private unmanagedResource As IntPtr
Protected disposed As Boolean = False

Public Sub New()
' Insert appropriate constructor code here.
sqlConn = new SqlConnection(....)
sqlConn.open()
End Sub

Protected Overridable Overloads Sub Dispose( _
ByVal disposing As Boolean)
If Not Me.disposed Then
If disposing Then

End If
' Add code here to release the unmanaged resource.
if sqlConn isnot nothing andalso
sqlConn.state=sqlconnection.open then
sqlConn.close
end if
' Note that this is not thread safe.
End If
Me.disposed = True
End Sub

Public Sub AnyOtherMethods()
If Me.disposed Then
Throw New ObjectDisposedException(Me.GetType().ToString, _
"This object has been disposed.")
End If
End Sub

#Region " IDisposable Support "
' Do not change or add Overridable to these methods.
' Put cleanup code in Dispose(ByVal disposing As Boolean).
Public Overloads Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
Protected Overrides Sub Finalize()
Dispose(False)
MyBase.Finalize()
End Sub
#End Region
End Class

 

[jhgonzales]

sorry, this line of code below
......
sqlConn.state=sqlconnection.open
......


is actually
.............
sqlConn.state=ConnectionState.open
............

 

[Alex Clark]

Hi,

You may want to reconsider your approach. Although, as Herfreid points out
in this thread, you can implement IDisposable, IMO you'd be better off
creating a connection only for the lifetime of any operations that need it -
rather than creating a connection in your constructor and holding it open
for the lifetime of your object, instead create & open immediately before
doing database work and close it immediately afterwards. This is
recommended practice as it frees up server resources and is generally a bit
more memory friendly.

HTH,
Alex

 

[Scott M.]

But, a SqlConnection itselft is a managed resource and, as such, should not
be cleaned up directly in the finalizer. It shouldn't even be referenced by
the Finalize method because we have no idea when or if that method will
execute and so, we have no certainty that the managed resource that is
referenced is still even on the heap. It should be cleaned up with the
class instance user is done with it and calls Dispose (or when the class
instance user uses "Using" and Dispose is called automatically). Also,
there's no need to check the state of a connection in .NET in order to close
it. If you call close() and it's already closed, nothing will happen,
unlike in VB 6, where you'd get an error if you tried to close an already
closed connection.

Public Class ResourceUser

#Region "IDisposable Implementation"
' Iplementing IDisposable announces to users of your class that the type
uses scarce resources
' and should be disposed when they are done using it.
Implements IDisposable

' This will let the instance internally track whether Dispose has been
called yet.
Private disposed As Boolean = False

Public Overridable Sub Dispose() Implements IDisposable.Dispose
' If a derived class overrides this method, it should ALWAYS call
MyBase.Dispose()in its Dispose method.
' We don't actually do the clean up here. This method is one of two ways
we can get to
' the REAL clean-up code. Think of this Dispose as a proxy to the real
one.
Dispose(True)

' This object will be cleaned up by the Dispose method we just returned
from. Therefore, we should
' prevent the finalization code for this object from executing. This
also will help the object be
' collected a generation sooner than if the finalizer were to execute.
GC.SuppressFinalize(Me)
End Sub

' This is the other proxy to Dispose().
' This will run only if the Dispose method does not get called (remember
GC.SurpressFinalize(Me)).
Protected Overrides Sub Finalize()
Dispose(False)
MyBase.Finalize()
End Sub

' The followig is where the actual clean-up occurs and can be gotten to by
two distinct scenarios.
' 1. The user of an instance of this class calls Dispose().
' 2. No one calls dispose, so the GC calls Finalize just before it
collects (Finalize calls this method).
Private Sub Dispose(ByVal disposing As Boolean)
' We have a way to know how we got here (disposing), but what if the
user calls Dispose twice?
If Not Me.disposed Then
' Ok, we haven't disposed this object yet, so it needs to do clean up.
If disposing Then
' This method has been called by a user's code. It's safe to dispose
managed resources.
con.Dispose()
End If
' If disposing = False, this method was called by the CLR from inside
the finalizer and therefor we
' should not attempt to reference managed objects because the GC may
have already collected them.

End If

'Flag that we've disposed what could/should be cleaned up.
disposed = True
End Sub
#End Region

Private con As SqlClient.SqlConnection ' managed resource this class
uses.

Public Sub New() ' Let's work those resources!
' Configure our connection...
con = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;Initial
Catalog=Northwind;Integrated Security=True;")
con.Open() 'Use the connection
End Sub
End Class

 

[jhgonzales]

Thank you, this reaffirms what I was originally thinking.

 

[Brian Gideon]

Hi,

You may want to reconsider your approach.  Although, as Herfreid pointsout
in this thread, you can implement IDisposable, IMO you'd be better off
creating a connection only for the lifetime of any operations that need it -
rather than creating a connection in your constructor and holding it open
for the lifetime of your object, instead create & open immediately before
doing database work and close it immediately afterwards.  This is
recommended practice as it frees up server resources and is generally a bit
more memory friendly.

HTH,
Alex

Yeah, plus connection pooling may be keeping the connection alive
anyway so it's not like the database would necessarily be seeing
repeated connection attempts even though that's how it appears in
code.