Filtering data ADO.NET Synchronization Services
If you dabble in Orcas or are a data aficionado, then you have probably at least heard of Synchronization Services. If you haven't heard of it, let me try to pique your interest.
The business case for Sync Services is simple. You have data in one place and you want to sync it with Data in another place. This is an old school problem that has been dealt with many times before by many different people. That should clue you in to the realization that such problems should probably be approached using design patterns and Data Oriented Design Patterns in particular. My memory is a little foggy but I don't specifically remember reading any 'data movement patterns' in the seminal design pattern book, Design Patterns: Elements of Reusable Object-Oriented Software by the Gang of Four. (As a slight digression, I've found quite a few superb design pattern books and a few stinkers. The GOF book is by far the best one I've come across in terms of being understandable and easy to follow and can say with comfort that it won't disappoint).
So you can roll your own code, you can use an existing mechanism like Replication, or you can go new school and try SS. I don't claim to be an expert at SS but so far, I've found it shocking easily to use and learn. Of all the zillions of new MS products rolling out of Microsoft, SS is one of the easiest I've come across, at least in terms of getting up and running.
Now, chances are that if you have the need to sync data back and forth (for instance, moving data from a database server to your salesforce's Tablet PC's), you will have a lot more data on your server than you'd need or be able to use on the remote machine. Even if you wanted all of that data, in many cases you won't be able to get it all b/c moving it would take forever and the target machine is resource constrained. To that end, the out of the box demo code that ships with SS will need some modification.
The first point I'd make is that Yes, it is possible to sync only portions of the data. This came up recently in one of the newsgroups I frequent and it's an excellent question. Just to get a conceptual feel for this, think of the same scenario I mentioned earlier. You have a db server and a mobile salesforce (or nursing force, or delivery force, or you're a large law firm, consulting group, accounting practice...you get the idea) and you only need to sync each target machine with a subset of data. In an ideal world remote users could see everything that users at the home base could, but moving the data could take forever in many large companies. Moreover, you probably wouldn't need most of it. If you're a nurse that makes house calls, you probably wouldn't need information for people that have passed on, or that have moved or even that you're not visiting. If your device was a PDA, you need to economize on every resource possible. But even if you had a laptop or tablet you still have some resource limits. Hence, envision a case where you might just want to sync only the data related to each person's active clients (and probably augment the query's restriction with a date filter of some sort and perhaps an active account indicator) that have engaged in at least one transaction in the last year.
- You could give each person some sort of UI tool which they could initiate that queries the db and writes the data to a file, which they in turn copy over to their machine.
- You could do the same thing, but run it as a job so it's done automatically
- You could expose the query results as a web service and let them retrieve data as they needed it
- You could export the data to Excel or Access (the latter of which I've seen done more times than I care to remember)
Each of these approaches is easy enough to implement but has major suckiness associated with it. The first approach is one of the simpler ones and fairly fool-proof. Writing out the file is something you could automate via a schedule but not copying it to the device, unless of course you were positive the device would always be available when the job ran. With the web service, you're depending on internet connectivity which, even in this day where everyone has mobile broadband, there are still a lot of coverage holes. If you had any sort of substantial dataset, pulling it down over a mobile broadband connection would be a doubleplusbad. Copying it to Access or Excel would have the same issues and shortcomings that the first approach, after all it really is the same approach but simply uses a different file format.
Referring to the SyncServices_CSharp_DownloadOnly sample included in the Sync Services install, refer to the SampleServerSyncProvider method of the SampleServerSyncProvider.cs or SampleServerSyncProvider.vb file, you see the following code (the Author was kind enough to include copious comments which explain each portion - I've removed them for readability but would encourage you to refer to them if you're unfamiliar with Sync Service):
public class SampleServerSyncProvider : DbServerSyncProvider
{
public SampleServerSyncProvider()
{
this.Connection = new SqlConnection(Properties.Settings.Default.ServerConnString);
string awNewAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor;
SqlCommand selectNewAnchorCommand = new SqlCommand();
selectNewAnchorCommand.CommandText = "SELECT " + awNewAnchorVariable + " = @@DBTS";
selectNewAnchorCommand.Parameters.Add(awNewAnchorVariable, SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[awNewAnchorVariable].Size = 8;
selectNewAnchorCommand.Parameters[awNewAnchorVariable].Direction = ParameterDirection.Output;
selectNewAnchorCommand.Connection = (SqlConnection)this.Connection;
this.SelectNewAnchorCommand = selectNewAnchorCommand;
SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder((SqlConnection)this.Connection);
builder.TableName = "Sales.Customer";
builder.TombstoneTableName = builder.TableName + "_Tombstone";
builder.TombstoneFilterClause = filterCommand;
builder.DataColumns.Add("CustomerId");
builder.DataColumns.Add("CustomerName");
builder.DataColumns.Add("SalesPerson");
builder.CreationTrackingColumn = "InsertTimestamp";
builder.UpdateTrackingColumn = "UpdateTimestamp";
builder.DeletionTrackingColumn = "DeleteTimestamp";
SyncAdapter customerSyncAdapter = builder.ToSyncAdapter();
customerSyncAdapter.TableName = "Customer";
this.SyncAdapters.Add(customerSyncAdapter);
}
}
[Note: Even with the comments, some of this code may be hard to follow when you first read it. One thing I did which was very enlightening and always a useful thing to do in such situations is fire up good ole' Sql Profiler, then run the application and see exactly what happens, what objects are being used and what commands are being sent to the db].
I show some of the relevant results from the trace below (I've color coded the results to the portion of the code that which caused it to happen):
declare @p3 binary(8)
set @p3=0x0000000000000FA0
exec sp_executesql N'SELECT @sync_new_received_anchor = @@DBTS',N'@sync_new_received_anchor timestamp
output',@sync_new_received_anchor=@p3 output
select @p3
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson] FROM Sales.Customer WHERE
(InsertTimestamp > @sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)',N'@sync_last_received_anchor binary(8000),@sync_new_received_anchor
binary(8)',@sync_last_received_anchor=0x00,@sync_new_received_anchor=0x0000000000000FA0
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson] FROM Sales.Customer WHERE
(CustomerName=''Sharp Bikes'') AND (UpdateTimestamp > @sync_last_received_anchor AND UpdateTimestamp <=
@sync_new_received_anchor AND InsertTimestamp <= @sync_last_received_anchor)',N'@sync_last_received_anchor
timestamp,@sync_new_received_anchor
timestamp',@sync_last_received_anchor=0x0000000000000000,@sync_new_received_anchor=0x0000000000000FA0
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson], [CustomerType], [DeleteId],
[DeleteTimestamp] FROM Sales.Customer_Tombstone WHERE (CustomerName=''Sharp Bikes'') AND (@sync_initialized = 1
AND DeleteTimestamp > @sync_last_received_anchor AND DeleteTimestamp <=
@sync_new_received_anchor)',N'@sync_initialized bit,@sync_last_received_anchor
timestamp,@sync_new_received_anchor
timestamp',@sync_initialized=0,@sync_last_received_anchor=NULL,@sync_new_received_anchor=0x0000000000000FA0
As it stands, the code above is pulling everything from the Sales.Customer table. But as I mentioned, chances are that you wouldn't want the whole resultset. Rather, you'd probably want a filtered subset. For the sake of visual simplicity, I'm going to violate one of my biggest coding Pet Peeves and concatenate a sql statement to include a parameter instead of doing it the right way, but I'll show the correct way shortly.
The SqlSyncAdapterBuilder has several properties and for filtering purposes, you can simply set the FilterClause property. Starting with the code beginning at the adapter instantiation, I modified the code to just return records that have a CustomerName value equal to the literal 'Sharp Bikes' (The relevant portions are included in Bold face):
SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder((SqlConnection)this.Connection);
String FilterCommand = "CustomerName='Sharp Bikes'";
builder.TableName = "Sales.Customer";
builder.TombstoneTableName = builder.TableName + "_Tombstone";
builder.FilterClause = FilterCommand ;
builder.TombstoneFilterClause = FilterCommand;
builder.SyncDirection = SyncDirection.DownloadOnly;
So, it's pretty much this easy, you simply need to set the FilterClause property of the SqlSyncAdapterBuilder and absent some really compelling reason to do so, set the TombStoneFilterClause to the same value. Because you use the same statement, it's best to use a String literal so that the two FilterClause values don't get out of sync. Just as you set the FilterClause and TombstoneFilterClause properties to facilitate filtering, if you use a parameter, you need to add a SqlParameter (or SqlParameter(s)) to the FilterParameters and TombstoneFilterParameters collections. So, the below code shows the previous snippet modified to use a SqlParameter instead of concatenating the Sql text, again, the relevant portions are included in Bold face:
SqlSyncAdapterBuilder builder = new SqlSyncAdapterBuilder((SqlConnection)this.Connection);
SqlParameter CustomerNameParameter = new SqlParameter("@CustomerName", SqlDbType.NVarChar);
String FilterCommand = "CustomerName=@CustomerName";
builder.TableName = "Sales.Customer";
builder.TombstoneTableName = builder.TableName + "_Tombstone";
builder.FilterClause = FilterCommand ;
builder.TombstoneFilterClause = FilterCommand;
builder.FilterParameters.Add(CustomerNameParameter);
builder.TombstoneFilterParameters.Add(CustomerNameParameter);
Now the results from Profiler show something notably different:
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson] FROM Sales.Customer WHERE
(CustomerName=@CustomerName) AND (InsertTimestamp > @sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)',N'@CustomerName nvarchar(11),@sync_last_received_anchor
binary(8000),@sync_new_received_anchor binary(8)',@CustomerName=N'Sharp
Bikes',@sync_last_received_anchor=0x00,@sync_new_received_anchor=0x0000000000000FA0
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson] FROM Sales.Customer WHERE
(CustomerName=@CustomerName) AND (UpdateTimestamp > @sync_last_received_anchor AND UpdateTimestamp <=
@sync_new_received_anchor AND InsertTimestamp <= @sync_last_received_anchor)',N'@CustomerName
nvarchar(11),@sync_last_received_anchor timestamp,@sync_new_received_anchor timestamp',@CustomerName=N'Sharp
Bikes',@sync_last_received_anchor=0x0000000000000000,@sync_new_received_anchor=0x0000000000000FA0
exec sp_executesql N'SELECT [CustomerId], [CustomerName], [SalesPerson], [CustomerType], [DeleteId],
[DeleteTimestamp] FROM Sales.Customer_Tombstone WHERE (CustomerName=@CustomerName) AND (@sync_initialized = 1
AND DeleteTimestamp > @sync_last_received_anchor AND DeleteTimestamp <=
@sync_new_received_anchor)',N'@CustomerName nvarchar(11),@sync_initialized bit,@sync_last_received_anchor
timestamp,@sync_new_received_anchor timestamp',@CustomerName=N'Sharp
Bikes',@sync_initialized=0,@sync_last_received_anchor=NULL,@sync_new_received_anchor=0x0000000000000FA0
You can see here clearly that not only is the @CustomerName parameter being used, it's being set to the literal Sharp Bikes
There's one catch though when using real parameters. If you modify the code as I did above and try to run it, you'll encounter the following exception:
Microsoft.Synchronization.Data.SessionVariableException was unhandled
Message="Unable to set session parameters in DbServerSyncProvider. Cannot obtain the value for command parameter '@CustomerName'."
Source="Microsoft.Synchronization.Data.Server"
SyncSource="ServerSyncProvider"
StackTrace:
at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.SetSessionParameters(IDbCommand cmd, SyncGroupMetadata groupMetadata, SyncTableMetadata tableMetadata, SyncSession syncSession, DataColumnCollection columns, SyncStage stage)
at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.EnumerateChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession, IDbTransaction transaction, EnumerateChangeType changeType)
at Microsoft.Synchronization.Data.Server.DbServerSyncProvider.GetChanges(SyncGroupMetadata groupMetadata, SyncSession syncSession)
at Microsoft.Synchronization.SyncAgent.DownloadChanges(SyncGroupMetadata groupMetadata)
at Microsoft.Synchronization.SyncAgent.Synchronize()
at Microsoft.Samples.Synchronization.MainForm.btnSynchronize_Click(Object sender, EventArgs e) in C:\SyncServicesCode\SyncServices_CSharp_DownloadOnly\Client\Forms\MainForm.cs:...
Nothing too shocking though right, after all, although we specified a parameter be used in the CommandText property, we never set the value so it's not surprising that an exception was thrown unless the parameter had a default value, which in this case it clearly did not.
But if you change the code to explicitly set the value for the parameter, for instance like this...
SqlParameter CustomerNameParameter = new SqlParameter("@CustomerName", SqlDbType.NVarChar);
CustomerNameParameter.Value = "Sharp Bikes";
you'll still get the same exception. This really threw me for a loop b/c I used the debugger and proved to myself that the value wasn't null or left out. If you step through with the debugger, you can verify for yourself that the above code is in fact setting the value of @CustomerName = "Sharp Bikes"
One point that's extremely important... Normally, as long as you modify your CommandText to refer to the SqlParameter and add a SqlParameter object to a SqlCommand object's Parameters collection, you don't need to do anything else. But as I just babbled on about, that won't cut it here... something is still missing.
As it turns out, (and makes perfect sense once I cleared my tunnel vision), the SyncAgent (Microsoft.Synchronization.SyncAgent) class is what actually does much of the heavy lifting and just because you have a Parameter referenced in code in your SqlSyncAdapterBuilder, the SyncAgent doesn't know anything about it. Since it doesn't know anything about it, it doesn't know to pass in a value or what value to pass to it, so when it tries to finish processing the value for the parameter isn't set. It didn't matter that we set it explicitly because we set it in the wrong place.
Fortunately, it's very easy to address. The code below is the SyncAgent's code, the last line in Bold is what you need to add if you're using a parameterized filter:
public class SampleSyncAgent : Microsoft.Synchronization.SyncAgent
{
public SampleSyncAgent()
{
SqlCeClientSyncProvider clientSyncProvider = new SqlCeClientSyncProvider(Properties.Settings.Default.ClientConnString, true);
this.LocalProvider = clientSyncProvider;
clientSyncProvider.ChangesApplied += new EventHandler<ChangesAppliedEventArgs>(clientSyncProvider_ChangesApplied);
this.RemoteProvider = new SampleServerSyncProvider();
SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption = TableCreationOption.DropExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.DownloadOnly;
this.Configuration.SyncTables.Add(customerSyncTable);
this.Configuration.SyncParameters.Add(
new SyncParameter("@CustomerName", "Sharp Bikes"));
}
}
(Part of this class was left out for brevity - all that is missing is the private variables and their corresponding public properties).
The corresponding code is available Here. Please note that this code is taken from the Synchronization Services samples that are included when you install Synchronization Services. I merely modified that code to support parameterized filters. I will be adding a UI Element on the MainForm object so that you can set the value dynamically instead of hard coding it as I did. Again, although this post is a little long, we really are talking about basically adding less than 10 lines of code to add filtering. You need to set the FilterClause, the TombstoneFilterClause, FilterParameters, TombstoneFilterParameters and then add a SyncParameter to the SyncAgent. As you can infer, you if you need more expressions, you can just add them accordingly to meet whatever your requirements happen to be.