Walkthrough: Updating a local database app for Windows Phone 8

Article
05/20/2016

[ This article is for Windows Phone 8 developers. If you’re developing for Windows 10, see the latest documentation. ]

Starting with Windows Phone OS 7.1, you can store relational data in a local database that resides in your app’s local folder. As your app evolves, you may need to make changes to the schema of your database after the app has been deployed to a device. For a majority of schema updates, you can use the DatabaseSchemaUpdater class. With DatabaseSchemaUpdater, you can add columns, indexes, tables, and associations to a local database as part of an app update. This walkthrough describes how to update a local database, using the Local Database Sample app as an example. For more information about using a local database in your Windows Phone app, see Local database for Windows Phone 8.

Note

If your local database requires changes that are more extensive than adding columns/indexes/tables, you may need to create a new database and migrate data from the old database to the new database. For more information, see Local database migration overview for Windows Phone 8.

In this walkthrough, you will update the Local Database Sample twice. The first update adds a column to store the priority of each task. The second update adds an index to the Items table. After each stage of the walkthrough, you will use Isolated Storage Explorer to test the update from an earlier version of the database. For more information about Isolated Storage Explorer, see How to use the Isolated Storage Explorer tool for Windows Phone 8.

After you complete this walkthrough, the updated Local Database Sample will look like the following example.

In this example, the priority column, added as part of the first update, allows the color of each task to change based on the task priority. Items without an assigned priority are left white. The following image shows how the main page and new task page use the new priority feature.

This topic contains the following sections.

Creating a baseline database for version 0

In this section, you run the Local Database Sample, add some tasks to the database, and save a copy of the database for testing the update later in this walkthrough. For more information about how the Local Database Sample works, see How to create a local database app with MVVM for Windows Phone 8.

Note

Because the database schema version is not explicitly specified when the database is created, the DatabaseSchemaVersion property of the local database sample is equal to zero at the beginning of this walkthrough. To reduce confusion related to the next version of the database, this walkthrough skips version 1.

To create a baseline database for version 0

