Summary
I’ve covered a little unit testing of NHibernate in the past. I showed how to use SQLite, which is the most common method of unit testing (technically, integration testing) an NHibernate application. If you’re using MS SQL server for your application, SQLite has some serious limitations that could hinder your unit tests. So I’m going to show you how to use Microsoft’s LocalDB.
The Problems with SQLite
One of the first issues I discovered with SQLite is that it doesn’t do multiple databases. Simulating a multi-database join in a unit test is not workable with SQLite. The second and most annoying “feature” of SQLite is that it doesn’t behave well with a command line MSTest. There are work-arounds, including the fact that you have to create a test settings file and point your script to that file to make MSTest work with SQLite unit tests. Other issues surround the 32bit/64bit dll’s and the difficulty in making it all work on Teamcity.
Microsoft SQL Express and LocalDB
Apparently, Microsoft came up with a solution to SQLite, called LocalDB. It’s a version of SQL Express that is lightweight and fully-featured. The 64-bit install is only 33MB in size and there is no special driver for NHibernate. If you are going to develop for MS SQL server, then the driver is the same for LocalDB.
Here’s the first document I stumbled across when I was looking for this product: Introducing LocalDB, an improved SQL Express. In order to get things rolling quick, you can go to this article: Getting Started with SQL Server 2012 Express LocalDB.
The Sample Project
So I put together a project based on my previous example with a Student, Teacher and Class table (I renamed “Class” into “SchoolClass” to prevent a clash with the keyword “Class”). I added a test project onto the solution and created a special context for the purpose of my unit tests. Here’s how the code looks:
public class TestSessionFactory
{
private static ISessionFactory SessionFactory
{
get
{
return Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2005
.ConnectionString("Server=(localdb)\testinstance;Integrated Security=True"))
.Mappings(m => m.FluentMappings.Add<SchoolClassMap>())
.Mappings(m => m.FluentMappings.Add<TeacherMap>())
.Mappings(m => m.FluentMappings.Add<StudentMap>())
.ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
.BuildSessionFactory();
}
}
public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
The first thing you’ll notice is that I was able to use the “Add” for the fluent mappings. SQLite doesn’t allow this. This can be handy if you have duplicate table names in separate databases, but create your class and mappings in one project. You have fine-grain control over which tables will appear in your context.
The second thing to note is that you can tell the session factory to generate a schema when it starts. If you were to use this session factory in your unit tests as-is, you’ll get nothing. That’s because Fluent NHibernate will not create the initial databases. For that, I wrote another method:
public static void Create(string databaseName, string instanceConnection)
{
string databaseDirectory = Directory.GetCurrentDirectory();
SqlConnection db;
db = new SqlConnection("server=" + instanceConnection + ";" +
"Trusted_Connection=yes;" +
"database=master; " +
"Integrated Security=true; " +
"connection timeout=30");
db.Open();
SqlCommand myCommand = new SqlCommand(@"CREATE DATABASE [" + databaseName + @"]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'" + databaseName + @"', FILENAME = N'" + databaseDirectory + @"" + databaseName +
@".mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'" + databaseName + @"_log', FILENAME = N'" + databaseDirectory + @"" + databaseName +
@"_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
", db);
myCommand.ExecuteNonQuery();
db.Close();
}
This method is contained in the “TestDatabase” class along with a “Remove” method to undo the database creation. I generated the database create command from the SQL Server management console. I made the application execute directory (bin directory) the default location for the database files to be created, using the Directory.GetCurrentDirectory() method.
The Unit Tests
The unit tests have an initialize and a cleanup method. The Initialize will setup the LocalDB instance and create a database. The cleanup reverses the operation by dropping the database first, then shutting down the instance and deleting it.
The ms test initialize method looks like this:
[TestInitialize]
public void Initialize()
{
// ... removed code not discussed
// create a new localdb sql server instance
startInfo = new ProcessStartInfo
{
WindowStyle = ProcessWindowStyle.Hidden,
FileName = "cmd.exe",
Arguments = "/c sqllocaldb create "testinstance" -s"
};
process = new Process { StartInfo = startInfo };
process.Start();
process.WaitForExit();
TestDatabase.Create("facultydata", "(localdb)\testinstance");
TestDatabase.Create("studentdata", "(localdb)\testinstance");
}
I used the Process class to execute a command line prompt to kick-off the LocalDB instance. I named this instance “testinstance”. That is used inside the context when Fluent NHibernate connects to the database. It can also be used by the SQL Server management console to connect and troubleshoot problems. Use the “(localdb)testinstance” syntax to connect to the database.
The unit tests look like this:
[TestMethod]
public void ThirdTest()
{
using (ISession db = TestSessionFactory.OpenSession())
{
Teacher teacher = new Teacher
{
Name = "test teacher"
};
db.Save(teacher);
teacher = new Teacher
{
Name = "test teacher2"
};
db.Save(teacher);
var query = (from t in db.Query<Teacher>() select t).ToList();
Assert.AreEqual(2, query.Count);
}
}
Where to Get the Code
I have added this Visual Studio 2012 project to my GitHub repository. You can go here and download the project yourself.