Windows Phone Local Database Schema Upgrade Part1 - Adding new columns

published on: 2/20/2012 | Views: N/A | Tags: LocalDB

by WindowsPhoneGeek

In this article I am going to talk about how to update your database schema when updating your app. The problem is that if you change the database schema(for.ex: add new columns or tables) in a future version of your app, then when users that use the old version of your app update to the new one an exception occurs. So in practice if there is no additional code to change the database so that it is compatible with the latest version of the app the following exception occurs:

So in this series of posts I will explain how to prevent this exception from happening.

To begin with lets first create a new Windows Phone application project, next follow the steps.

Step1. Add a new class Person to the project with the following properties:

[Table(Name = "People")]
public class Person
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID
    {
        get;
        set;
    }

    [Column(CanBeNull = false)]
    public string FirstName
    {
        get;
        set;
    }

    [Column(CanBeNull = true)]
    public string LastName
    {
        get;
        set;
    }

    [Column(CanBeNull = false)]
    public int Age
    {
        get;
        set;
    }
    public override string ToString()
    {
        return string.Format("{0} {1}, Age: {2}",
            this.FirstName, this.LastName, this.Age);
    }
}

Step2. Next add a new class PeopleDataContext to the project which will be our data context:

public class PeopleDataContext : DataContext
{
    public PeopleDataContext(string connectionString)
        : base(connectionString)
    {
    }

    public Table<Person> People
    {
        get
        {
            return this.GetTable<Person>();
        }
    }
}

 

Step3. Next we will add a new button that will be used to initialize the database:

private void btnInitialize_Click(object sender, RoutedEventArgs e)
{
    using (PeopleDataContext context = new PeopleDataContext(ConnectionString))
    {
        if (!context.DatabaseExists())
        {
            // create database if it does not exist
            context.CreateDatabase();
            this.WritePeople(context);
        }
    }
}

Where WritePeople is the following method:

public void WritePeople(PeopleDataContext context)
{
    for (int i = 0; i < 10; i++)
    {
        Person person = new Person()
        {
            FirstName = string.Format("FirstName#{0}", i),
            LastName = string.Format("LastName#{0}", i),
            Age = i
        };
        context.People.InsertOnSubmit(person);
    }
    context.SubmitChanges();
}

public void WritePeople()
{
    using (PeopleDataContext context = new PeopleDataContext(ConnectionString))
    {
        this.WritePeople(context);
    }
}

Step4. We will add one more button that we will use to read the data from the database(this is only for  testing purposes to prove that the database is functioning properly):

private void btnReadPeople_Click(object sender, RoutedEventArgs e)
{
    this.lbPeople.ItemsSource = this.ReadPeople();
}

public IEnumerable<Person> ReadPeople()
{
    IEnumerable<Person> people = null;
    using (PeopleDataContext context = new PeopleDataContext(ConnectionString))
    {
        people = context.People.ToList();
    }

    return people;
}

NOTE: All that the above code does is read the Person records from the database and show them in a ListBox.

Step5. If we run the application now and press the initialize button and after that the read people button we should see the following screen.I.e. this is the first version of our database:

Step6. For the next version of our app we will update the Person class with two additional properties:

NOTE: It is important to notice that in the snippet below  the Address and the Email properties are added in the second version of the schema.

[Table(Name = "People")]
public class Person
{
   //...

#if DB_VERSION_1
    [Column(CanBeNull = true)]
    public string Address
    {
        get;
        set;
    }

    [Column(CanBeNull = true)]
    public string Email
    {
        get;
        set;
    }
#endif

    public override string ToString()
    {
#if DB_VERSION_1
        return string.Format("{0} {1}, Age: {2}, Email: {3}",
            this.FirstName, this.LastName, this.Age, this.Email);
#else
        return string.Format("{0} {1}, Age: {2}",
            this.FirstName, this.LastName, this.Age);
#endif
    }
}

NOTE: We will use the DB_VERSION_1 conditional compilation symbol in order to turn on code related to the second version of the database schema. 

To turn the sections of the code marked with the DB_VERSION_1 symbol just add it to the conditional compilation symbols in the project`s Build properties:

Step7. If we were to run the application now without writing any additional code we will get the following exception:

To prevent this from happening we will add some code to update the database schema to include the Address and Email columns. Here is how our initialize database method should look like now:

private void btnInitialize_Click(object sender, RoutedEventArgs e)
{
    using (PeopleDataContext context = new PeopleDataContext(ConnectionString))
    {
        if (!context.DatabaseExists())
        {
            // create database if it does not exist
            context.CreateDatabase();
            this.WritePeople(context);
        }
        else
        {
            // create an instance of DatabaseSchemaUpdater
            DatabaseSchemaUpdater schemaUpdater = context.CreateDatabaseSchemaUpdater();
            // get current database schema version
            // if not changed the version is 0 by default
            int version = schemaUpdater.DatabaseSchemaVersion;

            // if current version of database schema is old
            if (version == 0)
            {
                // add Address column to the table corresponding to the Person class
                schemaUpdater.AddColumn<Person>("Address");
                // add Email column to the table corresponding to the Person class
                schemaUpdater.AddColumn<Person>("Email");
                // IMPORTANT: update database schema version before calling Execute
                schemaUpdater.DatabaseSchemaVersion = 1;
                // execute changes to database schema
                schemaUpdater.Execute();
            }
        }
    }
}

What will happen when the application is ran is the following:

• for an updated application, since the database probably already exists the code in the else branch will execute. Here we create a new instance of the DatabaseSchemaUpdater class. Then we retrieve the current version of the database schema and if it is the older version we update the schema with the new columns, increase the schema version and execute the schema changes.
• for a newly installed application a new database will be created

Step8. To verify that the schema upgrade code works properly we will add a new button and will use the new properties added with the second version of the database schema:

private void btnUpdatePeople_Click(object sender, RoutedEventArgs e)
{
#if DB_VERSION_1
    using (PeopleDataContext context = new PeopleDataContext(ConnectionString))
    {
        // for version 1 - update people records with email address
        List<Person> people = context.People.ToList();
        int count = people.Count;
        for (int i = 0; i < count; i++)
        {
            Person person = people[i];
            person.Email = string.Format("person{0}@domain.com", i);
        }
        context.SubmitChanges();
    }
#endif
}

Step9.Finally if we run the application and press the Initialize, Update and Read buttons we should see the following screen:

That`s it for now. In the next article we will discuss more complex changes to the database schema. Stay tuned.

You may also find helpful the following articles:

• Windows Phone Mango Local Database(SQL CE)
• Windows Phone Mango Local Database: mapping and database operations
• Using SqlMetal to generate Windows Phone Mango Local Database classes

Here is the full source code:

Hole the post was helpful.

You can also follow us on Twitter @winphonegeek

Comments

Add comment to 'Windows Phone Local Database Schema Upgrade Part1 - Adding new columns'

Title Your Name

Comment