Hi,
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)