Local database for Windows Phone 8

[ 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. Windows Phone apps use LINQ to SQL for all database operations; LINQ to SQL is used to define the database schema, select data, and save changes to the underlying database file residing in the local folder. This topic provides an overview of using a local database with your Windows Phone app. For a step-by-step walkthrough of creating an app that uses a local database, see How to create a basic local database app for Windows Phone 8.

This topic contains the following sections.

• Architectural overview
• Data context
• LINQ to SQL runtime
• Similarities and differences
• App deployment
• Defining the data context
• Creating the database
• Using the database
• Changing the database schema
• Database security
• Related Topics

Architectural overview

To store and retrieve data in a local database, a Windows Phone app uses LINQ to SQL. LINQ to SQL provides an object-oriented approach to working with data and comprises an object model and a runtime.

The LINQ to SQL object model is made up primarily by the System.Data.Linq.DataContext object, which acts as a proxy for the local database. The LINQ to SQL runtime is responsible for bridging the world of objects (the DataContext object) with the world of data (the local database). This relationship is summarized in the following image.

Data context

The data context is a proxy, an object that represents the database. A data context contains Table objects, each of which represents a table in the database. Each Table object is made up of entities that correspond to rows of data in a database. Each entity is a “plain old CLR object” (POCO) with attributes. The attributes on each entity determine the database table structure and define the mapping between the object model of the data and the schema of the database. For example, an entity having Name and PhoneNumber properties would result in a database table having Name and PhoneNumber columns.

LINQ to SQL runtime

LINQ to SQL provides object-relational mapping capabilities that enable your managed app to use Language Integrated Query (LINQ) to communicate with a relational database (that only “speaks” Transact-SQL). LINQ to SQL maps the object model, which you express with .NET Framework managed code, to a relational database. When your app runs, LINQ to SQL translates language-integrated queries into Transact-SQL and then sends the queries to the database for execution. When the database returns the results, LINQ to SQL translates the results back to objects that you can work with in your own programming language. For more information, see LINQ to SQL.

Note

LINQ to SQL on Windows Phone does not directly support executing Transact-SQL, including Data Definition Language (DDL) or Data Modeling Language (DML) statements. Additionally, Windows Phone apps cannot use LINQ to SQL to directly access ADO.NET objects. For more information, see LINQ to SQL support for Windows Phone 8.

Similarities and differences

Similar to a desktop app that uses a SQL Server relational database, a Windows Phone app can use a local database to select, insert, update, and delete data by using LINQ to SQL. This allows your Windows Phone apps to benefit from the powerful query capabilities of LINQ and the storage efficiencies of a relational database. Because a phone has fewer resources than a PC, there are a few ways in which a local database differs from a typical database. These differences include:

• A local database runs in the Windows Phone app’s process. Unlike a client-server database such as Microsoft SQL Server, it does not run continuously as a background service.

• A local database can be accessed only by the corresponding Windows Phone app. Because the database file resides in the local folder, no other apps can access that data.

• A local database can be accessed only with LINQ to SQL; Transact-SQL is not supported.

App deployment

In a standard app deployment, the local database is created in the local folder the first time that the app runs. After that, app data is added to the database as the app is used. To include a pre-populated set of reference data along with your app, add a local database file to your app. For step-by-step instructions, see How to deploy a reference database with an app for Windows Phone 8.

To deploy reference data with your app, you need to perform the following steps:

1. Create the helper application: 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.

2. Extract the local database from the helper application: Use the Isolated Storage Explorer (ISETool.exe) to copy the database from the helper app to a folder on your computer. For more information about the Isolated Storage Explorer, see How to use the Isolated Storage Explorer tool for Windows Phone 8.

3. Create the primary application: Create the app that will consume the reference data.

4. Add the reference data to the primary application: Use Visual Studio to add the local database file to the primary app from the folder where you saved it on your computer. To minimize the size of the app’s assembly, store the file as Content.

After a local database is deployed with an app, it resides in the installation folder in a read-only state. The installation folder is different than the local folder. To address the database file in this location, use the appdata: prefix. For an example of using this prefix with the database connection string, see Local database connection strings for Windows Phone 8.

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. For more information about how to copy it, see How to deploy a reference database with an app for Windows Phone 8.

To modify the database containing the reference data, move it out of the installation folder and save it in the local folder before attempting database changes. To move the database file, you can perform a stream-based copy with the Application.GetResourceStream method to create a stream from the installation folder, and the IsolatedStorageFileStream.Write method to write the stream to the local folder. The following example demonstrates how to address a database file in the installation folder when you create a stream object.

Stream str = Application.GetResourceStream(new Uri("appdata:/MyReferenceDB.sdf", UriKind.Relative)).Stream;

Defining the data context

To create a local database, you must first define the data context and the entities. These classes define the mapping between the object model of the data and the schema of the database. The object-relational capabilities of LINQ to SQL depend on these mapping details to create a relational database that maps to the corresponding data context.

For each entity, mapping details are specified by using LINQ to SQL mapping attributes. These attributes specify database-specific features such as tables, columns, primary keys, and indexes. For more information, see Attribute-based Mapping (LINQ to SQL). For example, the following code shows a data context named ToDoDataContext and the beginning of an entity class named ToDoItem.

public class ToDoDataContext : DataContext
{
    // Specify the connection string as a static, used in main page and app.xaml.
    public static string DBConnectionString = "Data Source=isostore:/ToDo.sdf";

    // Pass the connection string to the base class.
    public ToDoDataContext(string connectionString): base(connectionString) { }

    // Specify a single table for the to-do items.
    public Table<ToDoItem> ToDoItems;
}

// Define the to-do items database table.
[Table]
public class ToDoItem : INotifyPropertyChanged, INotifyPropertyChanging
{
    // Define ID: private field, public property, and database column.
    private int _toDoItemId;

    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int ToDoItemId
    {
        get
        {
            return _toDoItemId;
        }
        set
        {
            if (_toDoItemId != value)
            {
                NotifyPropertyChanging("ToDoItemId");
                _toDoItemId = value;
                NotifyPropertyChanged("ToDoItemId");
            }
        }
    }
         . . .
         . . .
         . . .

Note

This is only a portion of the data context code. For a step-by-step walkthrough of creating an app that uses a local database, see How to create a basic local database app for Windows Phone 8.

To use local database features in your code, you will need the following directives at the top of your code file.

using System.Data.Linq;
using System.Data.Linq.Mapping;
using Microsoft.Phone.Data.Linq;
using Microsoft.Phone.Data.Linq.Mapping;

Some common LINQ to SQL mapping attributes are shown in the following table. For a full list, see System.Data.Linq.Mapping.

Attribute	Example	Description
TableAttribute	[Table]	Designates a class as an entity class that is associated with a database table.
ColumnAttribute	[Column(IsPrimaryKey = true)]	Associates a class with a column in a database table. IsPrimaryKey specifies the primary key, for which an index is created by default.
IndexAttribute	[Index(Columns="Column1,Column2 DESC", IsUnique=true, Name="MultiColumnIndex")]	Written at the table level, designates additional indexes on the table. Each index can cover one or more columns.
AssociationAttribute	[Association(Storage="ThisEntityRefName", ThisKey="ThisEntityID", OtherKey="TargetEntityID")]	Designates a property to represent an association, such as a foreign key to primary key association.

Creating the database

After you create the DataContext object, you can create the local database and perform a number of additional database operations. The following code example demonstrates the creation of a database, based on the ToDoDataContext class data context.

// Create the database if it does not yet exist.
using (ToDoDataContext db = new ToDoDataContext("isostore:/ToDo.sdf"))
{
    if (db.DatabaseExists() == false)
    {
        // Create the database.
        db.CreateDatabase();
    }
}

As shown in this example, you must first specify the data context and the file location of the database file in order to create the data context. The DataContext constructor value specifies that the database file name is ToDo.sdf. The isostore:/ portion of the value specifies that the file is located in the local folder. Next, the CreateDatabase method is used to create the database after the DatabaseExists method confirms that the database does not yet exist.

Note

When the database is created, it is automatically assigned a version of 0. To determine the database version, use the DatabaseSchemaUpdater class, as shown in Changing the Database Schema, later in this topic.

Using the database

After the local database has been created, you can use LINQ and the data context to work with the local database. The following subsections describe how to select, insert, update, and delete data in the database. For a step-by-step walkthrough of creating an app that performs these operations, see How to create a basic local database app for Windows Phone 8.

Selecting data (database queries)

On Windows Phone, Language Integrated Query (LINQ) is used to query the database. LINQ bridges the gap between the world of objects and the world of data. Queries in LINQ to SQL use the same syntax as queries in LINQ. For more information about LINQ queries, see Introduction to LINQ Queries (C#).

Because objects referenced in LINQ to SQL queries are mapped to records in a database, LINQ to SQL differs from other LINQ technologies in the way that queries are executed. A typical LINQ query is executed in memory at the app layer. With LINQ to SQL, using the object-relational capabilities of the runtime, each LINQ query is translated to Transact-SQL and then executed directly in the database. This can yield a performance gain for queries such as selecting a few records out of a large database.

In the following example, a DataContext object named toDoDB is queried with LINQ to SQL and the results are placed into an ObservableCollection of ToDoItem objects named ToDoItems. Because of deferred execution, the database query is not actually executed until the ToDoItems collection is instantiated.

// Define query to gather all of the to-do items.
var toDoItemsInDB = from ToDoItem todo in toDoDB.ToDoItems
                    select todo;

// Execute query and place results into a collection.
ToDoItems = new ObservableCollection<ToDoItem>(toDoItemsInDB);

Inserting data

Inserting data into the database is a two-step process. First add an object to the data context, then call the data context SubmitChanges method to persist the data as a row in the database. For more information, see How to: Insert Rows Into the Database (LINQ to SQL).

In the following example, a ToDoItem object is created and added to the ToDoItems observable collection and corresponding database table in the data context named toDoDB.

// Create a new to-do item based on text box.
ToDoItem newToDo = new ToDoItem { ItemName = newToDoTextBox.Text };

// Add the to-do item to the observable collection.
ToDoItems.Add(newToDo);
            
// Add the to-do item to the local database.
toDoDB.ToDoItems.InsertOnSubmit(newToDo); 

Important Note:
Data is not saved to the database until the SubmitChanges method is called.

Updating data

There are three steps to updating data in the local database. First, query the database for the object that is to be updated. Then, modify the object as desired. Finally, call the SubmitChanges method to save the changes to the local database. For more information, see How to: Update Rows in the Database (LINQ to SQL).

If you bind objects in the data context to controls on the page, the data context can be updated automatically based on user interaction. Then, the only step required is to call the SubmitChanges method at the desired time. An example of this technique is found in the local database sample app, as described in How to create a basic local database app for Windows Phone 8. The following code example shows an example of calling the SubmitChanges method when the user navigates away from the page.

protected override void OnNavigatedFrom(System.Windows.Navigation.NavigationEventArgs e)
{ 
    //Call base method
    base.OnNavigatedFrom(e);
            
    //Save changes to the database
    toDoDB.SubmitChanges();
}

Important Note:
Data is not updated in the database until the SubmitChanges method is called.

Deleting data

Deleting data in the database is also comprised of three steps. First, query the database for the objects that are to be deleted. Then, depending on whether you have one or more objects to delete, call the DeleteOnSubmit or DeleteAllOnSubmit method, respectively, to put those objects in a pending delete state. Finally, call the SubmitChanges method to save the changes to the local database. For more information, see How to: Delete Rows From the Database (LINQ to SQL).

In the following example, a ToDoItem object is deleted from the database named toDoDB. Since only one object is deleted, the DeleteOnSubmit method is called before SubmitChanges.

//Get a handle for the to-do item bound to the button
ToDoItem toDoForDelete = button.DataContext as ToDoItem;

//Remove the to-do item from the observable collection
ToDoItems.Remove(toDoForDelete);

//Remove the to-do item from the local database
toDoDB.ToDoItems.DeleteOnSubmit(toDoForDelete);

//Save changes to the database
toDoDB.SubmitChanges();

Important Note:
Data is not deleted from the database until the SubmitChanges method is called.

Changing the database schema

Changes to your Windows Phone app may require changes to your local database schema. Any changes to your local database schema begin with changing the object model of the corresponding data context. The Microsoft.Phone.Data.Linq namespace provides the DatabaseSchemaUpdater class to help with database schema changes. For an example of how to use this class, see Walkthrough: Updating a local database app for Windows Phone 8.

The DatabaseSchemaUpdater class can perform additive changes to the database, such as adding tables, columns, indexes or associations. For more complex changes, you will need to create a new database and copy the data to the new schema, as applicable. The DatabaseSchemaUpdater class provides a DatabaseSchemaVersion property that you can use to programmatically distinguish different versions of your database.

Important Note:
The database does not change to reflect updates from the DatabaseSchemaUpdater object until the Execute method is called. When that method is called, all changes are submitted to the local database as a single transaction, including version updates. Using a single transaction helps the database maintain integrity, such as in cases where the user exits the app during an upgrade.

The following example demonstrates using the DatabaseSchemaUpdater class to modify the database based on the DatabaseSchemaVersion property.

using (ToDoDataContext db = new ToDoDataContext(("isostore:/ToDo.sdf")))
{
        //Create the database schema updater
        DatabaseSchemaUpdater dbUpdate = db.CreateDatabaseSchemaUpdater();

        //Get database version
        int dbVersion = dbUpdate.DatabaseSchemaVersion;

        //Update database as applicable
        if (dbVersion < 5)
        {   //Copy data from existing database to new database 
            MigrateDatabaseToLatestVersion();
        }
        else if (dbVersion == 5)
        {   //Add column to existing database to match the data context
            dbUpdate.AddColumn<ToDoItem>("TaskURL");
            dbUpdate.DatabaseSchemaVersion = 6;
            dbUpdate.Execute();
        }
}

Note

Any files saved in the local folder, including local database files, are not altered during the app update process.

Database security

The local database provides password protection and encryption to help secure your database. When you use a password with the database, the entire database is encrypted. In order to encrypt the database, provide a password in the database connection string (the data context constructor) before you create the database. Each time you access the database, you will need to provide the password. You cannot encrypt the database after it has been created. The database is encrypted using AES-128 and the password is hashed using SHA-256.

The following example demonstrates creating an encrypted database by specifying a password in the database connection string.

// Create the data context, specify the database file location and password
ToDoDataContext db = new ToDoDataContext ("Data Source=’isostore:/ToDo.sdf’;Password=’securepassword’");

// Create an encrypted database after confirming that it does not exist
if (!db.DatabaseExists()) db.CreateDatabase();

Tip

If only a limited number of non-indexed columns need to be encrypted, you may achieve better performance by encrypting that data before it is added to the database rather than encrypting the entire database. For more information about data encryption, see How to encrypt data for Windows Phone 8.

For more information about using connection strings with a local database, see Local database connection strings for Windows Phone 8.

See Also

Other Resources

Local Database Sample

Windows Phone Training Kit

Introduction to LINQ

LINQ to SQL Documentation

Query Examples (LINQ to SQL)

How to use the Isolated Storage Explorer tool for Windows Phone 8