As you have come to expect from Sync Services for ADO.NET programming model, you will need to decide how you want to implement batching and write some code in a form of SQL command. But before I go forward, let me set your expectations first:

• Batching is server only feature
  SqlCeSyncClientProvider does not support batching. The reasoning here is that client changes are not as big. Chances are high that the server will be able to absorb all client changes in one batch. The opposite is not true, however.
   
• Batching does not guarantee transactional consistency
  If you made changes to order row and its corresponding order_details rows, you might not get all these changes together. The changes will come down though once the client consumes all the batches in the session.

Below is a screen shot of the demo application as it makes progress downloading batch number 93 out of 105 batches in the session. Also, note the progress bar at the bottom showing 90% completion rate.
 

Now, let's get down to bossiness. In implementing this demo, I started with the Web Service demo to add batching. There is a reason why I did that. The early public previews of Sync Services did not have batching support but one of the enthusiastic developers, to whom I am very thankful, was unable to download the initial content of the server database to his client when running the web service sample; he shared his experience with us through the forum. For some reason IIS session ran out of memory. All the workaround we proposed did not work. I decided back then to proof the batching logic against this very scenario. I guess the moral of the story is that we listen to your complains and it drives the project forward, so share them whenever you can.

The following are the steps we need to take to add batching to the original web services demo:

Step 1: Enable Batching through SelectNewAnchorCommand

The SelectNewAnchorCommand can be extended to work with the following batch parameters:

Recall that without batching, the SelectNewAnchorCommand returns the current timestamp value (@@dbts) or better yet it returns "min_active_rowversion() - 1". Now, how about recording this value as the @sync_max_received_anchor and let the @sync_new_received_anchor be a lower point that meets a batch size criteria or anything that you might chose to define your current new anchor. The batch count, tells the SyncAgent how many times it needs to call GetChanges() method on the DbServerSyncProvider. Let's see how I implemented a really simple batching logic for this demo:

create procedure dbo.sp_new_batch_anchor (
        @sync_last_received_anchor timestamp ,
        @sync_batch_size int,
        @sync_max_received_anchor timestamp output,
        @sync_new_received_anchor timestamp output,          
        @sync_batch_count int output)      
as            
      if @sync_batch_size <= 0
            set @sync_batch_size = 1000     

      if @sync_max_received_anchor is null
          set @sync_max_received_anchor = @@DBTS -- use "min_active_rowversion()-1" if you can

      -- simplest form of batching
      if @sync_last_received_anchor is null or @sync_last_received_anchor = 0
      begin            
          set @sync_new_received_anchor = @sync_batch_size
            if @sync_batch_count <= 0
                  set @sync_batch_count = (@sync_max_received_anchor /  @sync_batch_size) + 1
      end
      else
      begin
          set @sync_new_received_anchor = @sync_last_received_anchor + @sync_batch_size
            if @sync_batch_count <= 0
                  set @sync_batch_count = (@sync_max_received_anchor /  @sync_batch_size) -
                                          (@sync_new_received_anchor /  @sync_batch_size) + 1
      end        

    -- check if this is the last batch       
    if @sync_new_received_anchor >= @sync_max_received_anchor
    begin
        set @sync_new_received_anchor = @sync_max_received_anchor       
            if @sync_batch_count <= 0
                  set @sync_batch_count = 1
    end   
go

Basically, what is happening in the code above is as follows:

• I set the batch size if it is not set.

• I set the max anchor is it is not set which happens only in the very first batch, in subsequent batches, the value I set will come back to me.

• I set the value for the new anchor and batch count.

• The new anchor value should not exceed the max anchor. The last check does ensure that for us.

Well, you get the idea. In this sample I decided to use simple math to calculate batch count and find the new anchor value for every batch. That is not the best way, but maybe the simplest. I bet you can do better!

Step 2: Configure DbServerSyncAdpater for batching

This is a simple step. All we need to do here is to set the SelectNewAnchorCommand to point to the new stored procedure on the server.

// select new anchor command       
SqlCommand anchorCmd = new SqlCommand();
anchorCmd.CommandType = CommandType.StoredProcedure;
anchorCmd.CommandText = "sp_new_batch_anchor";       
anchorCmd.Parameters.Add("@" + SyncSession.SyncMaxReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.InputOutput;
anchorCmd.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchSize, SqlDbType.Int);
anchorCmd.Parameters.Add("@" + SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp).Direction = ParameterDirection.Output;
anchorCmd.Parameters.Add("@" + SyncSession.SyncBatchCount, SqlDbType.Int).Direction = ParameterDirection.InputOutput;

The most important part to note in this code is the fact that SyncMaxReceivedAnchor and SyncBatchCount are input\output parameters. Without marking the direction this way, things will go wrong badly.

Lastly, we need to set the value for the batch size. The DbServerSyncProvider exposes a property for that:

// set the batch size
const int BatchSize = 25;
_serverProvider.BatchSize = BatchSize;

Step 3: User interface improvements

To get the feel of batching, you will need to make a lot of changes. For this reason, I've added a new bulk insert button to the main form. You get to choose the number of rows to insert on the server. With one click of a button data will be automatically generated.
 

The sync progress form shown at the beginning of the document has quite a bit of work. It uses the SessionProgressEvent to capture the overall progress of the session. This event fires when a batch is downloaded.

That's all I have for now. Happy Sync'ing!
 

Install OfflineAppDemo Application Steps:

Version 1.0 [C#] : Download Now