Time Tracker - a demo Sync application

I've created a little demo application now that I've had a chance to get some better real-world experience with creating a Microsoft Sync Framework for ADO.NET application.

I feel that the first rule of creating an application that will work in offline mode using Sync is to get your basic application working without sync, talking to a local CE database. It's important to keep in mind all the usual concurrency issues involved with creating a database application such as:

• multiple users updating the same row
• updating something that someone else deleted
• deleting something that someone else updated
• etc...

These issues become even more of a problem when using Sync since the time between the user pressing "Save" and when it actually gets written to the central database can be a long time if they're offline. Also due to this disconnected nature you'll need to handle it at the server, which means you might want to create a "conflict" table to store any client-side changes that are going to be rejected so that user can be notified that their change failed for some reason and given the chance to merge or retry their update.

In this demo app I'll keep it simple, we'll assume that whoever makes the last change to a row wins, since each person should only be updating their own time-sheet.

Our app flow is as follows:

1. Pick an employee from a list
2. Select a day (date) to add/edit/delete entries for
3. Add/edit/delete entries
4. Save changes to this day's entries
5. Go back to (2) and pick another day or exit (you could also select another employee if you want and start again)

You'll notice that there's no ability to do any updates to the list of Employees, Projects or BillingInfos (list of billing codes), these are considered management tasks and should be done by a separate application (which I might build later if people are interested).

I like to have a "Splash Window", especially since .NET (and WPF even more so) applications can take a while to start, and it'll come in handy later when we'll want to deal with checking the local DB, possibly showing a config window, or even asking for a login.

So, our app is very simple:

• SplashWindow - simple window to let people know the app is starting, will be handy later for other things
• TimeSheetWindow - where everything happens
• LocalDatabase.sdf - SQL CE database with our 4 tables
• CreateLinqToSql.cmd – a batch file to generate the LINQ to SQL classes
• Data\LocalDatabase.cs – generated code for LINQ to SQL
• Data\LocalDataContext.cs – partial class, uses connection string from config
• Data\Employee.cs – partial class, added a FullName property
• Data\TimeEntry.cs – partial class, sets ID to a new GUID when created

You’ll notice the user experience isn’t very satisfying and data validation is quite minimal, but it’s enough for this sample. One possible way to improve it is to use the Xceed DataGrid for WPF (free edition), which also includes a nice DatePicker control.

You can download the application from Time Tracker Demo on Google Code, if you have TortoiseSVN or similar you'll be able to easily download the code and try it out, this version is branch “Stage1”.

Using SQL Express (or Standard/Enterprise) at the Client

