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.

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.

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.

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.

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

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".

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.

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

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.

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

 Download this sample