Download the Local Database Sample (C#) and save the project to a folder on your computer.
Unzip the project and open it in Visual Studio.
Press F5 to start debugging and test the app.
From the emulator or device, tap the add button from the app bar to add several tasks to the app. To view tasks in each of the app pivot pages, add tasks with each of the categories: Home, Work, and Hobbies.

Tip

When using Windows Phone Emulator, press the Page Up key to enter text values with your keyboard.

The following image shows version 0 of the database with three tasks: **home task**, **work task**, and **hobby task**.

![](images/Hh394022.AP_Con_LocalDatabaseUpdate_Baseline0(en-us,VS.105).png)

From the Debug menu, click Stop Debugging. Do not close the emulator, untether the device, or launch the app from the emulator or device.
Create a folder on your computer named LocalDatabaseVersion0. This folder will be used for storing the baseline database for version 0 of your database schema.
Use Isolated Storage Explorer to copy the local database from your app to the LocalDatabaseVersion0 folder. For more information, see How to use the Isolated Storage Explorer tool for Windows Phone 8. The following example copies the database file from the emulator to c:\LocalDatabaseVersion0\ToDo.sdf.
```
ISETool.exe ts xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseVersion0"
```
Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.
You now have a copy of the database at version 0 of the database schema. With this database file, you can use the restore snapshot (rs) command of Isolated Storage Explorer to roll back the local database to a pre-update date.

Preparing version 2: Adding task priority

In this section, you prepare the second version of the app and local database schema. Version 2 allows the color of each task to change based on the task priority.

To implement this functionality, the following files will be updated or added:

Model\ToDoDataContext.cs: Update this file to define the new priority column.
ViewModel\ToDoViewModel.cs: Update this file to provide a list of valid priorities for new tasks.
NewTaskPage.xaml and NewTaskPage.xaml.cs: Update these files to add a ListPicker control for specifying the priority associated with a new task.
PriorityToColorConverter.cs: Add this file to convert the task priority to a SolidColorBrush object that can be bound to the tasks displayed in the main page UI.
MainPage.xaml: Update this file to bind the task color to the corresponding task priority.
App.xaml.cs: Modify this file to add the database-update logic.

To prepare version 2

In the file Model\ToDoDataContext.cs, add the following code to the ToDoItem class.

// Define Priority: field, public property, and database column.
// Added in Version 2 of the application.
private int? _priority;

[Column(CanBeNull = true)]
public int? Priority
{
    get { return _priority; }
    set
    {
        if (_priority != value)
        {
            NotifyPropertyChanging("Priority");
            _priority = value;
            NotifyPropertyChanged("Priority");
        }
    }
}

This code defines the new database column named Priority. The order that this code appears in the class is not material. The CanBeNull column attribute allows the existing data in the database to be compatible with the new column.

In the file ViewModel\ToDoViewModel.cs, add the following code to the ToDoViewModel class.

// Valid priorities for new tasks.
// Added in Version 2 of the application.
private List<int> _prioritiesList = new List<int> { 1, 2, 3 };
public List<int> PrioritiesList
{
    get { return _prioritiesList; }
}

In the file NewTaskPage.xaml, add the following code to the StackPanel named ContentPanel, just below categoriesListPicker.

<!-- Added in Version 2 of the application. -->
<TextBlock 
    Text="Priority"
    Style="{StaticResource PhoneTextNormalStyle}"
    />
<toolkit:ListPicker
    x:Name="priorityListPicker"
    ItemsSource="{Binding PrioritiesList}">
</toolkit:ListPicker>
<!-- End of Version 2 update. -->

This code adds a ListPicker control for specifying the priority associated with a new task.

In the file NewTaskPage.xaml.cs, update the code that creates the newToDoItem object to specify the Priority property as follows.

// Create a new task.
ToDoItem newToDoItem = new ToDoItem
{
    ItemName = newTaskNameTextBox.Text,
    Category = (ToDoCategory)categoriesListPicker.SelectedItem,

    // Set the priority of the new task to the specified priority.
    // Added in Version 2 of the application.
    Priority = (int)priorityListPicker.SelectedItem
};

In Solution Explorer, right-click your project and select Add, then New Item.
In the Add New Item window, select Code File and name the file PriorityToColorConverter.cs. Then click Add.

In the file PriorityToColorConverter.cs, replace all code with the following.

using System;
using System.Windows.Media;
using System.Windows.Data;

namespace LocalDatabaseSample
{
    // Convert task priority to a brush that can be bound to in the UI.
    // Added in Version 2 of the application.
    public class PriorityToColorConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        {
            // Allow for tasks that have no priority.
            int? priority = (int?)value;

            // Assign color based on priority.
            switch (priority)
            {
                case null:
                    return new SolidColorBrush(Colors.White);
                case 1:
                    return new SolidColorBrush(Colors.Red);
                case 2:
                    return new SolidColorBrush(Colors.Yellow);
                case 3:
                    return new SolidColorBrush(Colors.Green);
                default:
                    return new SolidColorBrush(Colors.White);
            }
        }

        // Unused; required for IValueConverter implementation.
        public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
        { throw new NotImplementedException();}
    }
}

In the file MainPage.xaml, add the following attribute to the phone:PhoneApplicationPage element.
```
    xmlns:local="clr-namespace:LocalDatabaseSample"
```
This code adds the namespace required for PriorityToColorConverter to be used on the main page.
In MainPage.xaml, add the following element to the phone:PhoneApplicationPage.Resources element, above the DataTemplate named ToDoListBoxItemTemplate.
```

<local:PriorityToColorConverter x:Key="PriorityToColorConverter" />     
```
This code adds PriorityToColorConverter as a resource on the main page.

In MainPage.xaml, within the DataTemplate named ToDoListBoxItemTemplate, update the TextBlock by specifying the Foreground property as follows.

<!-- Foreground specified in Version 2 of the application. -->
<TextBlock 
    Text="{Binding ItemName}" 
    FontSize="{StaticResource PhoneFontSizeLarge}" 
    Grid.Column="1" Grid.ColumnSpan="2" 
    VerticalAlignment="Top" Margin="-36, 12, 0, 0"
    Foreground="{Binding Priority, Converter={StaticResource PriorityToColorConverter}}"
    />

