Daily Archives: April 19, 2006

Detecting database connection

I have a service that’s absolutely critical to business functions that relies on both Exchange and SQL Server. The problem is that the service’s existing code has a number of places where a failure to connect to the database will result in a dummy, “bad” value being returned from the data access layer. The business layer doesn’t know if the bad values came from bad input data or no database connection, and it assumes the former. This leads to data loss for the customer.

I see three possible solutions for this:

  1. Have an exception for when the database is inaccessible. This is complicated because it changes the code flow, which would not be easy. Every place it tries to access the database would have to handle the exception. I would also have to develop a mechanism to reprocess the data later.
  2. Have a known “bad” value that means database couldn’t be accessed. This is tricky (if not impossible) and would significantly decrease the readability of the code.
  3. Detect whether the database is alive or not before attempting any processing. This avoids the problem of reattempting processing at a later time. It allows more of the code to stay as is (thus fewer chances of new bugs).

I went with 3 because it is also conceptually closer to what should happen. If the database is unavailable, the service can’t possibly do any meaningful work so it should just stop until the database comes back up.

The code to detect a database disconnect is very simple:

static bool IsDatabaseAlive()
{
    SqlConnection connection = null;
    try
    {
        
string strConnection = ConfigurationSettings.AppSettings["ConnectionString"];
        
using(connection = new SqlConnection(strConnection))
         {
             
connection.Open();
             
return (connection.State != ConnectionState.Open);
          }
     }
    
catch
    
{
         
return false;
     }
}

 

I run this code before running through the processing loops. If the database is alive I go ahead and process.  


Check out my latest book, the essential, in-depth guide to performance for all .NET developers:

Writing High-Performance.NET Code by Ben Watson. Available now as an eBook at: