Catch SqlException at the controller level?

Question

In my WebApi 2 project I follow service pattern as well as unit of work and generic repository patterns.

All controllers have try-catch, and just in case an action doesn't I have a global IAutofacExceptionFilter will catches unhandled exceptions.

The service layer also has exception handling. However, the repository don't have exception handling so that the exception bubble up to this first try-catch they encounter.

I also have a custom DatabaseException class that captures EF's database exceptions thrown on SaveChanges and this works fine.

However, as the project is currently under heavy development with database changes occurring at the same same as code changes, exceptions such SQLException or EntityCommandExecutionException are quite common. I would like to catch these kind of exceptions and throw my own DatabaseException.

Should I catch these exceptions at the repository level, the service level or the controller level?

Answer 1

To directly answer your question - I would catch exceptions at the service level, log them, and then re-throw them.

• Retrieving data from the database is a function of the service, so if that's failing, it's the service that's failing.
• The application doesn't "know" about the database. It doesn't get data from the database. It gets data from the service. It shouldn't depend on implementation details in the service like whether the data comes from SQL or somewhere else.

Now that the exception is rethrown, the application can also handle it, even logging it again - this time in the context of what controller action was called. But if, for example, the user is trying to save some data and it fails, it probably doesn't matter too much in that context why it failed. So it probably isn't that helpful to catch specific exception types. (Probably.)

Catching specific types of exceptions is only really helpful if the application is going to take some different action to handle or resolve the exception, which is uncommon. Being able to easily get to your logs and find an exception, any exception, will be way more valuable than converting a system exception to a custom exception.