July 2010

Volume 25 Number 07

Office Add-Ins - 3 Solutions for Accessing SharePoint Data in Office 2010

By Donovan Follette | July 2010

Millions of people use the Microsoft Office client applications in support of their daily work to communicate, scrub data, crunch numbers, author documents, deliver presentations and make business decisions. In ever-increasing numbers, many are interacting with Microsoft SharePoint as a portal for collaboration and as a platform for accessing shared data and services.

Some developers in the enterprise have not yet taken advantage of the opportunity to build custom functionality into Office applications—functionality that can provide a seamless, integrated experience for users to directly access SharePoint data from within familiar productivity applications. For enterprises looking at ways to improve end-user productivity, making SharePoint data available directly within Office applications is a significant option to consider.

With the release of SharePoint 2010, there are a number of new ways available to access SharePoint data and present it to the Office user. These range from virtually no-code solutions made possible via SharePoint Workspace 2010 (formerly known as Groove), direct synchronization between SharePoint and Outlook, the new SharePoint REST API and the new client object model. Just as in Microsoft Office SharePoint Server (MOSS) 2007, a broad array of Web services is available in SharePoint 2010 for use as well.

In this article, we’ll describe a couple of no-code solutions and show you how to build a few more-complex solutions using these new features in SharePoint 2010.

External Data Sources

Let’s start by taking a quick look at the SharePoint list types you can employ as data sources.

One particularly useful data source is an external list that displays data retrieved via a connection to a line-of-business (LOB) system. MOSS 2007 let you to connect to LOB data using the Business Data Catalog (BDC), which provided read-only access to back-end systems. SharePoint 2010 provides Business Connectivity Services (BCS), which is an evolution of the BDC that supports full read/write access to your LOB data.

Why would you want to bring LOB data into SharePoint? Consider the use case where you have a customer relationship management (CRM) system that only a limited number of people in the organization can access directly. However, there’s a customer table in the database with name and address data that could be used by many others if it were available. In real-life, you probably end up with users copying this information from various non-authoritative sources and pasting it into their Office documents. It would be better to access this customer data from the authoritative CRM system and expose it in SharePoint as an external list that Office clients can access.

SharePoint Designer 2010 is the tool used for configuring access to a LOB system and making its data available in a SharePoint external list. There are a couple steps required to do this.

The first step is to create a new External Content Type (ECT). The ECT contains metadata describing the structure of the back-end data, such as the fields and CRUD methods that SharePoint will use to interact with it. Once the ECT has been created, an external list can be generated from it on any site within SharePoint. External lists look and act like any other standard list in SharePoint, but the external list data is not stored in SharePoint. Instead, it’s retrieved via the ECT when accessed by an end user.

SharePoint Designer includes default support for connecting to external data sources including SQL Server, Windows Communication Foundation (WCF) and the Microsoft .NET Framework. Therefore, an ECT can be easily created for connecting to any SQL Server database table or view, WCF service or Web service. Custom .NET solutions can be built in Visual Studio 2010 using the new SharePoint 2010 Business Data Connectivity Model project template.

For the purposes of this article, the SQL Server data source type was used to create an ECT for a database table. Then the ECT was used to create an External List. Figure 1 shows the resulting “Customers From CRM” ECT after completing the configuration in SharePoint Designer.

image: ECT Configuration for Accessing External CRM Data

Figure 1 ECT Configuration for Accessing External CRM Data

There are a couple things to call out here. First, notice in the External Content Type Information panel that the Office Item Type property value is set to Contact. During the configuration process, you can map the external data fields to a corresponding Office item type like Contact. This isn’t a requirement, but because the name and address data from the CRM database can be mapped nicely to an Outlook Contact, this designation was chosen. You’ll be able to use the result of this configuration option in Outlook later.

Second, notice in the External Content Type Operations panel that full CRUD methods have been enabled for this ECT. This was due to the selections made in the configuration wizard. However, there certainly may be business reasons to limit the LOB system operations to read-only. In that case, you can simply select the Read List and Read Item operations during configuration. These are the only two operations required to create an ECT.

Once the ECT is created, it’s a simple step to create an external list from it. You can do this by creating a new external list from within SharePoint or SharePoint Designer.

SharePoint Standard Lists

