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.