How to catch a specific SqlException error

Question

Q  Is there a better way to handle SqlExceptions    The below examples rely on interpreting the text in the message     Eg1  I have an existing try catch to handle if a table does not exist  Ignore the fact that I could check if the table exists in the first place   try         code   catch SqlException sqlEx            if  sqlEx Message StartsWith  Invalid object name                            code                   else             throw      Eg2  without the try catch showing duplicate key exception  if  sqlEx Message StartsWith  Cannot insert duplicate key row in object      Solution  The start of my SqlExceptionHelper    -- to see list of error messages  select   from sys messages where language id   1033 order by message id public static class SqlExceptionHelper         -- rule  Add error messages in numeric order and prefix the number above the method        -- 208  Invalid object name     ls       public static bool IsInvalidObjectName SqlException sex        return  sex Number    208            -- 2601  Cannot insert duplicate key row in object     ls  with unique index     ls   The duplicate key value is  ls      public static bool IsDuplicateKey SqlException sex        return  sex Number    2601

User · Answer

You can evaluate based on severity type   Note to use this you must be subscribed to OnInfoMessage  conn InfoMessage    OnInfoMessage  conn FireInfoMessageEventOnUserErrors   true    Then your OnInfoMessage would contain   foreach SqlError err in e Errors      Informational Errors if  Between Convert ToInt16 err Class   0  10  true         logger Info err Message     Errors users can correct    else if  Between Convert ToInt16 err Class   11  16  true         logger Error err Message     Errors SysAdmin can correct    else if  Between Convert ToInt16 err Class   17  19  true         logger Error err Message     Fatal Errors 20    else       logger Fatal err Message        This way you can evaluate on severity rather than on error number and be more effective  You can find more information on severity here   private static bool Between  int num  int lower  int upper  bool inclusive   false         return inclusive           lower  lt   num  amp  amp  num  lt   upper           lower  lt  num  amp  amp  num  lt  upper

User · Answer

Sort of  kind of  See Cause and Resolution of Database Engine Errors class SqllErrorNumbers       public const int BadObject   208     public const int DupKey   2627     try            catch SqlException sex       foreach SqlErrorCode err in sex Errors             switch  err Number                case SqlErrorNumber BadObject           case SqllErrorNumbers DupKey                      The problem though is that a good DAL layer would us TRY CATCH inside the T-SQL  stored procedures   with a pattern like Exception handling and nested transactions  Alas a T-SQL TRY CATCH block cannot raise the original error code  will have to raise a new error  with code above 50000  This makes client side handling a problem  In the next version of SQL Server there is a new THROW construct that allow to re-raise the original exception from T-SQL catch blocks

User · Answer

The SqlException has a Number property that you can check   For duplicate error the number is 2601   catch  SqlException e       switch  e Number             case 2601              Do something           break        default           throw            To get a list of all SQL errors from you server  try this    SELECT   FROM sysmessages   Update  This can now be simplified in C  6 0  catch  SqlException e  when  e Number    2601          Do something

User · Answer

It is better to use error codes  you don t have to parse   try     catch  SqlException exception        if  exception Number    208                   else         throw      How to find out that 208 should be used   select message id from sys messages where text like  Invalid object name

User · Answer

If you are looking for a better way to handle SQLException  there are a couple things you could do   First  Spring NET does something similar to what you are looking for  I think    Here is a link to what they are doing   http   springframework net docs 1 2 0 reference html dao html  Also  instead of looking at the message  you could check the error code  sqlEx Number    That would seem to be a better way of identifying which error occurred   The only problem is that the error number returned might be different for each database provider   If you plan to switch providers  you will be back to handling it the way you are or creating an abstraction layer that translates this information for you   Here is an example of a guy who used the error code and a config file to translate and localize user-friendly error messages   https   web archive org web 20130731181042 http   weblogs asp net guys archive 2005 05 20 408142 aspx

User · Answer

For those of you rookies out there who may throw a SQL error when connecting to the DB from another machine For example  at form load   you will find that when you first setup a datatable in C  which points to a SQL server database that it will setup a connection like this   this Table nameTableAdapter Fill this DatabaseNameDataSet Table name     You may need to remove this line and replace it with something else like a traditional connection string as mentioned on MSDN  etc    http   www connectionstrings com sql-server-2008

User · Answer

I am working with code first  C  7 and entity framework 6 0 0 0  it works for me Add          bool isDuplicate   false       try                 add to database              catch  DbUpdateException ex                if  dbUpdateException InnerException    null                     var sqlException   dbUpdateException InnerException InnerException as SqlException            if sqlException    null               isDuplicate   IsDuplicate sqlException                         catch  SqlException ex                 isDuplicate   IsDuplicate ex                 if isDuplicate            handle here           bool IsDuplicate SqlException sqlException        switch  sqlException Number                case 2627              return true          default              return false           N B  my query for add item to db is in another project layer

User · Answer

If you want list of error messages met in Sql server  you can see with  SELECT   FROM master dbo sysmessages

User · Answer

With MS SQL 2008  we can list supported error messages in the table sys messages  SELECT   FROM sys messages

[c#] How to catch a specific SqlException error?

Examples related to c#

Examples related to exception

Examples related to sqlexception