Click here to Skip to main content
12,689,801 members (25,709 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

15.5K views
5 bookmarked
Posted

Generating a SQL Server CE Database Schema from a SQL Server Database using Entity Framework

, 28 Jun 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
How to generate a SQL Server CE database schema from a SQL Server database using Entity Framework

In a previous entry, I described how to programmatically create (& destroy) a SQL CE dB for integration testing using NUnit. Since getting that working, I ran into a couple of other problems which I've more or less solved so I thought I'd write those up. To begin with though, this is a prequel post describing how to obtain the SQL script to create the SQL CE dB.

If you happen to be working exclusively with CE, then you'll already have your schema file. In my case, I'm using SQLExpress and as this is experimental work, I created my dB by hand. However, using the EF, it's pretty easy to obtain the schema and have the EF wizard generate the CE schema. This is important as there are differences in the dialect of SQL used by SQL Express and SQL CE and it's easier to have a tool handle those, though it doesn't do all of them.

The basic flow is to generate an EF model (EDMX) file from the existing SQL Express database and then use the 'Generate database from model' functionality. It is at this point that the target SQL dB can be chosen, i.e., SQL Server, SQL Server CE or some others.

To create a model requires adding a 'New Item' of type 'ADO.NET Entity Data Model' to a VS project so first a new dummy project needs creating. This is where it gets a little complicated as not any type of project will do. I'm working with CE 4 and require a schema for that version of the dB (though creating one for 3.5 works but I like to have things as close to ideal as possible). Due to this constraint, it is necessary to chose a Web type project as for some reason the VS2010 integration provided by EF only supports the generation of CE 4 dBs for Web projects. If a simple C# Windows Console project is selected, then you're limited to CE 3.5. Thus, the simplest project type is the 'ASP.NET Empty Web Application' as shown below:

Having done this, next add a new item of type ADO.NET Entity Data Model as below. NOTE: The project will have to reference the Entity Framework assemblies. The easiest way to do this (and the one most people are probably using) is to use the NuGet package.

Then follow the wizard.

Selecting "Generate from database":

Choose your SQLExpress (or SQL Server) dB but uncheck the "Save entity connection settings in Web.Config as:" as we're converting to SQLCE so want to minimize anything related to other types of SQL Server.

Finally, select the SQL elements you require. In this example, only the existing tables were selected. As this is generating the EF model from an existing database, no SQL file is generated, just the model for which the diagram is shown, i.e.:


The next phase is to generate the SQL from the model (which was generated from the hand crafted db) but to make sure the SQL that's generated is compliant with SQL CE.

To generate the schema, right click and select "Generate model from database..."

This brings up the "Generate database" wizard which is very similar to the previously used "Entity Data Model" wizard used to create the model. From here, choose the "New Connection" option which pops up another set of dialogs. On the first, choose the type of data source as "Microsoft SQL Server Compact 4.0".

Clicking on continue then leads to the next dialog where you need to create a dB.

Ok-ing this leads back to the "Generate database wizard".

This time, check the "Save entity connection settings in Web.Config" checkbox. This information will be useful later (to be covered in a different post). Clicking "Next", the SQL is generated and present in the wizard.

This can be copied & pasted directly from here or pressing "Finish" will save the SQL to the file indicated at the top of the dialog box. This file is added to the project. The following prompt will appear when "Finish" is pressed.

This doesn't really matter as this is a throw away project but having the updated schemas may be useful so go with "Yes".

The SQL can now be used to configure an empty SQL CE 4.0 database. The easiest way is to open the SQL file and right-click selecting the "Execute SQL" menu item.

This brings up the SQL Server dialog from which if "New Database" is selected, a CE 4 one can be specified.

Having specified a location and pressed "Ok", the SQL script is executed. As can be seen below, this is not without errors. However, this isn't anything to worry about as the errors are to do with dropping tables and indices that currently don't exist as it's a newly created dB. Performing the same steps but missing out the creation of the dB file as it already exists sees the SQL script execute flawlessly.

The final picture shows the newly created database in VS2010's Server Explorer demonstrating that the tables were indeed created.

The basis for this post is my experimentation on using NUnit to programmatically test some dB based functionality. If a single instance of a database suffices for all your tests and you can execute the SQL by hand as above, then you can follow these steps. In many cases, I want to a fresh database per test so I need to automate the running of the SQL Script combined with the creation and destruction of the underlying database. The creation and deletion aspect were covered in a previous post but the next step will have to wait until a later one.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

PeteBarber
Team Leader
United Kingdom United Kingdom
My day job is mostly working in C++ with a bit of C#. I write a fair amount of command line based tools and really wish they could have a GUI front-end to them hence why I spend my spare time working with WPF.

I started a blog few years back but didn't do a lot with it. I've started describing some of the interesting programming things I come across on it. Please take a look.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionDB is being created without default value Pin
JoeWazen18-Nov-13 3:18
memberJoeWazen18-Nov-13 3:18 
QuestionRe: DB is being created without default value Pin
JoeWazen19-Nov-13 23:46
memberJoeWazen19-Nov-13 23:46 
Suggestion[My vote of 2] Too complicated Pin
ErkEJ1-Jul-12 3:54
memberErkEJ1-Jul-12 3:54 
GeneralRe: [My vote of 2] Too complicated Pin
PeteBarber1-Jul-12 5:15
memberPeteBarber1-Jul-12 5:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170113.4 | Last Updated 28 Jun 2012
Article Copyright 2012 by PeteBarber
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid