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>

Sync for ADO.NET - Client (Part 1)

Now it's time to set up our client. I'm not a big fan of wizards, and at this point in time the wizard for Sync is broken for my design so we'll code it all manually. I think this really helps with the understanding of the framework anyway.

For the Sync framework the concept is pretty simple, there's a few main roles:

• Client Sync Provider - this is what talks to our local SQL CE database
• Server Sync Provider - this talks to the server SQL database
• Sync Agent - this contains the group(s) of tables, instances of the providers, and exposes the Synchronize() method

If you're planning on using this in a distributed app where some clients don't have direct access to the SQL server you'll want to host the Server Sync Provider inside a WCF service, this provides for a secure way to talk to the database without exposing it to the Internet.

It's also worth noting that if you host using a WCF service layer, the server data store could be anything as long as it supports tracking changes using an anchor.

Sync Agent

This is where we create our groups of tables, each group will be updated at the server using a single transaction, if part of it fails the transaction fails (and will be retried on next sync).

For our purposes we'll have a single group with all our tables, we just have to make sure we add the tables in the right order so as not to break a relationship (for this demo it means TimeEntries is added last).

We can also add handlers to the StateChanged and SessionProgress events on this class if we want to show some feedback in our UI.

Client Sync Provider

To configure the local SQL CE database the out-of-the-box solution is good enough, but I like to make some minor changes like setting my primary key Guids to have RowGuid = true, as enforce foreign keys (these are dropped by Sync, probably because it's pretty easy to bring over partial results that might not include everything needed for relationships).

Again we use event handlers to add extra functionality by subscribing to CreatingSchema and SchemaCreated. There some events handy for logging too SchemaCreated, ChangesSelected, ChangesApplied, ApplyChangeFailed.

The above mentioned changes to Primary Key and Foreign Key references would be done in a CreatingSchema event handler, and any ALTER TABLE statements can be done in the SchemaCreated handler.

Server Sync Provider

We need to specify our SelectNewAnchorCommand, which can be as simple as getting the "Min_Active_RowVersion() - 1" (SQL 2005 SP2 fix for @@DBTS issue), or calling a stored proc that gets an anchor with support for batching.

For each table we want to synchronize against there's a bunch of SQL statements needed. If you want to go the easy way you can use the SqlSyncAdapterBuilder, just give it a little info about your table and it'll go off and read the schema and create the commands needed. I would suggest doing it by hand the first couple of times so you know exactly what's going on. For enterprise apps, make your DBA's happy and let them create stored procedures for the insert/update/delete pairs.

There's some events you can hook into here too, notably ChangesSelected, ChangesApplied, ApplyChangeFailed and ApplyingChanges. Mostly these would be for logging purposes, although in your ApplyingChanges handler you could modify the data about to be written.

One important note here, this class would normally exist inside a WCF layer, so we'll put it in another Class Library project for now that way it's easier to drop the reference in our client and use a wrapper around the WCF service at a later date.

What Next?

I'll go into a bit more detail showing some code for the client, perhaps even a download for the solution so far.

Sync for ADO.NET - Server

So we have the basic idea of the application, and due to the limited number of tables our Domain Design matches Data Design quite nicely. This means that for simplicity we can use LINQ to SQL, but I'll discuss that more in a post about the client.

For the server all we really need to do is create our database for now. It's worth noting that to support sync we'll need to add a few extra columns to each table. It's also important to decide how a delete will be handled, either as a logical delete (row is 'flagged' somehow to mark it as deleted) or a physical delete (row is removed from the database). If a physical delete is going to be used you'll need to add a "tombstone" table, somewhere to copy the row to when you delete it, otherwise there's no way for the clients to know the row has been removed (since they'll just ask for changes since last check).

Extra columns I suggest are:

• UpdateVersion timestamp NOT NULL
• CreateVersion binary(8) NOT NULL DEFAULT @@DBTS + 1
• DeleteVersion varbinary(8) NULL
• UpdateID uniqueidentifier NOT NULL
• CreateID uniqueidentifier NOT NULL
• DeleteID uniqueidentifier NULL

I've made an assumption here that only sync-aware applications will modify rows, this avoids the need for triggers and empty Guid values as defaults. Please note that we'll be filtering rows on all 6 columns so they should all have an index added.

Here's my database diagram:

You'll notice that I haven't included the DeleteVersion or DeleteID columns for the 3 related tables, since I don't want to allow deletes on them (later we could add an IsActive flag to allow marking them as inactive while not breaking existing links).

Sync for ADO.NET - Introduction

This is the first in a series of posts I plan on my experience with the Microsoft Sync Framework for ADO.NET.

First a little about the scenario for this, I'm creating a small app that must run on multiple machines, all talking to a central database (either via LAN or The Internet), supporting full off-line operation.

To make this a little more interesting I'm going to assume there's a mix of download-only tables (reference data) and full bi-directional sync tables (the stuff we'll be changing).

Clients will be running SQL CE 3.5 SP1, since that's the only choice we have (for now) with the Sync framework. Server will be running SQL 2005 SP2 (SP2 is required to fix an issue with @@DBTS, without it we could have missing data on clients). SQL 2008 would make it easier but it's still rare to find that out in the wild, and it's no big deal to change this to support that later.

For my test application it'll be a simple Time-sheet, here's the class diagram:

LINQ to SQL frustration

I've been experimenting quite a bit with LINQ to SQL, in combination with the Microsoft Sync Framework for ADO.NET. This places a limitation of SQL CE 3.5 SP1 on the client side, so no stored procs and a sub-set of field types only.

GUID as Primary Key

One thing I noticed is that you can't set a SQL CE table to use a GUID as the Primary Key using NewID() for the default. Sure SQL CE will let you do it, but LINQ to SQL has a fit when you try and insert a row, apparently you're not allowed to mark a 'uniqueidentifier' column as auto-generated... perhaps that's some hangover from the Beta or something, since Sync has some issues too (if you sync a server-side table to a local table it loses the default and loses the "is row guid" property, requiring you to intercept the schema creation to turn those features back on).

This is no big deal I guess, since if you are using SQL CE then your app is all that's going to be changing rows, so you can simply add a partial class that sets the GUID column to Guid.NewGuid() in the OnCreated() partial method.

No Designer Support for SQL CE

While you can use SQLMetal.exe to create your mapping, it's not the same experience. Then again, given the nature of my focus here it's probably a better idea since I want to adjust my schema often based on my ever-changing sync requirements.

It's Not a First Class OR/M

This one might get me some hate mail, but I feel that it's a pretty poor OR/M experience. For production code I would suggest NHibernate as it allows for the vast differences between your Domain Model and your Data Model.

While LINQ to SQL has some support for inheritance it's still a baby in the OR/M world. Any project beyond 5 tables in size would probably have to be adjusted to fit the technology instead of having the technology make the project easier.

Summary

From my experience so far, I think that LINQ to SQL is great for doing a demo, great for small projects and perhaps some web apps, but can't be taken seriously in the enterprise application development area.

Still, that wont stop me from using it as a replaceable data persistance layer for demo apps and in articles I plan to write.

Blogging again!

Ahh, it's good to be blogging again. I have been quiet for so long I couldn't remember the password for my old blog, nor the email I used to register it... so I've started another one here.

I've recently started a 6 month contract fixing a WPF app that has been using CAB, it's own custom Sync Framework, with a SQL 2005 backend... it's a bit of a mess so I'll be looking at changing the architecture. If Sync is needed, I'll be using the Microsoft Syncronization Framework (currently CTP2). I'm also planning to use NHibernate and Castle Active Record to simplify the DAL and get rid of the custom code-generation that's being done right now.

I plan to blog about this, and also my experiments (and frustration with) LINQ 2 SQL, WPF Data Binding, and other architecture I play with.

If I get any spare time I want to try out the iPhone SDK, since there's a high chance the iPhone will be released here in Australia next week!

Anyway, I'll post more soon.