CODEWITHSUNDEEP
c#sql-serverexception
Shantanu Gupta
Shantanu Gupta

Reputation: 21188

How to manipulate SqlException message into user friendly message

I want to obtain all the replaced values in the following exception from error message obtained on UI to display custom message up to a granular level

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Parameter_Status". The conflict occurred in database "LIMS", table "dbo.Status", column 'StatusId'.

The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.

I can observe

%ls is being replaced by UPDATE, FOREIGN KEY etc.

In C# I will be getting error message from which I want to extract the parts replaced by SQL Server. I will be maintaining SqlErrors in my library and will replace them with custom messages.

Custom message I want to display eg. 

string.Format("{0} failed!, incorrect value was supplied for {1} field", "UPDATE", "Status")

Upvotes: 4

Views: 3872

Answers (1)

Ekk
Ekk

Reputation: 5715

SQLException has Number property, you can use this number then translate to whatever text you want.

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;
}

Example code copy from: Get SqlException friendly messages using its Error Number

Upvotes: 2

Related Questions