While SyncAdapterBuilder is a pretty useful utility, it hides some sync logic that I believe is important to understand so that you can expand on the basic functionality shown in the previous demo. SyncAdapter commands are the extension point to this framework and where your custom logic goes.

In this demo, I want to show you how to write SyncAdapter commands using TSQL statements or stored procedures which you will have to create on the server database. To show you both in one demo, I decided to write TSQL statements for selecting changes and stored procedures for applying changes.

Before we delve into coding, let examine the interface of the SyncAdapter type. Below is a snapshot for what the object browser shows you when you navigate the Microsoft.Synchronization.Data.Server namespace and client on SyncAdapter type:

As you can see, there are eight properties that end with the word 'Command'. If you examine the type of the command you will see that it is of type System.Data.IDbCommand. This means that SyncAdapter is database agnostic thus you can assign any ADO.NET command object (i.e. OleDbCommand, SqlCommand, OracleCommand .. etc) to it.

The sync commands on the SyncAdapter can be classified into two categories:

1. Selecting Changes Commands
2. Applying Changes Commands

Do you really need to write all of the eight commands to sync data? Well, the answer is no. It all depends on your scenario though. The table below lists the commands needed for each scenario:

In this demo, I took the long path and implemented all the commands. The conflict detection one are beyond the scope of this demo and will be discussed in a future demo. Enough said, let's see the code.

Selecting Changes Commands

For the orders table, selecting changes commands are straight forward:

// orders table
SyncAdapter adaptorOrders = new SyncAdapter("orders");
adaptorOrders.CreationOriginatorColumnName = "update_originator_id";

// select incremental inserts command
SqlCommand incInsOrdersCmd = new SqlCommand();
incInsOrdersCmd.CommandType = CommandType.Text;
incInsOrdersCmd.CommandText =
    "select order_id, order_date from [orders] " +
    "Where create_timestamp > @sync_last_received_anchor " +
    "and create_timestamp <= @sync_new_received_anchor " +
    "and update_originator_id <> @sync_client_id_hash " +
    "order by create_timestamp desc ";

incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
incInsOrdersCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Binary, 8);

adaptorOrders.SelectIncrementalInsertsCommand = incInsOrdersCmd;

To select inserts that have happened since the last time the client application synchronized with the server, I need few things:

1. Timestamp value to mark the last time the client synchronized. This value is stored at the client local store and provided to the select inserts command through @sync_last_received_anchor session parameter (SyncSession.SyncLastReceivedAnchor). When the runtime sees this parameter name in the command (via the command parameters collection) it substitutes the value for me.
2. Timestamp value to define the high point where the enumeration should not exceed. In the next sync, this value will be the lower marker, you get the idea. The new anchor value is obtained through SelectNewAnchor command that is exposed on the provider interface. For this demo, the SelectNewAnchor command is so simple as in "Select @@DBTS"
3. Client ID. This is maybe less obvious. Suppose that the client did upload changes then it is now downloading changes from the server, if I cannot identify the changes the client made, then I will end up downloading the very same changes that I just uploaded. Therefore I need away to say "give me all rows except those that has this originator id", that's where client ID comes to play. Again, the session parameters SyncSession.SyncClientIdHash will be substituted by the runtime with the hash of the client GUID. This is good enough for the demo since I don't expect so many clients to run the risk of hash collision. You can use the client GUID itself if you so choose using SyncSession.SyncClientId.

SelectIncremenralUpdatesCommand and SelecctIncrementalDeletesCommand follow the same suit, the code below shows both commands (notice that I Clone()'d the inserts command since I am using the same parameters):
 

// select incremental updates command
SqlCommand incUpdOrdersCmd = incInsOrdersCmd.Clone();
incUpdOrdersCmd.CommandText =
    "select order_id, order_date from [orders] " +
    "where create_timestamp <= @sync_last_received_anchor " +
    "and update_timestamp > @sync_last_received_anchor " +
    "and update_timestamp <= @sync_new_received_anchor    " +
    "and update_originator_id <> @sync_client_id_hash " +
    "order by update_timestamp desc ";

adaptorOrders.SelectIncrementalUpdatesCommand = incUpdOrdersCmd; 

// select incremental deletes command
SqlCommand incDelOrdersCmd = incInsOrdersCmd.Clone();
incDelOrdersCmd.CommandText =
    "select order_id from [orders_tombstone] " +
    "where update_timestamp > @sync_last_received_anchor " +
    "and update_timestamp <= @sync_new_received_anchor " +
    "and update_originator_id <> @sync_client_id_hash " +
    "order by update_timestamp desc     ";
adaptorOrders.SelectIncrementalDeletesCommand = incDelOrdersCmd;

The highlight of this demo is the new concept of sync session parameters. These are key runtime values that you need to work with to enable sync just as I did with the select changes commands. So far you saw the anchor session parameters and  the client id session parameters. There are more to come.

Applying Changes Commands

Writing the sync commands as stored procedure is no big deal. If your familiar with ADO.NET, you know how easy that is. You will find the code for the stored procedure inside server_procs.sql file. The following code shows the InsertCommand along with the stored procedure on the server. The run time will substitue the session parameter and the actual column values from the the changes dataset that the ClientSyncProvider returns from GetChanges() call.

// insert order row command
SqlCommand insOrdersCmd = new SqlCommand();
insOrdersCmd.CommandType = CommandType.StoredProcedure;
insOrdersCmd.CommandText = "sp_orders_applyinsert";
insOrdersCmd.Parameters.Add("@order_id", SqlDbType.Int);
insOrdersCmd.Parameters.Add("@order_date", SqlDbType.DateTime);
insOrdersCmd.Parameters.Add("@" + SyncSession.SyncClientIdHash, SqlDbType.Int);
insOrdersCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Binary, 8);
insOrdersCmd.Parameters.Add("@" + SyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;

adaptorOrders.InsertCommand = insOrdersCmd;

// insert order row stored procedure
create procedure dbo.sp_orders_applyinsert
    @sync_last_received_anchor binary(8) ,
    @sync_client_id_hash int ,
    @sync_rowcount int out,
    @order_id int = NULL ,
    @order_date datetime = NULL)       
as
    insert into [orders] ([order_id], [order_date], [update_originator_id])
    values (@order_id, @order_date, @sync_client_id_hash)
    set @sync_rowcount = @@rowcount
go

Notice that the stored procedure (or TSQL for that matter) must return a row count and an output parameter. This is yet another sync session parameter (SyncSession.SyncRowCount) that you need to be familiar with. This value indicates is the row was applied (>0) or not. If the procedure failed to apply the row due to check constraint or PK violation or any other reason, the runtime will execute its conflict detection logic which I will discuss in a future demo.

Update and delete command are done similarly.
 

Install OfflineAppDemo Application Steps:

• Fire SQL server management studio and load demo.sql file
• Execute the script until the "test sample" marker
• Load server_procs.sql file and execute it to create sync stored procedures on the server
• Load VS solution (OfflineAppDemo-TSQL+SProc Project)
• Build the project
• You are ready to go

Version 1.2 [C#]: Download Now