The Wire Making Smartphones Smarter

Using SQLite in your WP7 app

There's been a lot of focus on the missing features of the new Windows Phone 7 devices.  Which is too bad since there are so many great features.  Cut & Paste?  Nice to have, but how often do you actually use it?  Fortunately, we won't have to wait too long for this or a slew of other features/improvements which Microsoft says will be arriving early 2011.

From a developer's perspective, there are many other missing features that may not be obvious or even important from a consumer perspective.  Fortunately, unlike consumers who are stuck waiting for Microsoft to release updates, developers can build their own systems and/or workarounds.

One of the biggest surprises to us was the lack of native database support.  Luckily there are several developers and teams working to fill this gap.  People are approaching it from multiple angles and solutions (isolated storage based tables, generic objects floating around in code, etc.).  Just do a quick and you'll see what I'm talking about.

Reading through these discussions can be interesting and some of these solutions may fit the device well.  But what about those of us that just want to focus on business logic and have a database that just works - and works like we expect?  Or maybe you're like us and already have some databases that are being used on other platforms with standard database support and don't want to rewrite or port your solution?

We already had some SQLite databases.  SQLite is supported on the iOS and we had hoped to just move those same files into our WP7 versions.  Thanks to at least Dan Ciprian Ardelean we can!  He provides some samples, but we'll try and expand on those to help you get up and running quickly and easily...

Dan posted some things a while back that included some source code and his Community C# SQLite project.  Go check it out here and be sure to download his sample code.  The Community C# SQLite project doesn't do everything yet and there is still some work being done to address performance (use transactions), but it's pretty good (and FREE).  For simple databases that are not dependent upon blob objects (bad idea in mobile apps anyway), it gets the job done!

UPDATE: Dan has put his SQLite Client on CodePlex and has made some great updates. Booleans, Blobs, and transactions are now supported and a good sample is included in the download. Currently there seems to be a problem with reading an existing database out of IsolatedStorage (the method we are demonstrating below). I have posted a discussion on CodePlex about the issue. Included in the discussion is the one line of code you will need to change before compiling the dll to make everything work.

pFile.fs = new IsolatedStorageFileStream(zConverted,
                                         dwCreationDisposition,
                                         dwDesiredAccess,
                                         dwShareMode,
                                         pFile.store);

pFile.fs = new IsolatedStorageFileStream(zConverted,
                                         FileMode.OpenOrCreate,
                                         dwDesiredAccess,
                                         dwShareMode,
                                         pFile.store);

