How to deploy a reference database with an 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 reference data in a local database and deploy it with your Windows Phone app. After your app is installed on the device, you can leave the reference database in the installation folder for read-only connections, or copy it to the local folder for read-write operations. This topic describes the process of creating a reference database and using it in your app. For more information about using a local database, see Local database for Windows Phone 8.

Important Note:
Microsoft SQL Server Compact (SQL CE) databases that have been created on the desktop may work with a Windows Phone app, but are not officially supported.

This topic contains the following sections.

Creating the reference database

A helper app is required to create the local database that you will deploy with the primary app. The helper app runs on your development computer, creates the local database in the local folder, and loads the database with the desired reference data.

In this section, you create the helper app, use the helper app to create the reference database, and then use Isolated Storage Explorer (ISETool.exe) to extract the local database file and save it to your computer. For more information about Isolated Storage Explorer, see How to use the Isolated Storage Explorer tool for Windows Phone 8.

To create the reference database

Create the helper app that creates the local database and loads it with reference data. For more information, see How to create a basic local database app for Windows Phone 8 and How to create a local database app with MVVM for Windows Phone 8.
Deploy the helper app to the Windows Phone Emulator or a Windows Phone device.
Run the helper app as appropriate to create the local database and load it with reference data. All local databases are created in the local folder.
Get the Product GUID for the app specified in the ProductID attribute of App element of the WMAppManifest.xml file. You will need this when you copy the local database file from the local folder.
While the tethered device or emulator is still running, using Isolated Storage Explorer to copy the local database to your computer. For more information, see How to use the Isolated Storage Explorer tool for Windows Phone 8.

Adding the reference database to your app

After you save the local database file to your computer, you can add it to your primary app in the same way that you would add other types of exiting files.

To add the reference database to your app

With Visual Studio, create a project for the Windows Phone app that consumes the reference database. This app, the primary app, is a different app than the helper app.
From the Project menu of the primary app, select Add Existing Item.
From the Add Existing Item menu, select the local database file that you saved to your computer with Isolated Storage Explorer, then click Add. This will add the local database to the project.
In Solution Explorer, right-click the local database file and set the file properties so that the file is built as Content and always copied to the output directory (Copy always).

Connecting to the reference database after deployment

When a local database is deployed with an app, it is stored in the installation folder after deployment. The installation folder is read-only. The primary app can connect to it there in a read-only fashion or copy it to the local folder for read-write operations. This section describes these two options in greater detail.

Important Note:
We do not recommend encrypting your reference database file if you are going to access it exclusively from the installation folder. Doing so prevents the system from performing routine database maintenance operations, such as re-indexing, upon the first connection. To use an encrypted reference database, copy it to the local folder before first use and then connect to it with a read-write connection.

We do not recommend encrypting your reference database file if you are going to access it exclusively from the installation folder. Doing so prevents the system from performing routine database maintenance operations, such as re-indexing, upon the first connection. To use an encrypted reference database, copy it to the local folder before first use and then connect to it with a read-write connection.

To read from the installation folder

When connecting to a reference database in the installation folder, you must use the File Mode property in the connection string to specify the connection as read-only. The following example demonstrates how to make a read-only connection to the installation folder. For more information about connection strings, see Local database connection strings for Windows Phone 8.
```
// Create the data context.
MyDataContext db = new MyDataContext("Data Source = 'appdata:/mydb.sdf'; File Mode = read only;");
```
In this example, the appdata prefix is used in the file path to distinguish a path in the installation folder (appdata) from a path in the local folder (isostore). Without a prefix, the data context applies the path to the local folder.

To copy the reference database to the local folder

To copy the reference database from the installation folder to the local folder, perform a stream-based copy. The following example shows a method named MoveReferenceDatabase that copies a local database file named ReferencedDB.sdf from the root of the installation folder to the root of the local folder.

using System;
using System.IO;
using System.IO.IsolatedStorage;
using System.Windows;


namespace PrimaryApplication
{
    public class DataHelper
    {

        public static void MoveReferenceDatabase()
        {
            // Obtain the virtual store for the application.
            IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();

            // Create a stream for the file in the installation folder.
            using (Stream input = Application.GetResourceStream(new Uri("ReferenceDB.sdf", UriKind.Relative)).Stream)
            {
                // Create a stream for the new file in the local folder.
                using (IsolatedStorageFileStream output = iso.CreateFile("ReferenceDB.sdf"))
                {
                    // Initialize the buffer.
                    byte[] readBuffer = new byte[4096];
                    int bytesRead = -1;

                    // Copy the file from the installation folder to the local folder. 
                    while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0)
                    {
                        output.Write(readBuffer, 0, bytesRead);
                    }
                }
            }
        }
    }
}