0

I have an ASP.NET 1.1 website with fairly heavy traffic. I have a class in this website project that handles all my database access called DBWrapper:

Public Class dbWrapper
Implements IDisposable

' the private _connVSC2000 property is used for all methods that return scalar and DataType types
Private _connVSC2000 As SqlConnection
Private _connectionString As String
Private _disposed As Boolean = False

Sub New(ByVal connectionString As String)
    _connectionString = connectionString
    _connVSC2000 = New SqlConnection(connectionString)
    _connVSC2000.Open()
End Sub

Public Overloads Sub Dispose() Implements IDisposable.Dispose
    Dispose(True)
    GC.SuppressFinalize(Me)
End Sub

Protected Overridable Overloads Sub Dispose(ByVal disposing As Boolean)
    If Not _disposed Then
        'If _connVSC2000.State = ConnectionState.Open Then _connVSC2000.Close()
        If disposing Then
            _connVSC2000.Dispose()
        End If
    End If
    _disposed = True
End Sub

Protected Overrides Sub finalize()
    Dispose(False)
    MyBase.Finalize()
End Sub

' Actual database access functions removed as they are not relevant to the question.
end class

I also have a class (actually several) with a number of shared functions that uses a shared dbWrapper object:

Public Class SomeClass

Private Shared dbWrapper As New dbWrapper(ConfigurationSettings.AppSettings("VSCConnectionString"))

public shared Function SomeFunction()
    'function that utilizes the shared dbWrapper object.
end Function
End Class

The problem I am having is that after a while, the connection pool fills up because the connections are not being released in a timely manner. My questions are threefold:

Is the disposal of the SQLConnection object in the dbWrapper class implemented correctly?

What is the correct way to implement disposal of the dbWrapper object in the 'SomeClass' to ensure that the dbWrapper object, and thus the SQLConnection object get disposed of properly and don't clog up the connection Pool? Do I need to call SomeClass.Dispose explicitly when I am done with it, or will it be disposed automatically when it falls out of scope?

Will Russell
  • 324
  • 2
  • 11
  • Where is the code that is closing connection? – volody Oct 02 '12 at 13:39
  • According to the MSDN documentation for SqlConnection.Dispose, the Dispose method also calls SqlConnection.Close, so I should not need to call it separately. – Will Russell Oct 02 '12 at 17:21
  • Try removing Shared keyword and use Private dbWrapper. "If you declare a variable with this modifier, that variable will only be created once no matter how many instances of the class are created. If you access this variable from any instance, or change this variable from any instance, then the value will be the same across all instances". Now the problem will occur if 2 instances of the same class will execute sql command. – volody Oct 03 '12 at 10:33
  • Yes, I only want one instance of dbWrapper created for SomeClass, and I believe the dbWrapper object must be shared in order to be accessed within a shared Function, is that not correct? – Will Russell Oct 04 '12 at 15:33

0 Answers0