Windows Phone 7.5 - Local SQL Database：总体概况

http://msdn.microsoft.com/en-us/library/hh202860(v=vs.92).aspx

Local Database Overview for Windows Phone

            Windows Phone

 

December 15, 2011

With Windows Phone OS 7.1, you can store relational data in a local database that resides in your application’s isolated storage container. Windows Phone applications 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 isolated storage. This topic provides an overview of using a local database with your Windows Phone application. For a step-by-step walkthrough of creating an application that uses a local database, see How to: Create a Basic Local Database Application for Windows Phone.

Architectural Overview

---

To store and retrieve data in a local database, a Windows Phone application 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 application 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 application 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 applications cannot use LINQ to SQL to directly access ADO.NET objects. For more information, see LINQ to SQL Support for Windows Phone.

Similarities and Differences

---

Similar to a desktop application that uses a SQL Server relational database, a Windows Phone application can use a local database to select, insert, update, and delete data by using LINQ to SQL. This allows your Windows Phone applications 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 application’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 application. Because the database file resides in isolated storage, no other applications can access that data.

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

Application Deployment

---

In a standard application deployment, the local database is created in isolated storage the first time that the application runs. After that, application data is added to the database as the application is used. To include a pre-populated set of reference data along with your application, add a local database file to your application. For step-by-step instructions, see How to: Deploy a Reference Database with a Windows Phone Application.

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

1. Create the helper application: The helper application runs on your development computer, creates the local database in isolated storage, 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 application to a folder on your computer. For more information about the Isolated Storage Explorer, see How to: Use the Isolated Storage Explorer Tool.

3. Create the primary application: Create the application 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 application from the folder where you saved it on your computer. To minimize the size of the application’s assembly, store the file as Content.

After a local database is deployed with an application, it resides in the installation folder in a read-only state. The installation folder is different than isolated storage. 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.

To modify the database containing the reference data, move it out of the installation folder and save it in isolated storage 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 isolated storage. The following example demonstrates how to address a database file in the installation folder when you create a stream object.

C#

Copy

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.

C#

Copy

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 application that uses a local database, see How to: Create a Basic Local Database Application for Windows Phone.

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

C#

Copy

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 Namespace.

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.

C#

Copy

// 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 isolated storage. 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 application that performs these operations, see How to: Create a Basic Local Database Application for Windows Phone.

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 application 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.

C#

Copy

// 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.

C#

Copy

// 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 application, as described in How to: Create a Basic Local Database Application for Windows Phone. The following code example shows an example of calling the SubmitChanges method when the user navigates away from the page.

C#

Copy

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.

C#

Copy

//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 application 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 Application for Windows Phone.

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 application during an upgrade.

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

C#

Copy

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 isolated storage, including local database files, are not altered during the application 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.

C#

Copy

// 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 in a Windows Phone Application.

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