4

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")
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shantanu Gupta
  • 20,688
  • 54
  • 182
  • 286
  • If you have composite foreign keys you might not be able to pin the problem on a specific column. – Martin Smith Oct 30 '11 at 11:13
  • You're on the right track, use Regex to match the required fields. Create an global exception handling mechanism where you will translate the SQL exception to your custom exception (i.e. Application_Error in ASP.NET etc) – Karel Frajták Oct 30 '11 at 11:16
  • @MartinSmith: I caught your hint. I am open for any kind of suggestions. I expect answers on the basis of assumptions as well eg. My DB will never contain composite foreign key(which is not true in my case). Then how would I do this.. – Shantanu Gupta Oct 30 '11 at 11:19

1 Answers1

2

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

Ekk
  • 5,627
  • 19
  • 27