SQLCE 3.5 Database Tutorial

SQLCE is a client-side database.

It can store data using the C# language and .NET Framework 3.5. You already know some things about databases, but the exact steps to take in Visual Studio are confusing.

Tutorial. SQLCE is a powerful client-side database, and it is fairly easy to use. We will make a client-side SQL database hosted on the desktop computer. It will store simple data and is written in the C# language.

Note: Let's look at the steps required to create the SQLCE database. Here is how this will look on your screen.

Getting started. The top, background window is the first step, and the front two windows follow. First, click on Data menu and then Add New Data Source. Click next and then click on New Connection to create a new database.

Then: Set the data source as Microsoft SQL Server Compact Edition 3.5. Leave the data source on your computer.

Connection properties. Click on Create New Connection properties. Type in a filename at the end of the path, such as dataname.sdf. OK the create dialog. Use the Test connection button to make sure your new database works.

Copy: Click next and then yes to copy the database to your project. Let Visual Studio save the connection string and click Finish.

Using statements. What happens next? Click on the References folder, and you will see the System.Data.SqlServerCe. That is a good thing. Now, we need to add the using statements to the top of our C# file. Here are the two you need.

Using statements: C#

using System.Data;
using System.Data.SqlServerCe; // Add this.

Make connection. To make a connection, you must use the connection string that was placed in the Settings file. Visual Studio has already generated the connection string you need. Let's look at how to easily access it.

Next: Open the C# class you are using. We can retrieve the connection string with this code.

Fragment that gets connection string: C#

{
    // Retrieve the connection string from the settings file.
    // Your connection string name will end in "ConnectionString"
    // So it could be coolConnectionString or something like that.
    string conString = Properties.Settings.Default.datanameConnectionString;
}

Result
    Connection string is acquired.

Connection. The string conString is now our connection string. If you are not familiar with the Settings.settings file, please see my article about Settings.settings. Next, we set up the connection itself.

Settings.settings

Fragment that opens connection: C#

{
    // Retrieve the connection string from the settings file.
    string conString = Properties.Settings.Default.datanameConnectionString;

    // Open the connection using the connection string.
    using (SqlCeConnection con = new SqlCeConnection(conString))
    {
	con.Open();
    }
}

Result
    Connection is opened.

Create table. You can create a table by using the Visual Studio interface. There are more steps to make a table. Here's how to make a table with Visual Studio. Right after the steps, I show a screenshot with the relevant user interface.

Double click on your ".sdf" file. On the left, Server Explorer will open. Click to expand the file, which in my case is named dataname.sdf. Right click on table, then Create Table. It will look similar to part of the following screenshot.

New table dialog. Now, there is the New Table dialog box. Click in the name box and type MyTable or whatever you need your table to be named. Next, I want to share the secret that baffled me for some time when learning this.

Add columns. You can add columns to a table by clicking in the dialog. To create columns on the table, you need to type directly into the grid in the New Table window. The white space there is for you to click on and type into.

Insert. You can insert values into a table using INSERT in C# code. Let's say we want to insert the number five into a new row. We use all of our previous code, but now create an SqlCeCommand variable called com.

Fragment that executes query: C#

// Retrieve the connection string from the settings file.
string conString = Properties.Settings.Default.datanameConnectionString;

// Open the connection using the connection string.
using (SqlCeConnection con = new SqlCeConnection(conString))
{
    con.Open();

    // Insert into the SqlCe table. ExecuteNonQuery is best for inserts.
    int num = 5;
    using (SqlCeCommand com = new SqlCeCommand("INSERT INTO MyTable VALUES(@num)", con))
    {
	com.Parameters.AddWithValue("@num", num);
	com.ExecuteNonQuery();
    }
}

Result
    Values are put into the table.

Read data. You can read data from the database using a SELECT command. At this point, we have a database, data in the table, and a connection string. Let's get that data out of the table and back into your C# program.

Tip: Use a SELECT query, now, which you will be familiar with. We want the column named 'Something'. Your column name will be different.

Code that reads integer from database: C#

// Open the same connection with the same connection string.
using (SqlCeConnection con = new SqlCeConnection(conString))
{
    con.Open();
    // Read in all values in the table.
    using (SqlCeCommand com = new SqlCeCommand("SELECT Something FROM MyTable", con))
    {
	SqlCeDataReader reader = com.ExecuteReader();
	while (reader.Read())
	{
	    int num = reader.GetInt32(0);
	    Console.WriteLine(num);
	}
    }
}

Result
    The number is written to the screen.

Copy. Visual Studio can sometimes copy SQLCE databases when you don't want it to, when you build your C# project. So, click on the sdf file in the Solution Explorer and select Copy if newer.

Visual Studio

Performance. The memory usage of embedded databases is important. We compare that of Microsoft's SQLCE and the open-source SQLite. The version of your program using SQL Server CE may not be fast enough. We look at how these two databases perform.

Comparison of SQLCE and SQLite

SQLCE program:  8.6 MB
SQLite program: 6.2 MB [smaller]

Notes: Figures are entire Windows Forms memory usage. The SQLCE version is 3.5. SQLite used 2.4 MB less memory.

You might be able to switch from SQLCE to SQLite for a performance advantage. The numbers here show how my program's memory usage changed, first with SQLCE and then SQLite. I was unable to accurately measure performance due to specifics.

SQLite uses less memory when it is loaded into memory than SQLCE. This decreased footprint would improve spatial locality. It could help other parts of your program stay in memory longer and be faster.

And: SQlite is compatible with Mac OS X and Linux, unlike SQLCE. Your Mac users could use the same databases on their systems.

However: Mac users will not have the .NET Framework installed. But this is not important in Windows-only offices.

Summary. Here we looked at using SQLCE databases. Use Visual Studio's interface together with the file system, the database, the assembly, and your own custom code. Figuring out all the steps was hard for me and it may be hard for others.

Finally: SQLCE can help your application perform better and can be used as a client-side cache of your central database.

Related Links