SqlException constraint violation

Question

I'm working on an asp.net app. Is there a way, when catching a SqlException, to know which constraint was violated?

Answer 1

SqlException has a collection of SqlError objects: Errors. The SqlError have properties for error Number and you can compare this with the known constraint violation error numbers (eg. 2627).

While is true that SqlException itself exposes a Number property, it is not accurate if multiple errors happen in a single batch and hence is better to inspect the Errors collection.

Answer 2

catch (SqlException ex)
{
    if (ex.Errors.Count > 0) // Assume the interesting stuff is in the first error
    {
        switch (ex.Errors[0].Number)
        {
            case 547: // Foreign Key violation
                throw new InvalidOperationException("Your FK user-friendly description", ex);
                    break;
            // other cases
        }
    }
}

Answer 3

You have to add exception handler for the ConstraintException if I understand your question correctly

try
{

}
catch(ConstraintException exc)
{
//exc.Message 
}

Answer 4

Are you letting the exception bubble up? If you don't catch it and turn custom errors off in the web.config i believe it will display it in your browser. If you are catching it i would put a break point in the catch section and inspect the exception there.

Answer 5

The best thing is to catch this catch exception in your C# code behind.

catch(SqlException ex)
{
    if (ex.Message.Contains("UniqueConstraint"))
        throw new UniqueConstraintException();

    throw;
} 

You can create your own exception and throw that from your data layer, otherwise you can directly catch the exception as mentioned above.

using System; 

public class UniqueConstraintException : Exception
{
}