Windows Phone 7.5 - Local SQL Database(简介）

http://www.silverlightshow.net/items/Windows-Phone-7.1-Local-SQL-Database.aspx

 

Once you start programming Windows Phone just after the first experiments, when you begin writing something of real, immediately you need something to store information locally.

Also if the phone is a mobile device and it is almost always connected, there are information you cannot store remotely but you need to have always available. I'm speaking for sure about configuration settings but also about locally cached relational data that is required to run the application fast, without continuously access the network.

 

In Windows Phone 7.0 the sole storage available was the Isolated Storage but it appear like a filesystem and it requires lot of code to serialize and deserialize entities. Some open source solutions has been created to work around this problem. I for sure remember Sterling, a real object oriented no-sql database that I used often to write my own applications. In the upcoming release of Windows Phone 7.1, Microsoft decided to add a Local Database implementation that is targeted to business applications and is created on top of SQL Server CE. This new feature completes the storage solutions available for the phone, supporting scenario where you need a real relational database and is really easy to configure and use. It does not totally override other solutions. Sterling remains a good solution for many scenarios, but having a real relational store is almost wonderful in many cases.

How does it work.

As I've said, the new Local Database available in Windows Phone 7.1, is based on the SQL Server Compact Edition, a well known and popular edition of SQL Server, originally created to support Windows Mobile and Windows CE with a local relational store. Due to the fact that under the hoods of the Windows Phone 7.x there is Microsoft .NET Compact Framework 3.7, SQL CE is a obvious choice and it is for sure a solid and realiable tool to support real world applications.

But, as you know, the Silverlight API in Windows Phone 7 does not includes ADO.NET so the access to a SQL CE database cannot be done using normal SQL queries. For this purpose the WP7's team taken the existing LinqToSQL code and ported it to the phone. This means that to access the SQL CE storage you have to use linq queries and the DataContext to modify and extract information from the database. LinqToSQL is not a real object oriented database. It use a simple attribute-based mapping that does not abstract so much the relational structure to a real consistent domain model and this is the reason why Microsoft decided to override it with the Entity Framework, but for the purpose of creating and accessing a local database it is a good, easy and realiable choice.

A SQL CE Database is usually associated with SDF files. In Windows Phone SDF still exists but they are placed into the isolated storage so the first thing you notice is a special connection string format that follow this pattern:

isostore://file.sdf

As a side consideration, having the sdf in isolated storage implies that the access to the file is strictly related to the application that creates it. With this release there is not any way of sharing a database between multiple application without duplicating it across different isolated storage directories.

From the developer point of view, working with a SQL CE database means adding a reference to an assembly (System.Data.Linq.dll). Once the reference is added there is not any requirement of deploying SQL CE runtime, because it is part of the Windows Phone 7.1 so it does not increase the size of your application.

Mapping entities to tables

In LinqToSQL there is a direct association between tables and entities so you will always have an entity mapped to each table, also in many-to-many scenarios where the relation table is represented by a real entity instead of two crossing collection as you expect. So in a Product/Order domain you will have also a ProductsOrders entity representing the many-to-many relation between the Product and Order entities.

The mapping of entities then, is really simplified because you have to create a class for each table, and always one-to-many associations. For this purpose you have a small set of attributes you can apply to the classes. TableAttribute to describe the table, ColumnAttribute for the fields and AssociationAttribute for relationships. Your entities are by default POCO object so you can add your own inheritance for the purposes of you application. As an example you can implement INotifyPropertyChanged to support databinding but you can also derive from base classes for same specific scenario. The following snippet shows an entity related to a feed with a bunch of properties mapped:

[Table(Name = "Feeds")]
public abstract class Feed
{
    [Column(IsPrimaryKey = true)]
    public int ID { get; set; }
    [Column]
    public string Title{ get; set; }
    [Column]
    public string Description{ get; set; }
    [Column]
    public string Address{ get; set; }
    [Column]
    public DateTime DateCreated { get; set; }
}

Since the TableAttribute simply names the table on the database,  the ColumnAttribute is able to determine the behavior of the field. It has a number of properties defined. In this snippet you see I've defined the primary key using the IsPrimaryKey property.

To map associations you have to work on both sided of the relationship. This means adding a collection property on the "many" side and a reference property ong the "one" side. Here is an example of the relation between Feed and Post where Feed has many Posts:

// on the Feed side //////////////////////////////////////////////////////////////

private readonly EntitySet<Post> postsRef = new EntitySet<Post>();

[Association(Name = "FK_Feed_Posts", Storage = "postsRef", ThisKey = "ID", OtherKey = "FeedID")]
public EntitySet<Post> Posts
{
    get { return this.postsRef; }
}

// on the Post side //////////////////////////////////////////////////////////////

private EntityRef<Feed> feedRef = new EntityRef<Feed>();

[Association(Name = "FK_Feed_Posts", Storage = "feedRef", ThisKey = "FeedID", OtherKey = "ID", IsForeignKey = true)]
public Feed Feed
{
    get { return this.feedRef.Entity; }
    set
    {
        Feed previousValue = this.feedRef.Entity;

        if (previousValue != value || this.feedRef.HasLoadedOrAssignedValue == false)
        {
            if (previousValue != null)
            {
                this.feedRef.Entity = null;
                previousValue.Posts.Remove(this);
            }

            this.feedRef.Entity = value;

            if (value != null)
            {
                value.Posts.Add(this);
                this.FeedID = value.ID;
            }
            else
            {
                this.FeedID = default(int);
            }
        }
    }
}

Expecially the Post side, it requires a number of lines of code, but since the code is always the same I suspect you can work to create a Visual Studio snippet to shorten and automate its creation.

The mapping is for sure simple and probably it sounds trivial to a refined palate used to the complex mapping of modern ORMs. However it reveal some surprises if you go deep analyzing the model. As LinqToSQL it supposts a raw Inheritance "table-per-hierarchy" mapping. For people that is not use to ORMs lingo, "table-per-hierarchy" is the simpler model for mapping inheritance of entities. It involve to have a single table mapping all the inherited entities so the fields that are not in common between inherited entities have to be nullable. Here is how to map inheritance:

[Table(Name = "Feeds")]
[InheritanceMapping(Code = "ATOM", Type = typeof(AtomFeed))]
[InheritanceMapping(Code = "RSS", Type = typeof(RssFeed), IsDefault = true)]
public abstract class Feed
{
    [Column(IsDiscriminator = true)]
    public string Type{ get; set; }

    // other properties here (these are common to other types
}

public class AtomFeed : Feed
{
    [Column(CanBeNull = true)]
    public string Email { get; set; }
}

public class RssFeed : Feed
{
    [Column(CanBeNull = true)]
    public string Generator { get; set; }
}

As you can see AtomFeed and RssFeed share all the properties of the base class but add other properties that are specific to each type. These are decorated with "CanBeNull" property because the row on the table will always contains all the properties of all the inherited entities. It is not the optimal solution but it is better than nothing.

Creating the database

Also if at the basis of SQL CE there is SDF files, you should never directly access them but you have to manage you database directly from code. If you search the internet you will find some examples to download the SDF from the phone and the upload it again, but in this article I will cover the official way. To me the creation of the structure of the database is very straightforward using code but I think someone can take advantage of the direct access to the SDF providing pre-populated databases.

The last thing you have to do before creating the physical database, just after creating entities, is a special class called DataContext. The DataContext wraps all the operations you can do with a database like inserting, updating and deleting data and exposes a number of sets, one for each "table" you have in the SDF. The DataContext appear like this:

public class FeedReaderDataContext : DataContext
{
    public const string ConnectionString = "isostore:/feeds.sdf";

    public FeedReaderDataContext(string connectionString)
        : base(connectionString)
    {
        this.Feeds = this.GetTable<Feed>();
        this.Posts = this.GetTable<Post>();
    }

    public Table<Feed> Feeds { get; set; }
    public Table<Post> Posts { get; set; }
}

In this example I show a datacontext made to store blog Feeds and Posts so in the first line I have a constant string with the connection string "isostore://feeds.sdf". Then I have two properties mapping the sets. A set is a collection created with the Table<T> class and gives a name to the "table" in the database. In the constructor I call the GetTable<T>() method that connects the DataContext sets with the SDF tables.

After the DataContext is in place you have to write code to create the database if it is not already in the isolated storage. For this purpose you have the CreateDatabase() method and the DatabaseExists() method. The pattern I use for the data access is creating a repository class that wraps the DataContext with my custom data access methods. In the repository I've a static method "Initialize" that is in charge of checking the existing database and eventually create it.

public class FeedReaderRepository
{
    public static void Initialize()
    {
        using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
        {
            if (dc.DatabaseExists() == false)
            {
                dc.CreateDatabase();

                // add here extra initialization like creating required rows

                dc.SubmitChanges();
            }
        }
    }
}

In place of the comment I'm use to add rows that I expect already exist into the new database, like lookup records and initialized data. The operation of checking the database must be made at the beginning of the life of the application just before it starts accessing rows. So the better place is the App class constructor where I add the following line:

FeedReaderRepository.Initialize();

When you deploy an application it is expected you make some changes to the database during the application lifecycle. It is almost impossible that the first database you create on the phone is able to support all the future scenarios. For this purpose Windows Phone 7.1 provides a DatabaseSchemaUpdater class. It lets you access a version number for the deployed schema and a bunch of methods that supports the creation of new tables, columns, indexes and relations. As far I can see there is nothing that let you delete something in existing databases.

Querying the data

Now that your mapping is created and the database has been deployed to the device it is time to start querying the data. The pattern is you create the DataContext instance, pointing to the right database, then use the sets to make queries and the Insert and Delete methods to make changes. Using linq is really simple and well documented so in this paragraph I will only show the basic concepts. To deal with joins, grouping and other advanced operations please refer to the LinqToSQL documentation:

public IEnumerable<Feed> GetRecentFeeds()
{
    using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
        return dc.Feeds
            .OrderByDescending(f => f.DateCreated)
            .Take(2)
            .ToArray();
}

public bool FeedExists(string address)
{
    using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
    {
        return (from fd in dc.Feeds
                where fd.Address == address
                select fd).Count() > 0;
    }
}

In the above sample I've put togheter two queries. The first select the most recent feeds taking the first two from a list orderer descending by the column DateCreated. The important thing to remember is to always call ToArray() or ToList() before returning the entities because it performs the query. Returning the reqult of the Take(2) does not returns the entities but a query that cannot be executed outside of the scope of the DataContext. So enumerating the query will raise an exception if you do not call the ToArray method here. The other query search for a field by the address string and verify it exists. In this case the Count() method perform the query and the return value will be a scalar that is not connected with the Datacontext.

To update entities you have to make changed to objects you have extracted from the DataContext. When you make the changes they are tracked and calling the SubmitChanges() method will persist all the changes in a unique batch. This also applies to Insert and Delete that map to the InsertOnSubmit and DeleteOnSubmit methods. This way you can watch at the DataContext as an implementation of the Unit-Of-Work pattern. You make changed then, at the end, you call SubmitChanges to persist to the database. Here is some examples:

public void DeleteFeed(Feed feed)
{
    using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
    {
        feed.DateCreated = DateTime.Now;
        dc.Feeds.DeleteOnSubmit(feed);
        dc.SubmitChanges();
    }
}

public void SaveFeed(Feed feed)
{
    using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
    {
        feed.DateCreated = DateTime.Now;
        dc.Feeds.InsertOnSubmit(feed);
        dc.SubmitChanges();
    }
}

public void UpdateFeedDate(int id, DateTime date)
{
    using (FeedReaderDataContext dc = new FeedReaderDataContext(FeedReaderDataContext.ConnectionString))
    {
        var feed = (from f in dc.Feeds
                    where f.ID == id
                    select f).FirstOrDefault();

        if (feed != null)
        {
            feed.DateLastUpdate = date;
            dc.SubmitChanges();
        }
    }
}

This is the beautiful part of the game. Linq is the most flexible tool for querying the data and it is plenty of examples on the Internet if you need help to extract the data in the way you prefer. Refer to this page for a number of samples.

Apply indexes

Everyone have used a database knows the importance of indexes. A local database on SQL CE probably will never contain million of records, nevetheless sometimes an index can make the difference when you need to search with a where  clause or when you need to sort a set using order by. In Windows Phone 7.1 you can easily apply indexes to tables and the underlying SQL CE engine will manage them transparently, without the need of wiring any code to handle them.

To apply an index you have simply to use the IndexAttribute, at the entity level, specifying the columns to which it applies, its name and the uniqueness constraint.

[Table(Name = "Feeds")]
[Index(Columns = "DateCreated", IsUnique = false, Name = "Feed_DateCreated")]
public abstract class Feed : ObservableObject
{
    /// ...
}

Index are created on the database when you call the CreateDatabase() method then are completely managed by SQL CE. If you need an additional index you can call the AddIndex method on the DatabaseSchemaUpdater class.

When to use SQL CE

SQL CE is a simple database and for sure it will power up applications developed for Windows Phone 7.1. You may consider using it both when you need to store something a bit complex than a configuration, but also in scenarios where you need to cache data for a smart application to continue working while it is offline. In both the cases it is a good solution able to speed up you development and thanks to the updating features you are able to maintain your application, adding features during the application lifecyle, in a reliable way.

Download the source code