How to catch sql exceptions gracefully when deleting some records

I use the following function (in the BLL) to delete some records:

PublicFunction DeleteStep4Dashboards()AsBoolean

Try

adpDashboards.DeleteStep4Dashboards()

Catch exAs Exception

ReturnFalse

EndTry

ReturnTrue

EndFunction

How can I catch the sql database errors when deleting the records goes wrong.

You can add a Catch for a SqlException e.g.

Catch sqlExAs SqlException

|||

You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).

However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.

Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.

|||

Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)

|||

Is it really best practise to bubble up the error.

Can't I show an general error message to the user.

And handle the error (logging, e-mail to administrator) in the BLL?

|||

JohanNL:

Is it really best practise to bubble up the error.

Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.

The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>