Entity Spaces 2011, SQL Server Compact Edition 4.0 and WebMatrix

Okay… I want to do some SQL queries that are wrapped in transactions and not just simple CRUD statements. So I pulled out my trusty DAL code generation library (EntitySpaces) and began integrating it into my WebMatrix project.

After installing EntitySpaces 2011 I referenced all the DLL’s that I needed in my project (I’m using a separate “Core” project in Visual Studio 2010 that is linked to my WebMatrix project), including the EntitySpaces.SqlServerCe4Provider:

WebMatrix uses SQL Server Compact Edition v4.0, and due to the way the EntitySpaces references the different CE versions, you need to supply this in the connection string for code generation:

Code generation went fine and I have my Generated and Custom classes all set to go. Next I needed to reference the EntitySpaces libraries in my Web.config file. Be sure to reference the correct provider for the Compact Edition:

Note, that we specify  “Data Source=|DataDirectory|yot.sdf;” where the Data Directory is going to default to the App_Data folder under your website root directory. This is handy too if you deploy the compact database to your intranet or production site. Because it’s all relative to your App_Data folder, it will still work without having to remember to change the path etc…

Lastly, we need to configure the EntitySpaces library on startup. This is done by adding the necessary code to the _AppStart.cshtml file:

Now, all that’s left to do is to load up a collection and populate a web grid to display it:

The rendered WebGrid looks something like this:

I am now ready to start putting together some more complex multi-step commits using EntitySpaces.