Instead of making a webservice call each time certain data was needed, the data was stored in the SqlCe database on the Pocket PC, to retrieve when needed. This allowed quickly displaying data 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 SqlCe 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 queries defining Create Table commands.

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

[csharp]
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();
SqlCeTransaction 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 */
[/csharp]

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

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

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

[csharp]
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 */
[/csharp]

Retrieving the songs can be done exactly as with the regular SqlClient classes.