I’m writing this article because a small project I’ve been working on required me to look for information on using SQL Server CE with C# 3.5. I couldn’t find very many useful articles on the topic so I’ve decided to write my own and summarise some of the useful bits I found.

For starters, why use SQL Server CE? I think it’s an easy way of implementing a high supported and functional database solution for various types of applications although this article will focus on using SQL Server CE in a small Windows forms application. To begin with you’ll need to download SQL Server CE from the Microsoft website.

In the past I think I’ve gone a bit overboard with the length of my posts so I’m going to attempt to keep this one short. The application I used this code in is pretty well split up and uses a number of different layers but you don’t need to go this far to use SQL CE with your applications. The code below is a summary only and covers the key things I think you need to know – feel free to ask any questions you need to.

I’m also aware that the code below *will* need modification before you can just throw it into your own applications but, as mentioned above, please post questions on this post if there is anything you need clarification on.

Creating the database

Connecting to SQL Server CE:

string connStr = String.Format("Data Source = C:tempdatabase.sdf");
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
  engine.CreateDatabase();
}

SQL query with no result

Running a SQL command that doesn’t return a result set (note that this is, in some cases, where you would execute SQL to create the database structure after using the code above to create the database file itself).

using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
  using (SqlCeCommand cmd = new SqlCeCommand())
  {
     cmd.Connection = conn;
     conn.Open();

     cmd.CommandText = "SELECT * FROM table_name";
  }
}

Getting a record from the database

using (var conn = new SqlCeConnection(connStr))
{
  conn.Open();

  string query = "SELECT * FROM Applications WHERE id = @Id";

  using (var cmd = new SqlCeCommand(query, conn))
  {
     // appId is a variable passed to the method that uses this code sample
     cmd.Parameters.AddWithValue("@Id", appId);
     var reader = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
     if (reader.HasRows)
     {
        while (reader.Read())
        {
           int Id = Convert.ToInt32(reader["Id"]);
           // more code here to deal with other columns in the record that was selected from the database, if any
        }
     }
  }
}

That’s about as basic as it gets but I’m also in the process of writing another article about a basic application that uses this code in a “real-world” situation.