Outlook Contacts Sync SQL Server – Iteration 1 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 1 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_1.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 AdventureWorks2012 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 1

Outlook Contacts Sync SQL Server – Iteration 1 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.

See Example Unit Test.

How to run Iteration 1

To run iteration 1, open OutlookSyncSQL.sln in Visual Studio 2010 and run the sample.

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

image

Example Unit Tests

    //Create
    [TestMethod]
    public void InsertContactSql()
    {
      using(QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_InsertContact("test FirstName", "test LastName", "test@adventure-works.com", "1234 Test", "TEST");

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

        Assert.IsTrue(id > 0);
      }
    }
    
    [TestMethod]
    public void InsertContact()
    {
      this.ThisAddIn_Startup();

      Outlook.MAPIFolder folder = application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);
      
      contactItem = application.CreateItem(OlItemType.olContactItem);

      contactItem.Email1Address = "test@adventure-works.com";
      contactItem.FirstName = "testFirst";
      contactItem.LastName = "testLast";
      contactItem.BusinessAddress = "1234 Test";

      using (QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_InsertContact(contactItem.FirstName, contactItem.LastName, contactItem.Email1Address, contactItem.BusinessAddress, "SAMPLE");
        IDataParameter param = aw.GetCommand(1).Parameters["@RETURN_VALUE"] as IDataParameter;

        contactItem.CustomerID = param.Value.ToString();
      }

      contactItem.Save();

      Assert.IsTrue(contactItem.CustomerID != string.Empty);

      this.ThisAddIn_Shutdown();
    }

    //Read
    [TestMethod]
    public void GetOutlookContacts()
    {
      this.ThisAddIn_Startup();

      Outlook.MAPIFolder folder = application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);

      Outlook.Items items = folder.Items;

      Assert.IsTrue(items.Count > 0);

      this.ThisAddIn_Shutdown();
    }

    [TestMethod]
    public void GetContact()
    {

      using (CustomerDataClassesDataContext db = new CustomerDataClassesDataContext())
      {
        // Get a typed table to run queries
        Table<vw_Customer> Customers = db.GetTable<vw_Customer>();

        vw_Customer contact = (from customer in Customers
                               where customer.EmailAddress == "donna0@adventure-works.com"
                               select customer).First<vw_Customer>();

        Assert.IsTrue(contact.EmailAddress == "donna0@adventure-works.com");
      }

    }

    //Find
    [TestMethod]
    public void FindContact()
    {
      string name = "[@id]";
      object value = 31967;
      bool IsItem = false;

      this.ThisAddIn_Startup();

      Outlook.MAPIFolder folder = application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);

      contactItem = folder.Items.Find(String.Format("{0}=\"{1}\"", name, value));

      IsItem = (contactItem != null);

      Assert.IsTrue(IsItem);

      this.ThisAddIn_Shutdown();
    }

    //Filter
    [TestMethod]
    public void FilterContacts()
    {
      //CustomerID
      string filter = "[CustomerID] <> 34320 And [CustomerID] <> 34338";
      this.ThisAddIn_Startup();

      Outlook.MAPIFolder folder = application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);

      var items1 = folder.Items;
      int c1 = items1.Count;

      var items2 = folder.Items.Restrict(filter);
      int c2 = items2.Count;
      Assert.IsTrue(c1 > c2);

      foreach (ContactItem item in items2)
      {
        var test = item.Email1Address;
      }

      this.ThisAddIn_Shutdown();
    }

    //Update
    [TestMethod]
    public void UpdateContact()
    {
      using (QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_UpdateContact("test@adventure-works.com", "test", "test LAST NAME", "Changed Address", "SAMPLE");
      }

      using (OutlookSyncSQLAddin.AdventureWorksLT2012DataSetTableAdapters.usp_GetContactFromEmailAddressTableAdapter ta = new
          OutlookSyncSQLAddin.AdventureWorksLT2012DataSetTableAdapters.usp_GetContactFromEmailAddressTableAdapter())
      {
          DataTable tbl = ta.GetData("test@adventure-works.com");

          Assert.IsTrue(tbl.Rows.Count > 0);
      }
    }

    //Delete
    [TestMethod]
    public void DeleteContact()
    {
      string name = "[Email1Address]";
      string value = "test@adventure-works.com";
      bool IsItem = false;

      this.ThisAddIn_Startup();

      Outlook.MAPIFolder folder = application.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);

      contactItem = folder.Items.Find(String.Format("{0}=\"{1}\"", name, value));

      IsItem = (contactItem != null);

      if (IsItem)
      {
        contactItem.Delete();
      }

      Assert.IsTrue(IsItem);

      this.ThisAddIn_Shutdown();
    }

    [TestMethod]
    public void DeleteContactSql()
    {

      using (QueriesTableAdapterSubclass aw = new QueriesTableAdapterSubclass())
      {
        aw.usp_DeleteContact("test@adventure-works.com");
      }

      using (OutlookSyncSQLAddin.AdventureWorksLT2012DataSetTableAdapters.usp_GetContactFromEmailAddressTableAdapter ta = new
          OutlookSyncSQLAddin.AdventureWorksLT2012DataSetTableAdapters.usp_GetContactFromEmailAddressTableAdapter())
      {

        DataTable tbl = ta.GetData("test@adventure-works.com");

        Assert.IsTrue(tbl.Rows.Count == 0);
      }
    }

Last edited May 12, 2012 at 8:39 PM by DerrickVMSFT, version 7

Comments

No comments yet.