Database Exception Handling Best Practices

This article would highlight on how to deal with exceptions in our code and the end result would be better code.

1. Validate data prior passing it to DB at client and server.

2. Left validation to DB and handle DB exceptions properly

3. Validate on both sides

4. Validate some obvious constraints in business logic and left complex
validation to DB

5. Strong type checking and validation are powerful tools to prevent unexpected
exceptions. So be ready to always check types, objects value etc before
passing to database.

6. Don't trust external data -External data is not reliable. It must be extensively checked.

7. Generic exception handling should be done in a central point in your
application. Each thread needs a separate try/catch block, or you'll lose
exceptions and you'll have problems hard to understand. When an application
starts several threads to do some background processing, often you create a
class for storing processing results. Don't forget to add a field for storing an
exception that could happen or you won't be able to communicate it to the
main thread. In "fire and forget" situations, you probably will need to duplicate the main application exception handler on the thread handler.

8. Log Exception.ToString(); never log only Exception.Message!

9. Cleanup code should be put in finally blocks

10. Don't use exception handling as means of returning information from a
method
11. Don't clear the stack trace when re-throwing an exception.

12. When in doubt, don't Assert, throw an Exception Don't forget that Debug.Assert is removed from release code. When checking and doing validation, it's often better to throw an Exception than to put an assertion in your code.
Save assertions for unit tests, for internal loop invariants, and for checks that should never fail due to runtime conditions (a very rare situation, if you think about it).

13. Use "using" everywhere -

Simply calling Dispose () on an object is not enough. The "using" keyword will
prevent resource leaks even on the presence of an exception.


14. Avoiding exception handling inside loops

Check if your code uses exceptions inside loops. This should be avoided. If you
need to catch an exception, place the try/catch block outside the loop for better performance.