Click here to Skip to main content
15,881,882 members
Articles / Database Development / SQL Server

TDD using SqlCE and NHibernate

Rate me:
Please Sign up or sign in to vote.
4.42/5 (7 votes)
10 Apr 2007CPOL4 min read 55.7K   410   28   5
This article walks through setting up a SqlCE test bench when using TDD


This article demonstrates how to use SQL Compact Edition to perform database unit testing of your Repository/DAO classes. More often than not, I cringe at the thought of performing database testing against the development database. It would be much easier and logical to perform database tests against a disposable datasource. This is where In-Memory and Embedded databases come into play.


When I first started looking at options for an In-Memory/Embedded database to use, I came across Ayende Rahien's blog posting about in-memory vs. embedded databases. I will let you read the posting to get more information rather than discuss the
differences here. The bottom line is, there are a number of options and any of them will fit into this solution thanks to NHibernate. Again, if you are not familiar with NHibernate then you can read more about it at the Hibernate Web site.

The reason I chose SqlCe is because it was the easiest to setup, has the best support for various datatypes, and because it is embedded. Because of this reason, you can create a database at the beginning of running your tests in TestFixtureSetUp, insert records and run assertions and then dispose of the database file. I would have liked to use an In-Memory database, the only one I looked at was Sqlite which has poor documentation and a couple of other shortcomings. At that point, I did some research on SqlCe and saw how easy it was to setup and get running.

Using the Code

The code is pretty basic. I will run through it briefly. A intermediate understanding of NHibernate is assumed, as well as NUnit and TDD.

The meat of the code is from Ayende's Rhino.Commons library. I snipped out the relevant portions because he also had some additional functionality for database cleanup/persistence during the tests that isn't relevant to the basic concept. I suggest that you go and look through his classes as they are very thorough and easy to follow. You can find Ayende's Rhino.Commons here. The Embedded TestFixture can be located under the ForTesting directory here, and the SqlCeDBHelper can be found under the Helpers directory here.

In the attached code, I have made EmbeddedTestBase the base class for Data Unit Tests. In this class, you can specify the database filename:

public static string DatabaseFilename = "TempDB.sdf";

The SDF database file will be placed in the bin directory of your Test assembly. Where this file sits is irrelevant, as it is deleted upon each launching of the tests via the SqlCEDbHelper.

The key here is the FixtureInitalize(params Assembly[] assemblies), which can be seen here:

/// <summary> 
/// Initialize NHibernate and builds a session factory 
/// Note, this is a costly call so it will be executed only one. 
/// </summary> 
protected void FixtureInitalize(params Assembly[] assemblies) {
    if (sessionFactory != null)

        Hashtable properties = new Hashtable();
        string connectionString =
        string.Format("Data Source={0};", DatabaseFilename);
        .Add("hibernate.show_sql", "true");

        configuration = new Configuration();
        configuration.Properties = properties;
        foreach (Assembly assembly in assemblies) {
            Console.WriteLine("Adding Assembly:" + assembly);
            configuration = configuration.AddAssembly(assembly);
        sessionFactory = configuration.BuildSessionFactory();

And CreateSession(); which is displayed here:

public ISession CreateSession() {
    return sessionFactory.OpenSession();

When you call CreateSession, it first calls Ayende's SqlCEDbHelper.SetupDb() which creates the database file that will be used via reflection. Next it creates the Schema via NHibernate's SchemaExport.

public void SetupDb() {
    try {
        new SchemaExport(configuration)
        .Execute(false, true, false, true);
    catch (Exception e) {
        Console.WriteLine("Error Message:" + e);

Within your test class, you call FixtureInitialize and CreateSession like so:

public void TestFixtureSetUp() {

    session = CreateSession();
    userRepository = new UserRepository();
    userRepository.session = session;

When you call FixtureInitialize, you pass it a reference to the Assembly that contains the mapping file that will be used to create the schema. In the sample code it's the same assembly, but in any real world situation it would be located in a separate assembly. This method will also build the NHibernate SessionFactory.

Finally, sessionFactory.OpenSession() is called which returns the NHibernate session to use.

The first thing I do in my test's SetUp method is insert the records I am going to test into the database. I feel that this can be done in a better way as a method needs to be run to ensure that the database is in a consistent state at the beginning of every test run. I will perhaps introduce another method in the base class in a future version that will "reset" the database after every test.

Points of Interest

Realistically, the UserRepository and any other Repository/Dao classes that you may have will derive from a common base class that would expose GetById, GetAll, etc.. instead of using the session object for all NHibernate access. For the sake of simplicity, I have omitted those details here. For more information on how to accomplish GenericDaos, take a look at Bill McCafferty's latest NHibernate Best Practices article.

I use many techniques that Bill illustrates in his article and should be embraced for ease of testing your Dao's such as Separated Interfaces, IoC with Castle Windsor and NHibernateSessionManager. Leveraging all of these tools will make testing your Repositories/Dao's much easier.

If you are using a reserved SQL keyword, Be sure to add backticks "`" I ran into a problem while first trying this out and stared at it for so long I didn't even notice. Unfortunately, SqlCe isn't too informative when it comes to this error. All I got was a "token error". Thanks to Ayende for helping me pull my head out of my @ss after ripping my hair out for a couple of hours.

Next time I hope to write an article on how to efficiently test your Castle MonoRail Controllers using NUnit! Stay tuned!


  • 4/10/2007 - Article first published


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralSqlCE and NHibernate Pin
Member 456347517-Dec-09 10:42
Member 456347517-Dec-09 10:42 
Question.NET Compact Framework Pin
ak081719-Apr-07 1:17
ak081719-Apr-07 1:17 
AnswerRe: .NET Compact Framework Pin
Sean Chambers19-Apr-07 2:28
Sean Chambers19-Apr-07 2:28 
GeneralLooking forward to MonoRail article Pin
Billy McCafferty17-Apr-07 10:44
Billy McCafferty17-Apr-07 10:44 
GeneralRe: Looking forward to MonoRail article Pin
Sean Chambers17-Apr-07 14:07
Sean Chambers17-Apr-07 14:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.