The source code for OutlookSyncSQL Iteration 1 is at http://msftdbprodsamples.codeplex.com/releases/view/87615 and the readme is at Outlook Contacts Sync SQL Server – Iteration 1 Setup.

image_thumb3

Before starting this blog post, I want to mention that sample iterations will have functional code; however, the sample will not be fully baked until the final release. Therefore, the expectation is that iterations might not contain the same fit and finish as a final release.

As a continuation from the previous blog post, creating unit tests as a first step in developing an application allows you to pass/fail specific functional methods. For the Outlook Data Sync sample, I created unit test methods to isolate various functional parts. More unit tests were created, but hopefully, you get the point. Successful unit test methods are added to a SimpleSync class.

As a simple unit test example, the InsertContactSql() TestMethod tests inserting an AdventureWorksLT contact. After clicking the Run Test in Current Context button, Visual Studio displays a Passed result.

[code]

This code is added to the SimpleSync class:

[code]

For more details about the Visual Studio Unit Test framework: Verifying Code by Using Unit Tests and Anatomy of a Unit Test.

Outlook Add-in

The source code shows how to wire together Outlook event delegates to perform SQL Server CRUD operations while using Outlook contacts. For example, assigning contactItem_BeforeDelete() delegate deletes multiple contact items for each selected contact item.

[code]

The contactItem_BeforeDelete delegate method calls simpleSync.DeleteContact() that deletes the AdventureWorksLT2012 customer row.

[code]

Outlook Events and SQL Server CRUD operations

Outlook Event

SQL Server CRUD operation

contactFolderItems.ItemAdd

InsertContact

explorer.FolderSwitch

ReadContacts (performs a simple contact item SQL sync)

contactItem.BeforeDelete

DeleteContact

contactItem.Write

UpdateContact

SimpleSync class

Iteration 1 includes the SimpleSync class with methods to perform SQL Server CRUD operations with Outlook contact items.

The CodePlex source includes

  • InsertContact(Outlook.ContactItem contactItem) - Inserts an Outlook Contact into the AdventureWorksLT2012 customer table, and saves the CustomerID back into the ContactItem. Uses the SalesLT.usp_InsertContact stored procedure that inserts a contact item into the AdventureWorksLT2012 customer and address table as a transaction.
  • InsertContact(DataRow row) - Inserts an Outlook contact from an AdventureWorksLT2012 customer DataRow.
  • UpdateContact(Outlook.ContactItem contactItem) - Updates an AdventureWorksLT2012 customer from an Outlook contact item. Calls the SalesLT.usp_UpdateContact stored procedure that updates a customer from an email address.
  • Outlook.ContactItem UpdateContact(Outlook.ContactItem contactItem, DataRow row) - Updates an Outlook contact item based on a AdventureWorksLT2012 customer DataRow.
  • ReadContacts() - Read contacts from AdventureWorksLT2012 performing a simple contact item to SQL table sync.
  • Outlook.ContactItem FindContact(string name, string value) – Given an Outlook Contact Item name and value, return the ContactItem.
  • DeleteContact(Outlook.ContactItem contactItem) – Deletes an AdventureWorksLT2012 customer and address as a cascade delete on the SQL Server instance.

Simple single-user sync

Iteration 1 includes a simple AdventureWorksLT2012 customer / contact items sync method. For each customer DataRow:

  1. Find the Outlook contact item from the customer email address.
  2. If the Outlook contact item does not exist, insert the customer into the Outlook contact list.
  3. Otherwise, if the modified date for the customer row is greater than the Outlook contact item, Update the contact item.
  4. To delete Outlook contact items deleted from the customer table, loop each contact item not in the customer rows. ReadContacts() calls the Outlook Restrict() method that filters items. In this case, the method takes the filter StringBuilder composed of CustomerID criteria not equal to a customer row. Thus, the method returns a filtered list of items not in the customer table.

[code]

Iteration 2 will show how to sync Outlook contact items stored in a SQL Express hub database to SQL Server Enterprise (or Developer edition) using the Microsoft Synchronization Framework. The idea is to store client contact data in SQL Express; thus, allowing other client applications to use the same local store while offline. Synchronization progress will be displayed within a SQL Server Synchronization task pane hosted in Outlook. The iteration will address the following user stories:

  1. As a user, I want my contacts automatically synchronized with a SQL Server or SQL Azure contact table.
  2. As a user, I want to be able to easily configure the sample application.
  3. As an Outlook user, I want to see the progress of an Outlook / contacts table synchronization.
  4. As an Outlook user, I want to view a history of synchronization values.

We value your participation in the process of creating SQL Server samples that reflect your needs.

Contact us directly:

Derrick VanArnam (derrickv@microsoft.com) or My Manager: dlittle@microsoft.com

Susan Joly (susanjo@microsoft.com)

Last edited May 12, 2012 at 11:37 PM by DerrickVMSFT, version 1

Comments

No comments yet.