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.
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).