As a side note, we are big fans of the free MVVM Light Toolkit and you will see it in the sample code below, but it is NOT required to work with SQLite.  However, you should be using it!  For managing/creating our databases we also use the free SQLite Manager extension for FireFox.  We think it's strange that it's included in FireFox, but it's the best SQLite frontend we've found (plus it's cross platform for when we are working on iOS apps)!

Once you've downloaded and compiled the Community C# SQLite project, open your app and add a reference to the Community.CsharpSqlite.WP.dll (from the bin directory of the compiled project).  Just right-click on your References folder in Solution Explorer choose Add Reference then switch to the browse tab to find it.

Add your database file to your project and set it's Build Action to Resource.  Don't worry, you can still edit this database using your favorite frontend directly from your solution folder.

We won't be discussing databinding directly since there are plenty of good posts about it, but we will show you how to select from the database into a collection of objects which can be databound (made even easier when using the MVVM Light Toolkit).  One of the things we use is a custom DBHelper class.  It's not yet been optimized or made generic, but it works and you can adjust it for your own projects pretty easily.  Just add a class to your project called DBHelper.cs and put the following at the top:

using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using SQLiteClient;
using System.Linq;
using System.IO.IsolatedStorage;
using System.Collections.Generic;
using System.Collections.ObjectModel;

This class has two fields:

public class DBHelper
{
  private String _dbName;
  private SQLiteConnection db = null;

There is also a parameterized constructor that takes your database's filename, and it makes sure your database is in IsolatedStorage (more on that below):

  public DBHelper(String dbName)
  {
    IsolatedStorageFile store =
      IsolatedStorageFile.GetUserStoreForApplication();
    if (!store.FileExists(dbName))
    {
      CopyFromContentToStorage(dbName);
    }
    _dbName = dbName;
   }

There are two helper methods Open and Close to make things easier:

  private void Open()
  {
    if (db == null)
    {
      db = new SQLiteConnection(_dbName);
      db.Open();
    }
  }
 
  private void Close()
  {
    if (db != null)
    {
      db.Dispose();
      db = null;
    }
  }

And then there are two generic select methods.  You'll notice that the SelectList method returns a List<T> object and is the default behavior of the SQLiteClient, but for MVVM and databinding an ObservableCollection<T> is much more useful so that is provided as well:

  public List<T> SelectList<T>(String statement) where T : new()
  {
    Open();
    SQLiteCommand cmd = db.CreateCommand(statement);
    var lst = cmd.ExecuteQuery<T>();
    return lst.ToList();
  }
  public ObservableCollection<T> SelectObservableCollection<T>(String statement)
      where T : new()
  {
    List<T> lst = SelectList<T>(statement);
    ObservableCollection<T> oc = new ObservableCollection<T>();
    foreach (T item in lst)
    {
      oc.Add(item);
    }
    return oc;
  }

Finally there are two methods included to make things easier for you.  The SQLiteClient assumes your file is located in isolatedstorage and your app will bomb if it isn't.  So the first helper method, CopyFromContentToStorage, puts it there.  This allows you to fully design your database with or without data outside of your app and then copy it to the file system on first usage. Be sure to replace YOURASSEMBLY with the actual name of your Assembly. The second method, CopyStream, is a generic method we got from somewhere (if it was you, let us know!) and it just copies a Stream to an IsolatedStorageFileStream:

  private void CopyFromContentToStorage(String dbName)
  {
    IsolatedStorageFile store =
        IsolatedStorageFile.GetUserStoreForApplication();
    System.IO.Stream src =
        Application.GetResourceStream(
          new Uri(@"/YOURASSEMBLY;component/" + dbName,
                  UriKind.Relative)).Stream;
    IsolatedStorageFileStream dest =
        new IsolatedStorageFileStream(dbName,
          System.IO.FileMode.OpenOrCreate,
          System.IO.FileAccess.Write, store);
    src.Position = 0;
    CopyStream(src, dest);
    dest.Flush();
    dest.Close();
    src.Close();
    dest.Dispose();
  }
 
  private static void CopyStream(System.IO.Stream input,
                                 IsolatedStorageFileStream output)
  {
    byte[] buffer = new byte[32768];
    long TempPos = input.Position;
    int readCount;
    do
    {
      readCount = input.Read(buffer, 0, buffer.Length);
      if (readCount > 0)
      {
        output.Write(buffer, 0, readCount);
      }
    } while (readCount > 0);
    input.Position = TempPos;
  }
}

So, what does this class do?  The easiest way to illustrate that is to show how we use it!

First put the following in your App.xaml.cs file somewhere in your App Class:
(This doesn't have to go here, but it makes it a lot easier to select from your database throughout your app!)

private DBHelper _db;
public DBHelper db
{
  get
  {
    if (_db == null)
      _db = new DBHelper("DATABASE.s3db");
    return _db;
  }
}

Be sure to replace the DATABASE.s3db with the filename of the database you added to your project. To select from your database is pretty simple but the SQLite Client expects you to give it an object with properties that match the column names you are selecting.  For this sample we are using a very simple object called CustomerEntry that has all of two properties:

public class CustomerEntry
{
  public int ID { get; set; }
  public string Name { get; set; }
}

This is a simple object, but more complex ones could easily be used.  We then populate these objects from our database using a simple SQL select statement (From our MainViewModel if you're playing along with MVVM).  Our ObservableCollection property looks like this for use in MVVM, but you could just have a simple object of type List<CustomerEntry> or OberservableCollection<CustomerEntry>:
(Be sure you have a using System.Collections.ObjectModel at the top in order to use ObservableCollection)

  /// <summary>
  /// The <see cref="CustomerEntries" /> property's name.
  /// <summary>
  public const string CustomerEntriesPropertyName = "CustomerEntries";
 
  private ObservableCollection<CustomerEntry> _customerEntries = null;
 
  /// <summary>
  /// Gets the CustomerEntries property.
  /// Changes to that property's value raise the PropertyChanged event.
  /// <summary>
  public ObservableCollection<CustomerEntry> CustomerEntries
  {
    get
    {
      return _customerEntries;
    }
    set
    {
      if (_customerEntries == value)
      {
        return;
      }
      RaisePropertyChanged(CustomerEntriesPropertyName);
    }
  }

Using a standard SQL statement you can perform your select like this:

  public MainViewModel()
  {
    if (IsInDesignMode)
    {
      // Code runs in Blend --> create design time data.
      _customerEntries = GenerateCustomerEntries();
    }
    else
    {
      // Code runs "for real"
      _customerEntries =
          (Application.Current as App).db
          .SelectObservableCollection<CustomerEntry>(
            "SELECT ID, Name
               FROM customers
               WHERE Active=1
               ORDER BY Name ASC");
    }
  }

A couple of things to note is that this won't work in design time (expression blend) since you are accessing IsolatedStorage.  Fortunately, the MVVM Light toolkit provides this convienent syntax to allow you to hardcode some entries when in design mode.  The key code above is the last line:

_customerEntries =
  (Application.Current as App).db
  .SelectObservableCollection<CustomerEntry>(
    "SELECT ID, Name
       FROM customers
       WHERE Active=1
       ORDER BY Name ASC");

Notice we are accessing that DBHelper property from the App class and just passing the object type and the sql statement, and suddenly we have a collection of .NET objects populated with data from our database!

The DBHelper class could easily be added to to help facilitate database schema commands or update, insert or delete commands.  You could also use the SQLiteClient objects directly, but we find simplifying everything into a helper class is helpful!

What do you do?  Let us know in the comments or tell us if you have ways of tweeking our code to make it better!