Detecting that a DB Connection is in error

Question

As part of our unit tests, we restore a blank database when the tests start . The unit tests then perform their tests by calling web services (hosted in the Visual Studio ASP.NET host).

This works fine for us the first time the unit tests are run, however if they are re-run without restarting the web services, an exception is raised as all the connections have been reset as part of the restore.

The code below simulates what occurs:

static void Main(string[] args)
{
    DoDBStuff();
    new Server("localhost").KillAllProcesses("Test");
    DoDBStuff();
}

private static void DoDBStuff()
{
    string constr = "Data Source=localhost;Initial Catalog=Test;Trusted_Connection=Yes";
    using (SqlConnection con = new SqlConnection(constr))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("SELECT TOP 1 id FROM sysobjects", con))
        {
            int? i = cmd.ExecuteScalar() as int?;
        }
    }
}

All the code, except for the KillAllProcesses runs in the web service's process, while the KillAllProcess runs in the Unit Test process. The same could be achieved by restarting the SQL server.

The problem we face is the webservices doesn't know when the connection is killed, and just picks a "bad" connection from the connection pool. Further, the creation of a connection and the execution of the command are several layers apart within the app.

How can we detect that a connection is "bad" before executing a command, without drastically affecting the performance of the application?

Answer 1

After killing SQL Server, your connection pool will contain stale connections to the old instance of SQL Server.

You can call SqlConnection.ClearAllPools() to clear the stale connections from the pool after restarting SQL Server, e.g.:

static void Main(string[] args)
{    
    DoDBStuff();    
    new Server("localhost").KillAllProcesses("Test");
    SqlConnection.ClearAllPools();
    DoDBStuff();
}

NB The ClearAllPools method was introduced in .NET 2.0

Answer 2

For unit testing purposes, can you adjust the connection pool timeout? That way, connections become stale much quicker, and they'd expire during your checkout/compile process, and would have all been cleaned out by the time your actual unit tests run?

Or, as another option, could you write a unit test that's executed first and calls a webservice that flushes your existing connection pool? It would force any new data requests to generate a new DB connection, which wouldn't cause an exception. According to MSDN:

ADO.NET 2.0 introduces two new methods to clear the pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for a given provider, and ClearPool clears the connection pool that is associated with a specific connection. If there are connections in use at the time of the call, they are marked appropriately. When they are closed, they are discarded instead of being returned to the pool.