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 Codeimage
  3. Specify a SQL Server database connection
    image
  4. Specify a SQL Compact database connection
    image
  5. Select the Tables and Columns to Sync and Specify Scope Nameimage
  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 Projectimage
  4. Select the Tables and Columns to Sync and Specify Scope Name image
  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 Scopeimage
  4. Select the Tables and Columns to Sync and Specify Scope Nameimage
  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 Databaseimage
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 Databaseimage
Scopes Folder

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

image

Deprovision Scope

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

image

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

Advertisements

11 comments

  1. Can I use this with SQL server 2008 express? Or just compact?

    1. the client is always a SQL Compact Database. if you use the generate local database cache code option, you can connect to a SQL Express or SQL Server or SQL Azure database but the client will still be SQL Compact. it’s not that hard to modify the code to change the client to SQL Express if thats what you’re after.

  2. […] and Explorer tree integration. You can read a nice walkthrough of the features in June’s blog post here. Notice that you will need to install the Sync Framework 2.1 bits for any of these features to work, […]

  3. When trying to generate a local cache for a Windows Phone 8 project it says it is the wrong target framework. Can I use the toolbox to provision a WP8 project for Sync?

    1. Sync Framework doesn’t run on WP8. You will have to use the Sync Framework Toolkit but you’ll have to make some changes to get it running on WP8.

      1. I was aware that I needed to use the toolkit, but I did not know that your toolbox only supported the desktop version of Sync. Thanks you for your quick reply. Still a great tool! Now I shouldn’t have to switch back to VS 2010 to update they database cache in the desktop version of our app.

      2. just a quick correction, i don’t own the ToolBox project, just collaborated with Erik on that one. I thought the documentation on the toolbox actually explicitly specifies that it requires Sync Fx. the code generated also uses the SqlSyncProvider and SqlCeSyncProvider.

  4. Rick Cloud · · Reply

    I just now did this, and the provisioning worked perfectly… However, the synchronization failed due to some component not being registered. I have the compact toolbox, which I installed 3/14/2014; it’s version 3.7.2
    I also reinstalled sqlce 3.5 sp2(x86, then x64) on 4/8/2014 AND
    Sync Framework SDK 2.1 on 4/4/2014..

    I also have Sync framework 1.0, which was installed in Oct-2013, when I relieved this machine from my company…

    Any wild guesses about the ‘what’ that’s not installed?

    Following is the actual error message:
    Microsoft.Synchronization.SyncException was unhandled
    HResult=-2146233088
    Message=Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
    Source=Microsoft.Synchronization
    StackTrace:
    at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)
    at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)
    at Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()
    at Microsoft.Synchronization.SyncOrchestrator.Synchronize()
    at TestSQLCompactTBx.MySyncDBScopeSync.Synchronize() in c:\Users\ccloudr2\Documents\Visual Studio 2012\Projects\SyncSQLServerandSQLCompact\TestSQLCompactTBx\MySyncDBScope.cs:line 49
    at TestSQLCompactTBx.Program.Main(String[] args) in c:\Users\ccloudr2\Documents\Visual Studio 2012\Projects\SyncSQLServerandSQLCompact\TestSQLCompactTBx\Program.cs:line 21
    at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
    at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
    at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
    at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
    at System.Threading.ThreadHelper.ThreadStart()
    InnerException: System.Runtime.InteropServices.COMException
    HResult=-2147221164
    Message=Retrieving the COM class factory for component with CLSID {EC413D66-6221-4EBB-AC55-4900FB321011} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
    Source=Microsoft.Synchronization
    ErrorCode=-2147221164
    StackTrace:
    at Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)
    InnerException:

    Thanks for any help you can provide.
    Rick Cloud

    1. check that your build platform target (x86/x64) matches the Sync Fx platform you installed and reference (Sync Fx x86/x64)

  5. Up until VS 2010 while using Generate Local Database Cache template, we used to generate code required for WFC services project selecting server location for N-tier scenario (using SQLCE3.5). Is it possible to do the same with the toolkit in VS2013?
    Is there a new compact type database (no-admin) available for N-tier Synchronisation in Visual Studio 2013?

    1. the Local Database Cache project has long been removed since VS 2012. and no, the one we put on the Toolkit does not generate the WCF project. You’ll have to write that or grab the WCF code created by the old wizard. that should still work since its just creating a proxy to WCF based sync provider.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: