Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

Change Tracking with SQL Server Compact (LINQ to SQL) on Windows Phone

As you are probably aware the Mango update for Windows Phone (ie Windows Phone OS 7.1) will include support for LINQ to SQL. According to Microsoft you shouldn’t need to know or care that this is SQL Server Compact (SQL CE) since they’ve limited access to the database to a set of managed wrappers, namely LINQ to SQL. Unfortunately this only represents a subset of the true capabilities of SQL CE. One of the sorely missing features is of course synchronization, as there is no support for Merge Replication, RDA or Sync Framework. In fact, even doing your own synchronization logic is crippled by the fact that you can’t tap into the native change tracking that SQL CE offers….. well, not using a supported set of APIs.

If we work on the assumption that what ships with Windows Phone OS 7.1 is actually SQL CE v3.5+ (to indicate that there have been some changes to the database engine to support Windows Phone but that the majority of core SQL CE functionality is there), then we can assume that the change tracking functionality, which is part of the SQL CE db engine, is available in the underlying database engine. It’s just not accessible via the managed interface (ie LINQ to SQL). However, if you’ve ever taken a look at change tracking in SQL CE you’d have noticed that all the information relating to  change tacking is maintained in private tables (__sysOCSTrackedObjects, __sysOCSDeletedRows and __sysTxCommitSequence) and private columns in the tracked tables (__sysChangeTxBsn. __sysInsertTxBsn, __sysTrackingContext). So in theory if change tracking was enabled (we’ll cover that in a minute) all we need to do in order to retrieve this information is to map these tables and columns. For the purpose of this post I’m just going to map the __sysChangeTxBsn column in the Person table. The bulk of this code was generated using the SqlMetal tool, although you might also want to try out Erik’s SQL Server Compact Toolbox . The bolded code was manually added to map the __sysChangeTxBsn column (the property ChangeTx is read only as you don’t want to modify this directly).

[global::System.Data.Linq.Mapping.TableAttribute()]
public partial class Person : INotifyPropertyChanging, INotifyPropertyChanged
{    
    private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
   
    private System.Guid _Id;
   private string _Name;

    private int? _ChangeTx;

    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnIdChanging(System.Guid value);
    partial void OnIdChanged();
    partial void OnNameChanging(string value);
    partial void OnNameChanged();
    #endregion
   
    public Person() {
        OnCreated();
    }
   
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Id", DbType="UniqueIdentifier NOT NULL", IsPrimaryKey=true)]
    public System.Guid Id {
        get {
            return this._Id;
        }
        set {
            if ((this._Id != value)) {
                this.OnIdChanging(value);
                this.SendPropertyChanging();
                this._Id = value;
                this.SendPropertyChanged("Id");
                this.OnIdChanged();
            }
        }
    }
   
