dotConnect for DB2 Documentation
In This Topic
    Sync Framework Tutorial
    In This Topic

    Microsoft Sync Framework is a data synchronization platform, allowing data synchronization between any applications, services or devices regardless of the network used and data source type. To allow data source agnostic synchronization, Sync Framework uses synchronization providers for connecting to each of the data sources. Sync Framework database synchronization providers, similar to ADO.NET providers, allow other Sync Framework components to work without the need to concern on the underlying database implementation.

    Sync Framework Support Overview

    Sync Framework architecture allows clients both to synchronize data via the central server or to exchange data directly. The following main classes are used for data synchronization:

    To include database data to synchronization, first you need to provision the database. Provisioning means the creation of Sync Framework auxiliary tables, stored procedures, functions, and triggers for tracking data changes in the specified synchronization scope - a table or a group of tables to synchronize data from. Both databases being synchronized must be provisioned in order to synchronise data.

    DB2SyncScopeProvisioning is the key class for performing database provisioning. It allows provisioning a synchronization scope. We will describe the provisioning and synchronization scopes in more details in our tutorial below.

    After the provisioning, the databases can be synchronised. When synchronizing databases, you may retrieve synchronization operation statistics. It includes the time synchronization took, the number of processed changes and failed changes.

    In case there is no need to synchronise a database or any specific scope any more, the database (or scope) can be deprovisioned. This means deleting of the unneeded Sync Framework database objects and/or data. User tables and data are retained. dotConnect for DB2 offers the DB2SyncScopeDeprovisioning class for scope deprovisioning.

    The parent class of the DB2SyncProvider class is the descendant of the standard Sync Framework DbSyncProvider class. Other synchronization classes of dotConnect for DB2 are not inherited from the standard Sync Framework classes, but provide the same interface.

    Sample Application

    For this tutorial you need Visual Studio 2008 or higher, and Sync Framework 2.1.

    In our tutorial we will synchronize two databases with identical structure. Let's call them the development database and the production database. We will use a simple database of one table "Product" for our tutorial. Here is its script: 

    CREATE table "Products"
  (
  "Id" INT NOT NULL PRIMARY KEY,
  "Name" VARCHAR(100),
  "CategoryId" INT,
  "IsAvailable" SmallInt
  );

  
