Getting Started with Microsoft Synchronization Services for ADO.NET
For the next portion, I use stored procedures, but you also could use inline code. The serverSyncProvider needs an anchor command, which will return the timestamp of the last inserted or updated row that has a timestamp column. The anchor allows sync services to determine which rows need to be updated in the client. The stored procedure is simple and looks like the following:
Listing 3: New anchor stored procedure
CREATE PROCEDURE dbo.sp_NewAnchor ( @sync_new_received_anchor timestamp output ) AS SELECT @sync_new_received_anchor = @@DBTS GO
In the constructor of your web service, you need to instruct the serverSyncProvider how to access the anchor using SqlCommand:
Listing 4: New anchor command hook-up code
#region SelectNewAnchorCommand SqlCommand selectNewAnchorCommand = new SqlCommand(); string newAnchorVariable = "@" + SyncSession.SyncNewReceivedAnchor; selectNewAnchorCommand.CommandText = "sp_NewAnchor"; selectNewAnchorCommand.CommandType = CommandType.StoredProcedure; selectNewAnchorCommand.Parameters.Add(newAnchorVariable, SqlDbType.Timestamp); selectNewAnchorCommand.Parameters[newAnchorVariable] .Direction = ParameterDirection.Output; selectNewAnchorCommand.Connection = serverConn; serverSyncProvider.SelectNewAnchorCommand = selectNewAnchorCommand; #endregion
The remainder of the constructor will be dedicated to constructing a SyncAdapter for each table that will need to be synchronized. The SyncAdapter tells sync services how to retrieve/update information from and to the server database. There are four types of tables that determine what needs to be defined in the SyncAdapter. The first is a download-only table, which can only be downloaded from the server by the client. The second is an upload-only table, which (exactly like it sounds) only allows the information to flow from the client to the server. The third is a bi-directional table, which will retrieve and send updates in both directions. Finally, a snapshot table refreshes data pulled from the server each time the database is synchronized. You have no snapshot tables in your example. Please see the documentation for sync services for more information on this type of sync.
The first table you will deal with is the Item table. You do not want the client to be able to modify the rows in this table, but you want any changes made at the server level to propagate down to the client databases. Thus, the Item table is a download-only table. Download-only tables require three commands that instruct the SyncAdapter how to select updated records from the server. These three commands are members of the SyncAdapter class, and they are SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, and SelectIncrementalDeletesCommand. These commands are also required in the bi-directional tables and will look very similar for all of the tables.
Again, I use stored procedures to encapsulate the commands. The SelectIncrementalInsertsCommand stored procedure for the Item table looks like this:
Listing 5: Item incremental updates stored procedure
CREATE PROCEDURE dbo.sp_Item_Incr_Updates ( @sync_last_received_anchor timestamp, @sync_new_received_anchor timestamp, @sync_client_id uniqueidentifier ) AS SELECT ItemID, ItemName FROM Item WHERE ( UpdateTimestamp > @sync_last_received_anchor AND UpdateTimestamp <= @sync_new_received_anchor AND UpdateId <> @sync_client_id AND NOT (InsertTimestamp > @sync_last_received_anchor AND InsertId <> @sync_client_id)) GO
As you can see, this command is responsible for selecting all of the fields of each row that has been updated since the anchor the client recorded when it received updates the last time and where the last client to update the record was not itself. Notice that for download-only tables, the last part will never be true because the UpdateId/InsertId will always be '00000000-0000-0000-0000-000000000000' as we instructed in the database setup.
The SelectIncrementalUpdatesCommand is similar except that it pulls out inserted rows. It looks like this:
Listing 6: Item incremental inserts stored procedure
CREATE PROCEDURE dbo.sp_Item_Incr_Inserts ( @sync_last_received_anchor timestamp, @sync_new_received_anchor timestamp, @sync_client_id uniqueidentifier ) AS SELECT ItemID, ItemName FROM Item WHERE ( InsertTimestamp > @sync_last_received_anchor AND InsertTimestamp <= @sync_new_received_anchor AND InsertId <> @sync_client_id) GO
Finally, the SelectIncrementalDeletesCommand uses the tombstone table you created above:
Listing 7: Item incremental deletes stored procedure
CREATE PROCEDURE dbo.sp_Item_Incr_Deletes ( @sync_initialized bit, @sync_last_received_anchor timestamp, @sync_new_received_anchor timestamp, @sync_client_id uniqueidentifier ) AS SELECT ItemID, ItemName FROM Item_Tombstone WHERE ( @sync_initialized = 1 AND DeleteTimestamp > @sync_last_received_anchor AND DeleteTimestamp <= @sync_new_received_anchor AND DeleteId <> @sync_client_id) GO
Page 3 of 8