    [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Name", DbType="NVarChar(100)")]
    public string Name {
        get {
            return this._Name;
        }
        set {
            if ((this._Name != value))
            {
                this.OnNameChanging(value);
                this.SendPropertyChanging();
                this._Name = value;
                this.SendPropertyChanged("Name");
                this.OnNameChanged();
            }
        }
    }

   [global::System.Data.Linq.Mapping.ColumnAttribute(
                 
Storage = "_ChangeTx",
                  Name="__sysChangeTxBsn", 
                  DbType = "bigint")]
    public int? ChangeTx {
        get {
            return this._ChangeTx;
        }
    }

    public event PropertyChangingEventHandler PropertyChanging; 
    public event PropertyChangedEventHandler PropertyChanged;
   
    protected virtual void SendPropertyChanging() {
        if ((this.PropertyChanging != null)) {
            this.PropertyChanging(this, emptyChangingEventArgs);
        }
    }
   
    protected virtual void SendPropertyChanged(String propertyName) {
        if ((this.PropertyChanged != null)) {
            this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

Now that you’ve mapped this column you actually need to enable change tracking. As I’ve indicated previously there is no way to do this via LINQ to SQL, which means that if you want to enable change tracking you’ll need to create your database on a platform that supports ADO.NET which includes the API to enable change tracking. For me this was a simple Windows Forms application.

Create your SQL Server Compact database using the designer tools in either SQL Management Studio or Visual Studio (my database is called ChangeData.sdf). Next, add this database file to a newly created Windows Forms application and set the Build Action to Content, and make sure that it is copied to the output directory. Add a button to the form and then in the Click event handler add the following code – this opens a connection to the database file in the executing directory and enables change tracking on the Person table.

private void button1_Click(object sender, EventArgs e) {
    var cnstr = "Data Source=" +
                Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase).Replace("file:\","") +
               
\ChangeData.sdf;Persist Security Info=False;;

   var connection = new SqlCeConnection(cnstr);
    connection.Open();

    var tracker = new SqlCeChangeTracking(connection);
    tracker.EnableTracking("Person", TrackingKeyType.PrimaryKey, TrackingOptions.All);
}

Run the Windows Forms application and click the button. You’re done, change tracking is enabled on your database file. Copy this database file (make sure you get the one that is in the executing folder for the Windows Forms application) into your Windows Phone application. Again make sure the Build property is set to Content.

Ok, now to write some code against your change tracking database. The first thing we’re going to do is to copy the database that is packaged with the application from the installation folder (appdata, where it’s read only) into isolated storage (isostore, where it’s read-write). The following code then iterates through all the Person entities (I created a few sample records in the database via Visual Studio) and modifies the Name property.

void MainPage_Loaded(object sender, RoutedEventArgs e) {
    CopyReferenceDatabase("ChangeData.sdf", "ChangeData.sdf");
 
   string ReferenceDBConnectionString = "Data Source=isostore:/ChangeData.sdf";
   using (var db = new ChangeData(ReferenceDBConnectionString)) {
        var people = from person in db.Persons
                               select person;
        foreach (var p in people) {
           p.Name = p.Name + " updated";
        }
        db.SubmitChanges();
    }
}

public static void CopyReferenceDatabase(string referenceDatabaseName,
                                                                               string isolatedStorageDatabaseName) {
    var iso = IsolatedStorageFile.GetUserStoreForApplication();
    if (iso.FileExists(isolatedStorageDatabaseName)) return;

    using (var input = Application.GetResourceStream(
            new Uri(referenceDatabaseName, UriKind.Relative)).Stream) {
        using (var output = iso.CreateFile(isolatedStorageDatabaseName)) {
            var readBuffer = new byte[4096];
            int bytesRead;

            while ((bytesRead = input.Read(readBuffer, 0, readBuffer.Length)) > 0) {
                output.Write(readBuffer, 0, bytesRead);
            }
        }
    }
}

Now to see the effects of change tracking. If you set a break point in the for loop you can take a look at the ChangeTx property on the Person entities. If you run the application a few times you’ll see that this value changes each time you update the Person entities.

image

If you want to interrogate the database file further you can export the file from either the emulator or a real device using the Isolated Storage Explorer Tool:

c:Program Files (x86)Microsoft SDKsWindows Phonev7.1ToolsIsolatedStorageExplorerTool>ISETool.exe ts xd 3abccaef-453b-4345-8f0e-7861873ddbf9 c:tempemu_export

The parameters for ISETool.exe are:
ts – Take snapshot (copy entire isolated storage files/folders for the specified application)
xd – Emulator (use de for actual, unlocked, device)
3abccaef-453b-4345-8f0e-7861873ddbf9 – The guid of the specified application. This is the ProductID attribute from the WMAppManifest.xml file for your application.
c:tempemu_export – The folder on the host computer that you want to push the snapshot too. I recommend specifying an empty folder.

Once you’ve exported the database file you can then connect to it via Visual Studio or SQL Management Studio. Note that in the designer both the private tables and private columns are hidden. However, if you run a SQL statement (eg select * from Person) you’ll see the private columns.

And that’s essentially it – you’ve got a change tracking enabled database which you can use to synchronize data back to a server. You might like to combine this with OData, in which case you might want to take a look at how I implement change tracking on the server side using WCF Data Services and Entity Framework (http://nicksnettravels.builttoroam.com/post/2010/08/03/OData-Synchronization-with-WCF-Data-Services.aspx).

Removing Strings in INotifyPropertyChanged and OData Expands

Removing Strings in INotifyPropertyChanged and OData Expands

Ok, so this is a rather random post but I wanted to jot down a couple of scenarios where I often see string literals in code.

Scenario 1: String Literals in INotifyPropertyChanged Implementation

The first is the default implementation of INotifyPropertyChanged. If you’ve done data binding (for example with WPF or Silverlight) you’ll be familiar with this interface – when a source property value changes if you raise the PropertyChanged event the UI gets an opportunity to refresh. The standard implementation looks a bit like this:

public class MainPageViewModel: INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
    private string _Title;
    public string Title
    {
        get { return _Title; }
        set
        {
            if (Title == value) return;
            _Title = value;
            RaisePropertyChanged("Title");
        }
    }
    private void RaisePropertyChanged(string propertyName)
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
        }
    }
}

I always cringe when I see this because the name of the property, ie “Title” is passed into the RaisePropertyChanged method. This wouldn’t be so bad except this code block gets repeated over and over and over again – for pretty much any property you end up data binding to. Having string literals littered through your code is BAD as it makes your code incredibly brittle and susceptible to errors (for example you change the property name, without changing the string literal). A while ago I adopted the following version of the RaisePropertyChanged method which accepts an Expression and extracts the property name:

public void RaisePropertyChanged<TValue>(Expression<Func<TValue>> propertySelector)
{
    var memberExpression = propertySelector.Body as MemberExpression;
    if (memberExpression != null)
    {
        RaisePropertyChanged(memberExpression.Member.Name);
    }
}

The only change you need to make in your properties is to use a lambda expression instead of the string literal, for example:

private string _Title;
public string Title
{
    get { return _Title; }
    set
    {
        if (Title == value) return;
        _Title = value;
        RaisePropertyChanged(()=>Title);
    }
}

Scenario 2: String Literals in the Expands method for OData

Let’s set the scene – you’re connecting to an OData source using either the desktop or the new WP7 (Mango) OData client library. Your code might look something similar to the following – the important thing to note is that we’re writing a strongly typed LINQ statement to retrieve the list of customers.

NorthwindEntities entities = new NorthwindEntities(new Uri("http://services.odata.org/Northwind/Northwind.svc"));
private void LoadCustomers()
{
    var customerQuery = from c in entities.Customers
                                           select c;

    var customers = new DataServiceCollection<Customer>();
    customers.LoadCompleted += LoadCustomersCompleted;
    customers.LoadAsync(customerQuery);
}

private void LoadCustomersCompleted(object sender, LoadCompletedEventArgs e)
{
    var customers = sender as DataServiceCollection<Customer>;
    foreach (var customer in customers)
    {
        var name = customer.CompanyName;
    }
}

By default the LINQ expression will only retrieve the Customer objects themselves. If you wanted to not only retrieve the Customer but also their corresponding Orders then you’d have to change the LINQ to use the Expand method:

var customerQuery = from c in entities.Customers.Expand("Orders")
                                       select c;

Now, if you wanted to be more adventurous you could extend this to include the OrderDetails (for each Order) and subsequent Product (1 for each OrderDetails record) and Category (each Product belongs to a category).

var customerQuery = from c in entities.Customers.Expand("Orders/OrderDetails/Product/Category")
                                       select c;

The Order is also connected to the Shipper and Employee tables, so you might want to also bring back the relevant data from those tables too:

var customerQuery = from c in entities.Customers
                                                                     .Expand("Orders/OrderDetails/Product/Category")
                                                                     .Expand("Orders/Employee")
                                                                     .Expand("Orders/Shipper")
                                       select c;

The result is that you have a number of string literals defining which relationships you want to traverse and bring back. Note that you only need to do this if you want to eager load this information. If you want your application to lazy load the related data you don’t require the Expand method.

The work around for this isn’t as easy as the RaisePropertyChanged method used to eliminate string literals for the INotifyPropertyChanged scenario. However, we essentially use the same technique – we replace the string literal with an expression that makes the necessary traverses. For example:

var customerQuery = from c in entities.Customers
                                    .Expand(c=>c.Orders[0].Order_Details[0].Product.Category)
                                    .Expand(c => c.Orders[0].Employee)
                                    .Expand(c => c.Orders[0].Shipper)
                    select c;

You’ll notice that in this case where we traverse from Orders to Order_Details we need to specify an array index. This can actually be any number as it is completely ignored – it’s just required so that we can reference the Order_Details property which exists on an individual Order object.

Ok, but how is this going to work? Well we’ve simply created another extension method for the DataServiceQuery class, also called Expand but accepts an Expression instead of a string literal. This method expands out the Expression and converts it to a string, which is passed into the original Expand method. I’m not going to step through the following code – it essentially traverses the Expression tree looking for MemberAccess nodes (ie property accessors) which it adds to the expand string. It also detect Call nodes (which typically corresponds to the array index accessor eg get_item( 0 )) which is skipped to move on to the next node in the tree via the Object property.

public static class ODataExtensions
{
    public static DataServiceQuery<TElement> Expand<TElement, TValue>(this DataServiceQuery<TElement> query, Expression<Func<TElement, TValue>> expansion)
    {
        var expand = new StringBuilder();
        var expression = expansion.Body as Expression;
        while (expression.NodeType != ExpressionType.Parameter)
        {
            if (expression.NodeType == ExpressionType.MemberAccess)
            {
                if (expand.Length > 0)
                {
                    expand.Insert(0, "/");
                }
                var mex = (expression as MemberExpression);
                expand.Insert(0, mex.Member.Name);
                expression = mex.Expression;
            }
            else if (expression.NodeType == ExpressionType.Call)
            {
                var method = (expression as System.Linq.Expressions.MethodCallExpression);
                if (method != null)
                {
                    expression = method.Object as MemberExpression;
                }
            }
        }
        return query.Expand(expand.ToString());
    }
}

And there you have it – you can now effectively remove string literals from the Expand method. Be warned though: using the Expand method can result in a large quantity of data being retrieved from the server in one hit. Alternatively if the server has paging enabled you will need to ensure that you traverse any Continuations throughout the object graph (more on that in a subsequent post).