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:
- 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.
- 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.
- 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. Â
What if the database gets disconnected just after you check everything is ok?
Also, if the business layer and the data layer dont run in the same computer, there will exist situations in which you can reach the database from the business layer and not from the data layer.
I would go for 1, but I know you maybe have more reasons for deciding for 3. By the way, I would go deeper, why the database are working so badly? I think the 2 things should be solved. The application but the database problem too.
Just a guy: You make some excellent points–my decision was based mostly on ease of implementation and lack of needing to change the existing code (for now) to prevent the immediate problems.
Regarding 1–I am actually rearchitecting the database access code from the ground up for a number of applications (including this one) and this is indeed the tack I am taking–I made a catch-all DatabaseException that wraps any inner exceptions from that layer. Anytime the application makes a database call, it must catch that exception. This is a more drastic change to the application that is being taken more slowly–which is why I went with #3 for the time-being. The priority is always preventing data loss to the customer. Ideally, the application will soon be completely different and use both types of error-handling.
And your point about the root-cause is important too. In our case I think that the root cause has been solved, but we need multiple layers of protection–just in case.