parsing SQLException errors in an ASP.NET (MVC) application?

Question

While validation can prevent most SQL errors, there are situations that simply cannot be prevented. I can think of two of them: uniqueness of some column and wrong foreign key: validation cannot be effective as the an object can be created or deleted by other parties just after validation and before db insertion. So there are (at least) two SQL errors that should lead to a message of invalid user input.

SQLException has a Number property for the error type, but I don't know how to find out which column is duplicated or which foreign key is wrong without trying to parse the actual error message text, which happens to be localized.

Is there any way to identify the offending column other than parsing the error message (which means at least to strictly choose a language for SQL Server and always use it)?

edit:

I should mention that I come from RubyOnRails, where the approach is: let's pretend that the db doesn't exist: no constraints, no db-enforced foreign keys etc. As I'm approaching ASP.NET MVC, I'd like to get rid of the rails biases, and accept the fact that the db indeed exists.

Answer 1

Are you sure these two situations absolutely cannot be prevented?

You can avoid unique constraint SQLexceptions on Insert, by using an Identity (database generated) primary key column. SQL Server will guarantee that the value is unique.

The same goes for inserting related rows into tables linked by a foreign key. Insert a row in each referenced table first, before inserting a row in the main table. Use IDENTITY_INSERT to get the value of each auto generated primary key and use this as the foreign key in your main table.

You should also wrap these individual statements in a transaction to ensure that either all tables are inserted successfully or none are. The transaction also isolates (hides) these changes from all other concurrent database accesses until the transaction is committed.