Of course, you can employ standard SharePoint lists to display business data. For example, say your department manages training-course content. You maintain two SharePoint lists: Course Category and Course. These lists contain the course information that employees on other teams use to create customer correspondence, brochures or advertising campaigns. So the data is maintained by a small team, but must be readily available for use by many people across the company.

SharePoint 2010 has a new capability whereby lookups form relationships between lists. When creating a new column on a list, one of the options is to make the column a lookup type, then indicate another list within the site as its source. SharePoint supports single-value lookups for one-to-many relationships or multi-value lookups for many-to-many relationships. If you choose, SharePoint will also maintain referential integrity between the lists supporting restricted or cascading deletes. This provides a number of options in how you set up and use lists in SharePoint.

Going back to our example, you could easily create a Course list lookup column named Category that’s sourced from the Course Category list as shown in Figure 2.

image: Using a Lookup List to Source Course Category Data

Figure 2 Using a Lookup List to Source Course Category Data

Bringing SharePoint List Data to Office

So far, we’ve looked at how to surface external data as SharePoint lists using the new BCS features in SharePoint 2010. Users can access the data via the browser on a PC or a mobile device, but users will probably appreciate the rich experience of the full Office client application. Let’s now turn our attention to using the SharePoint list data on the client in two ways. First, we’ll see how you can access data without writing any code by employing SharePoint Workspace and Outlook.

When developing our sample CRM solution, there are two Connect & Export buttons in the SharePoint ribbon for the external customers list: Sync to SharePoint Workspace and Connect to Outlook (see Figure 3). If SharePoint Workspace 2010 is installed on the client computer, Sync to SharePoint Workspace lets you synchronize lists and document libraries to the client with a single click. A local cached copy of the content is then available to the user in SharePoint Workspace whether the user is online or offline. When the user is in an offline state and modifies a list item or document and saves it locally, the list item or document will be synchronized with SharePoint automatically when the user is back online again.

image: Connect & Export Options in the SharePoint Ribbon

Figure 3 Connect & Export Options in the SharePoint Ribbon

This is a no-code-required solution. Data is made accessible in the SharePoint Workspace client application shown in Figure 4. And because full CRUD methods were defined in the ECT, any changes made to the customer data in SharePoint Workspace will be updated in the CRM database as well.

image: Accessing External List Data in a SharePoint Workspace

Figure 4 Accessing External List Data in a SharePoint Workspace

Because we mapped the CRM database fields to the Contact Office item type during ECT configuration, SharePoint can provide our external list data to Outlook as native Contact Items. By clicking the Connect to Outlook button on the ribbon, SharePoint will synchronize this external list directly to Outlook. Again, no code required, with SharePoint data landing in the Office client.

Using the REST API

No-code solutions, such as those enabled through SharePoint Workspaces and Outlook list connectivity, are great, but there are some user experiences that require a more-customized solution. To accommodate these, we need to provide access to the list data in the Office applications in a way that permits us to further tailor the solution.

Possibly one of the easiest ways for a developer to access SharePoint list and document library data is via the new REST API (listdata.svc). Most of the data in SharePoint is exposed as a RESTful endpoint. The standard location for SharePoint services is _vti_bin, so if you simply type into your browser the URL to your site and append /_vti_bin.listdata.svc, you will get back a standard ATOM services document that describes the collections available on the site (see Figure 5).

image: ATOM Services Document

Figure 5 ATOM Services Document

Notice that the Course and CourseCategory lists are present. By further appending /Course to the URL, you can retrieve all the courses in the list or you can retrieve any one specific course by appending a number. For example, this will return the third course:


You can do more advanced queries by appending the following property filter:


But an advanced query that’s important here is one that can return the Courses with their associated CourseCategory data. By appending the following to the site URL, you can retrieve the combined structure of Course and CourseCategory in a single payload:


You’ll see this implemented in a Word add-in in the next section.

Building a Word Add-In

Once you know how to leverage the REST APIs to acquire access to the data, you can surface the data in the client applications where users have a rich authoring experience. For this example, we’ll build a Word add-in and present this data to the user in a meaningful way. This application will have a dropdown list for the course categories, a listbox that loads with courses corresponding to the category selection and a button to insert text about the course into the Word document.

