Passing dynamic filter values to Sync Framework collaboration scenario sync scopes


NOTE (02 Sept 2010):
With the release of Sync Fx v2.1, I strongly suggest taking advantage of using the Filter templates instead. You may check out my post on
Sync Provisioning

One of the common complaints about filtering support in Sync Framework’s sync scope is the lack of support for passing dynamic filter values. When you define a filter using sync scope provisioning, the filter value is actually hardcoded as part of the SelectChanges stored procedure. And there is no facility to actually modify a scope or modify the filter value.  You can modify the SelectChanges stored procedure to add a parameter, but the out-of-the box providers doesn’t provide a way for you to actually pass the parameter value.

Take this for example, we want to create a filter on the CustomerType column. In this example, we’re setting it to ‘Retail’", which means when synchronization is fired, only those rows satisfying the filter will be included in the determination of incremental changes.

Code Snippet
  1. serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  2. serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Retail’";

Supposed, you have multiple customer types, and you want your users to sync data depending on the customer type they handle (Wholesale, Retail, Domestic, International, Online), you need to create a new scope for all the different customer type and dynamically set which  scope to synchronize depending on the user because there is no way for you to access the filter in the code above and dynamically replace ‘Retail’ with whatever other value you want. 

Each scope definition with a different filter will have an equivalent SelectChanges stored procedure for that table. If you only have 5 customer types, then that’s manageable. But what if you need to set the filter for example by customer territory and you’re a global company, you’ll easily end up with hundreds may be thousands of SelectChanges stored procedures (one post i read on the sync forum says they’ll end up with 2500)

So I thought I’d take a shot at finding a workaround. Luckily, there is a workaround if you don’t mind combining the out-of-the-box provider with a customer provider.

Let’s take a look at provisioning first.

Code Snippet
  1. //The SampleStats class handles information from the SyncStatistics
  2. //object that the Synchronize method returns.
  3. SampleStats sampleStats = new SampleStats();
  4.  
  5. // Create the connections
  6. SqlConnection serverConn = new SqlConnection(Utility.ConnStr_SqlSync_Server);
  7.  
  8. // this will act as our first client
  9. SqlConnection clientSqlConn = new SqlConnection(Utility.ConnStr_SqlSync_Client);
  10.  
  11. //this will be our 2nd client
  12. SqlConnection secondClientSqlConn = new SqlConnection(Utility.ConnStr_DbSync2);
  13.             
  14. SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning();
  15. serverConfig.ObjectSchema = "Sync";
  16.  
  17. // check if server has the scope provisioned already
  18. // otherwise, provision the scope
  19. if (!serverConfig.ScopeExists("filtered_customer", serverConn))
  20. {
  21.     // Create a scope named "filtered_customer".
  22.     // GetDescriptionForTable gets the schema of the table, so that tracking
  23.     // tables and triggers can be created for that table.
  24.     DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("filtered_customer");
  25.  
  26.     // Definition for Customer.
  27.     DbSyncTableDescription customerDescription =
  28.         SqlSyncDescriptionBuilder.GetDescriptionForTable("Sales.Customer", serverConn);
  29.  
  30.     scopeDesc.Tables.Add(customerDescription);
  31.     
  32.     // Create a provisioning object for "filtered_customer". We specify that
  33.     // base tables should not be created since they already exists in our server
  34.     // and that all synchronization-related objects should be created
  35.     // in a database schema named "Sync".
  36.     serverConfig.PopulateFromScopeDescription(scopeDesc);
  37.     serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
  38.     serverConfig.ObjectSchema = "Sync";
  39.     
  40.     // Specify which column(s) in the Customer table to use for filtering data,
  41.     // and the filtering clause to use against the tracking table.
  42.     // "[side]" is an alias for the tracking table.
  43.     // we will modify this in the database and replace with a parameter instead
  44.     serverConfig.Tables["Sales.Customer"].AddFilterColumn("CustomerType");
  45.     serverConfig.Tables["Sales.Customer"].FilterClause = "[side].[CustomerType] = ‘Retail’";
  46.  
  47.     // Configure the scope and change-tracking infrastructure.
  48.     serverConfig.Apply(serverConn);
  49. }
  50.  
  51. SqlSyncScopeProvisioning clientSqlConfig = new SqlSyncScopeProvisioning();
  52. clientSqlConfig.ObjectSchema = "Sync";
  53.  
  54. // check if the 1st client has the scope provisioned already
  55. // otherwise, provision the scope
  56. if (!clientSqlConfig.ScopeExists("filtered_customer", clientSqlConn))
  57. {
  58.     // Provision the existing database SyncSamplesDb_SqlPeer2 based on scope
  59.     // information that is retrieved from the SQL Server database.
  60.     DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
  61.     clientSqlConfig.PopulateFromScopeDescription(clientSqlDesc);
  62.     clientSqlConfig.ObjectSchema = "Sync";
  63.     clientSqlConfig.Apply(clientSqlConn);
  64. }
  65.  
  66. SqlSyncScopeProvisioning secondClientSqlConfig = new SqlSyncScopeProvisioning();
  67. secondClientSqlConfig.ObjectSchema = "Sync";
  68.  
  69. // check if the 1st client has the scope provisioned already
  70. // otherwise, provision the scope
  71. if (!secondClientSqlConfig.ScopeExists("filtered_customer", secondClientSqlConn))
  72. {
  73.     // Provision the existing database SyncSamplesDb_Peer2 based on scope
  74.     // information that is retrieved from the SQL Server database.
  75.     DbSyncScopeDescription clientSqlDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("filtered_customer", null, "Sync", serverConn);
  76.     secondClientSqlConfig.PopulateFromScopeDescription(clientSqlDesc);
  77.     clientSqlConfig.ObjectSchema = "Sync";
  78.     secondClientSqlConfig.Apply(secondClientSqlConn);
  79. }

