SQL Server Compact Toolbox Sync Framework support

Erik (blog | twitter)  has just recently blogged that the Preview of SQL Server Compact Toolbox version 3.0 is now available.

SQL Server Compact Toolbox  is a Visual Studio 2010 Pro or higher add-in (for 3.5/4.0) and standalone app (for 4.0), that adds scripting, import, export, migrate, rename, run script, manage replication and more to your SQL Server Compact Data Connections in VS Server Explorer.

The toolbox has already surpassed more than a 100,000 downloads and Erik has also recently appeared on the Visual Studio Toolbox show on Channel 9 demoing the features of the toolbox.

As a bit of a background, I’ve always wanted to write a Visual Studio add-in as an alternative to the Local Database Cache Project Item in Visual Studio. If you have dabbled with Sync Framework, you know that the Local Database Cache Project Item uses the older SqlCeClientSyncProvider/DbServerSyncProvider/SyncAgent combo. Although this VS tooling support provided a wizard interface to quickly configure and generate code for doing synchronization, this older sync provider combo only supports SQL CE on the client side.

However,  while Microsoft has created the newer SqlCeSyncProvider/SqlSyncProvider/SyncOrchestrator combo, the Local Database Cache Project Item was not updated to support these newer providers. As a result, developers have to manually code everything from scratch to use these newer providers.

So, I purposely sought out Erik during the last MVP Summit to ask if he’d be open to extending the toolbox to include Sync Framework synchronization options. Luckily, he agreed and after some sketching on paper, we came up with an initial list of features to experiment on and see if people would find it useful.

As Erik is also working on other enhancements to the toolbox, he decided to bump the version to v3.0 instead. So for the v3.0 release, here’s what we’ve included for Sync Framework:

• Generate Local Database Cache Code
• Add Sync Framework Class to Current Project
• Provision Sync Framework Scope
• Deprovision Sync Framework Objects from Database
• Generate  Snapshot Database to initialize other clients
• Scopes Folder
  • List of Scopes
  • Tables and Columns for Scope
• Deprovision Scope

Generate Local Database Cache Code

This feature tries to mimic what the Local Database Cache Project Item does. You can specify the SQL Server database from which you want to select which tables and corresponding columns will be synched. Compared to the VS Local Database Cache though, this will not automatically provision and initiate  synchronization. It simply generates the code for provisioning and synchronization and you can then inspect and modify it first before provisioning and synching for the first time.

To use this feature:

1. Right-click on the root of the Toolbox’s Explorer Tree
2. Select Generate Local Database Cache Code
3. Specify a SQL Server database connection
   
4. Specify a SQL Compact database connection
   
5. Select the Tables and Columns to Sync and Specify Scope Name
6. Click on OK and this will generate a class file in your VS project.

The generated code will have a class named after the scope name with suffix of Sync (e.g. Scopename = SampleScope, Classname = SampleScopeSync)

The class  includes the following methods:

• Synchronize()
• ProvisionSqlCeScope()
• ProvisionSqlScope()
• DeprovisionSqlCeStore()
• DeprovisionSqlStore()

In addition, it also adds an ApplyChangeFailed event handler that shows how to handle conflicts.

To synchronize, you just have to instantiate the class that was generated and call Synchronize():

var mySyncSample = new SampleScopeSync();
var syncStats = mySyncSample.Synchronize()

 

Add Sync Framework Class to Current Project

This option is almost similar to the Generate Local Database Cache Code option except that you don’t get to specify a SQL Server database. When you select this option, you instead get to pick the tables and columns from the currently selected SQL Compact database in the Toolbox’s Explorer tree.

To use this feature:

1. Select a SQL Compact Database from the Toolbox Explorer Tree
2. Right Click on the SQL Compact Database
3. Select Add Sync Framework Class to Current Project
4. Select the Tables and Columns to Sync and Specify Scope Name
5. Click on OK and this will generate a class file in your VS project.

Provision Sync Framework Scope

This feature simply provisions a scope in a SQL Compact Database without generating code. This is useful when you have the synchronization codes already and you just want to test using a different scope definition.

To use this feature:

1. Select a SQL Compact Database from the Toolbox Explorer Tree
2. Right Click on the SQL Compact Database
3. Select Sync Framework Tools –> Provision Sync Framework Scope
4. Select the Tables and Columns to Sync and Specify Scope Name
5. Click on OK and this will provision the scope in the currently select SQL Compact Database

Deprovision Sync Framework Objects from Database

This feature is the equivalent of calling DeprovisionStore on the SQL Compact database. This will remove all Sync Framework – created objects from the selected SQL Compact Database.

To use this feature:

1. Select a SQL Compact Database from the Toolbox Explorer Tree
2. Right Click on the SQL Compact Database
3. Select Sync Framework Tools –> Deprovision Sync Framework Objects from Database

Generate Snapshot Database to Initialize other clients

This feature allows you to create a new snapshot SQL Compact Database that you can distribute to other clients.

To use this feature:

1. Select a SQL Compact Database from the Toolbox Explorer Tree
2. Right Click on the SQL Compact Database
3. Select Sync Framework Tools –> Deprovision Sync Framework Objects from Database

Scopes Folder

This folder shows all provisioned scopes in a SQL Compact Database. It also shows the tables and columns included in each scope.

Deprovision Scope

This feature allows you deprovision a specific scope in a SQL Compact database.

You may download the toolbox from :SQL Server Compact Toolbox

Let us know what you think, we’d love to hear your feedback.

Cheers,

JuneT

Advertisement