Microsoft Internship

SQL Server CE

May 01, 2005
by David Cumps

Originally published on my old blog at cumps.be on May 1, 2005.

Instead of making a web service call each time certain data was needed, the data was stored in a local SQL Server CE database on the Pocket PC and retrieved when needed. This made it possible to display data quickly after having retrieved it once, while still giving the possibility to retrieve the latest data, and update the local cache with it as well.

To implement this, a Db class was used with the Singleton pattern to provide database access to the local SQL Server CE engine. A database on the Pocket PC is simply a file on the file system, MediaService.sdf in this case.

In the CheckDb method, the database was created in case it did not exist. This was done with normal SQL CREATE TABLE commands.

The following code made up the base functionality of the Db class:

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Collections;

namespace MediaService.Pocket {
  public class Db {
    private const String DB_NAME = "MediaService.sdf";
    private static Db instance = null;

    public Db() { }

    public static Db NewInstance() {
      lock(typeof(Db)) {
        if (instance == null) {
          instance = new Db();
        }
        return instance;
      }
    } /* NewInstance */

    private void CheckDB() {
      if (!File.Exists(DB_NAME)) {
        SqlCeConnection conn = null;
        SqlCeTransaction trans = null;
        SqlCeEngine engine = new SqlCeEngine("Data Source = " + DB_NAME);
        engine.CreateDatabase();
        try {
          conn = new SqlCeConnection("Data Source = " + DB_NAME);
          conn.Open();
          trans = conn.BeginTransaction();

          SqlCeCommand availableTable = conn.CreateCommand();
          availableTable.Transaction = trans;
          availableTable.CommandText = "CREATE TABLE Available(songId int, " +
                                       "songTitle nvarchar(200), songArtist nvarchar(200))";
          availableTable.ExecuteNonQuery();

          trans.Commit();
        } catch {
          trans.Rollback();
        } finally {
          if (conn != null && conn.State == ConnectionState.Open) {
            conn.Close();
          }
        }
      }
    } /* CheckDb */

Storing songs in the database was done every time results were returned from the web service with the following code:

private void OnGetSongs(IAsyncResult songsResult) {
  this.availableSongsCache = this.GetService().EndGetSongs(songsResult);
  Db.NewInstance().StoreSongs(this.availableSongsCache);

To store the songs, the table was first emptied, after which the new results were inserted all at once by using the following method:

public void StoreSongs(Song[] songs) {
  this.CheckDB();

  SqlCeConnection conn = null;
  SqlCeTransaction trans = null;

  try {
    conn = new SqlCeConnection("Data Source = " + DB_NAME);
    conn.Open();
    trans = conn.BeginTransaction();
    SqlCeCommand deleteSong = conn.CreateCommand();
    deleteSong.Transaction = trans;
    String deleteSql = "DELETE FROM Available";
    deleteSong.CommandText = deleteSql;
    deleteSong.ExecuteNonQuery();

    SqlCeCommand insertSong = conn.CreateCommand();
    String insertSql = "INSERT INTO Available(songId, songTitle, songArtist) " +
                       "VALUES (?, ?, ?)";
    insertSong.Transaction = trans;
    insertSong.CommandText = insertSql;

    foreach (Song song in songs) {
      insertSong.Parameters.Clear();
      insertSong.Parameters.Add("@songId", song.ID);
      insertSong.Parameters.Add("@songTitle", song.Title);
      insertSong.Parameters.Add("@songArtist", song.Artist);
      insertSong.ExecuteNonQuery();
    }
    trans.Commit();
  } catch (SqlCeException ex) {
    trans.Rollback();
    System.Windows.Forms.MessageBox.Show(FormatErrorMessage(ex));
  } finally {
    if (conn != null && conn.State == ConnectionState.Open) {
      conn.Close();
    }
  }
} /* StoreSongs */