| Note: |
|---|
| This topic is applicable only for full .NET Framework. |
SQLiteSync is a very powerful and easy to use application to compare, merge and keep in sync two sqlite databases. SQLiteSync smart visualization technology is also able to detect BLOB columns and very large TEXT columns and display all of them in a preview panel. SQLite Tutorial website helps you master SQLite quickly and easily. It explains the complex concepts in simple and easy-to-understand ways so that you can both understand SQLite fast and know how to apply it in your software development work more effectively.
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.
Note that Sync Framework is supported only in the Professional Edition of dotConnect for SQLite.
- Database Provisioning
- Synchronization
- Database Deprovisioning
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:
- SyncOrchestrator - creates and manages synchronization sessions. This class is provided by Sync Framework.
- DbSyncProvider - implements the synchronization service for interaction with a database and shields SyncOrchestrator from the specific implementation of the database. dotConnect for SQLite provides its own SQLiteSyncProvider class implementation for SQLite database.
- DbSyncAdapter - maintains the interaction between the DbSyncProvider and the database. This class is provided by Sync Framework.
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.
SQLiteSyncScopeProvisioning 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 SQLite offers the SQLiteSyncScopeDeprovisioning class for scope deprovisioning.
The parent class of the SQLiteSyncProvider class is the descendant of the standard Sync Framework DbSyncProvider class. Other synchronization classes of dotConnect for SQLite 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:
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:
- Devart.Data.Synchronization
- Devart.Data.SQLite.Synchronization
- Microsoft.Synchronization Version 2.1
- Microsoft.Synchronization.Data Version 3.1
- Devart.Data.SQLite
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 SQLiteSyncDescriptionBuilder 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:
| Visual Basic | Copy Code |
|---|
After executing this code, Sync Framework creates the following database objects in the database:
- schema_version table - contains the information on the version of Sync Framework that provisioned the database.
- scope_info table - contains the list of all the provisioned scopes.
- scope_config table - contains scope configuration information.
- products_tracking the table with metadata of the provisioned table 'products'.
- Insert/update/delete triggers for tracking changes in the provisioned table.
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.
The following code creates the synchronization scope with the filter.
| Visual Basic | Copy Code |
|---|
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.
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:
| Visual Basic | Copy Code |
|---|
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.
| Visual Basic | Copy Code |
|---|
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.
| Visual Basic | Copy Code |
|---|
Synchronization
Sqlite Sync Databases
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.
| Visual Basic | Copy Code |
|---|
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.
| Visual Basic | Copy Code |
|---|
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 SQLiteSyncScopeDeprovisioning 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 SQLiteSyncScopeDeprovisioning is used to deprovision a template.| Visual Basic | Copy Code |
|---|
Sqlite Sync
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
Sqlite Sync Framework
Entity Framework section | Entity Framework Support Overview