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