Introducing Microsoft SQL Server Compact Edition (Part I)

Microsoft SQL Server Compact Edition is an embeded database that allows you to integrate it in your destkop and mobile applications. It can be used in a variety of scenarios, just some examples:

• a single-user application that still stores its data in the SQL database
• a local cache of a remote SQL Server database,
• a mobile application that synchronizes its local compact database with a remote database from time to time.

SQL Server Compact Edition takes about 1.5 MB on HDD and consumes about 5 MB of RAM. It’s free to use and free to distribute with your application.

The 3.5 version is shipped with Visual Studio 2008, however if you don’t have it, you can download it here.

In this part of the tutorial we will learn how to:

• Create a desktop application that interacts with SQL Server CE by using strongly-typed datasets
• Deploy a desktop application with embedded SQL Server Compact Edition
• Use SQL queries with SQL Server Compact Edition
• Use LINQ with SQL Server Compact Edition

Desktop Application and DataSets

Create a new project, choose Windows Forms Application. Then go to Project -> Add Component and choose Local Database.

We will be prompted to create a DataSet, let’s do that.

Then find the newly created database in the Solution Explorer, it must be called Database1.sdf depending on the name you gave it, of course, double click on it, Server Explorer gets expanded, let’s create a table.

It looks exactly like if we were working with a regular SQL database.

Now to Data Sources, right-click on the only available DataSet and choose Configure DataSet with Wizard.

Add the only table. Then right-click on it, select Details and drag the Products table on the form.

You must get the form like this:

Compile the project and test it. It works :-)!

Deployment

Ok, now we are going to make an installer for our project that will also include the data file and the files necessary for SQL Server Compact Edition. Let’s add the setup wizard project to our solution. It is located in Other Project Types -> Setup and Deployment -> Setup Wizard.

Then we select Setup for Windows application and Primary Output for our project. We should include the Database file – Database1.sdf and the files necessary for running SQL Server Compact Edition. Open C:\Program Files\Microsoft SQL Server Compact Edition\3.5 and select all the DLLs.

Click Finish then. Build the application and the setup project and try to install it, it must be working.

SQL Queries

In the previous example we didn’t write a single line of code. But in real project we have to. Let’s see, how we can use SQL with SQL Server Compact Edition. This time we are going to create a console application. Create or include the previously created SDF database. You have to add a reference to Compact Edition assembly that is called System.Data.SqlServerCe and of course you have to add the necessary namespace. Executing SQL queries with Compact Edition is no harder than doing the same with SQL Server, you just have to use the classes that have ‘Ce’ at the end.

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Data.SqlServerCe;

 namespace SqlCeConsole
 {
     class Program
     {
         static void Main(string[] args)
         {
             SqlCeConnection con = new SqlCeConnection("Data Source=Db.sdf");
             con.Open();

             SqlCeCommand comInsert = new SqlCeCommand
<script src="http://www.mikeborozdin.com/editors/tiny_mce3/themes/advanced/langs/en.js" type="text/javascript"></script>
("INSERT INTO Products(Title, Category) VALUES('Ferrari F40', 'Sport cars')", con);
             comInsert.ExecuteNonQuery();

             SqlCeCommand comS
<script src="http://www.mikeborozdin.com/editors/tiny_mce3/themes/advanced/langs/en.js" type="text/javascript"></script>
elect = new SqlCeCommand("SELECT * FROM Products", con);
             SqlCeDataReader reader = comSelect.ExecuteReader();

             while (reader.Read())
             {
                 Console.WriteLine("{0} {1}", reader["Title"], reader["Category"]);
             }

             con.Close();
         }
     }
 }

LINQ

You can use LINQ with SQL Server CE, as well, the main difference is that you cannot use the visual designer which generates an error when dragging CE tables, however you can use a command line utilily called SqlMetal, just type:

SqlMetal Database1.sdf /dbml:Database1.dbml

Then include the generated file in your project and Visual Studio will easly pick it up, then you can write LINQ queries as usual.

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Data.SqlServerCe;

 namespace SqlCeConsole
 {
     class Program
     {
         static void Main(string[] args)
         {
             Db db = new Db(@"Data source=Db.sdf");

             var result = from p in db.Products
                          select p;

             foreach (var product in result)
             {
                 Console.WriteLine(product.Title);
             }
         }
     }
 }

Next Part

In the next part of the tutorial I'll show how to synchronize the data between SQL Server Compact Edition and SQL Server.

Resources 

Official Microsoft SQL Server Compact Edition Homepage

Developer resources

Compact Edition Books Online