Synchronize with Parameter Based Filters - Microsoft Sync Framework

Let’s imagine a scenario where you want to sync data based on a filter. For an 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

My requirement is download all employee information to relevant branch databases and that is all employees who has branch id as “B0001” to New York database and all employees who has 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 I am 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();
}

Here first I am doing is 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 my provisioning template on the server I am mentioning my “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 I am provisioning the server. I am supplying a parameter value and some comment and I am doing 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 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 I am going to provision the branch database. It’s basic provision; I am 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, I can see the following result on two branch databases.

Result

I am uploading a full sample with database backups, so you can play around in anyway you want. Appreciate your feedback.

Happy Coding.

Regards,
Jaliya