Monday, February 7, 2011

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:

image

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

image

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:




image



<?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:




image




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:


10 comments:

Unknown said...

Great :) Can't wait for part 2, I will need it in near feature :)

ErikEJ said...

Thanks, I am working on it.

Mykola Rykov said...

Thank you for your work!
Waiting for second part :)
I'd like 'Round Midnight too)

Programista said...

If you want to use sql ce 4.0 in WPF project and have full designer support without bother by details, workarounds, command line tools etc. just fallow this simple steps

- Add new project to existing app
- Choose some web project for example asp.net mvc 4 website
- Choose empty template
- Delete all files and folders after creating the web project
- Now just simple add new ado.net entity model to this project and it will just work
- Select the sql ce 4 database, it will be visible in the wizard
- Ok, generate, done
- Just reference this project to main wpf app

That's it! Now you can use the dbcontext in the app, update schema when you want, quick and easy, as it was supported, no difference.

Also using a separate dll for db classes is a good thing, so it is a good option.

Can can merge it later if you want.

Hope this helps.

ErikEJ said...

Programista: That looks like a lot of steps to me!? Use my VS add-in - one step!

Anonymous said...

Hi,

I want to develop a C# Desktop application with SQL Compact 3.5 SP1
. I know all except "THE LOCATION OF MY .sdf database file"

Would u ,please, like to tell where should i save the .sdf file in my computer hard disk ?

so that when I will create the Setup File of my application, The .sdf database will automatically be bundled with my Setup.exe File ?

Finally, when my application will be installed on Non-Development Computers, where will the .sdf file be saved ? I want to know this location too because the users might need to take backups of their Data stored in the .sdf file...

Please, help...


Best Regards

ErikEJ said...

Jewel: See my second blog post in this series: http://erikej.blogspot.dk/2011/01/entity-framework-with-sql-server.html

Unknown said...

How to add those data,model,repository references brother?

ErikEJ said...

Priom: Right click the References node in your project, and select Add Reference

ErikEJ said...

Link to download: https://1drv.ms/u/s!AitHcOtLnuVHgwGwRHTuVtLUPljq