Microsoft Sync Framework: Synchronize with Parameter Based Filters

Let’s imagine a scenario where you want to sync data based on a filter. For example, your server database has an “EMPLOYEE” master table and it has all the employee records of all your branches. Now you have two branch office databases for your two branches, and you want to download relevant employee data to each of these branch databases.

 

This is my “EMPLOYEE” table in the Server database.

Employee table in the Server

The requirement is to download all employee information to relevant branch databases and that is all employees who have branch id as “B0001” to New York database and all employees who have branch id as “B0002” to Boston database.

 

Now let’s see how to implement such a synchronization using Microsoft Sync Framework. Here a filter is used to restrict the data that is being synchronized between each branch and the server. With the release of Sync Framework 2.1 Microsoft has introduced dynamic parameter based filtering support and we are going to use dynamic parameter based filtering for this.

string filterTemplate = "filter_template";
DbSyncScopeDescription scopeDesc = new  DbSyncScopeDescription(filterTemplate);
scopeDesc.UserComment = "Template for filtering based on branch id.";
DbSyncTableDescription EMPLOYEE = SqlSyncDescriptionBuilder.GetDescriptionForTable("EMPLOYEE", serverConn);
scopeDesc.Tables.Add(EMPLOYEE);
 
//creating a provisioning template
SqlSyncScopeProvisioning serverProvisionTemplate = new  SqlSyncScopeProvisioning(serverConn, scopeDesc, SqlSyncScopeProvisioningType.Template);
serverProvisionTemplate.Tables["EMPLOYEE"].AddFilterColumn("BRANCH_ID");
serverProvisionTemplate.Tables["EMPLOYEE"].FilterClause = "[side].[BRANCH_ID] = @branchId";
SqlParameter param = new  SqlParameter("@branchId", SqlDbType.VarChar, 5);
serverProvisionTemplate.Tables["EMPLOYEE"].FilterParameters.Add(param);
if (!serverProvisionTemplate.TemplateExists(filterTemplate))
{
    serverProvisionTemplate.Apply();
}

First we are creating a provisioning template. Creating a provisioning template is same as creating a provisioning scope, except the only thing is the template cannot be used for synchronization. In this provisioning template on the server, we are mentioning “EMPLOYEE” table has filter column which is “BRANCH_ID” and the filter clause is where “BRANCH_ID” is equal to parameterized “branchid”.

 

Once the template is created you can see two new records added to “scope_config” and “scope_templates” tables in Server database. In the row created in “scope_config” table, you can see config data of the template which is an XML. Here you can see things we mentioned in the code such as parameter name, filter clause, filter column etc. in the XML.

<SqlSyncProviderScopeConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IsTemplate="true">
  <Adapter Name="[EMPLOYEE]" GlobalName="[EMPLOYEE]" TrackingTable="[EMPLOYEE_tracking]" SelChngProc="[EMPLOYEE_selectchanges]" SelRowProc="[EMPLOYEE_selectrow]" InsProc="[EMPLOYEE_insert]" UpdProc="[EMPLOYEE_update]" DelProc="[EMPLOYEE_delete]" InsMetaProc="[EMPLOYEE_insertmetadata]" UpdMetaProc="[EMPLOYEE_updatemetadata]" DelMetaProc="[EMPLOYEE_deletemetadata]" BulkTableType="[EMPLOYEE_BulkType]" BulkInsProc="[EMPLOYEE_bulkinsert]" BulkUpdProc="[EMPLOYEE_bulkupdate]" BulkDelProc="[EMPLOYEE_bulkdelete]" InsTrig="[EMPLOYEE_insert_trigger]" UpdTrig="[EMPLOYEE_update_trigger]" DelTrig="[EMPLOYEE_delete_trigger]">
    <Col name="EMPLOYEE_ID" type="int" param="@P_1" pk="true" />
    <Col name="FIRST_NAME" type="varchar" size="50" null="true" param="@P_2" />
    <Col name="LAST_NAME" type="varchar" size="50" null="true" param="@P_3" />
    <Col name="BRANCH_ID" type="varchar" size="5" null="true" param="@P_4" />
    <FilterParam name="@branchId" />
    <FilterClause>[side].[BRANCH_ID] = @branchId</FilterClause>
    <FilterCol>BRANCH_ID</FilterCol>
  </Adapter>
</SqlSyncProviderScopeConfiguration>

Next, based on this template, we are provisioning the server. We are supplying a parameter value and some comment during the provisioning.

string branchId = "B0001";
string branchFilteredScope = string.Format("{0}_Scope", branchId);
SqlSyncScopeProvisioning serverProvision = new  SqlSyncScopeProvisioning(serverConn, scopeDesc);
serverProvision.PopulateFromTemplate(branchFilteredScope, filterTemplate);
serverProvision.Tables["EMPLOYEE"].FilterParameters["@branchId"].Value = branchId;
serverProvision.UserComment = string.Format("Only for {0} branch.", branchId);
if (!serverProvision.ScopeExists(branchFilteredScope))
{
   serverProvision.Apply();
}

When the provisioning is completed, a scope is created on the server. Now when this step is completed if you examine the tables in “scope_info” and “scope_parameters”, again you can see two new rows created. “scope_info” will contain a row including the details of the particular provision we have done now and “scope_parameters” will contain a row including the details of parameters for the above provision. Now we are going to provision the branch database. It’s basic provision. We are getting the relevant scope from the server and applying it on the client.

DbSyncScopeDescription serverScopeDescForBranch = SqlSyncDescriptionBuilder.GetDescriptionForScope(branchFilteredScope, null, serverConn);
SqlSyncScopeProvisioning branchProvision = new  SqlSyncScopeProvisioning(branchConn, serverScopeDescForBranch);
if (!branchProvision.ScopeExists(branchFilteredScope))
{
    branchProvision.Apply();
}

We have done the difficult part and the next step is to do the synchronization. Once the synchronization is completed, we can see the following result on two branch databases.

Result

Here is a full sample with database backups, so you can play around in any way you want. Appreciate your feedback. Synchronize with Parameter Based Filters Demo Happy Coding.