dcsimg
December 4, 2016
Hot Topics:

Getting Started with Microsoft Synchronization Services for ADO.NET

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

The three stored procedures you saw in the download-only section would normally be the same for bi-directional as described above. For the example, however, because you only want clients to download information relevant to the specific user, you are going to do something a bit special. You will add a UserID parameter so that the service will only return the appropriate records. This will demonstrate how to pass custom parameters along with the default values passed by sync services. You will see how to configure this extra parameter later in the client section of this article. For clarity, here are the three incremental stored procedures for this table (you can compare them to the original ones above):

Listing 10: Customer sync download stored procedures

/*    Update Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Updates
(
   @UserID uniqueidentifier,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer
WHERE (UserID = @UserID AND
   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

/*    Insert Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Inserts
(
   @UserID uniqueidentifier,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer
WHERE (UserID = @UserID AND
InsertTimestamp > @sync_last_received_anchor
AND InsertTimestamp <= @sync_new_received_anchor
AND InsertId <> @sync_client_id)
GO

/*    Delete Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Deletes
(
   @UserID uniqueidentifier,
   @sync_initialized bit,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer_Tombstone
WHERE (UserID = @UserID AND
   @sync_initialized = 1
   AND DeleteTimestamp > @sync_last_received_anchor
   AND DeleteTimestamp <= @sync_new_received_anchor
   AND DeleteId <> @sync_client_id)
GO
Note: The only differences are the addition of the @UserID parameter and the check to determine whether this row "belongs to" the user attempting to synchronize.

The code to link up this table in the web service constructor is similar to the Item table and can be found in the source code provided.

The CustomerFavoriteItem table is a bi-directional table. Follow the same steps as above for this table. Again, this table is slightly different in that you have to join to the Customer table to determine the UserID before you can do the compare to make sure the client needs to retrieve the row. If you want to see this logic, please look at the sample database or scripts provided.

Finally, the User table is an upload-only table that will allow the client to update its own ClientID when it first generates it. For the User table, I will leave it to you to create (or look up in the provided source code) the necessary commands (and the hookup code in the web service constructor) for an upload-only table, which are: UpdateCommand, InsertCommand, DeleteCommand, and SelectIncrementalInsertsCommand. As a tip, when creating the stored procedures, remember to include brackets around the table name User because User also is a built-in keyword.

Your web service is complete. Compile and get ready to start developing the client application for your sync services example.

The Client

The first thing you will do in this section is to create a Windows Forms application that will serve as the client front end for your synchronization services scenario. I called mine SyncServicesSample_Client.

The next order of business is adding logic to the program to check for an existing ClientID for the application, and, if one doesn't exist, create it. There are many ways you could go about this. For brevity sake, if you would like to see my approach, please check the source code.

Once you have a method of generating a ClientID, you can start creating the sync layer for the client. The client side of sync services is driven by Microsoft.Synchronization.SyncAgent. The SyncAgent holds the reference to the ServerSyncProvider (that you have already created) and the ClientSyncProvider (that you are about to create). It also describes the SyncTables and their sync direction, which you also already know. The client requires references to Microsoft.Synchronization.Data.dll and Microsoft.Synchronization.Data.SqlServerCe.dll; they can be found in the same location as mentioned above. You also will need a reference to the System.Data.SqlServerCe.dll, which is located at "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop." You also will add a connection string to the app.config. SQL Server Compact Edition uses .sdf database files. The connection string will look like this:

<connectionStrings>
   <add name="SampleClientDB"
        connectionString="Data Source='SampleClientDB.sdf';
        Password=pass@word1"/>
</connectionStrings>

Now, you will define the ClientSyncProvider. Create a new class. I will name mine SampleClientSyncProvider. First, add the necessary using directives:

using Microsoft.Synchronization.Data.SqlServerCe;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;

Your class will extend the Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider class like so:

class SampleClientSyncProvider : SqlCeClientSyncProvider



Page 5 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