Recommended approach on handling SqlExceptions in db applications

Question

I work on a database application written in C# with sql server as backend. And for data integrity, I try to enforce as much as possible on database level - relations, check constraints, triggers.

Due to them, if data is not consistent, the save / update / insert can fail, and app throw SqlException.

I do various validations both in UI (to present user with meaningful info if data entered is not valid), also in BL, which reports errors back to UI which presents it to user.

However, there are things that really cannot be checked in the app, and are handled by the db: I mean errors on delete when no cascade delete and user try to delete a entity from a master table, etc.

E.g. Employees table acts as master in lot of relations - manager of employee, manager of department, cashier, team leader, teams members etc, etc. If I add anew employee which is not involved in any relation I can delete it, but of user try to delete one that is master oin such relation, the delete fails (as it should) due to RI rules enforced at DB level, and that's ok.

I write delete code in a try ... catch and handle the exception, telling user he cannot delete that employee. But I want to give user more meaningful info - the reason the record cannot be deleted. Maybe it was just a test employee record, which was also added to a test team. But user forget where added that and if I could tell "Cannot delete employee because it is part of team T1", user will know to go first to Team T1, remove user then try to delete it again. That's a simple example, since as I said an employee can be involved in lot of relations - in my app I have at least 20.

The solution is to display the Message reported by SqlException, but that's not elegant at all. First, that msg is very technical - it talks about FK, PK, Triggers, which are meaningless for users and will scare them. Second my app is uses multi-lang UI and all menus and msgs are shown in user selected language (selected either at login time or in user profile). And the msg from SqlException is english (if I use english version) or worst, less common languages, like german or dutch, if it happens that sql server is in that language.

Is there any common or recommended approach to extract meaningful info from sql exception to be able to present user a meaningful msg (e.g. what relation or child table caused the failure, or what trigger, etc). but something I can test in program in a lang-independent fashion and then format my own error msg in a user-friendly way?

How do you handle this situation?

Thanks for all answers

(PS: Sorry for the long post)

Answer 1

Unfortunately, there isn't an easy answer here.

The amount of work involved will depend on the consistency of your error messages coming from your business layer. You are going to need to do some form of translation from the "technical" error message to your user oriented message.

This should be a matter of making some forms of lookups from your error messages to a resource key, which can be used to pull out your language-specific error message. However, if you need to parse the messages for more information (ie: table names, etc), then it gets a bit trickier. In that case, you'll probably need to have something that maps an error message to a regex/processor of some form as well as a new resource string. Then you could format the user's string with the information you extract from the original error, and present it to the user.

Answer 2

Well, from the database, you'll only ever get these technical messages, e.g. "violation of foreign key relation FK_something_to_another" or such.

Typically, in the SqlException, you also get a SQL error code or something else.

The best approach would probably be to have a separate table in your database which basically maps those technical SQL errors that can happen to more meaningful, user-oriented messages. E.g. if your SQL error says something like "fk violation blablabaal", you could have an entry in your "UserErrorTable" which maps this to a user message saying "could not delete user (this.and.that), most likely because ..... (he's still member of a team)" or whatever.

You could then try to catch those SqlExceptions in your business layer, translate those technical infos into a custom exception for your users, put in a user-friendly message, and stick the technical exception into the .InnerException of your custom exception type:

public class UserFriendlyException : Exception
{
  public string UserErrorMessage { get; set; }

  public UserFriendlyException(string message, SqlException exc) : base(message, exc)
  {
     UserErrorMessage = MapTechnicalExecptionToUserMessage(exc);
  }
}

Marc