Windows Phone mango programming practices-Local Database

ArticleDirectory

• Database Query
• Update Data
• Change database architecture
• Database Security

 

Windows Phone mango programming practices

Windows Phone mango Programming Practice

Local Database

Windows Phone mango can store relational data in a local database, and the local database is stored as a file in the ApplicationProgramIndependent storage space. The Windows Phone application uses LINQ to SQL to perform all database operations. To define the database architecture, select data, and save the changes to the database file in the independent bucket. LINQ to SQL is the database of the. NET Framework's ORM (Object relationship ing) platform. When an application executes a LINQ statement at run time, it converts it to a Transact-SQL statement to perform operations on the database. Once the database returns the query result, the data in the LINQ to SQL statement is converted to an application object.

LINQ to SQL provides an object-oriented method for processing data, including object models and runtime. The LINQ to SQL object model is composed of the system. Data. LINQ. datacontext object, which acts as the local database of the proxy server. The datacontext object that is used to connect to the local database and applications during the execution of LINQ to SQL ,. The datacontext object is the object where the data context contains the table. Each of the objects represents the table in the database.

Figure 6-4 LINQ to SQL

The Windows Phone mango application uses the LINQ to SQL to access the local database. The LINQ to SQL provides an object-oriented method for operating the data stored in the database.System. Data. LINQ. datacontextClass connects the object model of the application to the data in the database.

After creating a local database, you can use the local database using the LINQ and data context. The following describes how to select, insert, update, and delete data in a database.

Database Query

Windows Phone uses language-Integrated Query (LINQ) to query databases. Because the objects referenced by SQL query in LINQ are mapped to records in the database, the method of executing queries by LINQ to SQL is different from that of other LINQ technologies. Typical LINQ queries are executed in the application layer in the memory. In LINQ to SQL, the runtime Object Relational capabilities used with each of the LINQ queries are translated in TRANSACT-SQL and then executed directly in the database. In this way, the performance will be significantly enhanced when the query of a few records of a large database is selected.

In the following exampleTododbOfDatacontextPut the object's LINQ to SQL query and resultObservablecollectionOfTodoitemObject NameTo-do list. The database query delayed because of execution is not actually executed beforeTo-do listThe 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 );

 

Insert data

Inserting data into the database is a two-step process. First, add the object to the data context, and then callSubmitchangesTo keep the data as the data context of the row in the database.

In the following example, createTodoitemObject and add itTo-do listThe observed set and corresponding database table are namedTododbData context.

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

Note:

The data that calls the submitchanges method is not saved to the database.

Update Data

There are three steps to update data in the local database. First, query in the object database to be updated. Second, modify the required object. Finally, callSubmitchangesTo save the changes to the local database.

The followingCodeThe example shows the applicationOnnavigatedfromMethodCallSubmitchangesUpdate Data in the local database. Data is not updated to the database before the submitchanges method is called.

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

{

// Call base Method

Base. onnavigatedfrom (E );

 

// Save changes to the database

Tododb. submitchanges ();

}

Delete data

There are also three steps to delete data in the database. First, the objects deleted from the queried database. Then, it depends on whether you want to delete one or more objects.DeleteonsubmitOrDeleteallonsubmitMethods, which are placed in the pending deletion status. Finally, callSubmitchangesTo save the changes to the local database.

In the following exampleTodoitemThe object is namedTododb. Because only one object will be deletedSubmitchangesPreviously calledDeleteonsubmitMethod.

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

Note:

Until the submitchanges method is called, data is not deleted from the database.

Change database architecture

The widows phone application may need to change the local database architecture.Microsoft. Phone. Data. LINQThe namespace providesDatabaseschemaupdaterClass.

DatabaseschemaupdaterClass can execute a database, for example, adding tables, columns, indexes, or association additives for changes. For more complex changes, you need to create a new database and copy the data to the new architecture. The databaseschemaupdater class provides databaseschemaversion attributes that can be used programmatically to differentiate different versions of your database.

The database does not reflect updates from the databaseschemaupdater object until the execute method is called. When this method is called, all changes are committed to the local database as a single transaction, including version updates.

The following example shows how to use the databaseschemaupdater class to modify databases based on the databaseschemaversion attribute.

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:

During application updates, no independent storage is changed, including files saved in local database files.

Database Security

The local database provides password protection and encryption to help protect your database. When you use the database password, encrypt the entire database.

The following example shows how to create an encrypted database.

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

Note:

If only a limited number of non-index columns need to be encrypted, You can encrypt the data before it is added to the database, instead of encrypting the entire database to achieve better performance.