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.]