There are many posts on the web about how easy it is to get started with Microsoft’s Entity Framework and SQL Server Compact Edition (SQL CE). This combination seems to be all the rage thanks to EF’s new “Code First” approach introduced in version 4.1. While I am impressed with the latest version of Entity Framework, I still prefer NHibernate for a lot of reasons, and it’s actually just as easy (maybe even easier) to get up and running with NHibernate+SQL CE as Entity Framework+SQL CE. In this post, I’ll walk you through the simple steps to get up and running.
[more]
Making Sure You’re Up To Date – Web Platform Installer to the Rescue!
There have been numerous updates related to Visual Studio 2010 flying around over the last couple of months, and you’ll need a few of them in order to perform the steps described in this article. The best way to grab these is to use the Web Platform Installer (WPI). This great little tool is a bit like NuGet, but it works on applications and tools instead of individual packages. It’s much, much easier than trying to wade through all the dependencies by hand!
Using the Web Platform Installer, find and install the following:
- Visual Studio 2010 SP1
- Microsoft SQL Server Compact 4.0
- Microsoft SQL Server Compact 4.0 Tools
- Microsoft Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0
If you’re missing any other dependencies, WPI should locate and install them for you, as you can see here:
Required References
Now that you have the necessary tooling installed, it’s time to create a project. These steps will work equally well with both a web application or a desktop application. Create whatever project type you want, and use NuGet to add references to the following packages:
This will add any additional required dependencies as well.
Project Setup
Your project now has the required dependencies. You just need to add a few more things to the mix. First, add a SQL CE 4.0 file to your project.
If you are creating an ASP.NET application, add an App_Data folder, and add the database file there. If you are making a WinForms app, you can add the database file wherever you want, just be sure to set it to copy to your output directory. Go ahead and add a SQL CE connection string to your application’s config file (either an app.config or web.config file depending on the project type):
<?xml version="1.0" encoding="utf-8"?>
<configuration>
..
<connectionStrings>
<add name="MyConnectionString" connectionString="Data Source=|DataDirectory|MyDatabase.sdf" providerName="System.Data.SqlServerCe.4.0" />
</connectionStrings>
..
</configuration>
Next, you need to decide where you will put your database entities. For simplicity, it’s best if these can all live in the same namespace within your project, but they certainly don’t have to. I typically organize my projects similar to this:
Configuring Fluent NHibernate
All that remains now is to configure Fluent NHibernate so that it knows where your domain entities live and how to map them to relational entities. We’ll leverage Fluent NHibernate’s auto mapping capabilities to achieve this. Derive a new class from DefaultAutomappingConfiguration, and override the base conventions with your own. Here’s the setup I typically use:
public class NHibernateConfig : DefaultAutomappingConfiguration
{
public override bool IsId(Member member)
{
return member.Name == "ID";
}
public override bool ShouldMap(Type type)
{
//TODO: Assuming all your entities are in the same namespace,
// just replace 'YourDomainType' with any of your entities.
if (type.Namespace != typeof(YourDomainType).Namespace)
{
return false;
}
return base.ShouldMap(type);
}
public override bool ShouldMap(Member member)
{
var prop = member.MemberInfo as PropertyInfo;
if (prop != null && !prop.CanWrite)
{
return false;
}
return base.ShouldMap(member);
}
}
One thing to note: if you decide to use Integers and the standard identity generator for the primary key properties on your domain entities, there is a known bug related to SQL Server CE. You can find out more at this question on Stack Overflow.
You will also need a bootstrapper of some sort to configure Fluent NHibernate at runtime. You need to tell it what database to use, what conventions to apply, etc. I almost always use a bootstrapper that is some variation of this:
public static class NHibernateBootstrapper
{
private static ISessionFactory _sessionFactory;
private static Configuration _configuration;
public static void Bootstrap()
{
_configuration = Fluently.Configure()
//TODO: Replace with the name of your connection string.
.Database(MsSqlCeConfiguration.Standard.ConnectionString(c => c.FromConnectionStringWithKey("MyConnectionString")))
//TODO: Replace 'YourDomainType' with the type of one of your entities.
.Mappings(m => m.AutoMappings.Add(
AutoMap.AssemblyOf<YourDomainType>(new NHibernateConfig())
.UseOverridesFromAssemblyOf<YourDomainType>()
.Conventions.AddFromAssemblyOf<YourDomainType>()
)
)
.ExposeConfiguration(cfg => cfg.SetProperty("connection.release_mode", "on_close"))
.BuildConfiguration();
_sessionFactory = _configuration.BuildSessionFactory();
}
public static void UpdateSchema()
{
new SchemaUpdate(_configuration)
.Execute(false, true);
}
public static void CreateSchema()
{
new SchemaExport(_configuration)
.Execute(false, true, false);
}
public static ISession GetSession()
{
return _sessionFactory.OpenSession();
}
}
There are a few additional things worth noting in my bootstrapper. First, it’s capable of both generating the initial schema for a new database as well as applying updates to an existing schema. NHibernate is capable of doing these things fairly well, though certainly not perfectly. I’ve found that it does well enough for the initial phase of application development though. Note that you don’t have to do anything else, like tell NHibernate about what to actually persist, how to set up relationships, etc. Fluent NHibernate’s automapping figures all that out for you!
Next, notice that the bootstrapper automagically loads additional conventions and overrides from the assembly containing the domain entities. If you want to keep those conventions and overrides elsewhere, you may do so easily by modifying the bootstrapper.
Finally, I opted for changing the connection.release_mode property to work around the bug with integer identifiers mentioned above. You won’t need to do this if you’re using Guid identifiers or another identifier generation strategy.
Wrapping Up
Congratulations, you now have all the plumbing in place. Just make sure you call your bootstrapper once at application startup, and then call the bootstrapper’s GetSession method whenever you need a session. During early development, I also call the bootstrapper’s UpdateSchema method once at application startup as well, that way I can make changes to my domain without worrying about managing updates to the underlying database schema.
Here’s what my typical Global.asax file looks like using this bootstrapper:
public class MvcApplication : HttpApplication
{
protected void Application_Start()
{
NHibernateBootstrapper.Bootstrap();
NHibernateBootstrapper.UpdateSchema();
}
...
}
And here’s the same boostrapper being consumed by a Windows Forms application:
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
BootstrapIoC();
NHibernateBootstrapper.Bootstrap();
NHibernateBootstrapper.UpdateSchema();
Application.Run(new HomeForm());
}
And there you have it: NHibernate with SQL Server CE is every bit as easy as using Entity Framework “Code First” with SQL Server CE, plus it’s more flexible to boot. You don’t have to manually specify what needs to be persisted or how, it’s all inferred for you automagically.
Can It Be Any Easier?
Yes, actually, it can. There’s a lot of boilerplate stuff going on here. That’s the sort of thing that’s ripe for becoming a NuGet package. I’m actually working on doing just that, but if someone would like to beat me to it, feel free. 