One of the top wish-list items for Windows Phone 7 developers was the ability to store data locally on the phone in a relational format. That was one of the top questions I always got asked when talking about Windows Phone – “Where is my local database?!?!”  Thanks to a LOT of feedback from all of you, and some clever engineering on the part of the Windows Phone team, we now have support for SQL Server Compact Edition with the new Windows Phone 7.1 SDK. This post will show some basic information on how to use the database platform on Windows Phone, as well as give some tips on how to update your database once it’s deployed, and how to deploy a database with your application.

Enter the Database

Using the SQL CE platform on Windows Phone is really straightforward – everything you need to do is done in code using the Linq to SQL framework. In a nutshell, creating a data model is as simple as creating classes for each of your model items, adding Linq to SQL attributes, and creating a subclass of DataContext. Then, with only a couple lines of code, you have a working, updatable SQL database.

Creating the Data Model

This is simply be done by creating a bunch of classes in your application and applying the appropriate attributes. For our sample application, we’re going to create a database that tracks widgets. We start off by creating the Widget class. I like to put all my Model classes in a Models folder, with associated Models namespace, but you can do whatever makes sense to you.

namespace DatabaseForMango.Models
{
    public class Widget : TableObject
    {
        // WidgetId
        private int _WidgetId;

        public int WidgetId
        {
            get { return _WidgetId; }
            set
            {
                if (_WidgetId == value)
                    return;
                NotifyPropertyChanging("WidgetId");
                _WidgetId = value;
                NotifyPropertyChanged("WidgetId");
            }
        }

        // WidgetName
        private string _WidgetName;

        public string WidgetName
        {
            get { return _WidgetName; }
            set
            {
                if (_WidgetName == value)
                    return;
                NotifyPropertyChanging("WidgetName");
                _WidgetName = value;
                NotifyPropertyChanged("WidgetName");
            }
        }
    }
}

Note that we’re using a base class here called TableObject, which only serves to implement INotifyPropertyChanged and INotifyPropertyChanging.  The former is used by the XAML DataBinding infrastructure, and the latter is used by Linq to SQL to detemine updates made to the data.

With the class defined, we have to tell Linq to SQL about it through the use of Attributes. To get access to these attributes, you need to add a reference to the System.Data.Linq.dll assembly – it should be in the list of default framework references for your Windows Phone 7.1 application

If you don’t see that dll in your references list, you are probably working on a Windows Phone 7.0 project that will have to be upgraded in order to take advantage of the database support, or you haven’t yet installed the Windows Phone 7.1 SDK tools from http://aka.ms/PhoneCK – so go do that now if you need to…

With the reference in place, we need to add some using statements to our Model class.

using System.Data.Linq;
using System.Data.Linq.Mapping;

Each class, in the Linq to SQL world, will map to a Table in the database. Each property in the Model class, can map to a Column (not all have to – just those that you want persisted in the database!) We can start off simple by adding the Table attribute to the Widget class, supplying the name of the database table we want created (this allows us to have a different table name than we do class name, if we so choose). Then for each of the properties, except the Id field, we add a plan vanilla Column attribute. For the Id field, we need to do something special because this field is going to be our Primary Key for the table, and is going to be generated by the system – like an Identity column in SQL Server. There are a lot of properties that you can set, but for our purposes, we will add only those that will help us get exactly the kind of column we want in the database:

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace DatabaseForMango.Models
{
    [Table(Name = "Widgets")]
    public class Widget : TableObject
    {
        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        // WidgetId
        private int _WidgetId;

        [Column(
            IsPrimaryKey = true,
            IsDbGenerated = true,
            DbType = "INT NOT NULL Identity",
            CanBeNull = false,
            AutoSync = AutoSync.OnInsert)]
        public int WidgetId
        {
            get { return _WidgetId; }
            set
            {
                if (_WidgetId == value)
                    return;
                NotifyPropertyChanging("WidgetId");
                _WidgetId = value;
                NotifyPropertyChanged("WidgetId");
            }
        }

        // WidgetName
        private string _WidgetName;

        [Column]
        public string WidgetName
        {
            get { return _WidgetName; }
            set
            {
                if (_WidgetName == value)
                    return;
                NotifyPropertyChanging("WidgetName");
                _WidgetName = value;
                NotifyPropertyChanged("WidgetName");
            }
        }
    }
}

