Getting Started with Microsoft Synchronization Services for ADO.NET

• facebook
• twitter
• in
• Email

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