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 http://msftdbprodsamples.codeplex.com/releases/view/87615.
Iteration 3 tackles these user stories:
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().
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.
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.
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
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.
More information about the various technologies used in the OutlookSyncSQL Iteration 3 sample:
SQL Express LocalDB Procedures
CREATE PROCEDURE with parameters
BEGIN TRANSACTION …
SET @local_variable using a subquery