One other note – you’ll see that I added a Version column to the database. This isn’t something that the developer will update, or even expose to the end user, but it’s something that makes Linq to SQL very happy and can help improve update performance on our table. Update performance improvments this simple should be part of every project!

Now that we have the basic Widget class and attributes defined, let’s define another entity that we want to track, which we’ll call Category. The Category entity will be defined in a similar way as our Widget class:

using System;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace DatabaseForMango.Models
{
    [Table(Name = "Categories")]
    public class Category : TableObject
    {
        // Version column aids update performance.
        [Column(IsVersion = true)]
        private Binary _version;

        // CategoryId
        private int _CategoryId;

        [Column(
            IsPrimaryKey = true,
            IsDbGenerated = true,
            DbType = "INT NOT NULL Identity",
            CanBeNull = false,
            AutoSync = AutoSync.OnInsert)]
        public int CategoryId
        {
            get { return _CategoryId; }
            set
            {
                if (_CategoryId == value)
                    return;
                NotifyPropertyChanging("CategoryId");
                _CategoryId = value;
                NotifyPropertyChanging("CategoryId");
            }
        }

        // CategoryName

        private string _CategoryName;

        [Column]
        public string CategoryName
        {
            get { return _CategoryName; }
            set
            {
                if (_CategoryName == value)
                    return;
                NotifyPropertyChanging("CategoryName");
                _CategoryName = value;
                NotifyPropertyChanging("CategoryName");
            }
        }
   }
}

With both tables in place, we now can configure the relationship between them. This is not as straightforward as defining the Table attributes – for this we need to define a set of Associations and use a couple of new classes instead of data types.

Creating Associations

An association represents a one-to-many relationship in a database, and is represented in Linq to SQL by two special classes – the EntityRef, which defines the “1” side of the equation and the EntitySet which defines the “many” side. In our example, a Widget can be assigned one category, but each Category can know multiple Widgets. The way we represent this in our data model is to define an EntityRef property on the Widget class, and an EntitySet property on the Category class. First, the EntityRef.

// Category
[Column]
internal int _categoryId;

private EntityRef<Category> _category;

[Association(
    Storage = "_category",
    ThisKey = "_categoryId",
    OtherKey = "CategoryId",
    IsForeignKey = true)]
public Category WidgetCategory
{
    get { return _category.Entity; }
    set
    {
        NotifyPropertyChanging("WidgetCategory");
        _category.Entity = value;
        if (value != null)
        {
            _categoryId = value.CategoryId;
        }
        NotifyPropertyChanged("WidgetCategory");
    }
}

As you can see, defining the EntityRef requires 3 parts:

  • The EntityRef<Category> that holds the reference to the Category object,
  • The private int field that  holds a reference to the primary key of the associated Category object
  • The Category property that returns the EntityRef<Category> and also serves to define the Association attribute

We also account for that specialty EnityRef<T> property by assigning it’s Entity property to the associated Category instance, instead of assigning it’s value directly.

The properties of the Association attribute identify this relationship as a Foreign Key, and also define which fields relate the two objects together:

  • Storage = the name of the local EntityRef field (in this case, we’ve stored that above in the _category field)
  • ThisKey = the name of the local property that defines where we are storing the foreign “key” itself (in this case, we’ve stored that in the int field _categoryId)
  • ThatKey = the name of the Primary Key property on the Category object we’re referencing (in this case, that’s been defined as the Category property).

Note that the value for the ThatKey property in the Association has to match up with the property on the Category class that has been attributed as a column and has been indicated as the primary key.

With this in place, we can move on to the Category class, and defining the EntitySet for its associated Widget classes

// Widgets
private EntitySet<Widget> _Widgets;

[Association(
    Storage = "_Widgets",
    ThisKey = "CategoryId",
    OtherKey = "_categoryId")]
public EntitySet<Widget> Widgets
{
    get { return _Widgets; }
    set { _Widgets.Assign(value); }
}

// Assign handlers for the add and remove operations, respectively.
public Category()
{
    _Widgets = new EntitySet<Widget>(
        new Action<Widget>(this.attach_Widget),
        new Action<Widget>(this.detach_Widget)
        );
}

// Called during an add operation
private void attach_Widget(Widget w)
{
    NotifyPropertyChanging("WidgetCategory");
    w.WidgetCategory = this;
}

// Called during a remove operation
private void detach_Widget(Widget w)
{
    NotifyPropertyChanging("WidgetCategory");
    w.WidgetCategory = null;
}

