Taking the BCS list offline in Sharepoint 2010

After you’ve created and secured your external list, SharePoint supports taking the list offline so that you can program against the cached data on the client from your external data source. BCS also offers a client-side object model to allow you to program against the cached data once you’ve taken it offline. in this post we’ll take the external list MyCustomers that we created offline and expose the list in SharePoint Workspace.

Here are few easy steps for how to do that

1. Open the MyCustomers list external list.
2. Click the List tab on the SharePoint ribbon. Note that in the “Connect & Export” group, you have different options available to you (for example, to sync with Outlook or SharePoint Workspace) to take the external list offline.
3. Click the “Sync to SharePoint Workspace” button, and you’ll be prompted to accept the Installation of a file to your local machine. Click Install to initiate the VSTO Package installation on your machine.
4. After the client-side package is installed, you will be prompted with a dialog that indicates the Office Customization was successfully installed. You can then open SharePoint Workspace to view the external list offline in SharePoint Workspace.

Programmatically Create Relation between lists in Sharepoint 2010

In this we will create two lists in SharePoint 2010 and setup the Relation between the lists by adding the lookup column. Please note that the belwo example is a console application.

string lookupFieldName = “RelatedField”;

using (SPSite site = new SPSite(“http://SpSite”))
using (SPWeb web = site.OpenWeb())
SPListCollection lists = web.Lists;
//Creating Parent list
Guid SourceListId = lists.Add(“Parent List”,”", SPListTemplateType.GenericList);
Console.WriteLine(“Parent List Done…”);

//Creating Child List
Guid TargetListId = lists.Add(“Child List”,”"
Console.WriteLine(“Child List Done…”);

SPList SourceList = lists[SourceListId];
SPList TargetList = lists[TargetListId];

SPFieldCollection Fields = TargetList.Fields;
//Adding a lookup Field in Child List
Fields.AddLookup(lookupFieldName, SourceList.ID, true);
Console.WriteLine(“Lookup Field Created”);

//Adding the Restrict behaviour
SPFieldLookup NewLookupField = Fields[lookupFieldName] as SPFieldLookup;
NewLookupField.Indexed = true;
NewLookupField.LookupField = “Title”;
NewLookupField.RelationshipDeleteBehavior = SPRelationshipDeleteBehavior.Restrict;
Console.WriteLine(“Lookup field integrity enforced”);

//Adding Items in the List
SPListItem NewSourceItem = SourceList.Items.Add();
NewSourceItem["Title"] = “Parent Data”;
Console.WriteLine(“Source listitem created”);

SPListItem NewTargetItem = TargetList.Items.Add();
NewTargetItem["Title"] = “Child Data”;
NewTargetItem[lookupFieldName] = new SPFieldLookupValue(1, “Source Data”);
Console.WriteLine(“Parent listitem created”);

User Profile Synchronization Service Hanged at “Starting” in Sharepoint 2010

A lot of people have mentioned their user profile Synchronization service getting stuck on ‘starting’. You could try the below steps to come of the situation.

Firstly, stop the User Profile Synchronization service which was stuck at a status of ‘starting. To do this we will use our big boss PowerShell.
1. Get the GUID of the Profile Synchronization Service application using get-spserviceinstance . It will probably show a status of ‘provisioning’ in PowerShell.
2. Use cmdlet stop-spserviceinstance [Servuce-guid] to stop the Synchronization service.

Now, to troubleshoot the issue lets look at some things

1. After starting the User Profile Synchronization service, wait for 5-10 minutes before proceeding to the next step.

2. Run services.msc and verify that the Forefront Identity Manager Synchronization Service and the Forefront Identity Manager Service are running.These services are started automatically when the User Profile Synchronization service is started. It may take up to 10 minutes for these to start after starting the User Profile Synchronization Service. Do not start them manually. If they are started then stop the service using powershell and proceed to the next step.

2. Confirm that the user account performing this procedure is a member of the Farm Administrators SharePoint group. If you are using a Windows Server 2003 AD DS forest, the Service Administrator account must be a member of the Pre-Windows 2000 Compatible Access group for the domain with which you are synchronizing. Now restart the service.

Lastly, If Central Administration and the User Profile Synchronization service are provisioned on the same server, you will need to run IISReset on that server.

How to Check if file is attached to a list item in sharepoint 2010

A quick way to check if the user has attached a file to a list item that they just entered, is by using the PreSaveAction that runs on the “Save” button of the List item.
So whenever a user creates or edits and list item a javascript or jquery code can run in PreSaveAction() to check if the user has attached a document to the list item.

In code words here is how you do it -

//The below code uses jQuery Library for SharePoint Web Services and needs to be added into NewForm or Editform.aspx in a content editor webpart.

<script type=”text/javascript”>// <![CDATA[
var isAttached=0;

function PreSaveAction()
$(“input[name^='fileupload']“).each(function() {
if ($(this).val() != “”)

alert(‘File is attached.’);
else if(isAttached==0)
alert(‘No File is attached’);
// ]]></script>

Rich text editor (RTE) and PreSaveAction() in Sharepoint 2010/2007

As you may know PreSaveAction() is manly used to validate controls before the item is saved( new item or edit). Last week we had some requirement where we needed to get some values from the Rich text box on the save of NewForm.aspx page and parse through all the urls in the Rich text box’s text value.

In that case what we did was to execute our code in the PreSaveAction() function to get and parse through the RichTextbox’s value. Firstly, to find all the Html controls on the page we will use a handy function “getTagFromIdentifier“.
Some details about this function from this article are below.

This function finds the HTML element rendered by a given SharePoint FormField control. It takes the following parameters:

tagName – The name of the tag rendered in the form’s HTML
identifier – The string associated with the SharePoint type of the relevant field
title – The value of the relevant HTML tag’s “title” attribute, which also matches the field’s display name

Here’s a partial table of SharePoint column types and their corresponding “identifiers” and “tagNames”:

SharePoint Field Type       identifier       tagName

Single Line of Text                                           TextField                              input

Multiple Lines of Text                                      TextField                              input

Number                                                            TextField                                 input

Currency                                                          TextField                                 input

Choice (dropdown)                                 DropDownChoice                      select

Lookup (single)*                                            Lookup                                  select

So using this Function and the PreSaveAction() we wrote something like below to get to our RTe’s value

function PreSaveAction()
var _body = getTagFromIdentifierAndTitle(“input”,”TextField”,”Body”);
var _body = RTE_GetEditorDocument(_body.id);
var _bodyText = _body.body.innerText;
return true;

function getTagFromIdentifierAndTitle(tagName, identifier, title)
var len = identifier.length;
var tags = document.getElementsByTagName(tagName);
for (var i=0; i < tags.length; i++)
var tempString = tags[i].id;
if (tags[i].title == title && (identifier == “” || tempString.indexOf(identifier) == tempString.length – len)) {
return tags[i];
return null;

How to Get current loggedin user in Sharepoint using Javascript

This a script that gets the current logged-in user’s name from the welcome menu and populates it in a People Picker control.

Firstly, to run the function on page sharepoint load user spBodyOnLoadFunctionNames something like below.


Next add this script in any content editor webpart or just on your custom page.

function AddCurrentUserToPP()
var currentUser = GetUser();
if(currentUser != null)
var pp = GetPeoplePickerText(document.getElementById(‘PeoplePickerId’).parentNode);

//set the People picker field to current user
if(pp != null)
pp.innerText = currentUser;

function GetUser()
var tags = document.getElementsByTagName(‘a’);
for (var i=0; i < tags.length; i++)
if(tags[i].innerText.substr(0,7) == ‘Welcome’)
return tags[i].innerText.substr(8,tags[i].innerText.length);


function GetPeoplePickerText(container)
var result = “”;

var _divs = container.getElementsByTagName(“DIV”);
for(var k=0; k < divs.length; k++)
if(divs[k].id.indexOf(identifier) > 0 && divs[k].id.indexOf(“UserField_upLevelDiv”) > 0)
result = divs[k];
// You can also access the text in the control by using the below
var innerSpans = divs[k].getElementsByTagName(“SPAN”);
for(var j=0; j < innerSpans.length; j++)
{ if(innerSpans[j].id == ‘content’)
{ return innerSpans[j].innerHTML;}

return result;

WSDL is not available by default for ASMX Web Services deployed to _VTI_BIN

Some SharePoint solution might require creating custom ASMX Web Services that operates within the context of SharePoint 2010, and there is a good description of how to create and deploy ASMX Web Service for SharePoint http://msdn.microsoft.com/en-us/library/ms464040.aspx.

However, after Web Service is built and deployed to _vti_bin folder you might experience one issue that <service>.asmx/?WSDL returns "<404> Page not found" error. This error is only specific to the _vti_bin folder, and service returns ?WSLD description if deployed to other folders (for example to _layouts).

The fix is adding "<protocols><add name="Documentation"/></protocols>" to the WebServices element of web.config of webservice.

Optimize deployment of multiple WSPs

In the highly customized SharePoint solution, you might end up with a large number of WSP packages (10-15 for example) that are deployed as separate packages. There is nothing wrong with such approach for a production deployment, but deploying number of WSP again and again for debuging/testing in a development environment might be cumbersome (regardless if they are visible or hidden features).

Such behavior is specific for WSP deployment, when SharePoint recycles application pools during deployment. In such situation, deployment of 10-15 packages is a time consuming process. This behavior can be improved by adding "ResetWebServer="FALSE" to the solution manifest, preventing IIS pool recycling. However, it won't work for upgrade scenarios (http://msdn.microsoft.com/en-us/library/aa544500.aspx).

To improve the deployment time of multiple WSP packages you could use "container" approach, packaging all WSPs in one single package to deploy it once, decreasing deployment time significantly.

For example, create a solution with 5 projects - three webpart projects, one list definition and one workflow project. Then, create empty SharePoint project (named "MyProduct") that will serve as "container". Navigated to Package.package, and add there the solution items we created before :

Optimize deployment of multiple WSPs

Sync Your SharePoint 2010/2007 Contacts with Outlook

In the article you will see how easy it is to leverage the Social Web Services in SharePoint in your Outlook Add-Ins. In particular I show you how to call the User Profile Services to pull your colleagues from SharePoint and create Outlook contacts.
In 3 Solutions for Accessing SharePoint 2010 Data in Office 2010 you will walkthrough the details on how everything works. Here is a visual walkthrough of the solution.

From SharePoint

Sync Your SharePoint 2010/2007 Contacts with Outlook

To Outlook

Sync Your SharePoint 2010/2007 Contacts with Outlook

Contact Details

Sync Your SharePoint 2010/2007 Contacts with Outlook

Build a Custom Outlook Ribbon

Sync Your SharePoint 2010/2007 Contacts with Outlook

The Code

private void button1_Click(object sender, RibbonControlEventArgs e) 


//Instantiate the Web service.

UserProfileService userProfileService =

new UserProfileService();

//Use the current user log-on credentials.

userProfileService.Credentials =


//Get My Colleagues

ContactData[] contacts =



//Add each Colleague as an Outlook Contact

foreach (ContactData contact in contacts)


//Get the users detailed Properties

PropertyData[] properties =


//Create a new Outlook Contact

Outlook.ContactItem newContact =



//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();

newContact.Department = properties[9].Values[0].Value.ToString();

newContact.JobTitle = properties[10].Values[0].Value.ToString();

newContact.CustomerID = properties[0].Values[0].Value.ToString();

newContact.PrimaryTelephoneNumber = properties[8].Values[0].Value.ToString();

//Notes field

newContact.Body = properties[13].Values[0].Value.ToString();

//Download the users profile image from SharePoint

SetContactImage(properties, newContact);




You can learn more about SharePoint’s Social Data features here on MSDN. This provides guidance for programmability issues related to user profiles and social data in Microsoft SharePoint Server 2010. In addition, this includes topics that offer step-by-step, how-to procedures for programming with user profiles and audiences.

Note: This article come from MSDN Blog

3 Solutions for Accessing SharePoint 2010 Data in Office 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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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).

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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:

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:

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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("http://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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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.

3 Solutions for Accessing SharePoint 2010 Data in Office 2010

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 13 shows 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.

This article come from MSDN.