SQLCE 3.5 Database Tutorial

You want to develop SQLCE databases that 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 2008 are confusing. Here are some instructions you can follow to start working with SQLCE 3.5, using the C# programming language.

SQLCE database introduction

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. 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. 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 properly. Click on Test Connection in lower left—it should succeed.

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

Add using statements

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

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

How can I make a connection?

You must use the connection string that was placed in the Settings file by Visual Studio. Visual Studio has already generated the connection string you need. Let's look at how to access it easily. Open your C# class you are putting this stuff in. Next, 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;
}

Use the connection object

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, let's setup the connection itself.

(See Settings.settings in Visual Studio.)

--- 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();
    }
}

How do I 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 2008. Right after the steps, I show a screenshot with the relevant user interface in Visual Studio.

Double-click the canister. Double click on your .sdf file, which looks like a can. On the left, Server Explorer will open. Click to expand your .sdf 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 quite some time when learning this.

How can I 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. This is a confusing interface for me, but I hope to save you the same difficulty.

How can I insert values into a table?

By 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. Let's take a look.

--- 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();
    }
}

How can I read data from the database?

Using a SELECT command. Finally, 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. 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);
        }
    }
}

What could go wrong?

Many things. One thing I want to mention here before I wind things up. 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. There may be crashes and other weirdness with Visual Studio, as well.

Summary

Here we looked at using SQLCE databases in Visual Studio and the C# programming language. Use Visual Studio 2008'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.

(Do not copy this page.)