Problem

Make and maintain SQLCE databases that store data using C# and .NET 3.5. We need the database to be created and things to work properly. We already know some things about databases, but the exact steps to take in Visual Studio 2008 are confusing. We want to follow instructions and accomplish our goal of working with SQLCE 3.5.

Solution

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 C#. Let's look at the steps required to create the SQLCE database. Here is how this will look on your screen.

1. Get 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.
2. New connection
   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.
3. 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.
4. Test connection
   Use the Test connection button to make sure your new database works properly. Click on Test Connection in lower left--it should succeed.
5. 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:

{
    // 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.

{
    // 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.

1. Double-click the canister
   Double click on your .sdf file (it looks like a can). On the left, Server Explorer will open.
2. Open the .sdf tree
   Click to expand your .sdf file (dataname.sdf, in my case). Right click on table, then Create Table. It will look as follows.

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 5 into a new row. We use all of our previous code, but now create an SqlCeCommand variable called com. Let's take a look.

// 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 C#. Use a SELECT query, now, which you will be familiar with. We want the column named Something (your column name will be different).

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

Conclusion

Use Visual Studio 2008's interface together with the file system (read about file handling), 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.

Ads by The Lounge