Contents

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

image_thumb3

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.

SQL Server Unit Test Example

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

    [TestMethod]
    public void InsertContactSql()
    {
	…
      using(QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_InsertContact(firstName, lastName, emailAddress, address, addressType);

        IDataParameter param = aw.GetCommand(1).Parameters["@RETURN_VALUE"] 
as IDataParameter; int id = (int)param.Value; Assert.IsTrue(id > 0); } }

This code is added to the SimpleSync class:

  class SimpleSync
  {
	…
    public void InsertContact(Outlook.ContactItem contactItem)
    {
      using (QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_InsertContact(contactItem.FirstName, contactItem.LastName, 
contactItem.Email1Address, Properties.Settings.Default.SampleBusinessAddress,
Properties.Settings.Default.AddressType); IDataParameter param = aw.GetCommand(1).Parameters["@RETURN_VALUE"]
as IDataParameter; contactItem.CustomerID = param.Value.ToString(); } contactItem.Save(); } … }

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

OutlookSyncSQL 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 the contactItem_BeforeDelete() delegate method to each selected item deletes each contact item.

      private void explorer_SelectionChange()
      {
        if (this.Application.ActiveExplorer().Selection.Count > 0)
        {
          foreach (object selection in this.Application.ActiveExplorer().Selection)
          {
            contactItem = selection as Outlook.ContactItem;

            if (contactItem != null)
            {
              try
              {
                //Assign Delete delegate method to each selected item
                contactItem.BeforeDelete += new ItemEvents_10_BeforeDeleteEventHandler
(contactItem_BeforeDelete); … } } } }

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

    private void contactItem_BeforeDelete(object Item, ref bool Cancel)
    {
        simpleSync.DeleteContact(Item as ContactItem);
    }
    
    public void DeleteContact(Outlook.ContactItem contactItem)
    {
      using (AWTableAdapterSubclass aw = new AWTableAdapterSubclass())
      {
        aw.usp_DeleteContact(contactItem.Email1Address);
      }
    }

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 2 includes the SimpleSync class with methods to perform SQL Server CRUD operations for 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 an AdventureWorksLT2012 customer DataRow.
  • ReadContacts() - Reads contacts from the AdventureWorksLT2012 customer table performing a simple contact item to SQL Server 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 2 includes a simple AdventureWorksLT2012 customer / Outlook 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 row 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 AdventureWorksLT2012 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 StringBuilder filter composed of CustomerID criteria not equal to a customer row. Thus, the method returns a filtered list of items not in the customer table.
    public void ReadContacts()
    {
      …

      foreach (DataRow row in rows)
      {
        item = FindContact("[Email1Address]", row["EmailAddress"].ToString());

        //Create a filter for ContactItems not in DataTable
        filter.Append(String.Format("[CustomerID] <> {0} And ", row["CustomerID"]));

        //Simple Sync
        if (item == null)
        {
          //Insert ContactItem if it does not exist
          this.InsertContact(row);
        }
        else
        {
          // Match ModifiedDate
          DateTime modifiedDate = (DateTime)row["ModifiedDate"];
          if (modifiedDate.ToBinary() > item.LastModificationTime.ToBinary())
          {
            // Update if SQL ModifiedDate > ContactItem ModifiedDate
            this.UpdateContact(item, row);
          }
        }
      }

      //Loop Contacts to delete any not in DataTable
      if (filter.ToString().StartsWith("[CustomerID]"))
      {
        …

        var items = folder.Items.Restrict(filter.ToString());
        foreach (Outlook.ContactItem deletedItem in items)
        {
          deletedItem.Delete();
        }
      }
    }

Iteration 3 will show how to sync Outlook contact items stored in a SQL Express hub database to SQL Server Enterprise edition (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)

Susan Joly (susanjo@microsoft.com)

Last edited May 14, 2012 at 7:32 PM by DerrickVMSFT, version 16

Comments

No comments yet.