This code binds the foreground color of the task text to the priority, based on the PriorityToColorConverter resource.

In the file App.xaml.cs, add the following directive at the top of the page.
```
// Directive for DatabaseSchemaUpdater
// Added in Version 2 of the application.
using Microsoft.Phone.Data.Linq;
```
This is used to create a DatabaseSchemaUpdater object in one of the following steps.

In the file App.xaml.cs, add the following code to the App class.

// The current version of the application.
public static int APP_VERSION = 2;

In App.xaml.cs, replace the using statement for the ToDoDataContext named db as follows.

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

        // Prepopulate the categories.
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Home" });
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Work" });
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Hobbies" });

        // Save categories to the database.
        db.SubmitChanges();

        // Set the new database version.
        DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
        dbUpdater.DatabaseSchemaVersion = APP_VERSION;
        dbUpdater.Execute();
    }
    else
    {
        // Check whether a database update is needed.
        DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();

        if (dbUpdater.DatabaseSchemaVersion < APP_VERSION)
        {
            // Add the Priority column (added in version 2).
            dbUpdater.AddColumn<ToDoItem>("Priority");

            // Add the new database version.
            dbUpdater.DatabaseSchemaVersion = 2;

            // Perform the database update in a single transaction.
            dbUpdater.Execute();
        }
    }
}

This code adds an else clause to the if statement, allowing the app to update the database if necessary. Changes are not made to the database until the Execute method from the dbUpdater object is called.

Rebuild the solution by clicking the Rebuild option in the Build menu. If you do not see the Build menu in Visual Studio Express, enable it in the Tools menu by clicking Settings and then selecting Expert Settings.
You have completed the version 2 updates to the Local Database Sample app. Press F5 and run the app to test the app functionality.

Note

Depending on the state of Visual Studio, the data you saved in the emulator or device may not appear in the app at this point. The data from version 0 of the database will be restored in the next section.

Testing the database update

In this section, you test the database-update code of the app. Depending on the state of Visual Studio when you get to this point, the data from version 0 of the database may not appear in the emulator or device.

To simulate an app update, you will use Isolated Storage Explorer to restore version 0 of the database (after the new version of the app has been deployed to the device). When an app is updated on the device by the Windows Phone Store, only the app code is updated; the local folder remains unmodified by the app update process.

To test the database update

Rebuild the solution by clicking the Rebuild option in the Build menu. If you do not see the Build menu in Visual Studio Express, enable it in the Tools menu by clicking Settings and then selecting Expert Settings.
Press F5 to start debugging. This will deploy the new version of the app to the emulator or device.

Note

To copy to the local folder of the app, Isolated Storage Explorer requires at least one instance of the app (having the same Product ID) to be present on the emulator or device. Running the debugger satisfies this condition.

From the Debug menu, click Stop Debugging. Do not close the emulator or untether the device. This closes the app, releasing control of the local database file in the local folder.
Use Isolated Storage Explorer to restore the version 0 database from the LocalDatabaseVersion0 folder to your emulator or device. For more information, see How to use the Isolated Storage Explorer tool for Windows Phone 8. The following example copies the version 0 database file from c:\LocalDatabaseVersion0\ToDo.sdf to the emulator.

Important Note:
To successfully restore the database, make sure the app is not running (with or without the debugger) when you perform this command. If the app has been tombstoned, you may also need to remove it from the app stack by pressing the Back button until the device navigates to Start.

ISETool.exe rs xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseVersion0" Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.

Your app is now in a state that simulates a Store update, just before the new version of the app runs with version 0 of the database. To step through the database-update code, set a debugging breakpoint in the App.xaml.cs file at the following line of code.
```
if (db.DatabaseExists() == false)
```
Press F5 to start debugging. The new version of the app will be redeployed to the device and begin running with version 1.0 of the database.
After the debugger reaches the breakpoint, click the Step Over button to walk through the database-update code. Observe the DatabaseSchemaVersion property of the dbUpdater object before and after the database is updated (hover your mouse over the dbUpdater object name).

