I don't need to execute duplicatation query like checking some column is unique or not because this operation always execute with SQL Server constraint. However, default error message from SQL Server is not well-defined for end-user.
Do you have any idea for change/redefine error message to be more user-friendly?
PS. I execute all query via Entity Framework 4.0.
The only way I know to do this is to catch SqlException and then check the Number property. This property is a short-cut to the first Error in the Errors property, and if you are worried about the possiblity of multiple error conditions, you could use that instead.
Once you know the sql error number, you can provide a more user-friendly message. Unfortunately, I do not know of a way to get a nice list of common sql server error numbers, besides doing select * from sys.messages, but it can be a little overwhelming to search through them.
I only cared about a few specific error numbers and created the error conditions manually to determine the numbers:
547 foreign key conflict
2601 unique index violation
2627 primary key violation
On any other error number, I just provide a generic message indicating that there was a problem with the database. In all cases, I log the exception information for later use (using NLog).
