A list of common SqlException numbers?

Question

What are the most common/useful SqlException numbers? I don't need the entire sysmessages table, just the typical or common errors you're likely to see in a data access layer so I can do sometihng like:

switch (exception.Number)
        {
            case (2):
            case(53):
                _error = DataAccessErrorType.NetworkAddressNotFound;
                break;
            case(4060):
                _error = DataAccessErrorType.InvalidDatabase;
                break;
            case (18452):
            case (18456):
                _error = DataAccessErrorType.LoginFailed;
                break;
            case (10054):
                _error = DataAccessErrorType.ConnectionRefused;
                break;
            case (547):
            case (2627):
            case (2601):
                _error = DataAccessErrorType.SqlError;
                break;
            default:
                break;
        }

Answer 1

1205: Deadlock victim error. It's usually reccomended that you check for this and then just re-try the opperation.

Answer 2

Slightly OT: it looks like you're writing code to translate a SQL error number into something more meaningful.

You're not the first person to do this. Spring.NET has an exception mapping system already, as well as some other nice data access features.

If you can use this data access system, you won't need to map error numbers in the first place...

Answer 3

These vary from DBMS to DBMS. I.E. SQLSERVER error numbers are different from ORACLEs are different from DB2s. In addition JDBC drivers have thier own "standard" set of error codes.

Most DBMSes have a function to translate these to a text message.

In practice your program will only be able to handle a small sub-set of possible errors. Like "row not found", "duplicate key" , "foriegn key missing".

There is no way for your program to reasonably handle thing like "table full", "database locked for maintenance" , "network unavailable" etc. So for most errors just through an exception with the transalated text.