The following example shows the DatabaseSchemaVersion of dbUpdater before (red arrow) and after (blue arrow) the Execute method is called.
Remove the breakpoint and press F5 to start debugging and continue running the app. The app will start and display the tasks that you saved with the baseline for version 0, as shown in the following image.

Because the tasks created with version 0 of the app did not have a priority value associated with them, the PriorityToColorConverter assigned them the color White.
Stop debugging when you are finished testing the update. From the Debug menu, click Stop Debugging. Do not close the emulator or untether the device. This closes the app, releasing control of the local database file in the local folder.

Creating a baseline database for version 2

In this section, you run version 2 of the app, add some tasks to the database, and save a copy of the database for testing the next version of the database-update code.

To create a baseline database for version 2

If version 2 of the app is not running, press F5 to start debugging and test the app.
From the emulator or device, tap the add button from the app bar to add new tasks with different priorities and completion states to the app. As needed, remove tasks from the version 0 baseline to make room for the new tasks.

The following image shows version 2 of the database with some tasks that were restored from version 0 and some new tasks that illustrate the new priority feature of the app: task priority 1, task priority 2, and task priority 3.
From the Debug menu, click Stop Debugging. Do not close the emulator or untether the device.
Create a folder on your computer named LocalDatabaseVersion2. This folder will be used for storing the baseline database for version 2 of your database schema.
Use Isolated Storage Explorer to copy the local database from your app to the LocalDatabaseVersion2 folder. For more information, see How to use the Isolated Storage Explorer tool for Windows Phone 8. The following example copies the database file from the emulator to c:\LocalDatabaseVersion2\ToDo.sdf.
```
ISETool.exe ts xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseVersion2"
```
Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.
You now have a copy of the database at version 2 of the database schema. Continue to the next section to make further updates to the database schema.

Preparing version 3: Adding an index, considering multiple upgrade paths

In this section, you prepare the third version of the app and local database schema. Version 3 demonstrates adding a secondary index to the database and preparing your database-update logic to consider multiple upgrade paths.

Important Note:
Your customers may not choose to install all updates of your app. It is important to consider that when your app launches, the database file on the device could be at any previous app version.

To prepare version 3

In the file Model\ToDoDataContext.cs, add the following directive at the top of the file.

// Directive for Index attribute.
// Added in Version 3 of the application.
using Microsoft.Phone.Data.Linq.Mapping;

In Model\ToDoDataContext.cs, add the following LINQ to SQL mapping attribute to the ToDoItem class, just below the [Table] attribute.
```
// Index added in version 3 of the application.
[Index(Columns = "Priority", Name = "PriorityIndex")]
```
This code adds an ascending index on the Priority column. Adding this index to the database is for demonstration only; updating the app to take advantage of the corresponding performance benefits is outside the scope of this topic.

In the file App.xaml.cs, update the APP_VERSION property in the App class.

// The current version of the application.
public static int APP_VERSION = 3;

In App.xaml.cs, update the using statement for the ToDoDataContext named db as follows.

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

        // Prepopulate the categories.
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Home" });
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Work" });
        db.Categories.InsertOnSubmit(new ToDoCategory { Name = "Hobbies" });

        // Save categories to the database.
        db.SubmitChanges();

        // Set the new database version.
        DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();
        dbUpdater.DatabaseSchemaVersion = APP_VERSION;
        dbUpdater.Execute();
    }
    else
    {
        // Check whether a database update is needed.
        DatabaseSchemaUpdater dbUpdater = db.CreateDatabaseSchemaUpdater();

        if (dbUpdater.DatabaseSchemaVersion < APP_VERSION)
        {
            // Perform version 2.0 update, as applicable.
            if (dbUpdater.DatabaseSchemaVersion < 2)
            {
                // Add the Priority column (added in version 2.0).
                dbUpdater.AddColumn<ToDoItem>("Priority");
            }

            // Add the Priority index (added in version 3.0).
            dbUpdater.AddIndex<ToDoItem>("PriorityIndex");

            // Set the new database version.
            dbUpdater.DatabaseSchemaVersion = 3;

            // Perform the database update in a single transaction.
            dbUpdater.Execute();
        }
    }
}

