Coding Tips & Tricks


Home > SQL CE

A Simple Guide to SQL Compact 4

There has been quite a lot of interest building up recently around SQL Compact 4. Now, if you are not familiar with SQL compact, it's basically a database engine that is free and totally embedded. You don't need to install a database anywhere on your server. Not only that, but it works with existing .NET based data API's (Entity Framework, NHibernate). Another great advantage of using a SQL CE database is that it is totally file based and you can store your database files within the \App_data folder of your web application. So, for someone like me who is running a simple blog that doesn't really require a massive DB, this is really ideal. If you use shared hosting services and you need to use SQL Compact - no problem, it runs under medium trust which means it works perfectly.

I decided to play around with a few sample exercises using SQL CE, and these are the steps that I took. Firstly, download the preview from this link. Even if you are running your application under .NET 3.5 this will work perfectly. Next, we need to create a database using SQL server:

1.In SQL Server Management Studio, open Object Explorer.

Note: You can use SQL Server Management Studio or SQL Server Management Studio Express to create a database. If you need to, you can install SQL Server Management Studio Express from Microsoft Download Center.

In Object Explorer, click Connect, and then choose SQL Server Compact.

Create SQL Compact

2.In the Connect to Server dialog box, select from the Database file drop-down list.

3.In the Create New SQL Server Compact Database dialog box, type a file path and file name for the new database file. You can optionally select the default collation, case sensitivity, and choose whether you want to encrypt or password-protect the database. If you choose to encrypt or password-protect the database, type a password, and then click OK.

SQL Compact

4.Click Connect to connect to the new SQL Server Compact database. The database is now displayed in Object Explorer.

UPDATED
At present you can only create a SQL 3.5 Compact database using SQL Management Studio. In order to create a SQL 4 Compact Database you need to use the SQL CE toolbox. Full instructions are available on the site.

Okay, so now we have created our database let's add a table to it. Right click on tables and choose new table. Let's name ours blog. After that, we are going to add some fields to the DB.

Create new table

Now that the database is complete, you need to copy the .sdf file from it's file location and paste in under the \App_Data folder on your website.

Copy sdf file

In order to start coding, we need to add a reference to the SQL Server Compact 4.0 libraries.

Copy Dlls

For this example I am going to use Entity Framework as the data API. Next, add a new entity data model to our project. Right click on the solution explorer and choose add => new item => ADO.net entity data model, let's call ours "CompactRepository".

Generate New

Next, choose generate from database. Then select your database from the drop down list. Then, choose all the tables / stored procedures you want to include in your model. Finally click finish.

Let's start writing some code. Using EF makes our life a lot easier and now we can start coding against our database. We can start by adding an entry to our blog table.

Insert Record

And then writing code to retrieve a record is just as simple.

Retrieve Record

One thing that I noticed while working on this is that it sometimes threw an exception - "SQL Server Compact Edition is not intended for ASP.NET development". I'm not sure what is causing this, but it could be down to this being a CT edition. If you add the following line of code to your app - - it should all be sorted. Depending on your solution structure, you might want to put it in the global.asax.

Global asax

For some further reading and information on SQL Compact 4, check out this link


Download Download this sample








Comments

ErikEJ - 9/7/2010
Hi Dean, the reason why you are getting errors is that you are not using version 4.0 on your website! In addition, you cannot create a 4.0 database using Managment Studio, you must use WebMatrix or http://sqlcetoolbox.codeplex.com (standalone)

Dean - 9/7/2010
@Erik Thanks for the advice, for some reason I thought that SQL management studio creates a 4.0 database. I will update accordingly.

Isaac Abraham - 12/13/2010
Scott Hanselman has a good post on VS2010 SP1 and links to a patch which gives built-in support for SQL Compact 4 (not the Codeplex add-in). I couldn't get the standard EF4 to work with it tho... http://www.hanselman.com/blog/VisualStudioExplosionVS2010SP1BETAReleasedAndContext.aspx

Dean Hume - 12/14/2010
@Isaac Abraham Thanks for that. I will definitely check it out!


Add your comment

300 Characters left


Please fill this in to confirm that you are human