This is the second tips’n’tricks post talking about the stuff I discover while working on the 2Day todo-list application for Windows Phone. In my last blog post I talked about random ArgumentException. This time, I’m talking about the way to handle version management in SQL database in a Windows Phone 7 app.

As soon as you want to push an update to an existing app using a SQL database you will have to deal with version management. The basic idea is that you might want to alter the schema of the database (for examples by adding tables or columns) while not breaking compatibility for your existing users. You must therefore be able to upgrade your database from version N to version N + 1.

On the Windows Phone platform, this is done using the DatabaseSchemaUpdater class. You can get an instance of this type using the CreateDatabaseSchemaUpdater() method of your DatabaseContext. You can then change the database by using the AddTable and AddColumn methods. You can retrieve the version of the database by using the DatabaseSchemaVersion property.  Simple, isn’t it ?

Here are some tips to update your database properly:

• define all your update operations in a single class – it will be easier for you to manage future updates
• define the current version of your database using a constant in your class – check this constant agains the DatabaseSchemaVersion property to determine if you must perform an upgrade
• handle incremental update, when you push an update for version 3, you can still have users with version 1 or 2, so handle upgrade from 1 to 2 and from 2 to 3 incrementally
• when you add new column to an existing table, the type of this colum must be nullable (bool?, string?, etc.)

Here is what the code looks like in 2Day:

using Japf.ToDo.Core.Model;
using Microsoft.Phone.Data.Linq;

namespace Japf.ToDo.Core.IO
{
    public class DatabaseVersion
    {
        // database history
        // v0. Feb. 20th 2011 first version
        // v1. May. 7th 2012 add the SpecialFolders table
        // v2. May. 19th 2012 add the Progress column in the Task table
        // v3. Auguest. 12th 2012 add the IsFocused column in the Folders & SpecialFolders table
        public const int Version = 3;

        public static void UpgradeIfNeeded(DatabaseContext context)
        {
            DatabaseSchemaUpdater dbUpdater = context.CreateDatabaseSchemaUpdater();
            if (dbUpdater.DatabaseSchemaVersion < Version)
            {
                // handle update for user with version 0
                if (dbUpdater.DatabaseSchemaVersion == 0)
                {
                    From0To1(context);
                    From1To2(context);
                    From2To3(context);
                }
                // handle update for user with version 1
                else if (dbUpdater.DatabaseSchemaVersion == 1)
                {
                    From1To2(context);
                    From2To3(context);
                }
                // handle update for user with version 2
                else if (dbUpdater.DatabaseSchemaVersion == 2)
                {
                    From2To3(context);
                }
            }
        }

        private static void From0To1(DatabaseContext context)
        {
            var dbUpdater = context.CreateDatabaseSchemaUpdater();

            // add a new table containing where each row is a SpecialFolder
            dbUpdater.AddTable();

            // add a column to the existing table Folder named ShowInSpecialFolders
            dbUpdater.AddColumn("ShowInSpecialFolders");

            // update the version of the schema
            dbUpdater.DatabaseSchemaVersion = 1;

            // send the changes to the db
            dbUpdater.Execute();

            // do not reuse the DatabaseSchemaUpdater for other update as it still contains
            // instructions to add the new table and the new column
        }

        // other methods omitted for clarity...
    }
}

Hope it helps 🙂