Trap "connection pool" errors [crosspost from .asp]

[no potted meat@hotmail.com David Browne]

"Sean Nolan" <sno...@harriton.com> wrote in message
news:Oa7doR7R...@tk2msftngp13.phx.gbl...
> We have implemented unhandled error trapping at the application level and
> log these errors to our database. One error, however, the does not get
> trapped is when the connection pool has exceeded the max number of
> connections.
>
> Obviously, we need to find the place(s) in our code where connections are
> not closed correctly (espcially in loops), but I'm wondering if it's
> possible to trap this error and to find out which part of our code (i.e.
> stack trace) caused it.

It it possible. The general idea is to have a object that will be Garbage
Collected in the same pass as your connection. Whenever a connection is
opened, store the stack trace of the opening method. And put a finalizer on
that object, and write out a trace entry if the finalizer runs and the
connection is still open.

One way to do this is to have a "wrapper object" for your connection.
But then your app code has to create the wrapper instead of the connection.
I think this is a good thing, since you can implement all the DAAB methods
as instance methods of your wrapper object. But that's another story.

Assuming you are using SQLServer (or some other connection that has a
StateChanged event), there may be an easier way.

Without a wrapper object, to get an object which will be finalized at the
same time as the connection we can use a "spy" object.

If we have a "spy" object which handles the StateChaned event of the
SQLConnection, and we give the spy object a reference to the connection we
will have what we want. If 2 objects mutually refer to each other, then
they will always be GC'd at the same time. The spy refers to the
SQLConnection and since the spy handles an event on teh SQLConnection, the
SQLConnection's delegate list contains a reference to the spy object. Voila!

There follows sample program to do this.

David

Imports System.Data.SqlClient

Class ConnectionFactory
Private Class ConnectionSpy
Private con As SqlConnection
Dim st As StackTrace
Public Sub New(ByVal con As SqlConnection, ByVal st As StackTrace)
Me.st = st

'latch on to the connection
Me.con = con
AddHandler con.StateChange, AddressOf StateChange
End Sub
Public Sub StateChange(ByVal sender As Object, ByVal args As
System.Data.StateChangeEventArgs)
If args.CurrentState = ConnectionState.Closed Then
'detach the spy object and let it float away into space
GC.SuppressFinalize(Me)
RemoveHandler con.StateChange, AddressOf StateChange
con = Nothing
st = Nothing
End If
End Sub
Protected Overrides Sub Finalize()
'if we got here then the connection was not closed.
Trace.WriteLine("WARNING: Open SQLConnection is being Garbage
Collected")
Trace.WriteLine("The connection was initially opened " & st.ToString)
End Sub
End Class

Public Shared Function OpenConnection(ByVal connect As String) As
SqlConnection
Dim con As New SqlConnection(connect)
con.Open()
Dim st As New StackTrace(True)
Dim sl As New ConnectionSpy(con, st)

Return con
End Function

End Class

Module Module1

Sub Main()
'pipe trace output to the console
'in your app this would go to a trace file
System.Diagnostics.Trace.Listeners.Add(New
TextWriterTraceListener(System.Console.Out))
Dim connect As String = "..."

Dim c As SqlConnection = ConnectionFactory.OpenConnection(connect)
c = Nothing '!!the connection was not closed

c = ConnectionFactory.OpenConnection(connect)
c.Close() 'this time it was closed
c = Nothing

GC.Collect(GC.MaxGeneration)
GC.WaitForPendingFinalizers()
'output will show 1 warning

End Sub

End Module