So, when do I actually need to catch exceptions? In essence, there are three distinct scenarios. First is called handling. It's when I know what kind of exception to expect and - more importantly - how to recover from it. For example, my stored procedure may become a victim of a SQL Server deadlock. In the managed code, this will result in a SqlException which I should handle (retry transaction up to the pre-defined number of times). Another example is trying to read some configuration data from a file:
configData = File.ReadAllText(configFilePath);
configData = DefaultConfigData;
As you can see, I am handling FileNotFoundException by force-feeding some default configuration data into the variable. It's important to emphasize that I didn't attempt to handle any other kind of exception that File.ReadAllText can throw. For instance, it may throw UnauthorizedAccessException or SecurityException and I'd rather have these bubble to the top and hopefully force program termination.
This brings us to the second scenario: unhandled exceptions. If the exception hasn't been handled anywhere in the call stack (which either means there is an unknown problem or a problem that I don't know how to recover from) it should be caught and properly logged. Windows applications should display a generic error message to the user and shut down, Web applications should redirect user to a generic error page, and services can either shut down or terminate failed thread.
Third scenario is called exception wrapping. The idea is to substitute a low-level exception object with higher-level exception class containing additional information (if you are absolutely positive that original error is not sufficient). Wrapping is different from handling because there is no recovery - a new exception is thrown. In the example below, I am replacing SqlException with ScriptException that adds stored procedure name in an effort to facilitate debugging:
ScriptException e = new ScriptException(storedProcName, ex);
Wrapping should be used with caution because it changes the call stack and makes debugging more difficult. It is imperative to assign original exception object to the InnerException property of the new exception (in the above example this is done using a constructor overload).
An interesting implication is that in order to handle exceptions I need to know what exceptions a method can throw in the first place. List of exceptions should really be part of the method signature. In fact, Java has the concept of checked exceptions and corresponding "throws" syntax while in .NET we need to rely on class documentation. If you are interested in comparative analysis of the two approaches, read this interview with Anders Hejlsberg, creator of C#.