Although this initially appears to be the same thing as we saw on the Widget class, we’re dealing some some different elements on the “many” side of the relationship:

  • A private storage field of type  EntitySet<Widget>  that will reference the associated set of Widget objects for this category.
  • A public property of type EntitySet<Widget> that defines the association between the two tables
  • Some delegate functions for handling the cases where a Category is attached or Detatched from a given Widget. These delegate functiions provide the simple “glue” for connecting the Category to the passed in Widget, thus ensuring the referential integrity of the foreign key relationship.

Like the Association attribute on the Widget class, this also defines a couple of key elements – although IsForeignKey is missing (we don’t put that on the “many” side of the relationship)

  • Storage = the name of the local EntitySet field (in this case, the _widgets field)
  • ThisKey = the name of the local property that identifies the Primary Key for this entity (in this case, the CategoryId property)
  • ThatKey = the name of the property (or field) on the Widget object that holds the Primary Key for this Category object (in this case, the _categoryId field).

Interestingly, the _categoryId field is private on our Widget class, but it still seems to work out! The same rule about the key properties still applies here – these fields have to be the ones attributed with the [Column] attributes, which applies both to _categoryId on Widget and CategoryId on Category.

Now with the Tables all defined and attributed, we are ready to move on to the DataContext

Creating the DataContext

The DataContext object is the portal by which your application interacts with the database. Linq to SQL defines a DataContext base class, loaded with behavior, for you to use in your own application.  The DataContext class we create will provide the endpoints – properties and methods – that our application will use to interact with our database. It is up to you how you choose to expose information here – you can provide methods, similar to Stored Procedures in SQL Server or you can provide a more OData-like interface where you just expose the IQueryable Table endpoints directly.  For our purposes, we’re going to forego the StoredProc-like route and just expose each table as an endpoint property on our custom DataContext:

using System;
using System.Data.Linq;

namespace DatabaseForMango.Models
{
    public class WidgetDataContext : DataContext
    {
        public WidgetDataContext()
            : base("Data Source=isostore:/WidgetDb.sdf")
        {
        }

        public Table<Widget> Widgets;
        public Table<Category> Categories;
    }
}

Not much to this one. We’ve provided a default connection string to the single constructor, and exposed each table as a public property of type Table<T> – one for Widget and one for Category. You could name these whatever you like, but it makes sense to use names that will make sense to the developer…

Now that the DataContext is in place, we can start to make our database a reality by actually creating it on the Phone platform.

Make the Database a Reality

With the DataContext, and all the classes in place, we can now ensure that our database is created and ready for action. Determining this information is really simple:

private static void SetupDb()
{
    WidgetDataContext db = new WidgetDataContext();
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
    }
}

Notice how simple it is to get the database created. There is a simple check to see if the database exists already, and if not it creates a new one.  The trick of course, is where to put this code, and what to do if the database in your application is newer than what has been deployed.  For the first part, I tend to put this information in the constructor for App.Xaml or in the Loading event. I know that adds time to the application’s load experience, but it’s probably pretty important that the database be up-to-date before doing any work. If you run into performance problems, you can always add some code around the loading of your Main Page and make some updates there, but don’t blame me if things go wonky Smile

Updating the Database

The other half of my question was determining how to make updates to your database after it’s been deployed. This is also pretty straight-forward, although not necessarily simple. Step 1 is determining if we need an update or not. For that, we use the DatabaseSchemaUpdater class from Linq to SQL. Here’s an expanded version of our SetupDB() method that includes this code:

private static void SetupDb()
{
    WidgetDataContext db = new WidgetDataContext();
    if (!db.DatabaseExists())
    {
        db.CreateDatabase();
        var updater = db.CreateDatabaseSchemaUpdater();
        updater.DatabaseSchemaVersion = 1;
        updater.Execute();
    }
    else
    {
        var updater = db.CreateDatabaseSchemaUpdater();
        if (updater.DatabaseSchemaVersion < 2)
        {
            // make some updates

            updater.AddTable<Log>();
            updater.AddColumn<Widget>("CreatedDate");
            updater.DatabaseSchemaVersion = 2;
            updater.Execute();
        }
    }
}

Using the the DatabaseSchemaUpdater class and it’s DatabaseSchemaVersion property, it’s fairly easy to figure out what updates need to be done. In this example we’re adding a Log table to the database, and adding a CreatedDate column to the Widget table. Of course, that Widget object needs to actually have a CreatedDate property exposed and properly attributed as a column, but this code here is all you need to do to get Linq to SQL to make the schema update for you.

