Database independence with NHibernate
time to read 3 min | 415 words

Karl has posted about his experience of porting an NHibernate application from SQL Server to PostgreSQL, long story short, he did it in 1 hour.

He does bring up a few points where the database that he was using bled into the code, hurting the database independence goal. I wanted to look at those and point out the builtin solutions that NHibernate provides for those.

Handling unique constraints violations, Karl has code like this:

try
{
    Session.SaveOrUpdate(user);
    transaction.Commit();
    return true;
}
catch (GenericADOException ex)
{
    transaction.Rollback();
    var sql = ex.InnerException as SqlException;
    if (sql != null && sql.Number == 2601)
    {
        return false;
    }
    throw;
}
catch (Exception)
{
    transaction.Rollback();
    throw;
}

Obviously, this code relies heavily on internal knowledge of SQL Server error codes, and wouldn’t translate to PostgreSQL.

With NHibernate, you deal with those issues by writing ISqlExceptionConverter, you can read Fabio’s post about this, but the gist of it is that you can provide your own exceptions in a database independent way.

The count function may return different types on different databases. NHibernate tries to stay true to whatever the database is giving it, so it wouldn’t truncate information. You can force the issue by overriding the count function definition by writing a derived dialect. You can see an example of that in this post.

Bits & Booleans, while SQL Server accepts 1 as a Boolean value, PostgreSQL requires that you would use ‘1’ instead. This is where NHibernate’s query.substitutions supports come in. I usually define substitutions for true and false and then use true and false in the queries. Based on the database that I am running on, I can select what will be substituted for those values.

Achieving database independence with NHibernate is very easy, as Karl’s 1 hour porting story proves.

Tweet Share Share 11 comments
Tags: