Handling database exceptions in .net

Question

When we can catch an exception like: Violation of UNIQUE KEY constraint 'IX_Product'. Cannot insert duplicate key in object 'Product'. (2627).
The challenge is how to dechiper the Index Name IX_Product as a Member (i.e. I don't want to substring out the message). There could be more than one unique constraint on a table and we would need to know which one to give more detailed information to the user. It would be preferable to catch it as DbException so it isn't SQL Server specific. Is there a way to get the affected index from the exception without having to parse the string?

The only solution I have came up with but I have not tested would to use a stored procedure and trap the error in there and returned the more detailed message from the stored procedure. But I believe this would still have issues.

Answer 1

uh...maybe i'm missing something obvious...but wouldn't it be a better use of your time to fix the bug instead of parsing the exception?

Answer 2

You'll have to either:

1. code your client component to recognize the constraint names that each insert/update statement might throw exceptions for,
2. rename all your constraints so that they are "decipherable" in the way you want to use them in client code, or...
3. check all the contraints in a stored proc before attempting the insert/update, and throw (Raise) your own custom exception in the proc if the check fails, BEFORE attempting the insert update and letting the contraint create the exception...

Answer 3

Once you have it as an Exception object parsing is your best option. I wouldn't be too quick to dismiss it as an implementation though -- regular expressions with a group shouldn't be too hard to perfect for your situation.

You may also end up with a similar solution (parsing) in your stored proc as well. Also, by pushing the parsing code to the database server, you are forcing your databases to have the ability to resolve the exact details without parsing (it may be trivial on database A but exceptionally difficult on databases B & C).

Answer 4

This sounds like a poor design issue. RDBMS should be enforcing these things, but the application should also be aware of, and built around, these constraints as well. It's a pretty brutal thing to expect your RBDMS to be handling logic exceptions that your application should be trapping or preventing to begin with. Database engines are for data operations, not for throwing exceptions to the application.

Answer 5

Don't parse exception text. (echoing andrew here...)

If you're expecting a number of possible pitfalls in data insert/update actions, you should trap for those in your C# layer. Extend from ApplicationException to build your own exceptions to handle specific constraints like these.

But this assumes your data model is positioned such that you can make those determinations without having to use the db to tell you that a statement can be successfully executed. If your data design doesn't allow you to know if you could be violating constraints without running it through the db engine, there's a flaw in your data design.