dcsimg
December 3, 2016
Hot Topics:

Getting Started with Microsoft Synchronization Services for ADO.NET

  • December 17, 2007
  • By Rachel Baker
  • Send Email »
  • More Articles »

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



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel