Visual Studio 2010 + ASP.NET MVC 3.0 + Entity Framework + Sql Server Compact 4.0 and Northwind

I wanted to play around with the latest version of SQL Server Compact Edition together with the Entities Framework in a ASP.NET MVC 3.0 site.


So I did the following:

1. Download and install Microsoft SQL Server Compact 4.0
2. Create a new ASP.NET MVC 3 project
3. Copy the database file for Northwind from C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Samples to the App_Data folder in the project.
4. Try to generate the generate an ADO.NET Entity Data Model based on the database.

Here we encounter the first problem. The ADO.NET Entity Data Model generator in Visual Studio 2010 does not seem to support ADO.NET connection strings to the SQL Server 4.0 Compact Edition. Even adding the DbProviderFactory for SQLServer 4.0 Compact in the machine.config does not seem to work. 

To the rescue comes the command line ADO.NET Entity Data Model generator 2 source code which can be downloaded here EdmGen2.exe.

EdmGen2 is a command-line tool for the Microsoft ADO.NET Entity Framework. The tool can be used as a replacement for the EdmGen.exe tool that ships with the .Net framework 3.5 SP1. EdmGen.exe can only read and write the CSDL, SSDL & MSL file formats. However, EdmGen2.exe can read and write the EDMX file format used by the Visual Studio design tools. Additionally, EdmGen2.exe can translate between EDMX and CSDL, SSDL & MSL formats, and the source code can act as examples on using the tooling APIs defined in the System.Data.Entity.Design assembly.

The first thing I found trying to run this tool with the following command line

/ModelGen "Data Source=D:\Users\kst\Visual Studio 2010\projects\MasterDetailCrud\MasterDetailCrud\App_Data\Northwind.sdf" System.Data.SqlServerCe.4.0 Northwind

was that the database I wanted to use was a SqlServer CE 3.5 database.

This was not obvious since the EdmGen2 tool just silently failed with no output. However running it through the debugger with the above command line parameters, yielded the following error.

"error 6003: The underlying provider failed on Open.

The database file has been created by an earlier version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade() method."

At this point in time, I am slowly beginning to wonder if going down this path using Sql Server CE is worth it (since I most likely will never use it professionally), or if I am missing something obvious (which is not entirely unlikely). But alas being stubborn I quickly created a small windows app with the following code 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;

namespace NorthwindDBUpdater
{
 public partial class Form1 : Form
 {
  public Form1()
  {
   InitializeComponent();
  }

  private void openDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
  {
   openFileDialog1.ShowDialog(this);
  }

  private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
  {
   selectedDatabaseFileTextBox.Text = openFileDialog1.FileName;
  }

  private void updateDatabaseButton_Click(object sender, EventArgs e)
  {
   var connectionStringTemplate = "Data Source={0}";
   var saveDirectory = string.Format(@"{0}\Upgrade", Path.GetDirectoryName(openFileDialog1.FileName));

   var sourceConnectionString = string.Format(connectionStringTemplate, openFileDialog1.FileName);
   var destinationConnectionString = string.Format(connectionStringTemplate, string.Format(@"{0}\{1}",saveDirectory, Path.GetFileName(openFileDialog1.FileName)));
   if (!Directory.Exists(saveDirectory))
   {
    Directory.CreateDirectory(saveDirectory);
   }

   SqlCeEngine sqlEngine = new SqlCeEngine(sourceConnectionString);
   sqlEngine.Upgrade(destinationConnectionString);
  }

 }
}

This allowed me to update the database file to a Sql Server 4.0 compatible database.
After copying the database to App_Data and invoking the EdmGen2 tool it now worked great and I could start mapping the ADO.NET Entity models to my domain models.


I will not provide the updated Northwind database here but you can download the NorthwindDBUpdater project and update the database yourself (I guess you should be able to use it to update any CE database, not just Northwind.)


The project can be downloaded here 2011-02-27-NorthwindDBUpdater.zip