SQLCE Database

The C# programming language

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. Let's look at the steps required to create the SQLCE database.

Note:Here is how this will look on your screen.

Creating an SQLCE database in Visual Studio

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.

Steps

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.

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

String type

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

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 in Visual Studio.

Double click on your ".sdf" file, which looks like a can. 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.

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

Adding columns to SQLCE table in Visual Studio

Insert values

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

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

Discussion

Note

Many things could go wrong with SQLCE. 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.

Note: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'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.


C#: Data