Outlook Contact Data Sync with Microsoft Synchronization Framework - Iteration 3

  • Article

Iteration 3 shows how to sync a SQL Express LocalDB database containing Outlook contacts to a SQL Server instance using the Microsoft Sync Framework 2.1. OutlookSyncSQL Iteration 2 used a SQL Server Express 2012 instance; however, I decided to change the code in iteration 3 to use SQL Express LocalDB. LocalDB provides for a smaller footprint, and offers the same T-SQL language, programming surface and client-side providers as the regular SQL Server Express.

The source code for the OutlookSyncSQL Iteration 3 sample is at https://msftdbprodsamples.codeplex.com/releases/view/87615.

Iteration 3 tackles these user stories:

  • As a user, I want my Outlook contacts synchronized with a SQL Server or SQL Azure contact table.
  • As an Outlook user, I want to see the statistics of an Outlook / contacts table synchronization.

As with the previous blog post, I have created an Outlook Contact Data Sync Iteration 3 document on our SQL Server Samples CodePlex site. This document goes into more details about the OutlookSyncSQL sample code.

While developing iteration 3, I modified the Tutorial: Synchronizing SQL Server and SQL Server Compact walkthrough to work with LocalDB. I used three unit tests to make sure the synchronization code worked as expected: ProvisionServerScope(), ProvisionClientScope(), and Synchronize().

ProvisionServerScope()

The ProvisionServerScope() test method defines a sync scope and provisions the AdventureWorksLT2012_SyncSample sample database on a SQL Server instance with scope related artifacts. In a sync scope, you define what you want to synchronize. A sync scope is a set of tables that must be synchronized as a single unit. OutlookSyncSQL Iteration 3 defines a ProductsScope containing [SalesLT].[Customer], [SalesLT].[CustomerAddress], [SalesLT].[Address].

For more information, see Walkthrough: Defining Scope and Provisioning a Server.

ProvisionClientScope()

The ProvisionClientScope() test method provisions AdventureWorksLT2012_localdb_SyncSample with ProductsScope artifacts from the SQL Server instance. Note that you connect to a LocalDB database using (localdb)\v11.0 as the Server Name.

After running ProvisionServerScope() and ProvisionClientScope(), the SQL Server AdventureWorksLT2012 database and the SQL Express LocalDB AdventureWorksLT2012 database are configured for synchronization.

For more information, see Walkthrough: Provisioning a SQL Compact Client. The OutlookSyncSQL sample was modified from this walkthrough to use SQL Express LocalDB.

Synchronize()

The Synchronize() method creates a sync orchestrator that synchronizes LocalDB AdventureWorksLT2012 and SQL Server AdventureWorksLT2012. The OutlookSyncSQL sample manually kicks off the synchronization process.

For more information, see Walkthrough: Executing Synchronization.

Summary of Sync Steps

  1. Prepare the server database for synchronization
    1. Describes a sync scope.
    2. Provisions the server database with scope related artifacts.
  2. Prepare the client database for synchronization
    1. Provision the LocalDB database with artifacts from the server database.
  3. Invoke the Microsoft Synchronization Framework process between SQL Server and SQL Express LocalDB.

The Synchronize() unit test code was then added to a SqlContactSync class that is called from a WPF User Control hosted on an Outlook task pane. The source code shows how to wire up an Outlook Custom Task Pane that hosts a WPF User Control.

As a recap, OutlookSyncSQL iteration 3 performs SQL Express LocalDB CRUD operations using the same code as iteration 2. Iteration 3 adds Microsoft Synchronization Framework 2.1 capabilities to perform advance multi-user data sync between SQL Express LocalDB and a SQL Server instance.

To help illustrate the progression of this sample, I created a step diagram to illustrate the OutlookSyncSQL sample to date.

  1. User Stories and Unit Tests
  2. Outlook/SQL Server Simple Data Sync to SQL Express
  3. Outlook integration of Microsoft Synchronization Framework sync SQL Server and LocalDB

More information about the various technologies used in the OutlookSyncSQL Iteration 3 sample:

 SQL Express LocalDB Procedures

 Links

[SalesLT].[usp_UpdateContact]

CREATE PROCEDURE with parameters

UPDATE

[SalesLT].[usp_InsertContact]

CREATE PROCEDURE with parameters

TRY/CATCH

BEGIN TRANSACTION …

INSERT

[SalesLT].[usp_DeleteContact]

CREATE PROCEDURE with parameters

TRY/CATCH

BEGIN TRANSACTION …

DELETE

SET @local_variable using a subquery