The MSDN documentation gives a walkthrough of how to update a local database application on the Windows Phone over time. The documentation gives quite a bit of detail but seems to miss a likely scenario. So let’s investigate the process step-by-step.

Initial Version

We are creating the first version of our application and decide that we need a User database table with the following columns: Name (primary key), LastAccessedDate

So we create a User class as follows:

[Table]
public class User : INotifyPropertyChanged, INotifyPropertyChanging
{
  private string name;
  private DateTime? lastAccessed;

  [Column(IsPrimaryKey = true, CanBeNull = false, AutoSync = AutoSync.OnInsert)]
  public string Name
  {
  get { return name; }
  set
    {
      NotifyPropertyChanging("Name");
      name = value;
      NotifyPropertyChanged("Name");
    }
  }

  [Column]
  public DateTime? LastAccessed
  {
    get { return lastAccessed; }
    set
    {
      NotifyPropertyChanging("LastAccessed");
      lastAccessed = value;
      NotifyPropertyChanged("LastAccessed");
    }
  }

  // Version column aids update performance.
  [Column(IsVersion = true)]
  private Binary version;

  // INotifyPropertyChanged Members
  // INotifyPropertyChanging Members
}

In App.xaml.cs, you would use the following code to create your database:

using (MyDataContext db = new MyDataContext(DBConnectionString))
{
  // Create the database if it does not exist.
  if (db.DatabaseExists() == false)
  {
    // Create the local database.
    db.CreateDatabase();
  }
}

That is all the code that is needed for the first version as there are no update scenarios. You deploy your app to the Marketplace and hundreds of customers download it. We will call this group of people that downloaded it: Group A.

It is important to note that when a database is created for the first time (unless specific) it has a DatabaseSchemaVersion of 0.

Version 2

Let’s say that for the next version of your app, you need to change the table schema for User to add a Theme id as now your app lets the user choose which theme to use.

This requires an update to the User class:

//added in DB_VERSION 2
[Column(CanBeNull = true)]
public int? ThemeId

This column must allow nulls so that it can be added to existing rows in User table.

The code is App.xaml.cs will need to change to take into account two scenarios:

· Those in Group A upgrading from the first release to version 2
· Those downloading the app for the first time at version 2 (we will refer to them as Group B)

At the top of App.xaml.cs, add a DB_VERSION field to keep track of the current version of the database changes:

private static int DB_VERSION = 2;

The replace the previous code as follows:

using (MyDataContext db = new MyDataContext(DBConnectionString))
{
  if (db.DatabaseExists() == false)
  {
    db.CreateDatabase();
    DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
    dbUpdater.DatabaseSchemaVersion = DB_VERSION;
    dbUpdater.Execute();
  }
  else
  {
    DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();

    if (dbUpdater.DatabaseSchemaVersion < DB_VERSION)
    {
      if (dbUpdater.DatabaseSchemaVersion < 2)
      {
        //added in version 2
        dbUpdater.AddColumn<User>("ThemeId");
      }

    dbUpdater.DatabaseSchemaVersion = DB_VERSION;
    dbUpdater.Execute();
    }
  }
}

We release this new version of the app to the Marketplace and those in Group A update their application. When they run it for the first time, they will fall into the else statement as the database already exists. The DatabaseSchemaVersion will be 0 which is less than our current version of 2, so it will go into the code that will add the ThemeId column to the existing User table. The DatabaseSchemaVersion will then be updated to 2 so that the update code will no longer run.

Now let’s look at Group B who are downloading the app for the first time when it was at version 2. For them, no database will exist so one will be created. It is important to point out that when the database is created it will include the ThemeId column on User since it is part of the User object. The DatabaseSchemaVersion is now updated so that on the next run of the app when the code hits the else block it will not try to add the column again and throw an exception.

Version 3

Let’s do a third version of the application and add a birthdate column to User:

//added in DB_VERSION 3
[Column(CanBeNull = true)]
public DateTime? DOB

In App.xaml.cs, change the version to 3:

private static int DB_VERSION = 3;

Now add a new if block for the new column:

using (MyDataContext db = new MyDataContext(DBConnectionString))
{
  if (db.DatabaseExists() == false)
  {
    db.CreateDatabase();
    DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
    dbUpdater.DatabaseSchemaVersion = DB_VERSION;
    dbUpdater.Execute();
  }
  else
  {
    DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
    if (dbUpdater.DatabaseSchemaVersion < DB_VERSION)
    {
      if (dbUpdater.DatabaseSchemaVersion < 2)
      {
        //added in version 2
        dbUpdater.AddColumn<User>("ThemeId");
      }

      if (dbUpdater.DatabaseSchemaVersion < 3)
      {
        //added in version 3
        dbUpdater.AddColumn<User>("DOB");
      }

      dbUpdater.DatabaseSchemaVersion = DB_VERSION;
      dbUpdater.Execute();
    }
  }
}

The above code now correctly handles the following scenarios:

· Those installing version 3 of the app as the first version they installed (new database will be created)
· Those with version 2 that are updating to version 3 (upgrade from previous version)
· Those at version 1 that didn’t update to version 2 but are now updating to version 3 (skip intermediate versions)

It should be easy to see how additional versions will require an increment to the DB_VERSION value and the addition of an if block to handle those changes added in a specific version.