ADO.NET Entity Data Model For SqlServer CE 4.0 CTP1

Introduction

In this tutorial we will create an ADO.NET Entity Data model for Sql Server CE 4.0 CTP1.As of now Visual Studio 2010 or Visual Web Developer 2010 Express do not have design time support for creating an Entity Data model for Sql server CE 4.0 CTP1. So we will be using some of the free tools available to create an Entity Data Model that can be used in Visual Studio.

Advantage of Using SqlServer CE 4.0 CTP1

1. No Database Installation Required
2. Database file is stored on disk.
3. Can be used in shared hosting.
4. Easy migration to SqlServer.

Since SqlServer CE 4.0 CTP1 is a go-live release. We can start coding now and can go-live when final version is released.

Background 

In this tutorial we will be using Microsoft Web Matrix and EdmGen2.exe a command-line tool to create Entity Data Model.

Project

First create a new web application project in visual studio named SQLCEWebApp and save it to location C:\SQLCEWebApp\ . Do not forget to uncheck the option Create Directory for solution.

Now we need to add sqlce 4.0 database to our project. Unfortunately visual studio does not provide support for creating Sql Ce 4.0 Database. So we will use Microsoft WebMatrix for creating Sql Ce 4.0 Database.

Create a new website using Microsoft WebMatrix. Goto WebMatrix-> Site From Template->Empty Site . Give a name for site(eg:SQLCE4.0) and click OK.

Now click the database tab in Web Matrix and Create a new Database and tables.

Next we need to copy the newly created database to the App_Data folder of our SQLCEWebApp project in Visual Studio 2010. In order to use SqlServer CE 4.0 database in our ASP.NET appliaction we need to add reference to two DLL's(System.Data.SqlServerCe.dll, System.Data.SqlServerCe.Entity.dll). These DLLs will be located in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private folder. Sql Server Compact Edition 4.0(Sql CE 4.0 CTP1) will be automatically installed when you install Web Matrix.

Next we need to create ADO.NET Entity Data Model for Sql CE 4.0 CTP1. But Visual Studio 2010 does not provide design time support for Creating Entity Data Model for Sql CE 4.0 databases. So we will use a Tool called EdmGen2.exe . This tool can be downloaded from http://code.msdn.microsoft.com/EdmGen2

Next Unzip the downloaded project file to C:\ EdmGen2 folder. Open the solution file(EdmGen2.sln) using Visual Studio 2010. Build the project to get EdmGen2.exe . This file will be located in C:\ EdmGen2\bin\Debug\ folder. Now we can use this command line tool to Generate the Entity Data Model for Sql CE 4.0 Database.

Open command prompt and Goto the folder containing EdmGen2.exe (eg: C:\ EdmGen2\bin\Debug\ ) . Type the following commands in the order below

• EdmGen2 /ModelGen " Data Source= C:\SQLCEWebApp\App_Data\Database1.sdf" System.Data.SqlServerCe.4.0 Database1

EdmGen2 /FromEdmx Database1.edmx

EdmGen2 /Validate Database1.edmx

EdmGen2 /ViewGen cs Database1.edmx

EdmGen2 /CodeGen cs Database1.edmx

The folllowing files will be generated for you in same folder (eg: C:\ EdmGen2\bin\Debug\ ). Navigate to this folder using Windows Explorer.

• Database1.edmx - This file is responsible for showing Table Design in Visual Studio
• Database1.cs - This is the class file that contains the properties and methods to interact with the database
• Database1.msl, Database1.ssdl - Metadata files

Next, Copy the Database1.edmx and Database1.cs to your SQLCEWebApp project using Add Existing Item option. A new Database1.Designer.cs file will be automatically created by Visual Studio for you underDatabase1.edmx . Replace all the code inside the Database1.Designer.cs file with code from our EdmGen2.exe generate Database1.cs file. Now delete the Database1.cs file. Otherwise we will get a compiler error. Then navigate to C:\SQLCEWebApp\obj\Debug\ using Windows Explorer create a new folder named "edmxResourcesToEmbed" without double qoutes. Now copy the 3 files(Database1.csdl,Database1.msl, Database1.ssdl ) to "edmxResourcesToEmbed" folder.

Come back to Visual Studio. Add the following connection string to web.config.

<add name="Database1Entities" connectionString="metadata=res://*/Database1.csdl|
res://*/Database1.ssdl|res://*/Database1.msl;provider=System.Data.SqlServerCe.4.0;
provider connection string=&quot;Data Source=|DataDirectory|\Database1.sdf;
Persist Security Info=True&quot;" providerName="System.Data.EntityClient"/>

Thats it!!!! Now you can start coding your LINQ queries againt Sql CE 4.0 the same way as querying againt Sql Server. Happy Coding!!!!!

Summary

It is very easy to create entity data model for Sql CE 4.0 using the WebMatrix and EdmGen2.exe.

In my next tutorial I will teach you how to use dynamic data with Sql CE 4.0.