WP 7 Local Database

Overview

To store and retrieve data in a local database, Windows PhoneProgramUse LINQ to SQL.

The LINQ to SQL object model consists of the system. Data. LINQ. datacontext object and can act as a proxy for the local database. During the execution of the LINQ to SQL statement, it is responsible for bridging the datacontext object and the data part (local database ). This relationship is summarized.

Data Context

The data context is a proxy and an object that represents the database. The data context contains several table objects, each of which represents a table in the database. Each table object is composed of entities of data rows in the corresponding database. Each object is a simple, traditional CLR object (POCO) with attributes ). The attributes on each object determine the database table structure and define the ing between the data object model and the database architecture. For example, an object with the name and phonenumber attributes will generate a database table with the name and phonenumber columns.

During the execution of LINQ to SQL

LINQ to SQL provides some object relationship ing functions. hosted applications can use these functions to communicate with relational databases (only Transact-SQL) using language Integrated Query (LINQ. The Object Model (managed by. NET Framework)CodeIng to a relational database. When your application is running, LINQ to SQL converts the language integration query to transact-SQL, and then sends the query to the database for execution. When the database returns the result, the result is converted back to your ownProgramming LanguageThe object to be processed. For more information, see LINQ to SQL.

Similarities and Differences

Similar to desktop applications that use SQL Server relational databases, Windows Phone applications can select, insert, update, and delete data from a local database through LINQ to SQL. In this way, your Windows Phone application can benefit from the powerful query functions of LINQ and the storage efficiency of relational databases. Because mobile phones have fewer resources than PCs, there are some differences between local databases and typical databases. These differences include:

• The local database runs in the Process of the Windows Phone application. Unlike the client server database (such as Microsoft SQL Server), it does not continue to run as a background service.
• The local database can only be accessed by the corresponding Windows Phone application. Because database files reside in independent storage, other applications cannot access the data.
• The local database can only be accessed through LINQ to SQL; the transact-SQL statement is not supported.

Step 1: create a connection string

 

Single Parameter usage

If you only use data source attributes, you do not need to include the attribute names in the connection string, as shown in the following example.

// Create the data context.

Mydatacontext DB = new mydatacontext ("isostore:/mydb. SDF ")

In this example, the isostore prefix indicates that the file is in the independent storage.

Read from the installation folder

The Installation Folder does not support write operations. When connecting to the local database, you must use the File mode attribute to specify the connection as read-only. The following example shows how to establish a read-only connection to the installation folder.

// Create the data context.

Mydatacontext DB = new mydatacontext ("Data Source = 'appdata:/mydb. SDF '; file mode = read only ;");

Create an encrypted database

To encrypt the database, you must specify a password in the connection string before creating the database. In this example, a password is specified before the database is created.

// Create the data context.

Mydatacontext DB = new mydatacontext ("Data Source = 'isostore:/mydb. SDF '; Password = 'securepassword ';");

 

// Create an encrypted database after confirming that it does not exist.

If (! DB. databaseexists () dB. createdatabase ();

Read-Only Connection

The following example shows how to connect to a large database in read-only mode.

// Create the data context.

Mydatacontext DB = new mydatacontext ("Data Source = 'mydb. SDF '; file mode = read only; Max database size = 256; Max buffer size = 1024 ;");

Create a database using a specific region

In this example, a database with German-language and case-sensitive sorting rules is created.

// Create the data context.

Mydatacontext DB = new mydatacontext ("Data Source = 'mydb. SDF '; Culture identifier = de-de; Case Sensitive = true ;");

// Create a database after confirming that it does not exist.

If (! DB. databaseexists () dB. createdatabase ();

Supported attributes

The following table lists all the parameters supported by the local database connection string.

Parameters	Description
Data Source or datasource	The file path and file name of the local database file. If you only specify this connection string attribute, you only need this attribute value to instantiate a data context object. Use the following prefix to explicitly specify the root location of the path. Isostore: path for independent storage Appdata: the path is applicable to the installation folder. If no prefix is specified, the file path applies to independent storage.
Password or PWD or database password or SSCE: Database Password	Database Password, up to 40 characters. If not specified, the default value is no password. If you enable encryption on the database, you need to use this attribute. If you specify a password, encryption is automatically enabled on the database. If you specify an empty password, the database will not be encrypted. Note: Encryption cannot be performed after the database is created. Databases are encrypted using AES-128, while passwords are hashed using SHA-256.
Max buffer size or SSCE: Max buffer size	The maximum amount of memory that can be used by the local database before the change is refreshed to the disk (in kilobytes ). If not specified, the default value is 384. Maximum Value: 5120.
Max database size or SSCE: Max database size	The maximum size (in MB) of the local database ). If not specified, the default value is 32. Maximum Value: 512.
Mode or file mode or SSCE: Mode	The mode used to open the database file. Valid values: Read Write: Allows multiple processes to open and modify the database. If the mode attribute is not specified, this value is the default setting. Read Only: allows you to open a read-only copy of the database. Exclusive: other processes are not allowed to open or modify the database. Shared read: When you open a database, other processes are allowed to read the database, but the database cannot be modified.
Culture identifier	The regional code used for the database. For example, en-US stands for "English (USA )" Note: If this attribute is used to connect to an existing database, it is ignored.
Case Sensitive or casesensitive	Boolean value used to determine whether database sorting rules are case-sensitive. You must set true to enable case-sensitive sorting rules. False indicates that the sorting rules are case-insensitive. If not specified, the default value is false. Note: If this attribute is used to connect to an existing database, it is ignored.
Important:
Some Microsoft SQL compact connection string parameters may apply to this Windows Phone application platform release. We recommend that you do not use any connection string parameters not listed in this topic.

 

Step 2: Create a table entity (which attributes are available in the 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 ");

}

}

}

 

