SQL Server CE – Great for prototyping and testing

Background

Yesterday I put together a simple POC app for my team lead who needed to see Spring.NET + NHibernate in action (no, not the book) for a client.  Even though these days I’m using the Castle stack for most of my IoC/DI and mini-framework needs, it was interesting to see how far Spring.NET has come (probably more on that in future posts…) since I last used it a long time ago. 

I had a very basic scenario I focused on; adding and retrieving employees with a first and last name.  I started out with a basic web app, service and repository.  I just created a simple EmployeeService class that took in an implementation of IEmployeeRepository via its constructor.  I ended up creating 4 different implementations of an IEmployeeRepository, for testing out different scenarios and used Spring.NET to inject the appropriate one into my service. 

I started out with no help from Spring.NET or NHibernate regarding repositories and just created a simple in-memory repository implementation which just stored the data in a private IList.  Simple stuff.  But I didn’t want to really jump to a full blown SQL Server 2005 repository just yet.  I wanted something a bit more portable, but still with the ability to store the data in a relational database.

In Comes SQL Server CE

So I’ve been wanting to try SQL Server CE anyway, so I went ahead and downloaded it.  I created another repository implementation that just used plain ol’ ADO.NET using the System.Data.SqlServerCe assembly.  As you can see, it’s very easy to set up the SQL Server CE engine…

   1: SqlCeEngine engine = new SqlCeEngine("data source=temp.sdf");

   2: engine.CreateDatabase();

Then I created yet another repository implementation, this time one that leveraged NHibernate, which has built-in support for SQL Server CE.  Instead of setting up a config file for NHibernate, I took a chapter out of Ayende‘s Rhino Commons and configured NHibernate all from code…

   1: Hashtable nhProperties = new Hashtable();

   2: nhProperties.Add("hibernate.connection.driver_class", "NHibernate.Driver.SqlServerCeDriver");

   3: nhProperties.Add("hibernate.dialect", "NHibernate.Dialect.MsSqlCeDialect");

   4: nhProperties.Add("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");

   5: nhProperties.Add("hibernate.connection.connection_string",

   6:                  string.Format("Data Source={0};", databaseFileName));

   7: nhProperties.Add("hibernate.show_sql", "true");

   8:  

   9: Configuration configuration = new Configuration();

  10: configuration.Properties = nhProperties;

  11: configuration.AddAssembly("SpringNHibernateApp.Domain");

  12:  

  13: ISessionFactory sessionactory = configuration.BuildSessionFactory();

This all worked great and I was very pleased at how quickly I was able to get all of this up and running.

Unexpected Problem

So all of my integration tests worked like a champ so I go and wire up Spring.NET to use one of my new SQL Server CE enabled repositories and fire up my web app and get a nice little YSOD with this…

“SQL Server Compact Edition is not intended for ASP.NET development”

Seemed a little strange to me, but Steve Lasker tries to explain why.  Luckily, it’s easy enough to workaround.  Just place this line of code before any code that starts mucking around with SQL Server CE…

   1: AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

Anyway, guess that’s it for now.