Introduction
If you ever encountered the following exception, here is one approach to find out which connections you forgot to close.
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.
Using the code
To get the necessary information, you will have to keep track of every connection you are creating. To do this, I have implemented the following classes:
Public Class ManagedConnection
Public CreatingMethod As String
Private _realConnection As SqlConnection
Public Property RealConnection() As SqlConnection
Get
Return _realConnection
End Get
Set(ByVal Value As SqlConnection)
_realConnection = Value
End Set
End Property
Public Sub New(ByVal pConnectionString As String)
Me.CreatingMethod = Environment.StackTrace
_realConnection = New SqlConnection(pConnectionString)
End Sub
End Class
ManagedConnection
stores the StackTrace
of every location where you are creating a new connection.
The second class keeps track of all created ManagedConnection
s:
Public Class ManagedConnectionPool
Dim _pool As New ArrayList
Public Function CreateConnection(ByVal pConnectionString) As SqlConnection
Dim curConn As New ManagedConnection(pConnectionString)
_pool.Add(curConn)
Return curConn.RealConnection
End Function
Public Sub CheckAndCleanPool()
Dim curConn As ManagedConnection
Dim problem As Boolean = False
Dim message As String
For Each curConn In _pool
If curConn.RealConnection.State <> ConnectionState.Closed Then
curConn.RealConnection.Close()
message &= curConn.CreatingMethod & Environment.NewLine & Environment.NewLine
problem = True
End If
Next
_pool.Clear()
If problem Then
Dim errorMsg As String = "<pre style='" & _
"margin-top: 8pt; margin-bottom: 8pt;background-color: #ffffee;" & _
"white-space:pre;border-style:solid;border-width:1px;border-color:#999999;" & _
"color:#333333;padding:10px;width:100%;'>Unclosed connection" & _
Environment.NewLine & Environment.NewLine & message & "</div>"
HttpContext.Current.Response.Write(errorMsg)
End If
End Sub
End Class
If you're using the ManagedConnectionPool
every time you need a connection, the class will store all the created connections. Whenever you are finished and think you didn't leave any connections open, you can use the CheckAndCleanPool
method to test whether there are still any connections left open.
To automate the whole thing, I have created a base class that you can use for all your web pages. This base class automatically creates a ManagedConnectionPool
whose connections are tested as soon as the page is no longer needed.
Public Class BasePage
Inherits System.Web.UI.Page
Protected _managedConnectionPool As New ManagedConnectionPool
Public Overrides Sub Dispose()
_managedConnectionPool.CheckAndCleanPool()
MyBase.Dispose()
End Sub
End Class
To use the whole thing, here is what you have got to do:
- Include the ManagedConnectionPool.vb file in your ASP.NET web project.
- Make sure that your web pages inherit from
BasePage
rather than from System.Web.UI.Page
. - Whenever you need a connection, use the
_managedConnectionPool.CreateConnection( xxx)
method instead of calling New SqlConnection
.
If you actually leave a connection open, the ManagedConnectionPool
will add an error message at the end of the original page output. This message will consist of the StackTrace
that leads you to the point where you created the unclosed connection.
History
- March 12, 2006 - Initial release.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.