// Define Item Name: Private field, public property, and database column.

Private string _ itemname;

 

[Column]

Public String itemname

{

Get {return _ itemname ;}

Set

{

If (_ itemname! = Value)

{

Notifypropertychanging ("itemname ");

_ Itemname = value;

Notifypropertychanged ("itemname ");

}

}

}

 

// Define completion value: Private field, public property, and database column.

Private bool _ iscomplete;

 

[Column]

Public bool iscomplete

{

Get {return _ iscomplete ;}

Set

{

If (_ iscomplete! = Value)

{

Notifypropertychanging ("iscomplete ");

_ Iscomplete = value;

Notifypropertychanged ("iscomplete ");

}

}

}

 

// Version column AIDS Update performance.

[Column (isversion = true)]

Private binary _ version;

 

 

// Internal column for the associated todocategory id value

[Column]

Internal int _ categoryid;

 

// Entity reference, to identify the todocategory "Storage" table

Private entityref <todocategory>

> _ Category;

 

// Association, to describe the relationship between this key and that "Storage" table

[Association (storage = "_ category", thiskey = "_ categoryid", otherkey = "ID", isforeignkey = true)]

Public todocategory category

{

Get {return _ category. entity ;}

Set

{

Notifypropertychanging ("category ");

_ Category. entity = value;

 

If (value! = NULL)

{

_ Categoryid = value. ID;

}

 

Notifypropertychanging ("category ");

}

}

 

 

# Region inotifypropertychanged members

 

Public event propertychangedeventhandler propertychanged;

 

// Used to sort y that a property changed

Private void policypropertychanged (string propertyname)

{

If (propertychanged! = NULL)

{

Propertychanged (this, new propertychangedeventargs (propertyname ));

}

}

 

# Endregion

 

# Region inotifypropertychanging members

 

Public event propertychangingeventhandler propertychanging;

 

// Used to modify y that a property is about to change

Private void policypropertychanging (string propertyname)

{

If (propertychanging! = NULL)

{

Propertychanging (this, new propertychangingeventargs (propertyname ));

}

}

 

# Endregion

}

 

To use the local database function in the code, add the following command at the top of the code file.

 

Using system. Data. LINQ;

Using system. Data. LINQ. Mapping;

Using Microsoft. Phone. Data. LINQ;

Using Microsoft. Phone. Data. LINQ. Mapping;

 

The following table shows some common attributes of LINQ ing to SQL. For a complete list, see system. Data. LINQ. Mapping namespace.

attribute	example	description
tableattribute	[Table]	specify a class as the object class associated with the database table.
columnattribute	[column (isprimarykey = true)]	associate a class with columns in the database table. Isprimarykey specifies the primary key. By default, an index is created for it.
indexattribute	[index (columns = "column1, column2 DESC", isunique = true, name = "multicolumnindex")]	write data at the table level, specifying other indexes on the table. Each index can cover one or more columns.
associationattribute	[Association (storage = "thisentityrefname", thiskey = "thisentityid", otherkey = "targetentityid")]	specifies the associated attributes, for example, the foreign key associated with the primary key.

 

Step 3: Create the context and Table Structure

To create a local database, you must first define the data context and entity.

These classes define the ing between the data object model and the database architecture.

Based on the detailed information of the mappings, the object relationship function of LINQ to SQL creates a relational database mapped to the corresponding data context.

For each object, use the LINQ to SQL ing attribute to specify the ing details. These attributes specify database-specific functions, such as tables, columns, primary keys, and indexes.

For example, the following code displays the data context named tododatacontext and the beginning of the object 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.

PublicTable <todoitem> todoitems;// Table

}

 

// 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 ");

}

}

}

...

...

...

 

Step 4: Create and connect to the created database

After creating a datacontext object, you can create a local database and perform other database operations. The following code example shows how to create a database based on the tododatacontext 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 ();

}

}

Instance:

// Specify the local database connection string.

String dbconnectionstring = "Data Source = isostore:/todo. SDF ";

 

// Create the database if it does not exist.

Using (tododatacontext DB = new tododatacontext (dbconnectionstring ))

{

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 ();

}

}

Step 5: Use the database (add, delete, query, and modify) Select data (Query )

In the following example, you can use LINQ to SQL to query the datacontext object named tododb, and the result is placed in the observablecollection OF THE todoitem object named todoitems. Due to delayed execution, the database query will not be executed until the todoitems collection is instantiated.

Need to learnRelated Syntax of LINQ

// 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 );

 

Insert count Data (Increase)

The process of inserting data into the database is divided into two steps. First, add an object in the data context, and then call the data context submitchanges method to save the data as a row in the database.

In the following example, A todoitem object is created and added to the todoitems observability set and the database table corresponding to 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:

The data is not saved to the database until the submitchanges method is called.

Update count Data (modification)

Update Data in the local database in three steps. First, query the objects to be updated in the database. Then, modify the object as needed. Finally, call the submitchanges method to save the changes to the local database.

Protected override void onnavigatedfrom (system. Windows. Navigation. navigationeventargs E)

{

// Call base Method

Base. onnavigatedfrom (E );

// Save changes to the database

Tododb. submitchanges ();

}

Important:

The data is not updated to the database until the submitchanges method is called.

Delete count Data (delete)

There are also three steps to delete data in the database:

First, query the objects to be deleted in the database.

Then, call the deleteonsubmit or deleteallonsubmit method to delete one or more objects as needed to put these objects in the pending deletion state.

Finally, call the submitchanges method to save the changes to the local database.

In the following example, A todoitem object is deleted from the database named tododb. Because 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:

Data will not be deleted from the database until the submitchanges method is called.