Detecting a DB connection error
As part of our unit tests, we restore an empty database when we run tests. The units under test then execute their tests by invoking web services (hosted on the Visual Studio ASP.NET host).
This works great for us the first time the unit tests are run, however if they restart without restarting the web services, an exception is thrown since all connections were reset as part of the recovery.
The code below simulates what is happening:
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 code, except for KillAllProcesses, runs in the web service process, and KillAllProcess runs in the Unit Test process. The same could be done by restarting the SQL server.
The problem we are facing is that the web services don't know when a connection is killed and just picks the "bad" connection from the connection pool. In addition, creating a connection and executing a command in multiple applications is split into multiple layers.
How can we find out that the connection is "bad" before executing the command without significantly impacting the performance of the application?
source to share
After killing SQL Server, the connection pool will contain stale connections to the old SQL Server instance.
You can call SqlConnection.ClearAllPools () to clear stale connections from the pool after restarting SQL Server, for example:
static void Main(string[] args)
{
DoDBStuff();
new Server("localhost").KillAllProcesses("Test");
SqlConnection.ClearAllPools();
DoDBStuff();
}
NB The ClearAllPools method was introduced in .NET 2.0
source to share
For unit testing purposes, can you configure the connection pool timeout? So the connections get stale much faster and they expire during the check / compile process, and would they all be cleaned up by the time your actual unit tests run?
Or, alternatively, you could write a unit test that gets executed first and calls a web service that cleans up your existing connection pool? This would force any new data requests to generate a new DB connection that would not throw an exception. According to MSDN:
ADO.NET 2.0 introduces two new methods for clearing a pool: ClearAllPools and ClearPool. ClearAllPools clears the connection pools for the given provider, and ClearPool clears the connection pool associated with the specific connection. If there are connections in use during the call, they are marked accordingly. When they are closed, they are discarded rather than the pool returned.
source to share