Contents

The source code for OutlookSyncSQL Iteration 2 is at http://msftdbprodsamples.codeplex.com/releases/view/87615.

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.

Outlook Contacts Sync SQL Server – Iteration 2 User Stories

  1. As an Outlook user, I want to synchronize contact fields to a SQL Server database.
  2. As an Outlook user, I am occasionally disconnected from the network / Internet. Therefore, I need my contacts available while offline.
  3. As a user, all of my existing contacts must be preserved while and after running the sample.

Outlook Contacts Sync SQL Server – Iteration 2 Prerequisites

  • Visual Studio 2010 SP1
  • Outlook 2012

How to install AdventureWorksLT2012_SyncSample database

The AdventureWorksLT2012_SyncSample database can be installed using the CREATE DATABASE statement or by attaching the database.

To install AdventureWorksLT2012_SyncSample database

  1. Download OutlookSyncSQL_Iteration_2.zip .

  2. From the License Agreement, click I Agree.

  3. From File Download, click Save and browse to a location on your desktop.

  4. Unzip OutlookSyncSQL.zip and copy \Database\AdventureWorksLT2012_SyncSample_Data.mdf to a folder.

  5. From SQL Server Management Studio, execute the following code:

    CREATE DATABASE AdventureWorksLT2012_SyncSample ON (FILENAME = '{drive}:\{file path}\AdventureWorksLT2012_SyncSample_Data.mdf’) FOR ATTACH_REBUILD_LOG;

    As an alternative to step 4, you can attach the database using the SQL Server Management Studio user interface. For more detailed information, see Attach a Database (SQL Server Management Studio) .

     Note:

    You must remove the log file from the list of files to attach. This will cause the operation to rebuild the log.

How to compile Iteration 2

Outlook Contacts Sync SQL Server – Iteration 2 includes a set of unit tests. The unit tests demonstrate

  • How to use relevant Outlook properties and methods with SQL Server.
  • How to perform CRUD operations against a modified version of AdventureWorksLT2012.

To run the unit tests, you will need to compile the OutlookSyncSQLAddin project before the MyDataTestProject (unit test) project.

How to run Iteration 2

To run iteration 2, open OutlookSyncSQL.sln in Visual Studio 2010 and run the sample. Once the solution opens Outlook, you will see a *SQL Server Samples* ribbon tab.

Note The sample source project will install the OutlookSyncSQLAddin Outlook 2010 add-in.

image

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 feedback in the process of creating SQL Server samples that reflect your needs.

Please post a comment on this page or contact us directly:

Derrick VanArnam (derrickv@microsoft.com)

Susan Joly (susanjo@microsoft.com)

Last edited May 14, 2012 at 9:27 PM by DerrickVMSFT, version 6

Comments

No comments yet.