This code updates the else clause to add the secondary index named PriorityIndex and, if necessary, add the Priority column. Your customers may not choose to install all updates of your app. It is important to consider that when your app launches, the database file on the device could be at any previous app version.

Rebuild the solution by clicking the Rebuild option in the Build menu. If you do not see the Build menu in Visual Studio Express, enable it in the Tools menu by clicking Settings and then selecting Expert Settings.
You have now completed the version 3 updates to the app. Continue to the next section to test the database update from earlier versions of the database.

Testing the version 3 update from earlier database versions

In this section, you test the version 3 database-update logic by simulating the app update from different versions of the database schema. Your customers may not install all of your app updates. It is important to make sure that the database-update logic performs as desired from previous version of your database schema.

To test the version 3 update from version 0

Test the version 3 database-update logic against version 0 of the database schema. Test with the database stored in the LocalDatabaseVersion0 folder using the process that was described earlier in this walkthrough, Testing the Database Update.

For example, the following command-line statement copies the version 0 database file from c:\LocalDatabaseVersion0\ToDo.sdf to the emulator.
```
ISETool.exe rs xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseVersion0"
```
Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.

After starting version 3 of the app with the version 0 database, only the tasks that you saved with the version 0 baseline should appear.

To test the version 3 update from version 2

Test the version 3 database-update logic against version 2 of the database schema. Test with the database stored in the LocalDatabaseVersion2 folder using the process that was described earlier in this walkthrough, Testing the Database Update.

For example, the following command-line statement copies the version 2 database file from c:\LocalDatabaseVersion2\ToDo.sdf to the emulator.
```
ISETool.exe rs xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseVersion2"
```
Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.

After starting version 3 of the app with the version 2 database, only the tasks that you saved with the version 2 baseline should appear.

Testing a clean installation: Removing the local database

In this section, you use Isolated Storage Explorer to clear the the local folder container associated with the app. While testing your app with earlier versions of database schemas, you may also want to test the experience for new customers that do not have a previous version of the database on their device. Removing the database with Isolated Storage Explorer provides a convenient alternative to restarting the emulator or uninstalling the app from the device.

Tip

When using Isolated Storage Explorer in the command window (Cmd.exe), use the Up and Down arrow keys to navigate your command history and quickly access and edit previous commands.

To create an empty folder

Copy one of the baseline database folders, for example LocalDatabaseVersion2.
Rename the folder LocalDatabaseEmpty.
Navigate to the folder containing the local database file. For example, c:\LocalDatabaseEmpty.
Delete the local database file, named ToDo.sdf.
You have now created a snapshot of an empty local folder. Restoring this folder to the app on the emulator or device removes the local database file from the app’s local folder.

To remove the local database

Restore the empty local folder captured in the LocalDatabaseEmpty folder. Use a process similar to what was described earlier in this walkthrough, Testing the Database Update.

For example, the following command-line statement copies an empty local folder from c:\LocalDatabaseEmpty to the emulator.
```
ISETool.exe rs xd 77a80316-384d-40dc-a8c3-c4054676e85c "C:\LocalDatabaseEmpty"
```
Note that the GUID value corresponds to the Product ID of your app, as found in the app manifest file. The Product ID of your app may be different than the ID used in this example.

The following image shows what version 3 of the app looks like after restoring the LocalDatabaseEmpty folder, simulating a clean installation.

You have now completed the local database update walkthrough.

Share via

Walkthrough: Updating a local database app for Windows Phone 8

Creating a baseline database for version 0

To create a baseline database for version 0

Preparing version 2: Adding task priority

To prepare version 2

Testing the database update

To test the database update

Creating a baseline database for version 2

To create a baseline database for version 2

Preparing version 3: Adding an index, considering multiple upgrade paths

To prepare version 3

Testing the version 3 update from earlier database versions

To test the version 3 update from version 0

To test the version 3 update from version 2

Testing a clean installation: Removing the local database

To create an empty folder

To remove the local database

See Also

Other Resources

Additional resources