In Visual Studio 2010, create a new Office 2010 Word add-in project in C#.

Now add a new service data source. On the Add Service Reference panel in the wizard, enter the URL for your SharePoint site and append /_vti_bin/listdata.svc to it. For example:


After entering the URL, click Go. This retrieves the metadata for the SharePoint site. When you click OK, WCF Data Services will generate strongly typed classes for you by using the Entity Framework. This completely abstracts away the fact that the data source is SharePoint or an OData producer that provides data via the Open Data Protocol. From this point forward, you simply work with the data as familiar .NET classes.

For the UI, you will create a custom task pane, which provides a UI in Office applications that can be docked on the top, bottom, left or right of the application. Task panes can have Windows Forms controls added to them, including the Windows Presentation Foundation (WPF) user control that will be used here.

Add a WPF user control to the project using the Add New Item dialog and name it CoursePicker. When the designer opens, replace the Grid element with the XAML snippet shown in Figure 6. This simply adds the ComboBox, Button and ListBox and sets some properties. You will add a couple events later.

Figure 6 Word Add-In UI Markup

    Name="cboCategoryLookup" Width="180" Margin="5" 
    HorizontalAlignment="Center" IsEditable="False" 
    SelectedValuePath="CategoryName" />
  <Button Name="button1" 
    Content="Insert Course Information" Margin="5" />
  <ListBox Name="courseListBox" ItemsSource="{Binding}">
          <StackPanel Orientation="Horizontal">
            <TextBlock Text="{Binding Path=CourseID}" 
              FontWeight="Bold" />
            <TextBlock Text=": " FontWeight="Bold" />
            <TextBlock Text="{Binding Path=Name}" />
          <TextBlock Text="{Binding Path=Description}" 
            Margin="5 0 0 0" />

Open the CoursePicker.xaml.cs file. Immediately following the namespace, you’ll add two using statements, one for your service reference, ServiceReference1, and one for System.Net:

namespace Conf_DS {
    using ServiceReference1;
    using System.Net;

In the CoursePicker Class, the first order of business is to instantiate the data context object. Here, you pass in the URL to your site, again appended by the _vti_bin/listdata.svc designation:

public partial class CoursePicker : UserControl {
  Office2010DemoDataContext dc = new Office2010DemoDataContext(
    new Uri("https://intranet.contoso.com/sites/spc/_vti_bin/listdata.svc"));

Next you’ll have a List class-level variable to cache the retrieved course items and save round-trips to the server:

List<CourseItem> courses = null;

The code to retrieve the Courses and CourseCategory data is in the OnInitialized override method. First, you designate your logged-in credentials to pass to the server. Then the course categories are retrieved via the data context object and bound to the category ComboBox. Finally, using the expand option, courses are returned with their associated category and loaded into the courses list object. This will cache the courses locally for better performance:

protected override void OnInitialized(EventArgs e) {
    dc.Credentials = CredentialCache.  
    // Load Category dropdown list
    cboCategoryLookup.DataContext =  
    cboCategoryLookup.SelectedIndex = 0;
    // To cache data locally for courses 
    // Expand to retrieve the Category as well.
    courses = dc.Course.Expand("Category").ToList();

Now you need to add a couple events. Return to the CoursePicker designer and double-click the button to create the button click event. Next, click on the ComboBox and in the properties menu, click the Events tab and double-click the SelectionChanged event. Add code to your SelectionChanged event handler so it looks like this:

private void cboCategoryLookup_SelectionChanged(
  object sender, SelectionChangedEventArgs e) {
  courseListBox.DataContext = from c in courses
    where c.Category.CategoryName == cboCategoryLookup.SelectedValue.ToString()
    orderby c.CourseID
    select c;

Here, a simple LINQ query searches the courses list object (the one loaded with data retrieved using the expand option) to find all the courses that have a category name that matches the name of the course category selected in the ComboBox. It also orders the results to provide a clean user experience.

Finally, add code to the button event handler to cast the selected listbox item into a CourseItem object.  Then you take the various data elements you want to present to the user and place them in the document at the location of the insertion point:

private void button1_Click(
  object sender, RoutedEventArgs e) {
  CourseItem course = (CourseItem)courseListBox.SelectedItem;
    String.Format("{0}: {1} \n{2}\n", course.CourseID, 
    course.Name, course.Description));

And that’s it—really simple code for accessing the data in SharePoint via WCF Data Services.

Now open the ThisAddIn.cs file. This is the main entry point for all add-ins for Office. Here you add the code to instantiate the task pane:

private void ThisAddIn_Startup(object sender, System.EventArgs e) {
  UserControl wpfHost = new UserControl();
  ElementHost host = new ElementHost();
  host.Dock = DockStyle.Fill;
  host.Child = new CoursePicker();
  CustomTaskPanes.Add(wpfHost, "Training Courses").Visible = true;

The CoursePicker WPF user control can’t be directly added to the custom task pane objects collection. It must be hosted in an ElementHost control, which provides the bridge between WPF controls and Windows Forms controls. Notice that the CoursePicker object is added as a child of the ElementHost object and then the ElementHost object is added to the custom task pane object collection. An Office application can have more than one custom task pane installed and available to the user at any given time, so the task pane for this add-in will just be one in the collection. Figure 7 shows the completed add-in.

image: The Word Add-In at Work

Figure 7 The Word Add-In at Work

With the data appearing in the Office application, you can take the solution further by adding code that interacts with the Word APIs. For example, you can add code so that when a user selects a course, the information is inserted and formatted in the document. The Office application APIs are rich and allow you to add more features to your custom solution that can make users even more productive. Next, we’ll see an example of this with Word content controls connected to a client-side SharePoint object model.

Using the Client Object Model

Using the REST APIs to gain access to the data is one among a few options available to you. For example, there are also three new APIs available for SharePoint 2010 that provide a consistent programming model across the JavaScript, .NET managed applications and Silverlight clients. These three client object models interact with SharePoint using a subset of the server object model capabilities and essentially interoperate with SharePoint at the site collection level and below: webs, lists, listitems, content types, fields and external lists. If you’re familiar with the server object model, you’ll be familiar with the client object model.

To demonstrate using the client object model, we’ll use the external list containing the CRM customers to build a document-level Word add-in where the action pane is loaded with the customers. This is a case where you’ll need to use the client object model because the List Data Service doesn’t provide access to external lists. In this example, the user can select a customer and insert his name and address information into content controls in a quote document template.

The previous Course and Category example was an application-level add-in. An application-level Word add-in will be present every time Word is started. Document-level add-ins, however, are bound to a document and will only load if a document of a certain type is opened. In this case, the external customers list will only be presented to the user when working on a quote document.

In Visual Studio, start by creating a new Word 2010 document project. In the wizard, you’ll need to select either a default document or a document that you’ve already saved. In my case, I used a quote document I had already saved. The document opens inside Visual Studio and Word becomes the document designer.

You can use the toolbox to place controls directly on the document surface as you would a Windows Forms application. Here you add Word content controls for the name and address information. These content controls will be populated with data from the user’s customer selection at run time.

To add a content control to the document, select the text on the document that you want to wrap in the content control. Then drag a RichTextContentControl from the Word Controls in the toolbox and drop it on the selected text. Then provide a Name for the control and a Text value in Properties. Do this for customer and company name, address, city and customer ID so your document looks like Figure 8.

image: Creating the Quote Document

Figure 8 Creating the Quote Document

Because the client object model does not provide strongly typed data from the server, you need to add a Customer class to the project. The Customer class will be used to map data returned from the client object model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace CSOM_Quote {
  public class Customer     {
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }

To use the client object model you need to reference Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime.

As with the previous example, data retrieval takes place in the OnIntitialized override method. There are a couple of major differences between coding against the client object model and WCF Data Services. First, the client object model expects that you have familiarity with SharePoint and its structure. With WCF Data Services, that’s abstracted away and you work with the data only. Second, with the client object model, the returned data is not strongly typed. You’re responsible for getting the data into objects that you can use for LINQ queries and data binding.

The data access code is shown in Figure 9. The client context is the central object here. Pass the site URL to create a new instance of the Client Context. Then you can start creating SharePoint objects using the following steps:

  1. Create a site
  2. Create a collection of site lists
  3. Get a list with a specific name
  4. Get all the items in the list
  5. Load the query into the context
  6. Execute the query

Figure 9 CRM Add-In Data Access Code

protected override void OnInitialized(EventArgs e) {
  SPClientOM.ClientContext context =  new ClientContext("https://intranet.contoso.com/sites/spc");
  SPClientOM.Web site = context.Web;
  SPClientOM.ListCollection lists = site.Lists;
  var theBCSList = lists.GetByTitle("Customers");
  SPClientOM.CamlQuery cq = new SPClientOM.CamlQuery();
  IQueryable<SPClientOM.ListItem> bcsListItems =  theBCSList.GetItems(cq);
  bcsList = context.LoadQuery(bcsListItems);
  var bcsCustomerData = 
    from cust in bcsList
    select new Customer {
      CustomerID = cust.FieldValues.ElementAt(1).Value.ToString(),
      ContactName = cust.FieldValues.ElementAt(2).Value.ToString() 
        + " " 
        + cust.FieldValues.ElementAt(3).Value.ToString(),
      CompanyName = cust.FieldValues.ElementAt(4).Value.ToString(),
      Address = cust.FieldValues.ElementAt(5).Value.ToString(),
      City = cust.FieldValues.ElementAt(6).Value.ToString(),  };
  foreach (var x in bcsCustomerData)  {
    Customer tempCustomer = new Customer();
    tempCustomer.CustomerID = x.CustomerID;
    tempCustomer.CompanyName = x.CompanyName;
    tempCustomer.ContactName = x.ContactName;
    tempCustomer.Address = x.Address;
    tempCustomer.City = x.City;
  customerListBox.DataContext = customers;

Before calling the ExecuteQuery method, all the previous statements are queued and then only sent to the server when execute query is called. This way, you’re in control of the bandwidth and payloads. Once the query returns with its results, the remaining code maps the data into a customers list object that can be bound to the customer listbox control. 

A WPF user control is used for this example as well. Because the XAML is similar to the previous example, it isn’t shown here. However, the code to instantiate a document-level action pane rather than an application-level task pane is a bit different, as you can see here:

public partial class ThisDocument {
  private CustomersCRM CustomerActionPane =  new CustomersCRM();
  private void ThisDocument_Startup(object sender, System.EventArgs e) {
    ElementHost host = new ElementHost();
    host.Dock = DockStyle.Fill;
    host.Child = new CustomerPicker();

Notice that the customer picker WPF user control is added to the ElementHost, the ElementHost object is added to the customer action pane controls collection, and then the customer action pane is added to the actions pane controls collection.

The last step is to add the button click event to populate the Word content controls with the appropriate name and address information, as shown in Figure 10.

Figure 10 Adding the Button Click Event to Word Content Controls

private void button1_Click(
  object sender, RoutedEventArgs e) {
  Customer customer = (Customer)customerListBox.SelectedItem;
  Globals.ThisDocument.wccContactName.Text = customer.ContactName;
  Globals.ThisDocument.wccCompanyName.Text = customer.CompanyName;
  Globals.ThisDocument.wccAddress.Text = customer.Address;
  Globals.ThisDocument.wccCity.Text = customer.City;
  Globals.ThisDocument.wccCustomerID.Text = customer.CustomerID;

First, you cast the selected listbox item to a customer object. Then data from the customer object is used to populate the content controls. The results will look like Figure 11.

image: The CRM Add-In Within Word

Figure 11 The CRM Add-In Within Word

Web Services as Social Services

So far you’ve seen a number of ways you can access SharePoint data from Office client applications. The final technique we’ll look at is using Web services. SharePoint offers Web services as the primary way to access SharePoint data remotely. Web services in SharePoint 2010 gives you access to nearly all of the functionality in SharePoint Server. Unlike some of the other data technologies you’ve seen, such as REST and the client object model, Web services covers both accessing data and accessing administrative functionality.

All of the Web services you love are still in there, such as the Lists.asmx and Search.asmx services. SharePoint Web services are implemented as ASP.NET Web services with the .asmx extension, and most of the new services in SharePoint 2010 are also written as ASMX services. This was mainly done to have the broadest compatibility with other products and tools.

A new focus of SharePoint Web services is social services. The center of all social applications is the user. SharePoint has a UserProfileService that allows you to access all of the profile information about a user. UserProfileService includes the standard properties such as name and address, but also includes other properties such as hobbies, skills, schools and colleagues. Colleagues (or friends as they’re called in other public social sites) are a key feature in the SharePoint social structure.

Another important aspect of social applications is what people think about content they encounter. SharePoint has a SocialDataService that enables users to tag, rate and comment on data, documents and pages within your sites.

The third important social aspect of SharePoint is publishing activities and subscribing to activities that your colleagues generate. SharePoint provides an ActivityFeed and APIs to publish activities as a feed.

Because this isn’t an article on the new social features in SharePoint, we won’t go into more detail on these, but they do provide some important context for the examples later in this article. See the SharePoint Developer Center or the “Managing Social Networking with Microsoft Office SharePoint Server 2007” white paper for more details.

Extending Outlook with Web Services

We’ve seen how SharePoint and Office provide a lot of choices when you’re determining the best way to access data for use in Office applications. Another way includes consuming SharePoint Web services. In this example, we’ll create a new Outlook Ribbon that lets you pull all of your SharePoint colleagues into Outlook as contact items. You’re even able to surface the user’s profile picture into Outlook, just as you’re accustomed to seeing with contacts provided by Microsoft Exchange.

Start by creating a new Outlook add-in in Visual Studio 2010. We’re going to write it in C#, but you could use Visual Basic if you prefer. In previous versions, Visual Basic had a slight advantage with support for features such as optional parameters, but C# now supports them, too.

The Ribbon provides a consistent and easy way to interact with all of the Office applications. Outlook 2010 now includes a Ribbon for the main screen. In this example, you’ll add a new Ribbon here. Visual Studio 2010 makes it easy to create Office Ribbons with a visual Ribbon Designer. You can simply drag controls from the toolbox on the left and drop them onto the design surface.

In this example, you just need to set some properties, such as the label for the tab and group. Next add a button control onto the surface. Once you have a button added to your Ribbon group, you can set the size to large and set an image for the button. Your Ribbon will look similar to Figure 12.

image: Creating a New Outlook Ribbon

Figure 12 Creating a New Outlook Ribbon

The last thing to do is set the property to determine when the Ribbon will be displayed. By default, the Ribbon is displayed on the Mail Explorer. This is the window you see when you open a mail item. In this sample, you want the Ribbon to display on the main screen. Select the Ribbon and set the RibbonType property to Microsoft.Outlook.Explorer. You can see there are a number of places where the Ribbon may appear, including the Mail and Contact Explorers.

Next, double-click on your Ribbon button to create a code-behind click event handler. This is the event you’ll use to create the Outlook contact.

You’re now ready to add the code that creates a contact in Outlook. Visual Studio 2010 makes this easy to do. I find it easier to break the problem into multiple smaller parts. First, you created the Outlook add-in, then you created the Ribbon. After each of these steps, make sure you press F5 to compile and run your application. Now you can create an Outlook contact using hard-coded values. After you verify that this is working, you can add the code that calls SharePoint. Again, at each step check that everything is working correctly before moving on to the next step.

Figure 13shows the code to create a new hard-coded contact. This uses the CreateItem method to create a new ContactItem object. Then you can set the properties of the ContactItem and call the Save method to commit the changes.

Figure 13 Boilerplate Code to Create a Contact

Outlook.ContactItem newContact = Globals.ThisAddIn.Application.CreateItem(Outlook.OlItemType.olContactItem);
newContact.FirstName = "Paul";
newContact.LastName = "Stubbs";
newContact.Email1Address = "pstubbs@microsoft.com";
newContact.CompanyName = "Microsoft";
newContact.JobTitle = "Technical Evangelist";
newContact.CustomerID = "123456";
newContact.PrimaryTelephoneNumber = "(425)555-0111";
newContact.MailingAddressStreet = "1 Microsoft Way";
newContact.MailingAddressCity = "Redmond";
newContact.MailingAddressState = "WA";

The only really challenging piece is that the way to set the contact picture is to call the AddPicture method, which takes a path to a picture on disk. This is problematic because you want to pull images from SharePoint. You’ll see how to do this in the next section. Once you verify that the code works and a contact is created in Outlook, you’re ready to call SharePoint and add real contacts.

Employing User Profile Service

UserProfileService is a SharePoint Web service you can use to access profile information, including a list of your colleagues and their profile information. To use this service, start by adding a reference to your project. Because this is a Web service and not a WCF service, you need to click the advanced tab of the Add Service dialog, then click the Add Web Service button. This opens the old Add Web Service dialog that you remember from Visual Studio 2005.

After you add the reference, you can add the code to retrieve your colleagues:

// Instantiate the Web service.
UserProfileService userProfileService = new UserProfileService();
// Use the current user log-on credentials.
userProfileService.Credentials = System.Net.CredentialCache.DefaultCredentials;

This code creates an instance of the service and passes your current credentials to the service. Next, call the GetUserColleagues method passing the user that you want to retrieve colleagues for. This will return an array of ContactData objects:

ContactData[] contacts = userProfileService.GetUserColleagues("contoso\\danj");

We can now loop through all of the ContactData objects that represent profile data for the user’s colleagues in SharePoint. We retrieve the extended properties by calling the GetUserProfileByName method, which returns an array of PropertyData that contains key and value pairs for each colleague:

// Add each Colleague as an Outlook Contact
foreach (ContactData contact in contacts) {
  // Get the users detailed Properties
  PropertyData[] properties = userProfileService.GetUserProfileByName(contact.AccountName);
  // Create a new Outlook Contact
  Outlook.ContactItem newContact = Globals.ThisAddIn.Application.CreateItem(Outlook.OlItemType.olContactItem);

Now we convert those key/value pairs into contact properties:

// Set the Contact Properties
newContact.FullName = contact.Name;
newContact.FirstName = properties[2].Values[0].Value.ToString();
newContact.LastName = properties[4].Values[0].Value.ToString();
newContact.Email1Address = properties[41].Values[0].Value.ToString();

Finally, we grab the contact photo and save the new contact:

// Download the users profile image from SharePoint
SetContactImage(properties, newContact);

The last piece of the puzzle is retrieving the contact’s picture from SharePoint. One of the extended properties includes a path to a thumbnail of the user’s profile picture. You need to download this picture to a temporary file on disk so that the Outlook API can add it to the ContactItem object:

private static void SetContactImage(
  PropertyData[] properties, 
  Outlook.ContactItem newContact){
  // Download image to a temp file
  string userid = properties[16].Values[0].Value.ToString();
  string imageUrl = properties[15].Values[0].Value.ToString();
  string tempImage = string.Format(@"C:\{0}.jpg", userid);
  WebClient Client = new WebClient();
  Client.Credentials = CredentialCache.DefaultCredentials;
  Client.DownloadFile(imageUrl, tempImage);

That’s it! Now you have an Outlook add-in Ribbon that calls SharePoint to pull social data into Outlook contacts. When you run the application, you’ll see a ContactItem populated with SharePoint data, including the user’s profile information and image.

Wrap Up

Now you’ve seen how easy it is to get data from SharePoint into Office clients. We’ve shown you a variety of options from no-code solutions to highly adaptable solutions using C# or Visual Basic.

Employing WCF Data Services to access SharePoint list data provides a common pattern for .NET developers that’s quick and easy to implement. The client object model provides the means to access SharePoint external lists and opens a world of opportunities for bringing LOB data into Office. And, finally, SharePoint Web services enables the most flexible access to data, but also requires a bit more commitment in terms of coding and testing.

Making data in SharePoint available to users as lists is an important step as it enables a great experience in the browser. Taking it a step further, you can leverage a variety of data access options to then bring the data into the Office applications that are familiar to users. Visual Studio 2010 makes all of this much easier to build, debug and deploy. As you can see, these represent some of the new and important development capabilities you can take advantage of with the new product releases.

More training, examples and information can be found online in the Office and SharePoint developer centers.

Donovan Follette  is a Microsoft technical evangelist working with technologies including Active Directory, Lightweight Directory Services and Active Directory Federation Services. He now focuses on Office development and building integrated solutions with SharePoint 2010. Visit his blog at blogs.msdn.com/b/donovanf/.

Paul Stubbs is a Microsoft technical evangelist who focuses on the information worker development community for SharePoint and Office, Silverlight and Web 2.0 social networking. He’s authored three books about solution development with Office, SharePoint and Silverlight. Read his blog at blogs.msdn.com/b/pstubbs/.

*Thanks to the following technical expert for reviewing this article:*John Durant