For new tables and columns, the process is really pretty easy. What’s hard is dealing with changes to existing columns. Currently, Linq to SQL doesn’t provide a good way to do this, so we have to get creative. One approach might be to create a new column in your table just like another column, and copy all the data over. Then, delete the old column, recreating it with new properties. Lastly, move the data back over from the temporary column to the new column and delete the old one. Ugly, I know, but that’s where we are right now. If you have suggestions or ideas on better ways to manage this, I’m all ears.

Deploying a Database with your Application

Another situation can arise when you have a lot of data to deploy with your application, or you want to improve the initial startup of your application by pre-loading a database and deploying it with your application. This too is really simple to do, and is made even easier using a “buddy project” – just create a console application in your solution and add a SQL Database object to it. Console applications have designer support for databases, so you can create your schema and pre-load it however you like, and just copy it into your phone project when you’re ready to deploy. Don’t forget to set the database properties to “Content” and “Copy Always” or you might get unexpected results when running your application.

Once you have the database deployed, you can either use it as a reference database to read data from, or copy it over to Isolated Storage and use it as an application database. Here’s a code snippet showing how you can do the latter in only a few simple lines of code:

// Obtain the virtual store for the application.
IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();

// Create a stream for the file in the installation folder.
using (Stream input = Application.GetResourceStream(new Uri("ReferenceDB.sdf", UriKind.Relative)).Stream)
{
    // Create a stream for the new file in isolated storage.
    using (IsolatedStorageFileStream output = iso.CreateFile("ApplicationDB.sdf"))
    {
        // Initialize the buffer.
        byte[] readBuffer = new byte[4096];
        int bytesRead = -1;

        // Copy the file from the installation folder to isolated storage.
        while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0)
        {
            output.Write(readBuffer, 0, bytesRead);
        }
    }
}

This code uses the stream infrastructure provided by Isolated Storage and Silverlight 4 to copy the ReferenceDB.sdf file from application storage into a file called ApplicationDB.sdf in IsolatedStorage. If you’d rather leave it as a read-only database in the application folder, just change the connection string to appstore:/ instead of isostore:/ and you’re good to go!

Parting Thoughts

Working with the SQL CE database on Windows Phone is pretty easy, thanks to Linq to SQL, but there is still room for improvement. The Visual Studio tooling for Windows Phone projects don’t yet support the SQL CE database designer, so you’ll either have to do all your coding by hand, or use a “buddy project” to create your data model. Once the model is created, you can use the techniques described here to deploy the database with your application, and provide updates to it as necessary. Keep in mind that the database you’re creating is running on a limited-capacity mobile device that is usually connected over an often slow network connection. For reliability and scalability, your application might still require the use of a server-side database to serve as a more permanent store for data as well as data archival. Think about the end users of your application when determining how much data to put in the database running on the phone, and how much to move over to a server. With the always available Windows Azure and SQL Azure platforms, data integrity is only a cloud-hop away…

Get the source code examples that accompany this post from my GitHub repository at the following URLs:

https://github.com/ChrisKoenig/DatabaseForMango – This is the Widget/Category code

https://github.com/ChrisKoenig/ReferenceDatabase – This is the Reference Database code

 

6 thoughts on “Using Databases with Mango

  1. Chris, excellent article!! 🙂

    One thought: I think the “buddy project” is not needed, you just have to open the Server Explorer window in Visual Studio, click on the “Connect to Database” toolbar button, click on the “Change” data source button, select “Microsoft SQL Server Compact 3.5”, click OK, and the use the “Create” button to get a brand new database file in the Windows Phone project folder (we can later add it to the project)

    After that, we can use the Server Explorer options to create and manage the database!

  2. Chris, am I mistaken or do you have the EntityRef and EntitySet allocations in the first paragraph about creating associations the wrong way around?

    “The way we represent this in our data model is to define an EntityRef property on the Widget class, and an EntitySet property on the Category class.”

    Isn’t it is Category class that is the EntitySet and the Widget which is the EntityRef? The code supports it way around, just on the description.

  3. first of thanks for our post , i am an exsisting database

    (database file ex:- say x.sdf) into my application but i am getting this error
    {System.Data.SqlServerCe.SqlCeException: The database file may be corrupted. Run the repair utility to check the database file.

Comments are closed.