Mango release adds a local database engine to the device. The database engine on the phone is based on SQL CE engine. Mango applications use LINQ to SQL for all database operations. LINQ to SQL provides an object-oriented approach to working with data and is comprised of an object model and a runtime. The database files stored in Isolated Storage on the phone and available to the application only (which means applications cannot share same database).
Suggested scenarios for local database usage in application vary between the following:
- “Shopping list” style applications
- Complex Schema databases -- usually has few tables (5-7), hundreds of records, but numerous relations and constraints and foreign keys
- “Dictionary” style applications
- Reference data – usually has a huge amount of static reference data with very few tables (2-3) and constraints. The tables (1-2 of them) holds huge amount of data (500K-1M)
- “Local cache” for applications
- Local cache for data fetched from cloud, sometime in combination with application specific data. Usually very few additional tables with relatively simple data; usually hundreds of records
LINQ to SQL provides object-relational mapping capabilities that allow your managed application to use Language Integrated Query (LINQ) to communicate with a relational database. LINQ to SQL maps the object model, which you express with .NET-managed code, to a relational database. When your application runs, LINQ to SQL translates language-integrated queries into database “language” and then sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.
LINQ to SQL works with DataContext, which defines the object model of the data. Usually DataContext defines the data using “plain old CLR objects” (POCO) and attributes convention. To create your own DataCntext classes derive them from DataContext base class. The DataContext uses managed class which defines database structure by defining table structure and mapping between the object model and the schema if the database. The mapping created by decorating objects with mapping attributes. These attributes specify database specific features such as tables, columns, primary keys, indexes, etc.
The DataContext and mapped class used to crate a local database on the phone. The code-first approach which uses managed code to define the schema and create database from it is a preferred approach for Windows Phone applications. Some key points about local database created by application:
- A local database runs only with parent application
- The database is not shared across different application on the device and accessible only by parent application
- A local database supports only LINQ to SQL query mechanism; T-SQL is not supported
Let’s define simple DataContext class and simple class with mappings. First, we have to start from creating managed classes and map them into desired database schema. In our simple case the database schema will like the following picture:
The Divisions table definition class is very simple:
[Table]
public class Divisions
{
public Divisions()
{
this._Employees = new EntitySet<Employees>(new Action<Employees>(attach_Employees), new Action<Employees>(detach_Employees));
}
[Column(IsPrimaryKey = true)]
public int ID { get; set; }
[Column]
public string Name { get; set; }
}
And Employees table:
[Table]
[Index(Name = "NameIndex", Columns = "FirstName,LastName", IsUnique = false)]
public class Employees
{
public Employees()
{
this._Division = default(EntityRef<Divisions>);
}
[Column(IsPrimaryKey = true)]
public int ID { get; set; }
[Column()]
public int DivisionID { get; set; }
[Column]
public string FirstName { get; set; }
[Column]
public string LastName { get; set; }
[Column(DbType = "DateTime", CanBeNull = true)]
public DateTime? Birthday { get; set; }
[Column]
public bool IsActive { get; set; }
}
The only thing missing in those classes is a relation between them. In LINQ to SQL we need to add a property attributed by Association attribute. The same attribute should be added on both parts of the relation. The Division class should hold a set of entities of Employee type, thus we are adding a property of EntitySet<T> type:
private EntitySet<Employees> _Employees;
[Association(Name = "FK_EmployeesDivision", Storage = "_Employees", ThisKey = "ID", OtherKey = "DivisionID")]
public EntitySet<Employees> Employees
{
get { return _Employees; }
set { _Employees.Assign(value); }
}
In order to properly initialize this property let’s change the class’ constructor and add two helper methods:
public Divisions()
{
this._Employees = new EntitySet<Employees>(
new Action<Employees>(attach_Employees),
new Action<Employees>(detach_Employees));
}
private void attach_Employees(Employees entity)
{
entity.Division = this;
}
private void detach_Employees(Employees entity)
{
entity.Division = null;
}
This code takes care of proper initialization of the Employees entity for current Division. Now it is a time to change the Employee class. It has a relation to entity of Division type, thus we need to add property of type EntityRef<T>:
private EntityRef<Divisions> _Division;
[Association(Name = "FK_EmployeesDivision", Storage = "_Division", ThisKey = "DivisionID", OtherKey = "ID", IsForeignKey = true)]
public Divisions Division
{
get { return _Division.Entity; }
set
{
Divisions previousValue = this._Division.Entity;
if (((previousValue != value)
|| (this._Division.HasLoadedOrAssignedValue == false)))
{
if ((previousValue != null))
{
this._Division.Entity = null;
previousValue.Employees.Remove(this);
}
this._Division.Entity = value;
if ((value != null))
{
value.Employees.Add(this);
DivisionID = value.ID;
}
else
DivisionID = default(int);
}
}
}
Lastly we need to initialize the EntityRef variable – we will change the constructor:
public Employees()
{
this._Division = default(EntityRef<Divisions>);
}
Now it’s a time to create our DataContext class:
public class SampleDC : DataContext
{
public SampleDC(string connectionString)
: base(connectionString)
{
}
public Table<Employees> Employees;
public Table<Divisions> Divisions;
}
From now on, the SampleDC could be used to create the database on the device and to perform operations on it.
Well, this was pretty easy, but what if you plan to use slightly bigger (and more complex) database? Fast search in my laptop returned me an AdventureWorks SQL CE sample. The schema of this database definitely makes a good candidate for Mango database feature sample:
(I had to minimize the schema in order to fit in on the page 
)
So, simple ah??? The DataContext for this database will end up at ~2.5K lines of code… How about generating this DataContext manually? Clearly, that generating a DataContext for this database is not simple process. So it is not possible to use real-world databases in Windows Phone?
Well… It is clearly possible, but we need some “external” help to create the lengthy DataContext class. Let’s create a DataContext. Windows SDK provides handy utility to create LINQ to SQL DataContext for full .NET framework from SQL Server based or Compact SQL Server based databases. It called SqlMetal. We will use it to create DataContext file.
Please not, that SqlMetal doesn’t directly support Windows Phone SQL CE databases and thus SqlMetal-generated DataContext files will not compile right away in your Windows Phone projects. Use them as a start point to save a lot of coding work.
Let’s generate the ExternalDC.cs file (we will use it in sample project after the generation):
%ProgramFiles(x86)%\Microsoft SDKs\Windows\v7.0A\Bin>SqlMetal.exe
/code:"D:\My Documents\Blog\Samples\DatabaseSample\Data\ExternalDC.cs"
"D:\My Documents\Blog\Samples\DatabaseSample\Data\ExternalDB.sdf"
After adding the generated file to the project we need to remove two constructors unsupported on Mango:
public ExternalDB(System.Data.IDbConnection connection) :
base(connection, mappingSource)
{
OnCreated();
}
public ExternalDB(System.Data.IDbConnection connection,
System.Data.Linq.Mapping.MappingSource mappingSource) :
base(connection, mappingSource)
{
OnCreated();
}
After removing them the file compiles and our complex DataContext is ready.
Once we created the DataContext we can create the database on the device. The database creation takes care of security options such as database password and encryption. Let’s create non-encrypted database first:
theDC = new SampleDC(DBName);
if (!theDC.DatabaseExists())
theDC.CreateDatabase();
The DBName is the database file location. The file location must be according to the following format:
isostore:/DIRECTORY/FILE.sdf
To help secure the database you could password protect the database access and encrypt whole database data. When you use a password with the database, the entire database is encrypted. To encrypt the database, provide a password in the database connection string before you create the database and each time you access the database, you will need to provide the password. The DBName in case of encryption will have the connection string which must be according to the following format:
"Data Source='isostore:/DIRCTORY/FILE.sdf';Password='MySecureP@ssw0rd'"
This connection string must to be used every time you access the encrypted database.
After the local database has been created you can use LINQ to SQL queries with the DataContext instance to access the database and stored data.
LINQ to SQL is used to query the database. LINQ queries works with DataContext instance to query for data. The following code sample selects non active employees from the database (mapped through the DataContext) and orders them by LastName:
var res = from emp in mainDB.Employees
where emp.IsActive == false
orderby emp.LastName
select emp;
To insert data to the database with we use two-phase approach. First we need to create a new object and add it to the DataContext and then execute the SubmitChanges function to persist the changes into the database:
Employees employee = new Employees();
employee.ID = rnd.Next();
employee.FirstName = "John";
employee.LastName = "Doe";
employee.IsActive = true;
employee.DivisionID = 103;
mainDB.Employees.InsertOnSubmit(employee);
mainDB.SubmitChanges();
Same approach works for multiple objects, but in case of multiple objects we need to create IEnumarable<T> first and call to InsertAllOnSubmit function:
List<Employees> employees = new List<Employees>();
Employees employee = new Employees();
employee.ID = rnd.Next();
employee.FirstName = "John";
employee.LastName = "Doe";
employee.IsActive = true;
employee.DivisionID = 103;
employees.Add(employee);
employee = new Employees();
employee.ID = rnd.Next();
employee.FirstName = "Nicole";
employee.LastName = "Patterson";
employee.IsActive = false;
employee.DivisionID = 104;
employees.Add(employee);
employee = new Employees();
employee.ID = rnd.Next();
employee.FirstName = "Vito";
employee.LastName = "Carleone";
employee.IsActive = true;
employee.DivisionID = 101;
employees.Add(employee);
mainDB.Employees.InsertAllOnSubmit(employees);
mainDB.SubmitChanges();
Updateing and deleting data requires three-stage process - first we need to find an object (or objects) to update/delete, then update/delete it from DataContext and finally SubmitChanges.
Update:
var res = from emp in mainDB.Employees
where emp.FirstName == "Michael" && emp.LastName == "Jackson"
select emp;
Employees employee = res.FirstOrDefault();
if (null != employee)
employee.IsActive = !employee.IsActive;mainDB.SubmitChanges();
Delete:
var res = from emp in mainDB.Employees
where emp.FirstName == "Nicole" && emp.LastName == "Patterson"
select emp;
Employees employee = res.First();
if (null != employee)
mainDB.Employees.DeleteOnSubmit(employee);
mainDB.SubmitChanges();
The approach used to update/delete multiple records similar to one used to insert multiple records. Those method used to delete multiple records called DeleteAllOnSubmit.
Let’s get back to the AdventureWorks database. Clearly that in case of such big and complex database with decent amount of “system” data it is not possible to create the database on the device. In such case we need to package the database file as a Content resource of the application, copy it to application’s Isolated Storage and use generated DataContect to access the data:
The following code snippet pulls the application resource stream, sends it to helper method which returns the stream content as array of bytes and finally saves those bytes to application’s Isolated Storage.
public static void CreateExternalDatabase(string DBName)
{
Stream str = Application.GetResourceStream(new Uri("Data/" + DBName, UriKind.Relative)).Stream;
using (IsolatedStorageFile isoStore = IsolatedStorageFile.GetUserStoreForApplication())
{
IsolatedStorageFileStream outFile = isoStore.CreateFile(DBName);
outFile.Write(ReadToEnd(str), 0, (int)str.Length);
str.Close();
outFile.Close();
}
}
public static byte[] ReadToEnd(Stream stream)
{
long originalPosition = stream.Position;
stream.Position = 0;
try
{
byte[] readBuffer = new byte[4096];
int totalBytesRead = 0;
int bytesRead;
while ((bytesRead = stream.Read(readBuffer, totalBytesRead, readBuffer.Length - totalBytesRead)) > 0)
{
totalBytesRead += bytesRead;
if (totalBytesRead == readBuffer.Length)
{
int nextByte = stream.ReadByte();
if (nextByte != -1)
{
byte[] temp = new byte[readBuffer.Length * 2];
Buffer.BlockCopy(readBuffer, 0, temp, 0, readBuffer.Length);
Buffer.SetByte(temp, totalBytesRead, (byte)nextByte);
readBuffer = temp; totalBytesRead++;
}
}
}
byte[] buffer = readBuffer;
if (readBuffer.Length != totalBytesRead)
{
buffer = new byte[totalBytesRead];
Buffer.BlockCopy(readBuffer, 0, buffer, 0, totalBytesRead);
}
return buffer;
}
finally
{
stream.Position = originalPosition;
}
}
Accessing the database created such way is similar to database created from DataContext on the device:
externalDB = new ExternalDB(ExternalDBFileName);
var products = (from p in externalDB.Products
select p).Take(10);
Last part of this post will focus on database schema updates. Normal Windows Phone application lifestyle includes updating the application from time to time. This also could introduce changes in the database schema. In most case we want to preserve user data stored in the database, that’s why we need to Update the database using special class – DatabaseSchemaUpdated from Microsoft.Phone.Data.Linq namespace. 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:
DatabaseSchemaUpdater schemaUpdater = theDC.CreateDatabaseSchemaUpdater();
if (schemaUpdater.DatabaseSchemaVersion < 2)
{
//Incompatible change detected, create new database,
//copy data from old database to new and delete old database
MigrateDataToNewDatabase(theDC);
}
else if (schemaUpdater.DatabaseSchemaVersion == 2)
{
//Compatible change. Add new column to existing table
//NOTE: IsRetired is a new property defined in Employees
schemaUpdater.AddColumn<Employees>("IsRetired");
}
The DatabaseSchemaUpdater allows adding Table, Column, Index and Association. The new additions have to be defined in the corresponding DataContext class first. In the previous code snippet, the updated Employee class has additional column, named IsRetired:
[Column]
public bool IsRetired { get; set; }
This is it for now…
Sample used to demonstrate those features hosted here.
Stay tuned for part 2 – “Local Data Access”
Alex