In the above code, we are provisioning the server and two clients. You’ll notice that the above code is pretty much the standard way of provisioning using the out-of-the-box providers. You’ll also notice that we’re actually providing the filter here as well.

The above code would provision all the necessary database objects (triggers, tracking tables, stored procedures) to enable change tracking and synchronization. Let’s pay particular attention to the SelectChanges stored procedure that was created.

Code Snippet
  1. CREATE PROCEDURE [Sync].[Customer_selectchanges]
  2.     @sync_min_timestamp BigInt,
  3.     @sync_scope_local_id Int,
  4.     @sync_scope_restore_count Int
  5. AS
  6. BEGIN
  7. SELECT    [side].[CustomerId], [base].[CustomerName], [base].[SalesPerson],
  8.         [base].[CustomerType], [side].[sync_row_is_tombstone],
  9.         [side].[local_update_peer_timestamp] as sync_row_timestamp,
  10.         case when ([side].[update_scope_local_id] is null
  11.                         or [side].[update_scope_local_id] <> @sync_scope_local_id)
  12.             then COALESCE([side].[restore_timestamp], [side].[local_update_peer_timestamp])
  13.             else [side].[scope_update_peer_timestamp]
  14.         end as sync_update_peer_timestamp,
  15.         case when ([side].[update_scope_local_id] is null
  16.                         or [side].[update_scope_local_id] <> @sync_scope_local_id)
  17.             then case when ([side].[local_update_peer_key] > @sync_scope_restore_count)
  18.                     then @sync_scope_restore_count
  19.                     else [side].[local_update_peer_key]
  20.                  end
  21.             else [side].[scope_update_peer_key]
  22.         end as sync_update_peer_key,
  23.         case when ([side].[create_scope_local_id] is null
  24.                     or [side].[create_scope_local_id] <> @sync_scope_local_id)
  25.             then [side].[local_create_peer_timestamp]
  26.             else [side].[scope_create_peer_timestamp]
  27.         end as sync_create_peer_timestamp,
  28.         case when ([side].[create_scope_local_id] is null
  29.                     or [side].[create_scope_local_id] <> @sync_scope_local_id)
  30.             then case when ([side].[local_create_peer_key] > @sync_scope_restore_count)
  31.                         then @sync_scope_restore_count
  32.                         else [side].[local_create_peer_key]
  33.                 end
  34.             else [side].[scope_create_peer_key]
  35.          end as sync_create_peer_key
  36. FROM [Sales].[Customer] [base]
  37.     RIGHT JOIN [Sales].[Customer_tracking] [side]
  38.         ON [base].[CustomerId] = [side].[CustomerId]
  39. WHERE (([side].[CustomerType] = ‘Retail’)
  40.         OR ([side].[sync_row_is_tombstone] = 1
  41.         AND ([side].[update_scope_local_id] = @sync_scope_local_id
  42.         OR [side].[update_scope_local_id] IS NULL)
  43.         AND [side].[CustomerType] IS NULL))
  44.         AND [side].[local_update_peer_timestamp] > @sync_min_timestamp
  45. END

Notice the filter is hardcoded as:

