3

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.

Improve this question

5 Answers 5

Reset to default
5

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...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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?

Improve this answer

22 Comments

In this case it is not a bug. The user entered data that violates a database constraint. I need to give them the correct information so that they can correct what they entered and try again. This is more of a validation issue not an error.
@Kay: LOL! Yes, that is still a bug. Users should never see database exceptions, and if you find yourself parsing database exceptions to present them to the user then pretty clearly you have overlooked some input validation rules. Fix the missing validation instead.
@Steven, How do you know what business logic this particular application has encoded into database constraints? ... And the gentleman's objective is to PREVENT the user from seeing the raw database exception...
@Steven, No it is not a 'bug' (error). If you have a name varchar(20) UNIQUE NOT NULL, then the user might try to enter a 'James' after one is already entered. Even if the system checks first it is still possible to violate the constraint with race conditions.
@[Charles Bretana]: because he just told me, in the comment. He's not trying to prevent the user from seeing the db exception, he's trying to parse the db exception to present the info in more detail. Instead of checking for the violation in validation in the first place.
|
1

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.

Improve this answer

2 Comments

unique is a constraint that you aren't aware of until you hit the db. You can do a check first but it is possible for someone to insert after the check.
Sure, but again, this shouldn't happen. If you have a uniqueness issue to worry about, there should be a simple cache at the application level for just this sort of thing. You shouldn't be trying to parse database exception messages, period.
0

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).

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.