Notes on Local SQL CE Databases and WP 7.1 Mango

Introduction

In Windows Phone 7.1 Beta “Mango” tools, support for a local relational database in a client application was added. This database is based on SQL CE 4, and is stored in the application’s isolated storage (see image below). Databases need to be designed in a code-first pattern, using POCOs (plain old CLR objects) to define and create the database. It would be tempting to create a SQL CE 4 database in our project, and include it in the XAP, but then this database would be an embedded resource, not a local database, and XAP file size would be increased (not even our XapOptimizer minimizer/obfuscator could help you with the database). Since databases are not included with our application download, we need to include code to populate it with any seed data. The MSDN documentation for working with local databases starts at http://msdn.microsoft.com/en-us/library/hh202865(v=VS.92).aspx.aspx "http://msdn.microsoft.com/en-us/library/hh202865(v=VS.92).aspx").

[update 2011-05-29]

I say above the database is SQL CE 4, but Chris Walsh has evidence showing it's 3.5 (http://twitpic.com/52m94i). As with the Silverlight version, it may actually neither 3.5 nor 4. I'll see what I can find out and post updates here.

[/update]

[update 2011-06-02]

There is a way to copy an embedded resource database to isolated storage, see http://erikej.blogspot.com/2011/06/populating-windows-phone-mango-sql.html. Again, consider the size of your download, especially if your app is to be delivered OTA.

[/update]

Windows Phone DataContext and Local Database

(the above image is copied from documentation)

Linq-to-SQL is used to set the database context and query the local database in a Windows Phone 7 application. The Mango implementation of Linq-to-SQL is an incomplete version of that used in the full CLR. The implementation seems to be complete enough for most data-centric applications, but Linq-to-SQL masters might find some of the missing functionality a step backwards.

Because space is limited on a WP7 device, there are limits to our database’s size. The default maximum database size is 32 MB, and the maximum allowable size is 512 MB. If more space is needed, especially if images are involved, it might be time to think about storing data in Azure. Sharding might be possible, but probably wouldn’t be a best practice on a phone.

Linq-to-SQL on Windows Phone

There are some differences between the full Linq-to-SQL, and the implementation on WP7 (see http://msdn.microsoft.com/en-us/library/hh202872(v=VS.92).aspx.aspx "http://msdn.microsoft.com/en-us/library/hh202872(v=VS.92).aspx") for the full story). Notably:

  • The ExecuteCommand method is not supported, so there is no ability to directly execute T-SQL statements. Since stored procedures aren’t supported in SQL Server CE 4.0 anyway, this shouldn’t be a major concern, but we also cannot execute DDL or DML statements. If the database schema needs to be updated after it is created, we can use the DatabaseSchemaUpdater object.
  • No ADO.NET objects, such as DataReaders or DataTables. Linq queries usually return an anonymously-typed IQueryable, which is awesome if you’ve never used these. Anonymous typing basically means the type is determined when the object is created (e.g., when results are returned). An IQueryable list is very easy to manipulate, and you can bind iQueryable lists directly to our components (FTR--you can bind any IEnumerable to our components, and IQueryable inherits IEnumerable).
  • Since SQL CE is the underlying database, and SQL CE doesn’t fully support every SQL Server data type, we can only use SQL CE data types. SQL CE supported data types and limitations can be found at http://msdn.microsoft.com/en-us/library/ms172424(SQL.110).aspx.aspx "http://msdn.microsoft.com/en-us/library/ms172424(SQL.110).aspx").
  • The Linq-to-SQL API is only partially implemented on Mango. Linq-to-SQL masters should refer to the tables at http://msdn.microsoft.com/en-us/library/hh202872(v=VS.92).aspx.aspx "http://msdn.microsoft.com/en-us/library/hh202872(v=VS.92).aspx") before counting on something being implemented.

Very basic query samples are in the walk-through, but the real power of Linq is seen in the 101 Linq Samples code samples, found at:

Connection Strings and the Data Context

With SQL CE on the phone, connections aren’t made in the traditional ADO.NET sense. Instead, we use a DataContext, more similar to Linq-to-SQL or Entity Framework. A DataContext object provides us with a set of methods for accessing data, but has the added advantage of knowing our database’s schema before connecting to the database (in this case, we had to define the schema to create our database). This allows us to work completely disconnected from our data, but still have the string typing when we write our queries and results handling code.

The pointer to the database “server” is “isostore:”. There are three main parameters to know for connection strings:

  • datasource (required) – the name of the database file
  • Password (optional) – SQL CE 4 databases can be password encrypted (they don’t have users like SQL Server does). This specifies the password used to encrypt the database. Note that a database must be encrypted when it is created—we can’t go back later and encrypt a database.
  • Mode (optional) – Database can be opened in read/write, read only, exclusive or shared modes. Typically we’ll use read/write or read, and it might be beneficial to have two separate data contexts in an application—one which opens the database in read only mode, and another used by administrative or update code which opens it in read/write mode.

A simple data context looks like this (copied directly from the docs):

// Create the data context.
MyDataContext db = new MyDataContext ("isostore:/mydb.sdf")

The MSDN doc for connection strings can be found at http://msdn.microsoft.com/en-us/library/hh202861(v=VS.92).aspx.aspx "http://msdn.microsoft.com/en-us/library/hh202861(v=VS.92).aspx").

Creating the Database

Since databases are described in a code-first scenario, we write our classes and use attributes to dictate how the tables are created. Each class corresponds to a single table, and we decorate the class with the [Table] attribute. Properties are decorated with [Column], and parameters are used to dictate primary keys, data types, nullability, and so on.

A partial example from the documentation is shown below, showing the class and property level attributes which define a ToDoItem table in the database (the table name is automatically generated from the class name):

[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");
}
}
}

A list of the attributes and parameters is found at http://msdn.microsoft.com/en-us/library/bb386971(v=VS.100).aspx.aspx "http://msdn.microsoft.com/en-us/library/bb386971(v=VS.100).aspx"). The database is then created at run time with the following code:

// Create the database if it does not exist.
using (ToDoDataContext db = new ToDoDataContext(ToDoDataContext.DBConnectionString))
{
if (db.DatabaseExists() == false)
{
//Create the database
db.CreateDatabase();
}
}

Updating the Database

Since the databases are stored in the application’s Isolated Storage, which is separate from the application space, application updates will not affect the database. Should database schema changes be necessary, we can use the DatabaseSchemaUpdater object (documentation was not available when I wrote this post, but an example can be found at http://msdn.microsoft.com/en-us/library/hh202860(v=VS.92).aspx.aspx "http://msdn.microsoft.com/en-us/library/hh202860(v=VS.92).aspx")). There is a DatabaseSchemaVersion property we can apply to our databases; the sample copied from the documentation is seen below:

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("TaskURL");  
        dbUpdate.DatabaseSchemaVersion = 6;  
        dbUpdate.Execute();  
    }

}

I’m updating some samples, and will have a demo application available soon.

MESCIUS inc.

comments powered by Disqus