Fluent NHibernate, right way to implement ISQLExceptionConverter

Question

I want to catch a Violation of UNIQUE KEY constraint, on insert of a row, but dont know how.

1. RDBMS - MSSQL Server 2008
2. Version of NHibernate 3.3

The NHibernate configuration:

public NHibernate.Cfg.Configuration GetConfiguration()
    {
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionSqlServer"].ConnectionString;
        var cfg = Fluently.Configure()
                          .Database(MsSqlConfiguration.MsSql2008
                          .ConnectionString(connectionString)
                          .ShowSql()
                          .Driver<NHibernate.Driver.Sql2008ClientDriver>()
                          .Provider<NHibernate.Connection.DriverConnectionProvider>()
                          .Dialect<NHibernate.Dialect.MsSql2008Dialect>())
                          .Mappings(m => m.FluentMappings.AddFromAssemblyOf<EntityBase>().Conventions.Add(DynamicUpdate.AlwaysTrue()))
                          .ExposeConfiguration(config =>
                              {
                                  config.SetInterceptor(new SqlStatementInterceptor());
                                  config.SetProperty(Environment.SqlExceptionConverter,
                                      typeof(MsSqlExceptionConverter).AssemblyQualifiedName);
                                  config.Properties[Environment.CurrentSessionContextClass] = "web";
                                  var schemaUpdate = new SchemaUpdate(config);
                                      schemaUpdate.Execute(false, true);
                              })
                          .BuildConfiguration();
        return cfg;
    }

The ISQLExceptionConverter implementation:

public class MsSqlExceptionConverter : ISQLExceptionConverter
{
    public Exception Convert(AdoExceptionContextInfo exInfo)
    {
        var sqle = ADOExceptionHelper.ExtractDbException(exInfo.SqlException) as SqlException;
        if (sqle != null)
        {
            switch (sqle.Number)
            {
                case 547:
                    return new ConstraintViolationException(exInfo.Message,
                        sqle.InnerException, exInfo.Sql, null);
                case 208:
                    return new SQLGrammarException(exInfo.Message,
                        sqle.InnerException, exInfo.Sql);
                case 3960:
                    return new StaleObjectStateException(exInfo.EntityName, exInfo.EntityId);
            }
        }
        return SQLStateConverter.HandledNonSpecificException(exInfo.SqlException,
            exInfo.Message, exInfo.Sql);
    }
}

The try-catch statement:

public void InsertCultures()
    {
        using (var uow = _unitOfWorkFactory.Create())
        {
            var CULTURE_DEFAULTS = new List<Culture>()
            {
                new Culture() {Symbol = "ro-RO", Language = "Romana"},
                new Culture() {Symbol = "ru-RU", Language = "Rusa"},
                new Culture() {Symbol = "en-US", Language = "Engleza"}
            };

            foreach (var culture in CULTURE_DEFAULTS)
            {
                try
                {
                    _cultureRepository.Create(culture);
                }
                catch (ConstraintViolationException e)
                {

                }

            }
           /// Other stuff
        }
    }

If somebody has an Eagle-Eye please share :) thank you!

Answer 1

This is my working non-localized version for catching unique key violations, hope it helps. I throw a custom UniqueKeyException that calling code can catch and handle. Your Fluent configuration code looks correct to me.

public class SqlServerExceptionConverter : ISQLExceptionConverter
{
    public Exception Convert(AdoExceptionContextInfo adoExceptionContextInfo)
    {
        var sqlException = adoExceptionContextInfo.SqlException as SqlException;
        if (sqlException != null)
        {
            // 2601 is unique key, 2627 is unique index; same thing: 
            // http://blog.sqlauthority.com/2007/04/26/sql-server-difference-between-unique-index-vs-unique-constraint/
            if (sqlException.Number == 2601 || sqlException.Number == 2627)
            {
                return new UniqueKeyException(sqlException.Message, sqlException);
            }
        }
        return adoExceptionContextInfo.SqlException;
    }
}