INSERT INTO "Products"
  VALUES (1, 'HP 2000-2b19WM ', 1, 1
  ), (2, 'Dell Adamo XPS', 1, 0
  ), (3, 'DELL LAPTOP I14Z', 1, 1
  ), (4, 'HP DC5750 Desktop Computer Dual Core/ ', 2, 1
  ), (5, 'Apple iMac All In One', 2, 0
  ), (6, 'Sharp - AQUOS - ', 3, 1
  ), (7, 'Samsung ST76', 4, 1
  ), (8, 'Canon PowerShot', 4, 1
  ), (9, 'Olympus OM-D E-M5', 4, 1
  );

    If you want to configure data synchronization for a new database, and its tables are not created yet, they can be automatically generated during the provisioning. However, in our tutorial we will create them manually.

    Create a new console application in Visual Studio. It could be any other project type as well, but for simplicity's sake we'll use console project throughout the tutorial.

    Add the references to the following assemblies to your project:

    Database Provisioning

    The first step of the synchronization is database provisioning. We will start from the development database and demonstrate different provisioning scenarios. Provisioning includes defining the synchronization scope and creating the necessary database objects.

    The synchronization scope is one or more tables (with the specified columns to synchronize) that will be synchronized as a whole. The synchronization scope can include all columns in a table or just a subset of them. It can include all rows or only the rows that match some filter condition. You also can create a template for filters (a condition including a variable), and then create multiple synchronization scopes using filters based on this template.

    When creating a synchronization scope, you must create descriptions for the tables of this scope. The synchronization scope tables can either already exist in the database, or they be created during the database provisioning based on their descriptions.

    Creating Synchronization Scope without Filter

    You can create table descriptions in two ways. The first way is to create the description manually, and the second is to use the DB2SyncDescriptionBuilder class to generate the description based on an existing database table. Since the tables are already created, we will use the second option.

    Add the following code to the Main method: 

    
      // Specify the connection to development database. Replace the sample connection string with your one.
      DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");    

      // Define the scope, named ProductsScope
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope");

      // Retrive the description for the Products table from the database 
      DbSyncTableDescription tableDesc = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);

      // Add the table description to the scope definition
      scopeDesc.Tables.Add(tableDesc);

      // Create the scope
      DB2SyncScopeProvisioning devProvision = new DB2SyncScopeProvisioning(devConnection, scopeDesc);

      // Start the provision
      devProvision.Apply();


    
      ' Specify the connection to development database. Replace the sample connection string with your one.
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      ' Define the scope, named ProductsScope
      Dim scopeDesc As New DbSyncScopeDescription("ProductsScope")

      ' Retrive the description for the Products table from the database 
      Dim tableDesc As DbSyncTableDescription = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)

      ' Add the table description to the scope definition
      scopeDesc.Tables.Add(tableDesc)

      ' Create the scope
      Dim devProvision As New DB2SyncScopeProvisioning(devConnection, scopeDesc)

      ' Start the provision
      devProvision.Apply()

    After executing this code, Sync Framework creates the following database objects in the database:

    Creating Synchronization Scope with Filter

    Now we will demonstrate creating synchronization scope with a filter. The filter is just a SQL WHERE condition that allows synchronizing not all the data of a table, but only the rows that meets certain condition. This filter condition is added to the selectchanges procedure that gets the changed data.

    The following code creates the synchronization scope with the filter.

    
      //Specify connection to dev database
      DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

      DbSyncScopeDescription devScope = new DbSyncScopeDescription("DevCategoryScope");
      DbSyncTableDescription tableDesc = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);
      devScope.Tables.Add(tableDesc);

      SyncScopeProvisioning devProvision = new DB2SyncScopeProvisioning(devConnection, devScope);

      //Specify filter clause
      devProvision.Tables["Products"].AddFilterColumn("CategoryId");

      devProvision.Tables["Products"].FilterClause = "t.CategoryId = 1";

      //Skip create Sync Framework objects because we have already created them on the previous step
      devProvision.SetCreateTableDefault(DbSyncCreationOption.Skip);
      //Create new selectchanges procedure for our scope
      devProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);
          
      devProvision.Apply();


    
      'Specify connection to dev database
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      Dim devScope As New DbSyncScopeDescription("DevCategoryScope")
      Dim tableDesc As DbSyncTableDescription = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)
      devScope.Tables.Add(tableDesc)

      Dim devProvision As SyncScopeProvisioning = New DB2SyncScopeProvisioning(devConnection, devScope)

      'Specify filter clause
      devProvision.Tables("Products").AddFilterColumn("CategoryId")

      devProvision.Tables("Products").FilterClause = "t.CategoryId = 1"

      'Skip create Sync Framework objects because we have already created them on the previous step
      devProvision.SetCreateTableDefault(DbSyncCreationOption.Skip)
      'Create new selectchanges procedure for our scope
      devProvision.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create)

      devProvision.Apply()

    The AddFilterColumn call specifies the column from the provisioned table, that will be used in the filter and must be added to the tracking table. FilterClause specifies the condition. Note that we use the alias t when specifying the condition. This alias refers to the tracking table.

    After this code is executed, the specified filter column is added to the products_tracking tracking table. An updated products_insert trigger will set values to this column. Sync Framework also creates the stored procedure for selecting changes with the specified filter applied.

    Creating Synchronization Scope with Filter Based on Template

    Creating a filter template is similar to creating a synchronization scope with a filter. The difference is that the filter condition uses a parameter instead of value. You can then create multiple synchronization scopes based on this template by supplying a value that will substitute this parameter in the filter condition. The template itself cannot be used for synchronization.

    For example, in our tutorial we will create an IsAvailableTemplate template with the condition "t.Available = f_Available" where f_Available is a parameter of a boolean type. We will create two synchronization scopes by providing values for this parameter. They will be the AvailableScope scope with f_Available = true and NotAvailableScope with f_Available = false.

    Here is the code for creating the template:

          DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

      // Create a template named "IsAvaibleTemplate"
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("IsAvaibleTemplate");

      // Definition for tables.
      DbSyncTableDescription productsDescription =
          DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection);
      scopeDesc.Tables.Add(productsDescription);

      SyncScopeProvisioning templateProvisioning = new DB2SyncScopeProvisioning(devConnection, scopeDesc, SyncScopeProvisioningType.Template);

      templateProvisioning.Tables[0].AddFilterColumn("IsAvailable");
      templateProvisioning.Tables[0].FilterClause = "t.\"IsAvailable\" = f_available";
      templateProvisioning.Tables[0].FilterParameters.Add(new DB2Parameter("f_available", DB2Type.Boolean));

      templateProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create);      

      templateProvisioning.Apply();
    
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      ' Create a template named "IsAvaibleTemplate"
      Dim scopeDesc As New DbSyncScopeDescription("IsAvaibleTemplate")

      ' Definition for tables.
      Dim productsDescription As DbSyncTableDescription = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", devConnection)
      scopeDesc.Tables.Add(productsDescription)

      Dim templateProvisioning As SyncScopeProvisioning = New DB2SyncScopeProvisioning(devConnection, scopeDesc, SyncScopeProvisioningType.Template)

      templateProvisioning.Tables(0).AddFilterColumn("IsAvailable")
      templateProvisioning.Tables(0).FilterClause = "t.""IsAvailable"" = f_available"
      templateProvisioning.Tables(0).FilterParameters.Add(New DB2Parameter("f_available", DB2Type.[Boolean]))

      templateProvisioning.SetCreateProceduresForAdditionalScopeDefault(DbSyncCreationOption.Create)

      templateProvisioning.Apply()

    After executing this code, the new selectchanges procedure will contain the parameter in the filtering condition.

    And now let's create synchronization scopes based on the template. To create such scope, you need to call the PopulateFromTemplate method and pass the new scope name, the name of the template, and the value of the filter parameter to it. 

           DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

      // Create a synchronization scope
       DB2SyncScopeProvisioning devProvAvailable = new  DB2SyncScopeProvisioning(devConnection);

      // Populate the scope description from template
      devProvAvailable.PopulateFromTemplate("AvaibleScope", "IsAvaibleTemplate");

      // Set value to the parameter
      devProvAvailable.Tables["Products"].FilterParameters["f_available"].Value = true;

      // Set description for template.
      devProvAvailable.UserComment = "Scope for available products";
     
      devProvAvailable.Apply();

    
      Dim devConnection As New  DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      ' Create a synchronization scope
      Dim devProvAvailable As New  DB2SyncScopeProvisioning(devConnection)

      ' Populate the scope description from template
      devProvAvailable.PopulateFromTemplate("AvaibleScope", "IsAvaibleTemplate")

      ' Set value to the parameter
      devProvAvailable.Tables("Products").FilterParameters("f_available").Value = True

      ' Set description for template.
      devProvAvailable.UserComment = "Scope for available products"

      devProvAvailable.Apply()

    The code for creating the NotAvaibleScope is almost the same, just change the scope name and the filter parameter value.

    Creating Synchronization Scope with Subset of Columns of Existing Table

    Here is an example of creating synchronization scope for synchronizing data in only a subset of table columns.

          DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");
      
      // Define scope with name ProductsScope"
      DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScopeWithColumnSpec");

      // Specify the list of columns of the Products Table to include
      Collection<string> columnsToInclude = new Collection<string>();
      columnsToInclude.Add("Id");
      columnsToInclude.Add("Name");
      columnsToInclude.Add("CategoryId");
      columnsToInclude.Add("Available");

      // Retrive description for Products with specified columns from database 
      DbSyncTableDescription tableDesc =
          DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", columnsToInclude, devConnection);

      // add the table description to the sync scope definition
      scopeDesc.Tables.Add(tableDesc);

      // create a development scope provisioning object based on the OrdersScope
      DB2SyncScopeProvisioning devProvision = new DB2SyncScopeProvisioning(devConnection, scopeDesc);

      // start the provision
      devProvision.Apply();

    
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      ' Define scope with name ProductsScope"
      Dim scopeDesc As New DbSyncScopeDescription("ProductsScopeWithColumnSpec")

      ' Specify the list of columns of the Products table to include
      Dim columnsToInclude As New Collection(Of String)()
      columnsToInclude.Add("Id")
      columnsToInclude.Add("Name")
      columnsToInclude.Add("CategoryId")
      columnsToInclude.Add("Available")

      ' Retrive description for Products with specified columns from database 
      Dim tableDesc As DbSyncTableDescription = DB2SyncDescriptionBuilder.GetDescriptionForTable("Products", columnsToInclude, devConnection)

      ' add the table description to the sync scope definition
      scopeDesc.Tables.Add(tableDesc)

      ' create a development scope provisioning object based on the OrdersScope
      Dim devProvision As New DB2SyncScopeProvisioning(devConnection, scopeDesc)

      ' start the provision
      devProvision.Apply()

    Synchronization

    To synchronize databases you need first to provision the Production database, and then to actually synchronize the databases.

    Provisioning Production Database

    When provisioning the production database, the DbSyncScopeDescription class is used to specify the synchronization scope name and tables to synchronize. The latter can be either specified explicitly or retrieved from the already provisioned development database. 

         DB2Connection productionConnection = new DB2Connection(@"user id=db2admin;server=db2prod:50000;database=PRODUCTIONDB");
     DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

     //Get definition for ProductsScope from dev
     DbSyncScopeDescription scopeDescription = DB2SyncDescriptionBuilder.GetDescriptionForScope("DevCategoryScope", devConnection);
     
     //Provise production using this definition
     SyncScopeProvisioning productionProvisioning = new DB2SyncScopeProvisioning(productionConnection, scopeDescription);

      productionProvisioning.Apply();

    
     Dim productionConnection As New DB2Connection("user id=db2admin;server=db2prod:50000;database=PRODUCTIONDB")
     Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

     'Get definition for ProductsScope from dev
     Dim scopeDescription As DbSyncScopeDescription = DB2SyncDescriptionBuilder.GetDescriptionForScope("DevCategoryScope", devConnection)

     'Provise production using this definition
     Dim productionProvisioning As SyncScopeProvisioning = New DB2SyncScopeProvisioning(productionConnection, scopeDescription)

     productionProvisioning.Apply()

    Synchronization

    Synchronization is performed via the SyncOrchestrator class. You need to assign the synchronization provider instances to its RemoteProvider and LocalProvider properties and then run its Synchronize method. 

          DB2Connection productionConnection = new  DB2Connection(@"user id=db2admin;server=db2prod:50000;database=PRODUCTIONDB");
      DB2Connection devConnection = new  DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

      SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

      //Specify source database
      syncOrchestrator.RemoteProvider = new DB2SyncProvider("DevCategoryScope", devConnection, null, null);
      
      //Specify production database
      syncOrchestrator.LocalProvider = new DB2SyncProvider("DevCategoryScope", productionConnection, null, null);

      syncOrchestrator.Synchronize();

    
      Dim productionConnection As New DB2Connection("user id=db2admin;server=db2prod:50000;database=PRODUCTIONDB")
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      Dim syncOrchestrator As New SyncOrchestrator()

      'Specify source database
      syncOrchestrator.RemoteProvider = New DB2SyncProvider("DevCategoryScope", devConnection, Nothing, Nothing)

      'Specify production database
      syncOrchestrator.LocalProvider = New DB2SyncProvider("DevCategoryScope", productionConnection, Nothing, Nothing)

      syncOrchestrator.Synchronize()

    Database Deprovisioning

    When deprovisioning the database, the objects, created by Sync Framework, that are not used any more are deleted. If there is at least one scope that still uses these objects, they will not be deleted. User database objects are not deleted at all. You can deprovision a provisioned scope, a template with all scopes built on it, or the whole database.

    Deprovisioning Synchronization Scope

    To deprovision a single synchronization scope you need to create a DB2SyncScopeDeprovisioning object and execute its DeprovisionScope method, which accepts the scope name as its parameter.

    Deprovisioning Template

    Template deprovisioning deletes the template and all the synchronization scopes, based on this template. The DeprovisionTemplate method of DB2SyncScopeDeprovisioning is used to deprovision a template. 

          DB2Connection devConnection = new DB2Connection(@"user id=db2admin;server=db2dev:50000;database=DEVDB");

      // Remove the "IsAvaibleTemplate" template from the dev database.       
      DB2SyncScopeDeprovisioning templateDeprovision = new DB2SyncScopeDeprovisioning(devConnection);

      // Remove the scope.
      templateDeprovision.DeprovisionTemplate("IsAvaibleTemplate");
    
      Dim devConnection As New DB2Connection("user id=db2admin;server=db2dev:50000;database=DEVDB")

      ' Remove the "IsAvaibleTemplate" template from the dev database.       
      Dim templateDeprovision As New DB2SyncScopeDeprovisioning(devConnection)

      ' Remove the scope.
      templateDeprovision.DeprovisionTemplate("IsAvaibleTemplate")

    Deprovisioning Storage

    Storage deprovisioning removes all the templates, synchronization scopes, and all the Sync Framework database objects in the connection schema. The DeprovisionStore method is used for storage deprovisioning.

    See Also

    Entity Framework section  | Entity Framework Support Overview

     

     

    © 2013 - 2024 Devart. All Rights Reserved.

    [email protected]