How get correct Exception from ADO.NET about foreign key violation

Question

I would like to get correct Exception from ADO.NET about foreign key violation. Is there a way to do that?

I am using try to catch ADO.Exception and check it message text for 'foreign'. So, if there is 'foreign' text in exception text, it is a violation and I can alert.

Is it the right way to do or any other method?

try{
    base.Delete();
IList<Issue> issues = Issue.LoadForX(this);
foreach (Issue issue in issues)
{
  issue.X= null;
  issue.SaveAndCheckChanged(user);
}

}
catch(NHibernate.ADOException exception)
{...

Answer 1

You can do this by creating a class that implements the ISQLExceptionConverter interface.

Here is an example implementation for SQL Server:

Public Class MsSqlExceptionConverter
    Implements ISQLExceptionConverter


    Private Enum SqlServerError As Integer
        ConstraintViolation = 2627
        ConstraintConflict = 547
    End Enum


    Public Function Convert(ByVal adoExceptionContextInfo As Global.NHibernate.Exceptions.AdoExceptionContextInfo) As System.Exception _
    Implements Global.NHibernate.Exceptions.ISQLExceptionConverter.Convert
        Dim sqle As SqlException = TryCast(ADOExceptionHelper.ExtractDbException(adoExceptionContextInfo.SqlException), SqlException)
        If sqle IsNot Nothing Then
            Select Case sqle.Number
                Case SqlServerError.ConstraintConflict
                    Return New ConstraintConflictException(InternalExceptionMessages.ConstraintConflictOccured, adoExceptionContextInfo.SqlException)
                Case SqlServerError.ConstraintViolation
                    Return New ConstraintViolationException(InternalExceptionMessages.ConstraintViolationOccured, adoExceptionContextInfo.SqlException)
            End Select
        End If
        Return SQLStateConverter.HandledNonSpecificException(adoExceptionContextInfo.SqlException, adoExceptionContextInfo.Message, adoExceptionContextInfo.Sql)
    End Function


End Class

To make use of this, define it in your NHibernate config file as follows:

    <property name="sql_exception_converter">YourProduct.Infrastructure.NHibernate.ExceptionConverters.MsSqlExceptionConverter, YourProduct.Infrastructure</property>

Overall, this feature is (more or less) undocumented, but you can find some information on Fabio Maulo's blog.