You may have heard the “We’re all in” message, but some of you may be wondering how the cloud applies to Office. The answer is that it applies in many different ways, which I’ll be exploring over the next few weeks and months with you. For example, the following are a couple of ways in which Office and the cloud come together:

  • Rich Office client apps that integrate with the cloud (e.g. Word or Outlook add-ins that leverage cloud-based services or data). These apps can range from consuming oData, Web 2.0 applications, and Azure.
  • Integrating Office and SharePoint through Office server-side services. InfoPath, Excel Services, Access Services, etc.

As mentioned above, one of the new ways you can leverage cloud-based data is by using oData; in this post I’d like to walk through a sample that integrates Outlook 2010 with oData. oData is the Open Data protocol (http://www.odata.org/) that supports leveraging web-based data through light-weight RESTful interfaces.

First, if you’re looking to get started with some pre-existing data, you can use one of a couple of oData samples: 1) Netflix data (http://odata.netflix.com/Catalog/) or 2) Northwind data (http://services.odata.org/Northwind/Northwind.svc/). For example, if you navigate to the Northwind URI you’ll see the following feed. Note that you can filter on results using this URI, for example entering http://services.odata.org/Northwind/Northwind.svc/Customers will return all of the Northwind customers.

image

Using oData interfaces, you can leverage data like the Northwind data in a rich Office client application. Let’s walk through creating an Outlook add-in that leverages the Northwind data via oData.

To begin, open Visual Studio 2010 and use the Office 2010, Outlook add-in project template. Right-click and add a new WPF user control. In the figure below you can see that I’ve created a straightforward XAML-based user control. The UI includes a combo-box that will load customers, labels (e.g. Contact Name, Contact Address, etc.) that will display customer information, two buttons (one to view order data for a specific customer and another to create a contact from the customer data), and a listbox to display the order data.

image

The following shows the code for the above user control.

<UserControl x:Class="NorthwindCustomers.CustomerPicker"
             xmlns="
http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
             mc:Ignorable="d"
             d:DesignHeight="600" d:DesignWidth="280">
    <StackPanel Height="600">
        <StackPanel Orientation="Horizontal">
            <Label Content="Northwind Customer Data" Height="28" Name="lblTitle" FontWeight="Black"/>
        </StackPanel>
        <StackPanel Orientation="Horizontal">
            <StackPanel Orientation="Vertical" Width="140">
                <Label Content="Customers:" FontWeight="SemiBold" Height="28" Name="lblCustomers"/>
            </StackPanel>
            <StackPanel Orientation="Vertical" Width="141">
                <ComboBox Height="23" Name="cmbobxCustNames" Width="120" SelectionChanged="cmbobxCustNames_SelectionChanged"/>
            </StackPanel>
        </StackPanel>
        <StackPanel Orientation="Horizontal">
            <StackPanel Orientation="Vertical" Width="140">
                <Label Content="Contact Info:" FontWeight="SemiBold" Height="28" Name="lblContactInfo"/>
            </StackPanel>
            <StackPanel Orientation="Vertical" Width="141">
                <Label Content="-Contact Name-" Height="28" Name="lblContactName"/>
                <Label Content="-Contact Address-" Height="28" Name="lblAddress"/>
                <Label Content="-Contact City-" Height="28" Name="lblCity"/>
                <Label Content="-Postal Code-" Height="28" Name="lblPostalCode"/>
                <Label Content="-Country-" Height="28" Name="lblCountry"/>
                <Label Content="-Phone-" Height="28" Name="lblPhone"/>
            </StackPanel>
        </StackPanel>
        <StackPanel Orientation="Horizontal">
            <StackPanel Orientation="Vertical" Width="140">
                <Button Name="btnViewData" Content="View Data" Margin="5" Click="btnViewData_Click" Height="24" Width="100" />
            </StackPanel>
            <StackPanel Orientation="Vertical" Width="141">
                <Button Name="btnAddCustomer" Content="Create Contact" Margin="5" Click="btnAddCustomer_Click" Height="24" Width="100" />
            </StackPanel>
        </StackPanel>
        <TextBlock x:Name="txtblckTitle" Text="  Northwind Order Data" FontWeight="Bold"/>
        <ListBox Name="orderListBox" ItemsSource="{Binding}" Width="270" Height="250">
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <StackPanel>
                        <StackPanel Orientation="Horizontal">
                            <TextBlock Text="{Binding oOrderCustID}" FontWeight="Bold" />
                            <TextBlock Text=": " FontWeight="Bold" />
                            <TextBlock Text="{Binding oOrderShipName}" />
                        </StackPanel>
                        <StackPanel>
                            <TextBlock Text="Order ID: " FontWeight="Bold" />
                            <TextBlock Text="{Binding oOrderID}" Margin="5 0 0 0" />
                        </StackPanel>
                        <StackPanel>
                            <TextBlock Text="Shipping: " FontWeight="Bold" />
                            <TextBlock Text="{Binding oOrderDate}" Margin="5 0 0 0" />
                            <TextBlock Text="{Binding oRequiredDate}" Margin="5 0 0 0" />
                            <TextBlock Text="{Binding oShippedDate}" Margin="5 0 0 0" />
                            <TextBlock Text="{Binding oShipVia}" Margin="5 0 0 0" />
                            <TextBlock Text="{Binding oFreight}" Margin="5 0 0 0" />
                        </StackPanel>
                    </StackPanel>
                </DataTemplate>
            </ListBox.ItemTemplate>
        </ListBox>
    </StackPanel>
</UserControl>

After you’ve created the user control, you’ll now want to add a service reference to the Northwind oData source using the following URI: http://services.odata.org/Northwind/Northwind.svc) by right-clicking the project and selecting Add Service Reference. Add the aforementioned URI into the service reference dialog, and you’ll be able to use the Northwind data in your application. To get to the code-behind, you can right-click the XAML file and select View Code.

The code-behind for the user control is as follows. Note that you can use the oData connection as a data context (see bolded line of code). This application loads two sets of data from Northwind, Customers and Orders. You’ll also note that you can use LINQ queries against the oData feed, which in this application are used to populate the list collections for the customers and orders (the list collections use custom objects: oDataCustomer and oDataOrders). The LINQ queries also enable you to take the selected customer in the combo-box and display the orders for that customer. One of the interesting parts of this application is the integration of the oData Northwind data with the Outlook object model. For example, you can see in the btnAddCustomer_Click method you’re creating a new contact from the selected customer data.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Net;
using NorthwindCustomers.NWODataCustomerFeed;
using Outlook = Microsoft.Office.Interop.Outlook;
using System.Xml;

namespace NorthwindCustomers
{

    public partial class CustomerPicker : UserControl
    {
        string strName;
        string strAddress;
        string strCity;
        string strCountry;
        string strPhone;

        //This is the data context for the oData feed.
        NorthwindEntities dc = new NorthwindEntities(new Uri("
http://services.odata.org/Northwind/Northwind.svc"));
        //List collections (for in-memory data processing) that will be used by the add-in.
        List<oDataCustomer> listOfNorthwindCustomers = new List<oDataCustomer>();
        List<oDataOrders> listOfNorthWindOrders = new List<oDataOrders>();

        public CustomerPicker()
        {
            InitializeComponent();
            //Setting up customer and order data from oData feed.
            populateCustomersInComboBox();
            populateOrdersInMemory();
            //Making view data button disabled.
            btnViewData.IsEnabled = false;
        }

        //Populates the order data within a list collection for in-memory access.
        private void populateOrdersInMemory()
        {
            var orders = from o in dc.Orders
                       select o;

            foreach (var item in orders)
            {
                oDataOrders tempOrder = new oDataOrders();
                tempOrder.oOrderCustID = item.CustomerID;
                tempOrder.oOrderShipName = item.ShipName.ToString();
                tempOrder.oOrderID = item.OrderID.ToString();
                tempOrder.oOrderDate = item.OrderDate.ToString();
                tempOrder.oRequiredDate = item.RequiredDate.ToString();
                tempOrder.oShippedDate = item.ShippedDate.ToString();
                tempOrder.oShipVia = item.ShipVia.ToString();
                tempOrder.oFreight = item.Freight.ToString();
                listOfNorthWindOrders.Add(tempOrder);
            }
        }

        //Adds the customers to the customer combo-box.
        private void populateCustomersInComboBox()
        {
            var cust = from c in dc.Customers
                       select c;

            foreach (var item in cust)
            {
                oDataCustomer tempCustomer = new oDataCustomer();
                tempCustomer.oCustID = item.CustomerID;
                tempCustomer.oCompName = item.CompanyName;
                tempCustomer.oContactName = item.ContactName;
                tempCustomer.oContactTitle = item.ContactTitle;
                tempCustomer.oAddress = item.Address;
                tempCustomer.oCity = item.City;
                tempCustomer.oPostalCode = item.PostalCode;
                tempCustomer.oCountry = item.Country;
                tempCustomer.oPhone = item.Phone;
                listOfNorthwindCustomers.Add(tempCustomer);
                cmbobxCustNames.Items.Add(tempCustomer.oCompName);
            }
        }

        //Adds customer to Microsoft Outlook Contacts.
        private void btnAddCustomer_Click(object sender, RoutedEventArgs e)
        {
            Outlook._Application myOutlookObject = new Outlook.Application();
            Outlook.MAPIFolder custContacts = (Outlook.MAPIFolder)myOutlookObject.Session.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderContacts);
            Outlook.ContactItem newSalesContact = (Outlook.ContactItem)custContacts.Items.Add(Outlook.OlItemType.olContactItem);

            newSalesContact.FirstName = strName;
            newSalesContact.BusinessAddress = strAddress;
            newSalesContact.BusinessAddressCity = strCity;
            newSalesContact.BusinessAddressCountry = strCountry;
            newSalesContact.Business2TelephoneNumber = strPhone;

            newSalesContact.Save();

            MessageBox.Show("Customer Added!");
        }

        //Filters order data for selected customer.
        private void btnViewData_Click(object sender, RoutedEventArgs e)
        {
            string nameFilter = cmbobxCustNames.SelectedValue.ToString();

            var filteredOrders = from orders in listOfNorthWindOrders
                                 where orders.oOrderShipName == nameFilter
                                 select orders;

            orderListBox.DataContext = filteredOrders;
        }

        //Updates the combo-box when you change the customer selection.
        private void cmbobxCustNames_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            string nameFilter = cmbobxCustNames.SelectedValue.ToString();

            var filteredCust = from f in listOfNorthwindCustomers
                               where f.oCompName == nameFilter
                               select f;
            foreach(var x in filteredCust)
            {
                lblContactName.Content = x.oContactName;
                strName = x.oContactName;
                lblAddress.Content = x.oAddress;
                strAddress = x.oAddress;
                lblCity.Content = x.oCity;
                strCity = x.oCity;
                lblPostalCode.Content = x.oPostalCode;
                lblCountry.Content = x.oCountry;
                strCountry = x.oCountry;
                lblPhone.Content = x.oPhone;
                strPhone = x.oPhone;
            }

            btnViewData.IsEnabled = true;
        }

    }
}

The code for the oDataCustomer object is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NorthwindCustomers
{
    class oDataCustomer
    {
        public string oCustID {get; set;}
        public string oCompName { get; set; }
        public string oContactName { get; set; }
        public string oContactTitle { get; set; }
        public string oAddress { get; set; }
        public string oCity { get; set; }
        public string oPostalCode { get; set; }
        public string oCountry { get; set; }
        public string oPhone { get; set; }
    }
}

The code for the oDataOrders object is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace NorthwindCustomers
{
    class oDataOrders
    {
        public string oOrderCustID { get; set; }
        public string oOrderShipName { get; set; }
        public string oOrderID { get; set; }
        public string oOrderDate { get; set; }
        public string oRequiredDate { get; set; }
        public string oShippedDate { get; set; }
        public string oShipVia { get; set; }
        public string oFreight { get; set; }
    }
}

You’ll also note that I used the WinForm user control to host the XAML user control—simply build and then drag and drop from the Toolbox to the user control. You can also do this programmatically.

The last thing to do is to render the user control in Outlook, which is done by adding the following bolded code in the ThisAddIn.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Windows.Forms;
using Outlook = Microsoft.Office.Interop.Outlook;
using Office = Microsoft.Office.Core;
using OfficeTools = Microsoft.Office.Tools;

namespace NorthwindCustomers
{
    public partial class ThisAddIn
    {
        private CustomersCRM ctrlTaskPane;
        string title = "Northwind Customers";

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            ctrlTaskPane = new CustomersCRM();
            OfficeTools.CustomTaskPane ctp = this.CustomTaskPanes.Add(ctrlTaskPane, title);
            ctp.DockPosition = Office.MsoCTPDockPosition.msoCTPDockPositionRight;
            ctp.Width = 325;
            ctp.Visible = true;

        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        #endregion
    }
}

At this point, you can build and test out the app. Hit F5. When the add-in loads, select a customer. Then click View Data to load the orders data in the Northwind Order Data. See the figure below for the result.

image

You can then click Create Contact to add that customer to your Outlook contacts. See the figure below to see that the customer is now added.

image

There are a ton of other ways to integrate with Office, and in future blogs I’ll discuss other types of integrations—e.g. Azure and Web 2.0.

If you’re looking for the code, you can get the code for this post here: http://cid-40a717fc7fcd7e40.office.live.com/browse.aspx/Outlook%5E_oData%5E_Code.

Happy coding!

Steve