try-catch every db connection?

Question

Is it recommended to put a try-catch block in every function that opens a DB connection and log the error there, or should I rather catch errors in a higher layer of the application?

public static Category GetCategoryByName(string name)
{
    Category result;
    try
    {
        using (IDbConnection conn = ConnectionHelper.CreateDbConnectionByName(_connectionStringName))
        {
            conn.Open();
            using (IDbCommand cmd = conn.CreateCommand())
            {
                //do stuff
            }
        }
    }
    catch(Exception e)
    {
         // log error here?
    }
    return result;
}

or rather

try
{
    Category myCat = DataTools.GetCategoryByName("myCat");
    // other stuff
}
catch(Exception e)
{
   // log error here?
}

To sum it up: Should errors be caught as early as possible in the code? Or should I rather catch them where I have more information about the context?

Answer 1

When catching exceptions, always try to use the most accurate exception you can. For example, when using SQL Server, catch the SqlException as it will contain far more information about the exceptin than a generic Exception. You can get actual line numbers and other useful pieces of diagnostic information.

After you have extracted and logged all that is relevent, rethrow the exception or wrap it in less specific exception such as an InvalidDataException or Exception and throw that. You can then catch these more generic exceptions at higher levels.

try
{
    // Execute DB call here
}
catch(SqlException exp)
{
    // Log what you need from here.
    throw new InvalidOperationException("Data could not be read", exp);
}

When you call this method from a higher level, you can just catch the InvalidOperationException. If the higher levels do need more detail, the InnerException will provide the SqlException which can be accessed.

The general approach to exception handling that I follow is to only catch what I can usefully act upon. There no point in catching really general Exception at lower levels of the code since you can really expect everything to go wrong or to be able to recover from every exception e.g. OutOfMemoryException or StackOverflowException.

Answer 2

As always, it depends, but in general, only catch an exception if you can do something about it, or you have specific code (e.g. a retry) to happen, otherwise, let the exception bubble up and the top most layer can log it/deal with it in a centralised fashion.

Any other way results in a lot of logging code interspersed with all the business logic.

Answer 3

I Usually only handle exceptions in the UI, everything below that I always throw it back to the top level. This way the stack trace has been maintained all the way though. You could always log and throw it.

I have used this before also:

try
{
   DB Command
}
catch (Exception ex)
{
   Log(ex)
   throw; //preserve stacktrace
}