Code Snippet
  1. WHERE (([side].[CustomerType] = ‘Retail’)

Now, we want to be able to change the filter value dynamically. To do that, we’ll add a new parameter @CustomerType to the stored procedure and modify the filter clause appropriately. So let’s alter the stored procedure and add a parameter as follows:

Code Snippet
  1. ALTER PROCEDURE [Sync].[Customer_selectchanges]
  2.     @sync_min_timestamp BigInt,
  3.     @sync_scope_local_id Int,
  4.     @sync_scope_restore_count Int,
  5.     @CustomerType varchar(10)

Then, let’s modify the filter:

Code Snippet
  1. WHERE (([side].[CustomerType] = @CustomerType)

Now on to the next problem, how do we passed a value for the parameter CustomerType.

Since we don’t have access to the command objects to actually pass a parameter, we’ll build a custom adapter. However, this custom provider will simply call the previously provisioned stored procedures including the SelectChanges stored procedure we just changed.

Here’s the full code for the custom adapter.

Code Snippet
  1. public class SampleServerSyncProvider
  2.  {            
  3.      public DbSyncProvider ConfigureDbSyncProvider(string peerConnString, string scopeName, string customerType)
  4.      {
  5.  
  6.          DbSyncProvider sampleDbProvider = new DbSyncProvider();
  7.  
  8.          SqlConnection peerConnection = new SqlConnection(peerConnString);
  9.          sampleDbProvider.Connection = peerConnection;
  10.          sampleDbProvider.ScopeName = scopeName;
  11.  
  12.          //Create a DbSyncAdapter object for the Customer table and associate it
  13.          //with the DbSyncProvider. Following the DataAdapter style in ADO.NET,
  14.          //DbSyncAdapter is the equivalent for synchronization. The commands that
  15.          //are specified for the DbSyncAdapter object call stored procedures
  16.          //that are created in each peer database.
  17.          DbSyncAdapter adapterCustomer = new DbSyncAdapter("Sales.Customer");
  18.  
  19.  
  20.          //Specify the primary key, which Sync Framework uses
  21.          //to identify each row during synchronization.
  22.          adapterCustomer.RowIdColumns.Add("CustomerId");
  23.  
  24.  
  25.          //Specify the command to select incremental changes.
  26.          //In this command and other commands, session variables are
  27.          //used to pass information at runtime. DbSyncSession.SyncMetadataOnly
  28.          //and SyncMinTimestamp are two of the string constants that
  29.          //the DbSyncSession class exposes. You could also include
  30.          //@sync_metadata_only and @sync_min_timestamp directly in your
  31.          //queries:
  32.          //*  sync_metadata_only is used by Sync Framework as an optimization
  33.          //   in some queries.
  34.          //* The value of the sync_min_timestamp session variable is compared to
  35.          //   values in the sync_row_timestamp column in the tracking table to
  36.          //   determine which rows to select.
  37.          SqlCommand chgsCustomerCmd = new SqlCommand();
  38.          chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
  39.          chgsCustomerCmd.CommandText = "Sync.Customer_SelectChanges";
  40.          chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
  41.          chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
  42.          chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
  43.  
  44.          // Pass the filter parameter value here
  45.          chgsCustomerCmd.Parameters.Add(new SqlParameter("@CustomerType", customerType));
  46.  
  47.  
  48.          adapterCustomer.SelectIncrementalChangesCommand = chgsCustomerCmd;
  49.  
  50.          //Specify the command to insert rows.
  51.          //The sync_row_count session variable is used in this command
  52.          //and other commands to return a count of the rows affected by an operation.
  53.          //A count of 0 indicates that an operation failed.
  54.          SqlCommand insCustomerCmd = new SqlCommand();
  55.          insCustomerCmd.CommandType = CommandType.StoredProcedure;
  56.          insCustomerCmd.CommandText = "Sync.Customer_Insert";
  57.  
  58.          insCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier,0,"CustomerId");
  59.          insCustomerCmd.Parameters.Add("@P_2", SqlDbType.NVarChar,0,"CustomerName");
  60.          insCustomerCmd.Parameters.Add("@P_3", SqlDbType.NVarChar,0,"SalesPerson");
  61.          insCustomerCmd.Parameters.Add("@P_4", SqlDbType.NVarChar,0,"CustomerType");
  62.  
  63.          insCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  64.  
  65.          adapterCustomer.InsertCommand = insCustomerCmd;
  66.  
  67.  
  68.          //Specify the command to update rows.
  69.          //The value of the sync_min_timestamp session variable is compared to
  70.          //values in the sync_row_timestamp column in the tracking table to
  71.          //determine which rows to update.
  72.          SqlCommand updCustomerCmd = new SqlCommand();
  73.          updCustomerCmd.CommandType = CommandType.StoredProcedure;
  74.          updCustomerCmd.CommandText = "Sync.Customer_Update";
  75.  
  76.          updCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  77.          updCustomerCmd.Parameters.Add("@P_2", SqlDbType.NVarChar, 0, "CustomerName");
  78.          updCustomerCmd.Parameters.Add("@P_3", SqlDbType.NVarChar, 0, "SalesPerson");
  79.          updCustomerCmd.Parameters.Add("@P_4", SqlDbType.NVarChar, 0, "CustomerType");
  80.  
  81.          updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
  82.          updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  83.          updCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
  84.  
  85.          adapterCustomer.UpdateCommand = updCustomerCmd;
  86.  
  87.  
  88.          //Specify the command to delete rows.
  89.          //The value of the sync_min_timestamp session variable is compared to
  90.          //values in the sync_row_timestamp column in the tracking table to
  91.          //determine which rows to delete.
  92.          SqlCommand delCustomerCmd = new SqlCommand();
  93.          delCustomerCmd.CommandType = CommandType.StoredProcedure;
  94.          delCustomerCmd.CommandText = "Sync.Customer_Delete";
  95.  
  96.          delCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  97.  
  98.          delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
  99.          delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  100.          delCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncForceWrite, SqlDbType.Int);
  101.  
  102.          adapterCustomer.DeleteCommand = delCustomerCmd;
  103.  
  104.          //Specify the command to select any conflicting rows.
  105.          SqlCommand selRowCustomerCmd = new SqlCommand();
  106.          selRowCustomerCmd.CommandType = CommandType.StoredProcedure;
  107.          selRowCustomerCmd.CommandText = "Sync.Customer_SelectRow";
  108.  
  109.          selRowCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  110.  
  111.          selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
  112.          selRowCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
  113.  
  114.          adapterCustomer.SelectRowCommand = selRowCustomerCmd;
  115.  
  116.  
  117.          //Specify the command to insert metadata rows.
  118.          //The session variables in this command relate to columns in
  119.          //the tracking table.
  120.          SqlCommand insMetadataCustomerCmd = new SqlCommand();
  121.          insMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
  122.          insMetadataCustomerCmd.CommandText = "Sync.Customer_InsertMetadata";
  123.  
  124.          insMetadataCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  125.  
  126.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
  127.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
  128.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
  129.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
  130.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
  131.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
  132.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
  133.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
  134.          insMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  135.  
  136.          adapterCustomer.InsertMetadataCommand = insMetadataCustomerCmd;
  137.  
  138.  
  139.          //Specify the command to update metadata rows.
  140.          SqlCommand updMetadataCustomerCmd = new SqlCommand();
  141.          updMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
  142.          updMetadataCustomerCmd.CommandText = "Sync.Customer_UpdateMetadata";
  143.  
  144.          updMetadataCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  145.  
  146.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
  147.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
  148.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerKey, SqlDbType.Int);
  149.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCreatePeerTimestamp, SqlDbType.BigInt);
  150.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerKey, SqlDbType.Int);
  151.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncUpdatePeerTimestamp, SqlDbType.BigInt);
  152.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowIsTombstone, SqlDbType.Int);
  153.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
  154.          updMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  155.  
  156.          adapterCustomer.UpdateMetadataCommand = updMetadataCustomerCmd;
  157.  
  158.          //Specify the command to delete metadata rows.
  159.          SqlCommand delMetadataCustomerCmd = new SqlCommand();
  160.          delMetadataCustomerCmd.CommandType = CommandType.StoredProcedure;
  161.          delMetadataCustomerCmd.CommandText = "Sync.Customer_DeleteMetadata";
  162.  
  163.          delMetadataCustomerCmd.Parameters.Add("@P_1", SqlDbType.UniqueIdentifier, 0, "CustomerId");
  164.  
  165.          delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
  166.          delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowTimestamp, SqlDbType.BigInt);
  167.          delMetadataCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  168.  
  169.          adapterCustomer.DeleteMetadataCommand = delMetadataCustomerCmd;
  170.  
  171.  
  172.          //Add the adapter to the provider.
  173.          sampleDbProvider.SyncAdapters.Add(adapterCustomer);
  174.  
  175.  
  176.          // Configure commands that relate to the provider itself rather
  177.          // than the DbSyncAdapter object for each table:
  178.          // * SelectNewTimestampCommand: Returns the new high watermark for
  179.          //   the current synchronization session.
  180.          // * SelectScopeInfoCommand: Returns sync knowledge, cleanup knowledge,
  181.          //   and a scope version (timestamp).
  182.          // * UpdateScopeInfoCommand: Sets new values for sync knowledge and cleanup knowledge.            
  183.          // * SelectTableMaxTimestampsCommand (optional): Returns the maximum timestamp from each base table
  184.          //   or tracking table, to determine whether for each table the destination already
  185.          //   has all of the changes from the source. If a destination table has all the changes,
  186.          //   SelectIncrementalChangesCommand is not called for that table.
  187.          // There are additional commands related to metadata cleanup that are not
  188.          // included in this application.
  189.  
  190.  
  191.          //Select a new timestamp.
  192.          //During each synchronization, the new value and
  193.          //the last value from the previous synchronization
  194.          //are used: the set of changes between these upper and
  195.          //lower bounds is synchronized.
  196.          SqlCommand selectNewTimestampCommand = new SqlCommand();
  197.          string newTimestampVariable = "@" + DbSyncSession.SyncNewTimestamp;
  198.          selectNewTimestampCommand.CommandText = "SELECT " + newTimestampVariable + " = min_active_rowversion() – 1";
  199.          selectNewTimestampCommand.Parameters.Add(newTimestampVariable, SqlDbType.Timestamp);
  200.          selectNewTimestampCommand.Parameters[newTimestampVariable].Direction = ParameterDirection.Output;
  201.  
  202.          sampleDbProvider.SelectNewTimestampCommand = selectNewTimestampCommand;
  203.  
  204.          //Specify the command to select local replica metadata.
  205.          SqlCommand selReplicaInfoCmd = new SqlCommand();
  206.          selReplicaInfoCmd.CommandType = CommandType.Text;
  207.          selReplicaInfoCmd.CommandText = "SELECT " +
  208.                                          "scope_id, " +
  209.                                          "scope_local_id, " +
  210.                                          "scope_sync_knowledge, " +
  211.                                          "scope_tombstone_cleanup_knowledge, " +
  212.                                          "scope_timestamp " +
  213.                                          "FROM Sync.Scope_Info " +
  214.                                          "WHERE scope_name = @" + DbSyncSession.SyncScopeName;
  215.          selReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
  216.  
  217.          sampleDbProvider.SelectScopeInfoCommand = selReplicaInfoCmd;
  218.  
  219.  
  220.          //Specify the command to update local replica metadata.
  221.          SqlCommand updReplicaInfoCmd = new SqlCommand();
  222.          updReplicaInfoCmd.CommandType = CommandType.Text;
  223.          updReplicaInfoCmd.CommandText = "UPDATE  Sync.Scope_Info SET " +
  224.                                          "scope_sync_knowledge = @" + DbSyncSession.SyncScopeKnowledge + ", " +
  225.                                          "scope_id = @" + DbSyncSession.SyncScopeId + ", " +
  226.                                          "scope_tombstone_cleanup_knowledge = @" + DbSyncSession.SyncScopeCleanupKnowledge + " " +
  227.                                          "WHERE scope_name = @" + DbSyncSession.SyncScopeName + " AND " +
  228.                                          " ( @" + DbSyncSession.SyncCheckConcurrency + " = 0 OR scope_timestamp = @" + DbSyncSession.SyncScopeTimestamp + "); " +
  229.                                          "SET @" + DbSyncSession.SyncRowCount + " = @@rowcount";
  230.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeKnowledge, SqlDbType.VarBinary, 10000);
  231.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeCleanupKnowledge, SqlDbType.VarBinary, 10000);
  232.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeName, SqlDbType.NVarChar, 100);
  233.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncCheckConcurrency, SqlDbType.Int);
  234.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeId, SqlDbType.UniqueIdentifier);
  235.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncScopeTimestamp, SqlDbType.BigInt);
  236.          updReplicaInfoCmd.Parameters.Add("@" + DbSyncSession.SyncRowCount, SqlDbType.Int).Direction = ParameterDirection.Output;
  237.  
  238.          sampleDbProvider.UpdateScopeInfoCommand = updReplicaInfoCmd;
  239.  
  240.  
  241.          //Return the maximum timestamp from the Customer_Tracking table.
  242.          //If more tables are synchronized, the query should UNION
  243.          //all of the results. The table name is not schema-qualified
  244.          //in this case because the name was not schema qualified in the
  245.          //DbSyncAdapter constructor.
  246.          SqlCommand selTableMaxTsCmd = new SqlCommand();
  247.          selTableMaxTsCmd.CommandType = CommandType.Text;
  248.          selTableMaxTsCmd.CommandText = "SELECT ‘Customer’ AS table_name, " +
  249.                                         "MAX(local_update_peer_timestamp) AS max_timestamp " +
  250.                                         "FROM Sync.Customer_Tracking";
  251.          sampleDbProvider.SelectTableMaxTimestampsCommand = selTableMaxTsCmd;
  252.  
  253.          return sampleDbProvider;
  254.      }
  255.  }

