Getting Started with Microsoft Synchronization Services for ADO.NET

To tie it all together, you will instruct the serverSyncProvider to use these three stored procedures. Place the following code in your web service constructor:

Listing 8: Item sync adapter code

#region ItemSyncAdapter

//Create the SyncAdapter
SyncAdapter itemSyncAdapter = new SyncAdapter("Item");

#region SelectIncrementalUpdatesCommand

//Select updates from the server
SqlCommand itemIncrUpdates  = new SqlCommand();
itemIncrUpdates.CommandText = "sp_Item_Incr_Updates";
itemIncrUpdates.CommandType = CommandType.StoredProcedure;

itemIncrUpdates.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
itemIncrUpdates.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);
itemIncrUpdates.Connection = serverConn;

itemSyncAdapter.SelectIncrementalUpdatesCommand =
   itemIncrUpdates;

#endregion

#region SelectIncrementalInsertsCommand

//Select inserts from the server
SqlCommand itemIncrInserts  = new SqlCommand();
itemIncrInserts.CommandText = "sp_Item_Incr_Inserts";
itemIncrInserts.CommandType = CommandType.StoredProcedure;

itemIncrInserts.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

itemIncrInserts.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);

itemIncrInserts.Connection = serverConn;
itemSyncAdapter.SelectIncrementalInsertsCommand =
   itemIncrInserts;

#endregion

#region SelectIncrementalDeletesCommand
//Select deletes from the server
SqlCommand itemIncrDeletes = new SqlCommand();
itemIncrDeletes.CommandText = "sp_Item_Incr_Deletes";
itemIncrDeletes.CommandType = CommandType.StoredProcedure;

itemIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized,
   SqlDbType.Bit);
itemIncrDeletes.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
itemIncrDeletes.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);
itemIncrDeletes.Connection = serverConn;

itemSyncAdapter.SelectIncrementalDeletesCommand = itemIncrDeletes;

#endregion

//Add the SyncAdapter to the server synchronization provider
serverSyncProvider.SyncAdapters.Add(itemSyncAdapter);

#endregion

The code in Listing 8 just creates a SyncAdapter for the table, configures the incremental commands, and adds the SyncAdapter to the serverSyncProvider. A similar pattern will follow for the other tables as well. I will just discuss portions of the next tables that differ from this one.

Next, do a bi-directional table. For a bi-directional table, you will need to configure UpdateCommand, InsertCommand, and DeleteCommand, in addition to the three commands listed above for the download-only table. Dtart with the new commands. These commands are responsible for inserting information from the clients into the server database. They are very straightforward, so I will just outline the stored procedures here:

Listing 9: Customer sync upload stored procedures

/*    Update Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Updates
(
   @CustomerID UniqueIdentifier,
   @UserID UniqueIdentifier,
   @LastName VarChar(50),
   @FirstName VarChar(30),
   @sync_force_write bit,
   @sync_last_received_anchor timestamp,
   @sync_client_id uniqueidentifier,
   @sync_row_count int
)
AS
UPDATE Customer SET
CustomerID = @CustomerID,
UserID     = @UserID,
LastName   = @LastName,
FirstName  = @FirstName,
UpdateId   = @sync_client_id
WHERE CustomerID = @CustomerID AND
(@sync_force_write = 1
   OR (UpdateTimestamp <= @sync_last_received_anchor
   OR UpdateId = @sync_client_id))
SET @sync_row_count = @@rowcount
GO

/*    Insert Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Inserts
@CustomerID UniqueIdentifier,
@UserID UniqueIdentifier,
@LastName  VarChar(50),
@FirstName VarChar(30),
@sync_client_id uniqueidentifier,
@sync_row_count int
AS
INSERT INTO Customer (CustomerID, UserID, LastName, FirstName,
                      InsertId, UpdateId)
VALUES (@CustomerID, @UserID, @LastName, @FirstName,
        @sync_client_id, @sync_client_id)
SET @sync_row_count = @@rowcount
GO

/*    Delete Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Deletes
(
   @CustomerID UniqueIdentifier,
   @sync_force_write bit,
   @sync_last_received_anchor timestamp,
   @sync_client_id uniqueidentifier,
   @sync_row_count int
)
AS
DELETE FROM Customer
WHERE (CustomerID = @CustomerID)
AND (@sync_force_write = 1
OR (UpdateTimestamp <= @sync_last_received_anchor
OR UpdateId = @sync_client_id))
SET @sync_row_count = @@rowcount
IF (@sync_row_count > 0)  BEGIN
UPDATE Customer_Tombstone
SET DeleteId = @sync_client_id
WHERE (CustomerID = @CustomerID)
END
GO

Page 4 of 8