Setup SQL Server Compact 4 to unit test NHibernate related code

Most of the time to fake data base SQL lite data base is used. But it has certain differences from SQL server. With release of SQL Compact 4 it becomes really good choice to mock data base calls. So in this post I will describe a way of setting up NHibernate to work with SQL CE local data base. So here are our goals:

1. Each test fixture has its own fresh DB instance
2. NHibernate SessionFactory is same for all the tests (performance is still important in tests)
3. Caching of NHibernate doesn’t stands on isolation way (each test will have clean factory, without any cached entities)
4. Works just after getting from source control with no additional configuration

First of all we need to install sql ce tools for visual studio. After done that we can add an empty data base file to our tests project:

lets call it db. This file is going to be the one that is going to be copied for each test and where NHibernate will create its tables.

Now lets create a base class for tests that are going to use NHibernate:

public class DbTests
{
    protected static ISessionFactory factory;
    static Configuration nhConfig;

    static DbTests()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        nhConfig = NhConfigure();
        factory = nhConfig.BuildSessionFactory();
    }

    [TestFixtureSetUp]
    public void Setup()
    {
        File.Copy("db.sdf", "Temp.sdf", true);
        new SchemaExport(nhConfig).Execute(true, true, false);
    }

    [TestFixtureTearDown]
    public void TearDown()
    {
        File.Delete("Temp.sdf");
    }

    static Configuration NhConfigure()
    {
        DomainMapper mapper = new DomainMapper();
        HbmMapping mappings = mapper.CompileMappingFor(new[] { typeof(TestEntity) });

        var configuration = new Configuration();
        configuration.SessionFactory()
            .Integrate.Using<MsSqlCe40Dialect>()
            .Connected.By<SqlServerCeDriver>()
            .Using("Data Source=Temp.sdf");

        configuration.AddDeserializedMapping(mappings, "domain");
        
        return configuration;
    }
}

What is done here is pretty straight forward. Each test fixture will get its own empty data base with new schema installed.

Also you will need to install SqlServerCompact package from nuget in order to get SqlServerCeDriver support.

There is an interesting bug when using identity columns with SQL CE. You can get NHibernate.AssertionFailure : null identifier exception. Here is how you can solve it.

The last thing we want to take care about is NHibernate cache. Each test probably will have its own ISession instance, so only second level caching should be handled. Here is how we can clean up it:

static void ClearCache()
{
    factory.EvictQueries();
    foreach (var collectionMetadata in factory.GetAllCollectionMetadata()) 
        factory.EvictCollection(collectionMetadata.Key);
    foreach (var classMetadata in factory.GetAllClassMetadata()) 
        factory.EvictEntity(classMetadata.Key);
}

Just add this method call to Setup and that it. As always working sample attached:

Source code sample doesn’t contain nuget packages and uses this way of working. So don’t be scared of everything red in ReSharper after open solution. Just build it.