In the code above, we manually mapped the individual adapter commands to the corresponding stored procedures in our database. (Similar to the way you build adapters in Offline Scenario)

Here’s the snippet where we actually pass the filter value to the stored procedure:

Code Snippet
  1. SqlCommand chgsCustomerCmd = new SqlCommand();
  2.                 chgsCustomerCmd.CommandType = CommandType.StoredProcedure;
  3.                 chgsCustomerCmd.CommandText = "Sync.Customer_SelectChanges";
  4.                 chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeRestoreCount, SqlDbType.Int);
  5.                 chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncMinTimestamp, SqlDbType.BigInt);
  6.                 chgsCustomerCmd.Parameters.Add("@" + DbSyncSession.SyncScopeLocalId, SqlDbType.Int);
  7.  
  8.                 // Pass the filter parameter value here
  9.                 chgsCustomerCmd.Parameters.Add(new SqlParameter("@CustomerType", customerType));

Our ConfigureDBSyncProvider takes 3 parameters: a connection string, a scope name and the customer type filter value.

Here’s how we call the sync:

Code Snippet
  1. SampleServerSyncProvider sampleServerSyncProvider = new SampleServerSyncProvider();
  2. SyncOrchestrator sampleSyncAgent;
  3. SyncOperationStatistics syncStatistics;
  4.  
  5. // Use custom server provider
  6. sampleSyncAgent = new SampleSyncAgent(
  7.                      new SqlSyncProvider("filtered_customer", clientSqlConn, null, "Sync"),
  8.                      sampleServerSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_SqlSync_Server, "filtered_customer", "Retail")
  9.                                       );
  10. syncStatistics = sampleSyncAgent.Synchronize();