First off, why would anyone want to use SQL Express (or one of it's bigger brothers) at the client? For us there's the case of a Site (grain silo) that wants offline functionality (if the link to head-office goes down), but still have changes on one PC be visible on all others. This is a perfect case for all the apps at that site to talk to a local SQL Server, and have a service running on the server that will sync with head-office when the connection is up.

We took a look at the SQL Express Client Synchronization using Sync Services for ADO.NET sample provided, which was very helpful. Some of the coding standards were not quite how I would have liked, but as a proof-of-concept it did the job.

After a few false starts we finally got it working in our trial application. There's one very interesting issue that we found: all your tables in your SyncAgent must be defined as BiDirectional, otherwise the SqlExpressClientSyncProvider sample class ignores them!

I was concerned that this might allow changes to download only tables, but if your server provider (which in our trial app is inside a WCF service) doesn't set the UpdateCommand or DeleteCommend then these changes are silently swallowed by the server. The client thinks the changes have been made server-side, but the server doesn't actually do anything.

This represents the final stage in proving our architecture to management, so now we'll be going full-steam ahead next week, with two new staff members starting on Monday: Nigel Spencer and Richard Hollon. That leaves only one person left for our "dream team" who will be starting in a few weeks.

Sync for ADO.NET CTP2 - Batch Anchor proc is broken?

In the documentation for the CTP2 it gives a sample stored procedure called usp_GetNewBatchAnchor, but from my tests it doesn't seem to work.
I had to make a few changes in my version:

• parameters are defined as "timestamp" data type, changed them to "binary(8)"
• for consistency I changed the "out" suffix on 2 of the 3 output params to be "output" like the 3rd one
• multiple checks of "IF @sync_batch_count <= 0" need to be replaced with "IF @sync_batch_count IS NULL OR @sync_batch_count <= 0"

NOTE: you'll also need to change the tables it's using to check for the min timestamp value, in my case the following:

SELECT @sync_last_received_anchor = MIN(TimestampCol)
FROM
    (
        SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.BillingInfo
        UNION
        SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.BillingInfo
        UNION
        SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.Employees
        UNION
        SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.Employees
        UNION
        SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.Projects
        UNION
        SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.Projects
        UNION
        SELECT MIN(CreateVersion) AS TimestampCol FROM dbo.TimeEntries
        UNION
        SELECT MIN(UpdateVersion) AS TimestampCol FROM dbo.TimeEntries
        UNION
        SELECT MIN(DeleteVersion) AS TimestampCol FROM dbo.TimeEntries_Tombstone
    ) MinTimestamp

Before I made the above changes I was getting erratic behaviour depending on the batch size specified. Now everything seems to be working as expected.

Sync for ADO.NET - Resolving Conflicts

I've been spending my spare time for the last few days looking into how to resolve conflicts. The samples in the help files with CTP2 are (IMHO) missing a key bit of info: the ApplyChangeFailed event on the SqlCeClientSyncProvider may not be what some expect, it is only raised if a change is made to a record after the upload phase but before a server change is downloaded and applied.

On slower connections that aren't using batching this can happen more often since the time from upload to commit of the "apply server changes" stage is longer.

I admit that I was expecting the following:

1. A data row exists on the server (lets call it ID:1, Label:Original, Version:1000)
2. Client A downloads it and makes a change (ID:1, Label:ClientA)
3. Client B downloads it and makes a change (ID:1, Label:ClientB)
4. Client A syncs (last anchor 1000), server saves new version (ID:1, Label:ClientA, Version:1001)
5. Client B syncs (last anchor 1000), server provider gets an ApplyChangeFailed event, we choose to Continue (basically saying Server copy wins)
1. I was EXPECTING the client to then raise an ApplyChangeFailed event so I could tell the user that their change has a conflict with the server (record 1001), and allow them to pick one or merge
2. What actually happens is that version 1001 is downloaded and replaces our change, destroying it forever

So, what can we do here? What we need to do is in the Server Provider decide what to do with the conflicting rows. If we want to keep the first change (saved as version 1001) we need to write the change from Client B somewhere else, such as <TableName>_Conflicts, including a note of the ClientID that caused the conflict. If this table is made part of our sync then that client (or all clients, depending on your needs) will now have it's change in the <TableName>_Conflicts table and the version from Client A in the <TableName> table. The UI can respond to this and show a conflict resolution dialog, if a merge or replace is decided upon then the <TableName> row is updated (and will be part of the next sync) and the <TableName>_Conflicts row will be deleted (and will be removed from the server next sync).

A special thanks goes out to Paul Stovell for helping me discover where my expectation and the actual implementation differed.

Sync for ADO.NET - Client (Part 2) - DbServerSyncProvider

While preparing the code for this part I tried to use the SqlSyncAdapterBuilder, which works great if you want to do Download Only or Snapshot, but if you want to do Bidirectional and you don't have a separate tombstone table you're out of luck. At this stage I have a choice to make, I either change my database schema to use a tombstone table, write all the SQL myself, or tinker with the SqlSyncAdapterBuilder and see if I can at least get it to do the bulk of the work.

• Change database schema:
  • A tool or framework you use shouldn't force you to alter your design as a general rule, the framework is happy with my planned design but the helper class doesn't support it
  • At this stage it's not a big deal to change the schema, and it has the added benefit of moving inactive rows or virtual deletions out of the main table, making them easier to manage and find
  • If I need to show inactive rows on the client I'll need to get creative (like sync the tombstone table as a download-only table without the extra columns)
• Write all the SQL myself
  • I'm planning to do this eventually, wrapping it all up in stored procedures. In a production environment this is the best option as it allows DB logic to be managed easily and adjusted as required if changes are made to the backing store that don't require UI changes
  • It's not so quick to create a quick prototype or demo, but it's a better long-term option
• Tinker with the SqlSyncAdapterBuilder
  • I love to tinker with code, but I expect the end result will be letting it create an adapter for download only and then changing it and writing the delete commands myself

End result? At this stage of development we're after quick results and a good demo, so I'll modify the schema. When we do change to using stored procedures I can then go back to my old schema and change the procs without having to change any client code at all! That makes my inner-DBA very happy.

Making the CustomServerProvider becomes trivial now, but I made 2 static methods to make it easier to read:

		private static SyncAdapter GetDownloadOnlyAdapter( SqlConnection cnn, string tableName, string primaryKey )
		{
			var builder = new SqlSyncAdapterBuilder( cnn )
			              	{
			              		TableName = tableName,
			              		RowGuidColumn = primaryKey,
			              		SyncDirection = SyncDirection.DownloadOnly,
			              		CreationTrackingColumn = "CreateVersion",
			              		CreationOriginatorIdColumn = "CreateID",
			              		UpdateTrackingColumn = "UpdateVersion",
			              		UpdateOriginatorIdColumn = "UpdateID"
			              	};

			SyncAdapter adapter = builder.ToSyncAdapter();
			adapter.TableName = tableName;
			return adapter;
		}

		private static SyncAdapter GetBidirectionalAdapter( SqlConnection cnn, string tableName, string primaryKey )
		{
			var builder = new SqlSyncAdapterBuilder( cnn )
			              	{
			              		TableName = tableName,
			              		TombstoneTableName = tableName + "_Tombstone",
			              		RowGuidColumn = primaryKey,
			              		SyncDirection = SyncDirection.Bidirectional,
			              		CreationTrackingColumn = "CreateVersion",
			              		CreationOriginatorIdColumn = "CreateID",
			              		UpdateTrackingColumn = "UpdateVersion",
			              		UpdateOriginatorIdColumn = "UpdateID",
			              		DeletionTrackingColumn = "DeleteVersion",
			              		DeletionOriginatorIdColumn = "DeleteID"
			              	};

			SyncAdapter adapter = builder.ToSyncAdapter();
			adapter.TableName = tableName;
			return adapter;
		}

I have a few tables I want to sync using DownloadOnly and one using Bidirectional, here's the code:

SyncAdapters.Add( GetDownloadOnlyAdapter( cnn, "BillingInfo", "BillingInfoID" ) );
SyncAdapters.Add( GetDownloadOnlyAdapter( cnn, "Employees", "EmployeeID" ) );
SyncAdapters.Add( GetDownloadOnlyAdapter( cnn, "Projects", "ProjectID" ) );
SyncAdapters.Add( GetBidirectionalAdapter( cnn, "TimeEntries", "TimeEntryID" ) );

I'm happy with the CustomServerProvider class now, so it's time to work on the actual UI next.

Code highlighting workflow, using free tools

This time I created the below by pasting my code into Notepad++, selected all text, and went to TextFX, TextFX Convert, Encode HTML. Lastly I added a <pre class="C#" name="code"> to the top and </pre> to the bottom and copied it all, then used Past Special in Live Writer so I could tell it to paste as HTML.

Here's the result:

		public CustomServerProvider()
		{
			var cnn = new SqlConnection( Properties.Settings.Default.ServerConnectionString );
			Connection = cnn;

			const string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
			var selectNewAnchorCommand = new SqlCommand
											 {
												 CommandText =
													 string.Format( "SELECT {0} = min_active_rowversion() - 1",
																	newAnchorVariable ),
												 Connection = cnn
											 };
			selectNewAnchorCommand.Parameters.Add( newAnchorVariable, SqlDbType.Timestamp ).Direction = ParameterDirection.Output;
			this.SelectNewAnchorCommand = selectNewAnchorCommand;

			var downloadOnlyTables = new Dictionary<string, string> { { "BillingInfo", "BillingInfoID" }, { "Employees", "EmployeeID" }, { "Projects", "ProjectID" } };

Much better in my opinion!

Testing SyntaxHighlighter

This post is to check that I've setup SyntaxHighlighter correctly.

	public class CustomServerProvider : DbServerSyncProvider
	{
		public CustomServerProvider()
		{
			var cnn = new SqlConnection( Properties.Settings.Default.ServerConnectionString );
			Connection = cnn;

			const string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
			var selectNewAnchorCommand = new SqlCommand
											 {
												 CommandText =
													 string.Format( "SELECT {0} = min_active_rowversion() - 1",
																	newAnchorVariable ),
												 Connection = cnn
											 };
			selectNewAnchorCommand.Parameters.Add( newAnchorVariable, SqlDbType.Timestamp ).Direction = ParameterDirection.Output;
			this.SelectNewAnchorCommand = selectNewAnchorCommand;

			var downloadOnlyTables = new Dictionary { { "BillingInfo", "BillingInfoID" }, { "Employees", "EmployeeID" }, { "Projects", "ProjectID" } };

			foreach( var pair in downloadOnlyTables )
			{
				var tableName = pair.Key;
				var primaryKey = pair.Value;

				var builder = new SqlSyncAdapterBuilder( cnn )
								  {
									  TableName = tableName,
									  RowGuidColumn = primaryKey,
									  SyncDirection = SyncDirection.DownloadOnly,
									  CreationTrackingColumn = "CreateVersion",
									  CreationOriginatorIdColumn = "CreateID",
									  UpdateTrackingColumn = "UpdateVersion",
									  UpdateOriginatorIdColumn = "UpdateID"
								  };

				SyncAdapter adapter = builder.ToSyncAdapter();
				adapter.TableName = tableName;
				this.SyncAdapters.Add( adapter );
			}

			this.SyncAdapters.Add( TimeEntriesAdapter.GetNew( cnn ) );
		}

So, there's still some tweaking needed it seems, but it's looking good, perhaps I just need to create a tool to make the code HTML safe, and perhaps wrap it in the right PRE tag for me... something to think about for later

new Dictionary

vs

new Dictionary<string , string>