Finer granularity of SQL Exceptions?

Question

In C# Is there a way of getting a finer granularity with SQL exceptions?

I'm aware that an aweful lot can go wrong but I want to deal with certain cases differently and parsing the Error message doesn't seem to be very elegant.

Also Are the error messages created by the framework or are they db specific?

For example If i have a primary key violation on INSERT is the error message going to always be:

Violation of PRIMARY KEY constraint 'PK_tblProduct'. Cannot insert duplicate key in object 'dbo.tblProduct'.

or is that SQLServer specific.

Update

I looks like I'm after the error number, any idea where I can get the various error numbers?

best I can do with a quick google is: 18456: Logon Failed 18488: Password Expired

Answer 1

If you are catching a SqlException, you should have access to .Number, which gives you the SQL error number; this is probably your best approach... (you can get the same from the InfoMessage event as well for non-fatal errors).

Of course, this won't distinguish between tables - you could argue that if you need this level of granularity, you should check first (in a serializable isolation level, ideally with UPDLOCK), raising your own error if there is conflicting data.

Answer 2

In SQL Server you can use various error functions which returns various information about the errors encountered from sys.messages

You can retrieve more information from BOL.

Answer 3

To answer the last part of your question: the specifics of the error message will depend on the database. There are some standard SQL error codes, but the actual text is going to change from DBMS to DBMS. Even whether or not SQL standard error codes are returned depends on the DBMS.

Answer 4

You can find the error codes here

Answer 5

The .Number member of SQLException seems to be what I am after, Here are the numbers I've managed to find out about so far:

• 17: SQL Server does not exist or access denied.
• 4060: Invalid Database
• 18456: Login Failed
• 547: ForeignKey Violation
• 1205: DeadLock Victim
• 2627, 2601: Unique Index/Constriant Violation