Getting Started with Microsoft Synchronization Services for ADO.NET
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 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