To synchronize another client using another filter value:

Code Snippet
  1. SampleServerSyncProvider sampleServerSyncProvider = new SampleServerSyncProvider();
  2. SyncOrchestrator sampleSyncAgent;
  3. SyncOperationStatistics syncStatistics;
  4.  
  5. sampleSyncAgent = new SampleSyncAgent(
  6.                         new SqlSyncProvider("filtered_customer", secondClientSqlConn,null,"Sync"),
  7.                         sampleServerSyncProvider.ConfigureDbSyncProvider(Utility.ConnStr_SqlSync_Server, "filtered_customer", "Wholesale")
  8.                                     );
  9. syncStatistics = sampleSyncAgent.Synchronize();

You may download the full project from here: SyncFilterDemo.zip

You just have to replace the connection strings, table names and stored procedure names with your respective test databases. I hope this helps. I’ve done a couple of testing and this seems to work. But I would love to hear your feedback if it works or if it doesn’t work in your scenario.

Advertisements

17 comments

  1. June, This is exactly what I am after. I have 6 scopes with somewhere around 1,000 clients targeted meaning 6,000+ stored procedures :-(There are some things you have not considered here: You have used the standard SqlSyncProvider for the client side. Your client provisioning will include whatever fixed filter you had in the server database, or if done after you modified the one at the server, then the parameterized one. Either way, SqlSyncProvider is no good for that (it will work with one client if it is the fixed filter or none if the conditional one). Therefore, the client side SP would need to change. If the data at each client is a separate subset of the data (eg you either get retail or wholesale but never both, or in my case filtering on a unique client id), there is a choice of two solutions: use the same modified paramterized SP and use your sampleServerSyncProvider or, you could still use SqlSyncProvider if you have a modified SP that does not include the fixed filter conditional. I like the latter better as you have an out-of-the-box provider and you have a no filter when you don\’t need it. On second thoughts, I like the former (the parametered SP at the client too) since the accidental inclusion of data outside of the expected set (eg whole data at a retail client), you don\’t get that data propogated to the server.One more thing: You mention in the main code snippet that are are other commands related to metadata that are not included. Is that because they are not needed, or because you didn\’t get to it? If it is the latter I\’d appreciate it if you could find the time to do that.Finally, I assume you add an adapter to the provider for each table in the scope and that there would be a customized select changes SP for each table.Hopefully this week I can try your code in our application.Thanks again,Steve

  2. June,I noticed that you have SP\’s named, for example in the InsertCommand, "Sync.Customer_ApplyInsert". The standard SqlSyncScopeProvisioning would have created "Sync.Customer_insert" (without the "apply"). Is there any particular reason that you did this?

  3. Hi Steve, Thanks for the feedback. I\’ll update the post and sample to reflect the correct stored procedures. I didnt pay much attention on those part since i just wanted to demonstrate the filtering part on the server side.As for you second comment regarding the sample using the default client sync provider. I\’m not sure if you\’ll have the same findings as mine, but when i try to provision a client using either by retrieving the scope description from the server or using populate from scope, the filters are not really copied to the client. I looked at the assemblies and it seems its not retrieving the filters either. I could have provided a filter also for the client side but i didnt show it on the sample because am assuming the client would take care of making sure the data it accepts via the application should be within its scope (filter). But you make a good point of creating the filter on the client side as well to prevent accidental upload of out of scope data that made it thru the client.And yes, you have to add Adapters for each table in your scope ( quiet tedious task if you have multiple tables. A good to minimize the work would be to create all filtered tables in one scope and all other non-filtered in a separate scope so you don\’t have to build and manually add the adapters for other tables not using a filter.Btw, some of the comments in the code (the one you noticed about metadata) are the comments from the sample code\’s that I didnt bother to clean up.Cheers,JuneT

  4. June,Oh, one more thing if found too:In your code examples here, you hava a size for DbSyncSession.SyncScopeKnowledge and DbSyncSession.SyncScopeCleanupKnowledge of 10,000 each (since this is what the MSFT examples show). My opinion is that, if you use SqlSyncScopeProvisioning, that this is not correct. They are not varbinary(10000), they are varbinary(max). Therefore, they should be specified with a length of 0 in the Parameter.Add() calls.Here\’s the definition of the scope_info file:CREATE TABLE [dbo].[scope_info]( [scope_local_id] [int] IDENTITY(1,1) NOT NULL, [scope_id] [uniqueidentifier] NOT NULL, [scope_name] [nvarchar](100) NOT NULL, [scope_sync_knowledge] [varbinary](max) NULL, [scope_tombstone_cleanup_knowledge] [varbinary](max) NULL, [scope_timestamp] [timestamp] NULL, [scope_config_id] [uniqueidentifier] NULL, [scope_restore_count] [int] NOT NULL, CONSTRAINT [PK_scope_info] PRIMARY KEY CLUSTERED ( [scope_name] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Steve

  5. Thanks Steve. I didnt notice the varbinary part as these codes I grabbed directly from the documentation. Great work on the codes for using filtering using adapters.junet

  6. I have the same problem, but I want to execute the solution slightly differently. In my scenario I don\’t want to pass a parameter because the filter state is contained in a database view on the server. Based on your approach above I was assuming that I could simply add a filter parameter to the provisioning code and then alter the _selectchanges stored procedure to match that filter parameter against the view, and that I would not need to write a custom provider at all. The database view is itself a view on a table that is synchronized from the client, so I am effectively "passing a parameter". That table is part of a different scope which is always synchronized prior to the filtered scope. In part I want to take this approach because I need to filter a large number of tables and the approach outlined above seems like a lot of code/maintenance and I would rather stay as close to the provided sync implementation as possible,The approach does not appear to work however: – the first time I run the sync the filtered table is filtered out, which is what I expect – when I change the state of the view so that the filter data should come back, it does not because the sync_min_timestamp value passed to the sproc is greater than the highest value in that tableThe sync_min_timestamp that is passed to the _selectchanges sprocs is the same for all _selectchanges sprocs for that scope; your custom adapter implementation above seems to be pulling a SelectTableMaxTimestampsCommand that is local to that table. The questions I have are:1) is my approach valid?2) if it is valid, and the singular issue is a table local timestamp, is there a way to make that happen with the default implementation (without a custom adapter)thank youNickI notice that your code above in the custom adapter s

  7. JuneI\’ve been looking into this further, and I don\’t see how this approach can work practically, or perhaps I mistake the intention of the filtering approach. The problem centers around the sync_min_timestamp which is the taken as the max value of all timestamps that have been synchronized (by default and in your example above). What this means is that if you synchronize when some data row is filtered out, and then you change the filter parameter such that the data row should be included, it will only synchronize if the timestamp for that row in the side table is greater than the sync_min_timestamp, which will only occur if the value of that row has been updated since the last sync. It works in the example that you have above because you are only dealing with one table and consequently sync_min_timestamp is tied to that specific table. When you introduce a second table, you will start running into problems. The only way I can see to address this would be to have a separate sync_min_timestamp per table, which does not appear to be supported by the sync framework.

  8. nick,i dont actually follow your question regarding sync_min_timestamp or are you mistaking the max_timestamp query above for the sync_min_timestamp?the max_timestamp is completely different from the sync_min_timestamp, the latter not being based on a query but rather thru an inspection of the replica\’s knowledge. in fact, max_timestamp is optional. it\’s just an optimization option so as not to do the SelectChanges. And also, the max_timestamp checking is per table.the sample code is to demonstrate having a single scope definition cater to different synching clients with different filtering requirements.client A can use a filter of X, client B can use a Filter of Y, etc…if client A suddenly decides it wants a Filter of Y now, an issue will actually be on how to clean up the rows that were previously downloaded by Filter X.you\’re scenario below however holds thru. If assuming your Filter X gives you a minimum time stamp of 100. Then you suddenly change to Filter Y, however the rows or some of the rows satisying Filter Y has timestamps less than 100, then they wont be selected because when it asks what rows have i previously sent to the client, the client will reply back saying it has rows that has timestamp 100 and above. Sync would then simply determine if those previously sent rows has changed or if there were new rows added. It doesnt care about those rows whose timestamp is lower than 100. (We\’re talking of Incremental syncs here).

  9. Nick,just a clarification on your view question:Table is in another sync scope? this one has filter?View is in another sync scope? the view itself has a filter on the table above? then in your sync scope youre passing another filter? where is the sync_min_timestamp greater than the table\’s max timestamp? in client or in server?

  10. JuneApologies for any confusion in the semantics of my previous post – the key point seems to be that dynamic filters will only work where the data that would be included in the filter criteria has changed since the last synchronization regardless of whether you take the approach you outline above or my variation on your approach.This is not how dynamic filters work in Sync Framework 1.0/2.0 in a client/server situation, and I don\’t see it as a practical solution to the question of dynamic filters in a collaboration scenario. In response to your last response:- the view is based on a table in another scope which is always synchronized prior to the scope which has the filters applied- the sync_min_timestamp is greater than the side tables timestamp on the serverThanks for the post – it was very useful, but a present I dont feel like I have a practical solution to the question of dynamic filtering, which is a shame because I really need it.Thanks for taking the time to respond,Nick

  11. June, I need dynamic filtering accross WCF using peer providers. Do you have any clues as to how to get a dynamic runtime parameter passed over the wire? I know in 1.0 hub->spoke style syncs (offline scenarios) a SyncSession is available which you can add dynamic parameters to. But the 2.0 peer providers use DbSyncSession internally and it isn\’t surfaced except for DbSelectingChangesEventArgs.Session which is only available on the server side (in my case b/c the sync is download only) so no way for a client to add to the DbSyncSession.SyncParameters??

  12. Brendon · · Reply

    Hi June – hope you see this post as this entry seems to have gone a bit quiet. Have been working on using your/Steve\’s methods along with the n-tier peer to peer example using WCF. Everything works great so far and the custom parameter works for selectchanges. I\’m trying to also add custom parameters to the insert, update and delete (and selectrow as well if this is necessary) stored procedures and provider commands on the DbSyncProvider on the server. However when following the same approach as for select changes, I receive the following exception: "Cannot find a value to set for command parameter \’@ClientID\’. Ensure that the schemas of the tables being synchronized are consistent, and that the configuration for this scope is correct.". Any idea what I could be missing? Basically I\’m trying to validate the changes on the server so that each client is prevented from uploading/changing data that is not part of their dynamic filter (even if they do this directly in their own database). If this is attempted, I would ideally like these to show up as conflicts – at the moment I\’m simply not performing the changes in the stored procs on the server – not sure if this will achieve the required result? I\’m still using the regular SqlSyncProvider on the client (at least for now) – the server should simply not accept any invalid data received. I\’ve done something similar with Merge Replication and data partitioning in the past – but there are a number of reasons Sync Framework is preferable in this case (flexibility – especially with web endpoints, less complex generated database objects – some of the triggers generated on the publisher for replication are pretty complex and harder to customise if needed and more transparent for development – merge replication was pretty much black-box from .NET code by comparison). Thanks!

  13. Todd, you can modify one of the WCF service operation contracts to pass a parameter on the server side and inject the parameter similar to my example above.

  14. Brendon,Is @ClientID a parameter you defined? can you check that you passed the parameter and that the parameter is in the stored proc.as for preventing clients from uploading rows that are out of their respective scopes, you can do it several ways: you can detect it during change selection in the client so you dont incur the overhead of uploading them. do this either in the selectchanges stored procedure or by inspecting the changes dataset in code (ChangesSelected event) or on the server side you can do it before applying changes by inspecting the change dataset (ApplyingChanges event). you may also do it in the insert and update stored procedures either by raising an error or by making sure the rowcount is equal to zero.

  15. Very nice and informative article. Thanks for the quality content and I hope you update your blog frequently as I`m interested in this topic. I`ve already bookmarked this article. Thanx!

  16. HRPrathibha · · Reply

    Hi,
    I am looking same for the project but it is throwing error that “Failed to convert parameter value from a Int32 to a guid” and i couldn’t able to track the error … can you pls help me in this regard
    Thanx
    Prabha

    1. kindly post it in the sync framework forum. if you can share the code snippet and the stack trace of the error, the better.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: