Wednesday, November 24, 2010

Scenario: You want to deploy a SQL Compact / .NET based application, using private deployment only. Your application depend on a certain .NET Framework version. The table below illustrates your options, as you can see there are special considerations, if your clients only have .NET 4.0 and not .NET 3.5 SP1 installed.

Also, notice that all files required for private deployment are included in the private folder in either Program Files or Program Files (x86) (C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private) for version 4.0.

Tuesday, November 16, 2010

As you may know by now, the major new feature of SQL Server Compact 4.0 is support for ASP.NET starter websites. In addition, the Entity Framework support has been improved with the following features:

- Support for Code First development (requires EF 4 Feature CTP 4), allowing you to create database and schema in code.

. Support for paging (can be implemented with Skip and Take in LINQ).

- Support for server generated keys (IDENTITY)

The purpose of this post is to show how to use Entity Framework 4 with SQL Server Compact 4.0 and ASP.NET without having the full support for SQL Server Compact 4.0 in VS 2010.

I will cover the following scenarios in the sample application:

1: Migrate data and schema for the solution from either SQL Server Compact 3.5 or SQL Server 2005 or later.

3: Create a layered application, where the UI has no knowledge of Entity Framework, in order to improve testability, minimize UI code and allow the Data Access layer to be replaced with another Data Access Technology (unlikely, I know )

4: Provide the initial implementation of a music track list displayed in a grid, which can be sorted and paged.

7: Use various extension methods, that make your life with Entity Framework smoother.

For this sample I am using the Chinook database from CodePlex, which is delivered in script format and as a 3.5 SDF file. The Chinook database schema represents a digital media store, including tables for artists, albums, media tracks (3500 tracks), invoices and customers.

Creating the database (optionally migrating from 3.5 or Server)

Depending on your starting point, there are many ways to get to a 4.0 database.

If you have a 3.5 database, you can use SqlCeCmd40.exe to upgrade a copy of this database to 4.0:

Have a 3.5 database handy with the exact same table definition (schema) as the 4.0 database. After generating the EDM based on the 3.5 database, tweak the model to work with a 4.0 database. Let’s see how this is done:

Start by creating an empty solution, named Chinook, to hold our projects:

Then add a C# Class library project to the solution, and name it Chinook.Data:

Remove Class1.cs, then go to file system, and copy the Chinook.sdf (the 3.5 version) to the solution folder (C:\projects\chinook).

(I have also copied the 4.0 version, so I can easily find it later). Now we can add the EDM based on the 3.5 database file:

Name the model ChinookModel.edmx and click Add. In the next step, select Generate From Database, and click Next.

On the “Choose you data connection” step, create a new connection to the Chinook.sdf 3.5 version database, using the SQL Server Compact 3.5 provider, and click Next:

Then you get this question:

(I suggest you reply No)

Select all tables, select pluralize and click Finish. Now we have our Model:

Now, let’s make the Model work with our SQL Server Compact 4.0 file. First, modify the connection string in app.config as follows:

Then modify the emdx file (right click the file, and select Open With.. XML Editor).

Verify your changes by opening the edmx file in the visual designer.

Creating the POCO Entities for use with Repository

Now let us create a separate class library for our POCO entities based on the model, This will allow us to reference this Model project from both the data access layer and the UI layer. In order to do this, follow the steps in this EF Team blog post, and you will end up with a solution structure like this:

Notice the following change,that was made to ChinookModel.tt in order to have the entities in a separate project:

string inputFile = @"..\Chinook.Data\ChinookModel.edmx";

Also, a reference to the Chinook.Model project was added to Chinook.Data:

And the Custom Tool Namespace for the Chinook.Model.Context.tt file was set to Chinook.Model:

Creating the Repository

Now, add a Chinook.Repository project to the solution, to create the data access methods used by the UI. This project should reference the Chinook.Model and the Chinook.Data projects. As always, remove the Class1.cs file. Now, we want to create a method that allows the UI to get a page of Track data for display in a GridView. This is an initial implementation (notice the parameters, which are passed by default when using and ObjectDataSource from the UI):

I will be present in the Technical Learning Centre, SQL Server Data Technologies & Developer Tools booth during the event, so feel free to come and have a chat about some of the technologies and tools, that the SQL Server Data Programmability team provides.