Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

In this and a following post, I will show, how you can use SQL Server Compact 4.0 with a desktop application, despite full tool support for this. Reasons for wanting to use SQL Server Compact 4 rather than 3.5 SP2 could be: Better support for Entity Framework 4, including support for Server Generated Keys and the Code First CTP, paging support and new ADO.NET APIs such as GetSchema and SqlCeConnectionStringBuilder. For an overview of SQL Server Compact 4, see this.

I will also show how to include the SQL Server Compact DLL files for private deployment with a desktop application, including in a Visual Studio Setup project (in part 2).

I will reuse the Data Access Layer based on Entity Framework 4 and POCO classes that I created in this blog post. Notice that there is no direct tooling to create a Entity Framework Model from database in the SQL Server Compact 4 tools for Visual Studio SP1, and therefore you must use one of the workarounds described. A third workaround is to install the VS 2010 SP1 SQL Compact Tools, create the 4.0 based model in a web project, and move this model to a Class library.

Start by opening the Chinook solution, and add a new WPF Application, Chinook.WPF:

Add references to the 3 project that make up the Data Access Layer (Data, Model and Repository):

Add a WPF DataGrid to the MainWindow.xml file, so it looks like so:

<Window x:Class="Chinook.WPF.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid AutoGenerateColumns="True" Margin="5,5,5,5" Name="dataGrid1" />
    </Grid>
</Window>

Notice that for this demo, I have set AutoGenerateColumns=True.

Add an event handler for the Loaded event, and add this code to MainWindow.xaml.cs:

var repo = new TrackRepository();
dataGrid1.ItemsSource = repo.GetAll(null, 50, 0); ;

Also add using Chinook.Repository; to the using statements.

For this demo, let us assume that each user has her own database file. So where can we put the file – the users ApplicationData is a possibility.

Now add an app.config file with the Entity Framework connection string:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ChinookEntities" connectionString="metadata=res://*/ChinookModel.csdl|res://*/ChinookModel.ssdl|res://*/ChinookModel.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Chinook40.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Notice that the SQL Server Compact connection string is:

Data Source=|DataDirectory|\Chinook40.sdf

So to make the application look for the database in the proper location, we can redefine the DataDirectory macro, by adding an event handler to App.xaml.cs like so:

private void Application_Startup(object sender, StartupEventArgs e)
{
    // This is our connection string: Data Source=|DataDirectory|\Chinook40.sdf
    // Set the data directory to the users %AppData% folder
    // So the Chinook40.sdf file must be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\
    AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
}

Now place a copy of the Chinnok40.sdf file in the proper location (on my Windows 7 PC, it is C:\\Users\\ErikEJ\\AppData\\Roaming\\, set Chinook.WPF as the startup project and run the application:

To make the code run faster, you can add this line to the TrackRepository.GetAll method:

context.ContextOptions.ProxyCreationEnabled = false;

You now have implemented a WPF application using Entity Framework 4 using POCO and Database First with SQL Server Compact 4.0.

In the next part I will show how to add the SQL Server Compact DLL files for private deployment, and create a MSI that installs these files and the database file, so the application can run on any machine with .NET 4.0 and a supported platform. (And .NET 4.0 being the only required component).

Part 2- Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment: http://erikej.blogspot.dk/2011/01/entity-framework-with-sql-server.html

You can download the solution so far from here: