WP7 DatabaseSchemaUpdater multiple version update

While testing the Windows Phone 7 DatabaseSchemaUpdater I started getting the following error: "A column ID occurred more than once in the specification."

I searched the web for a while and got nowhere.

So, analizing my code for the 10th time i noticed something. On the second update (version 1 to version 2) I was adding a new table:

schemaUpdater.AddTable();

And on the third update (v2 to v3) I was adding a new column to that same table:

schemaUpdater.AddColumn< ClassName >("PropertyName");

If the user updates the app regularly everything goes smoothly, but if the user doesn't update for some time and then has to go through all the updates the app crashes.

The problem is that when the new table was added, since the user is already at the newest version, the table already has the column that the next update will try to add. At that point it will crash, and continue crashing at every update unless the DatabaseSchemaVersion is properly updated.

This can lead to a lot of frustration and data loss for the user and must be taken into account when updating.

Since I couldn't find a way to get the current schema to check for table columns before adding them I made a simple workaround.

When updating keep a list of added table names in that update and when adding columns only add them if the table where the column is being added doesn't have its name on the list.

Check the example below

private const int LatestDatabaseVersion = 2;

private List tablesAddedInThisUpdateSession = null;

public List TablesAddedInThisUpdateSession

{

get

{

if (tablesAddedInThisUpdateSession == null)

{

tablesAddedInThisUpdateSession = new List();

}

return tablesAddedInThisUpdateSession;

}

}

public void UpdateIfNeeded()

{

// create an instance of DatabaseSchemaUpdater

DatabaseSchemaUpdater schemaUpdater = this.CreateDatabaseSchemaUpdater();

int version = schemaUpdater.DatabaseSchemaVersion;

// if current version of database schema is old

while (version < LatestDatabaseVersion)

{

switch (version)

{

case 0:

version = UpdateFromVersion0ToVersion1();

break;

case 1:

version = UpdateFromVersion1ToVersion2();

break;

default:

break;

}

}

}

private int UpdateFromVersion0ToVersion1()

{

DatabaseSchemaUpdater schemaUpdater = this.CreateDatabaseSchemaUpdater();

schemaUpdater.AddTable<Contact>();

TablesAddedInThisUpdateSession.Add("Contact");

// IMPORTANT: update database schema version before calling Execute

schemaUpdater.DatabaseSchemaVersion = 1;

schemaUpdater.Execute();

return schemaUpdater.DatabaseSchemaVersion;

}

private int UpdateFromVersion1ToVersion2()

{

DatabaseSchemaUpdater schemaUpdater = this.CreateDatabaseSchemaUpdater();

if (!TablesAddedInThisUpdateSession.Contains("Contact"))

{

schemaUpdater.AddColumn<Contact>("Phone");

}

schemaUpdater.DatabaseSchemaVersion = 2;

schemaUpdater.Execute();

return schemaUpdater.DatabaseSchemaVersion;

}