Complex applications are difficult too debug. You might have lots of components, some exposed
thru COM, some managed, some called from an ASP page, some used from a VB6 application that instanciates
a ServicedComponent.
And this complex applications will use databases. And one complex bug to track in applications
is when you have Connection Leaks.
Connection leaks are those situations where the application is not releasing connections to the ConnectionPool.
How can you detect the problems and their origins? One possibility could be use ADO.NET
and ODBC tracing (but that is for another post).
Now I will show an easier approach. Use extension methods!!!
What? you would say, what do extension methods have to do with this?
Well you can use an extension method to intercept all database connection. The way we do this is the following:
First create an extension method like:
public static class DBTrace
{
private static string LogDBTrace = @"C:\DBTrace.txt";
/// <summary>
///
/// </summary>
/// <param name="conn"></param>
public static void OpenWithTrace(this DbConnection conn)
{
conn.ConnectionString = NewConnectionstring(conn.ConnectionString);
#if DBTrace
File.AppendAllText(LogDBTrace,
"Opening connection [" + conn.ConnectionString + "] " +
new System.Diagnostics.StackTrace().ToString());
#endif
conn.Open();
}
/// <summary>
///
/// </summary>
/// <param name="factory"></param>
/// <returns></returns>
public static DbConnection CreateConnectionWithTrace(this DbProviderFactory factory)
{
#if DBTrace
File.AppendAllText(LogDBTrace, "Creating connection " +
new System.Diagnostics.StackTrace().ToString());
#endif
return factory.CreateConnection();
}
public static string NewConnectionstring(String connectionString)
{
#if ConnectionPoolOff
if (!connectionString.Contains("Pooling="))
{
if (connectionString.EndsWith(";"))
{
connectionString += "Pooling=false;";
}
else
{
connectionString += ";Pooling=false;";
}
}
return connectionString;
#else
return connectionString;
#endif
}
}
This will allow you to use compilation directives like: ConnectionPoolOff, and DBTrace which will
help you to turn on and off the database tracing and connection pooling in order to be able to detect connection leaks.
Second,
Look for any calls to Connection.Open() and change them to Connection.OpenWithTrace()
(A simple Find and Replace or regular expression should work).
This is a simple mechanism to implement database connection tracing.