dcsimg
June 18, 2018
Hot Topics:

Getting Started with Microsoft Synchronization Services for ADO.NET

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

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



Comment and Contribute

 


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

 

 


Enterprise Development Update

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

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

Sitemap

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date