guyS's WebLog

IShare, My DotNet Fingerprint

Get SqlException friendly messages using its Error Number

Within the Dal layer we could catch SqlException and then format the Exception Message to a more user friendly message using the application localization language and by reading the message from a resource file.

Then we can and should create our application custom exception (inherits from Exception) using the localize message and rethrow it to the calling layer. 

public static string GetSqlExceptionMessage(int number)
{
  //set default value which is the generic exception message
  string error = MyConfiguration.Texts.GetString(ExceptionKeys.DalExceptionOccured);  
  switch (number)
  {
case 4060:
// Invalid Database
error = MyConfiguration.Texts.GetString(ExceptionKeys.DalFailedToConnectToTheDB);  
break;
case 18456:
// Login Failed
error = MyConfiguration.Texts.GetString(ExceptionKeys.DalFailedToLogin);  
break;
case 547:
// ForeignKey Violation
error = MyConfiguration.Texts.GetString(ExceptionKeys.DalFKViolation);  
break;
case 2627:
// Unique Index/Constriant Violation
error = MyConfiguration.Texts.GetString(ExceptionKeys.DalUniqueConstraintViolation);
break;
case 2601:
// Unique Index/Constriant Violation
error =MyConfiguration.Texts.GetString(ExceptionKeys.DalUniqueConstraintViolation);  
break;
default:
// throw a general DAL Exception
MyConfiguration.Texts.GetString(ExceptionKeys.DalExceptionOccured);  
break;
   }

   return error;
}
[* Hey, the switch statement according error number is from Fons Sonnemans short article about Dal Layer - so thanks Fons.]

Posted: May 20 2005, 03:10 PM by guyS | with 14 comment(s) |
Filed under:

Comments

charles said:

very helpful. thanks

# May 21, 2007 2:11 AM

Yongping Wang said:

Your Article is very good!

Thanks!

My English is very Poor.

# July 12, 2007 1:30 AM

Ganesh said:

One of a kind article....found it after a lot of searching for sql error codes....would be helpful if you can put a list of error codes and description...i had the infrastructure ready (just like yours) but needed actual error codes

# March 27, 2008 4:36 PM

David said:

Thx for sharing this function

# February 13, 2009 6:33 AM

Marwan said:

Realy Nice Job Thanks

# July 9, 2009 5:50 AM

GerardMX said:

Ganesh. You can find all the error codes in the sysmessage table in the master DB in SQL Server for all languages, 1033 for english.

# August 13, 2009 7:59 AM

Gary said:

select *

from master.dbo.sysmessages

where msglangid = 1033 -- English

order by error

# August 20, 2009 3:43 PM

arun said:

thanks a lot, nice stuff....

I executed that query for sysmessages, now my doubt is, is it possible for us to create our own query language using these error codes..?

# July 6, 2010 8:23 AM

Yukasung said:

Very helpful.thanks

# July 20, 2010 10:46 PM

Binay Tiwari said:

Excellent Article very Useful .

Thanks

# August 6, 2010 3:00 AM

D said:

Very useful article. Thanks a lot Gary.

# February 3, 2011 12:59 PM

Soner Gonul said:

Nice.

# May 9, 2011 12:56 AM

A said:

How to Get ASP.net Exception friendly messages

# October 15, 2011 5:18 AM