http://www.codeproject.com/useritems/TDD_and_SqlCE.asp
Introduction
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.
Background
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. Bottom line is, there is 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 website which can be found here.
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:
Collapse/// <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)
return;
Hashtable properties = new Hashtable();
properties
.Add("hibernate.connection.driver_class",
"NHibernate.Driver.SqlServerCeDriver");
properties
.Add("hibernate.dialect",
"NHibernate.Dialect.MsSqlCeDialect");
properties
.Add("hibernate.connection.provider",
"NHibernate.Connection.DriverConnectionProvider");
string connectionString =
string.Format("Data Source={0};", DatabaseFilename);
properties
.Add("hibernate.connection.connection_string",
connectionString);
properties
.Add("hibernate.show_sql", "true");
properties
.Add("hibernate.connection.release_mode",
"on_close");
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() {
SetupDb();
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 NHibernates SchemaExport.
public void SetupDb() {
SqlCEDbHelper.CreateDatabaseFile(DatabaseFilename);
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:
[TestFixtureSetUp]
public void TestFixtureSetUp() {
FixtureInitalize(typeof(User).Assembly);
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 seperate 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 tests SetUp method is Insert the records I am going to test into the database. I feel that this can be done a better way as a method needs to be ran 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 ommitted those details here. For more information on how to accomplish GenericDao's take a look at Bill McCafferty's latest NHibernate Best Practices article which can be found here.
I use many techniques that Bill illustrates in his article and should be embraced for ease
of testing your Dao's such as Seperated 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 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 effeciently test your Castle MonoRail Controllers using NUnit! Stay tuned!
