SharePoint 2010 – Using Business Connectivity Services in SharePoint 2010

Kirk Evans

One of my kids’ favorite books is called If You Give a Mouse a Cookie by Laura Numeroff─a children’s book based on the slippery-slope notion that if you give a mouse a cookie, he will ask for milk, realizing he is thirsty. When he drinks the milk, he will ask for a mirror, then some scissors, then a broom, then some crayons, and on and on, asking for a new item once he realizes the utility of the item he currently has.

This is how I see many users progressing along the adoption timeline with SharePoint: they begin slowly, uploading a few documents, and then progress to using workflows and forms and ultimately wanting to surface external data on their site. It isn’t until you see how easy one task was to accomplish that you decide to go for the next challenge.

It’s a common scenario: I have a portal, I have some external data, and I want to integrate that data into my portal in a meaningful way. Microsoft Office SharePoint Server 2007 (MOSS 2007) introduced the Business Data Catalog (BDC) to facilitate this exact scenario: make it easy to surface external data inside a portal experience. For example, you can create an XML definition file that defines the mapping between SharePoint and your external data so it can be rendered in a Web Part, used as a column within a list and even integrated into search.

When I show companies how they can use BDC to map to existing line-of-business applications, the light bulb usually turns on after I show search results that include people, documents, list data and data from their existing LOB system─all within the same set of results.

Even though the BDC in MOSS 2007 enabled connectivity to external systems, it was difficult to create solutions because of the lack of a designer. Furthermore, though BDC made it relatively easy to create read-only solutions that display data in the Business Data List Web Part, it was not so simple to create a solution that enabled users to make changes and write that data back to the external store.

Business Connectivity Services (BCS) in SharePoint 2010 is all about connecting to external data. BCS enhances the SharePoint platform’s capabilities with out-of-box features, services and tools that streamline development of solutions with deep integration of external data and services. BCS builds upon its BDC predecessor in the key areas of presentation, connectivity, tooling and lifecycle management. For example, in SharePoint 2010 it’s easy to create an external content type with SharePoint Designer, create an external list in SharePoint’s Web user interface and take the list offline into Outlook as a set of contacts. Also, you can make updates to contacts in Outlook that will cause the data in the external system to update as well.

BCS Architecture

Figure 1 demonstrates the key components that comprise BCS.

Business Connectivity Services Architecture

Figure 1 Business Connectivity Services Architecture

  • BDC Metadata Store – The BDC Metadata Store provides storage for a collection of external content types, each of which describes how to connect to the external store. The Metadata Store acts as part of the services layer. External content types are a fundamental building block of BCS.
  • BDC Server Runtime – The BDC Server Runtime understands how to reach into the back-end store and connect to data based on the external content types defined within the content type store. It’s important to note the new usage of the acronym BDC to refer to the set of services that provides connectivity that is a component of BCS.
  • Security – BCS provides integration with the Secure Store Service (SSS), as well as enabling your own security model.
  • Solution Packaging – Solutions built with BCS can be packaged as a Visual Studio Tools for Office (VSTO) package to be delivered to a rich client, including SharePoint Workspace, Outlook and Word. BCS also exposes APIs to extend solution packaging to target additional clients.
  • Out of Box UI – BCS carries forward the ability to display external data through a Web Part UI and provides deeper integration through the addition of external lists.
  • BDC Client Runtime – A symmetrical runtime is provided for client and server, enabling you to take solutions offline with a client-side cache and to connect and push changes back to the server in a consistent manner. Use of the BDC Client Runtime enables offline operations, interacting with the external data cache.
  • Design Tools – SharePoint Designer provides a wealth of out-of-box functionality for creating BCS solutions, including the ability to define external content types and external lists, and to define InfoPath forms to surface the data to create simple solutions. Visual Studio provides the ability for the professional developer to extend those capabilities to create advanced solutions while leveraging the existing framework.

These investments in the BCS architecture enable interaction with additional capabilities in the SharePoint platform, including business intelligence, enterprise content management, collaboration and social features, and enterprise search. Additionally, the use of BCS provides a compelling developer platform that enables the creation of solutions to quickly take advantage of these capabilities.

The first time I saw BCS, two things immediately jumped out at me. First, from a developer’s perspective, it’s now incredibly easy to take external data offline through smart-client solutions that are easily distributed due to the generation of VSTO add-ins that leverage the ClickOnce capabilities in the Microsoft .NET Framework. The second thing that really stood out is the inclusion of a client-side cache that leverages SQL Compact as its durable store and provides the ability to sync changes to and from the client-side solution with the external system. This should provide a strong sense of reliability to developers because it leverages proven and widely adopted technologies.

Once you recognize that BCS essentially provides connectivity to external data, a symmetrical client and server runtime to interact with the data, and packaging for BCS solutions to be used with smart-client applications, you can immediately see solutions where you can integrate with existing systems and quickly provide rich capabilities to end users.

It should also be apparent to anyone familiar with its predecessor that BCS encompasses more scenarios than simply connecting to data, making it a very compelling choice for developers to enable solutions. The remainder of this article will discuss the creation of external content types and the offline capabilities of BCS in more detail.

Understanding External Content Types

The concept of external content types is fundamental to BCS. Windows SharePoint Services 3 introduced the concept of a content type to describe a reusable schema for lists. An external content type extends this notion by describing both the schema as well as the data access capabilities of an external system and its behavior within Office and SharePoint. You might think of an external content type as the mapping between SharePoint and the external system, describing both the entities and behaviors of the data source.

An external content type is defined using metadata in an XML grammar that is deployed to the BDC Metadata Store. For those familiar with the BDC in MOSS 2007, this concept was formerly known as a BDC Entity, but the concept has been expanded to include how the external data behaves within SharePoint and Office.

SharePoint Designer 2010 offers a simple interface to quickly create external content types and to create external lists based on those external content types. Why, then, would you use Visual Studio to create an external content type?  Sometimes you need to do something that SharePoint Designer 2010 doesn’t provide out of the box. Creating custom external content types is useful in aggregation scenarios where you make multiple calls to the same external system, make multiple calls to multiple external systems or even read from one external system and write to another. Creating custom external content types also is useful for complex transformations, where there is no clear mapping between the format of the data in the external system and how it should be presented within SharePoint. You might implement custom security code or even implement custom business logic needed to interact with a complex backend system.

IT professionals can use SharePoint Designer 2010 to create an external content type that consumes and writes data to SQL Server, a Windows Communication Foundation (WCF) Service or a .NET type. This enables the creation of simple solutions quickly by IT professionals, allowing them to consume data from sources that developers have exposed. As an example, an IT professional would open SharePoint Designer 2010, click External Content Types in the navigation pane on the left, click the New External Content Type button in the ribbon, and click the “Click here to discover external data sources and define operations” link. In the resulting Operation Designer dialog, you can add a connection to a database table, then right-click the table to “Create All Operations” to create methods to read, select a single record, update and even delete rows from a database table (providing you have adequate credentials to do this, of course). To see what this looks like, see Figure 2.

Creating Operations Using SharePoint Designer 2010

Figure 2 Creating Operations Using SharePoint Designer 2010

An external content type is really a reusable description of external data to allow it to participate as a native Office entity across multiple applications such as:

  • SharePoint as external list
  • Outlook
  • SharePoint workspace
  • Word
  • InfoPath
  • Access
  • Other Office applications via code

This is a hugely powerful capability, enabling knowledge workers to connect SharePoint external systems and to surface data in a readable and writeable fashion through multiple types of client applications. This also provides a very interesting opportunity for developers to empower knowledge workers with solutions that perform complex operations not possible when using SharePoint Designer 2010 by itself, such as aggregating data from multiple sources, providing complex transformations, evaluating complex business logic or custom security requirements, or calling systems multiple times in order to provide a single view of data.

As you work with the BCS framework and read various documentation or articles on BCS, you’ll probably notice the ambiguous use of the terms external content type and entity. They are one and the same. The term entity existed in the SharePoint 2007 environment and still exists at the runtime API level. This term is generally used in environments that cater primarily to developers, as opposed to the use of external content type in environments such as SharePoint Designer 2010 that cater to multiple audiences. Don’t get confused when the tools or documentation seem to switch between external content type and entity─they mean the same thing.

Creating an Entity with Visual Studio 2010

Let’s look at an aggregation scenario to better understand the role of an external content type from a developer’s perspective. We will use the Twitter API as an example of a RESTful service that exposes data about customers, and we’ll use a custom database that mimics a customer relationship management (CRM) system to include additional information about our customers such as last purchase date and last purchase amount. We will aggregate the data from these two external sources together and will present that data in a SharePoint list transparently to the end user.

In the New Project dialog in Visual Studio 2010, choose “Business Data Connectivity Model” and use the project name “Msdn.Samples.BCS”. The new project wizard will ask you to choose a SharePoint site to use for debugging. Finally, Visual Studio 2010 stubs out code for an entity, an entity service, the model definition and a diagram file used to support the Visual Studio design surface.

The Entity1.cs file describes the schema for the entity and is modeled as a typical class with public properties.  The Entity1Service.cs file provides the implementation for CRUD (Create, Read, Update, Delete) methods, of which two methods are created for you as examples. The first method created is ReadItem, which allows you to retrieve a specific record from the external store based on an identifier. This is mapped to the XML metadata as a method instance of type “SpecificFinder.” The second method that is created is ReadList, which retrieves all records from the external store. This is mapped in the XML metadata as a “Finder” method instance. These are the minimum two methods that your entity needs to implement in order to serve as a connector for BCS.

code samples deleted at this point

Creating a List and Taking It Offline

So far, we have only created the external content type. We have not yet created a list to represent an instance of the external content type. Because we hit F5 to deploy our external content type in the previous step, you should now be looking at your SharePoint site that the debugger is attached to. In the Site Actions menu in the top left of the screen, click the “View All Site Content” option. Once the viewlsts.aspx page is displayed, click the “Create” link that will bring up a new dialog that allows you to create several different types of content. Choose External List from the set of installed items and click the Create button. In the resulting page, you can provide the Name for the new list, and choose an existing external content type from a list (see Figure 8).

Creating a List Using an External Content Type

Figure 8 Creating a List Using an External Content Type

After you press the Create button, you should finally be greeted with a list in SharePoint that displays the fruits of your labors as a list of data in SharePoint (see Figure 9).

A List Built Using an External Content Type

Figure 9 A List Built Using an External Content Type

Taking the List Offline

One of the huge improvements for external connectivity in SharePoint 2010 is the ability to take lists offline and sync the data. The next step is to open SharePoint Designer 2010 so we can see how the developer and IT professional roles work together in the creation of external content type solutions.

As we saw at the beginning of the article, a knowledge worker can use SharePoint Designer 2010 to create or consume external content types. Open your SharePoint site with SharePoint Designer 2010 and click on the External Content Types link in the navigation pane. You will see our Msdn.Samples.BCS.SocialModel.Customer type listed as one of the external content types to choose from. Click that link and you are taken to the summary view for our external content type where we can see the methods we defined in metadata and implemented in code.

In the External Content Type Information section of this page, you will see a drop-down for specifying the Office Item Type. Click this drop-down to reveal the following options:

  • Generic List
  • Appointment
  • Contact
  • Task
  • Post

This list allows you to represent the data in an external content type as one of these types, which will in turn influence how the data is displayed and synchronized. For example, selecting the Contact type lets you take the list offline into Outlook, represented as a list of contacts. Similarly, if your data looks like a calendar item, you can model it as an appointment in Outlook. This provides strong integration between Outlook as an offline client for SharePoint data, while also providing the ability to update data and synchronize it back to the external data source. To map your data as a contact in Outlook, change the Office Item Type drop down to Contact. This will generate a new warning telling us that the mappings are incorrect. Double-click the GetCustomer method to display the mapping dialog. Click Next until you come to the Return Parameter Configuration screen. The minimal requirement for mapping a Contact type is to map the last name parameter. To do this, click the LastName property in the Data Source Elements tree view, and on the right change the Office Property to the LastName property. While we are here, map the FirstName property to the FirstName Office Property. The results are shown in Figure 10.

Mapping Data Source Elements to Office Properties

Figure 10 Mapping Data Source Elements to Office Properties

Once we’ve provided the mapping to an Office type, we can go back to the Web UI for SharePoint, click the Lists section in the ribbon UI, and choose “Connect to Outlook.” This will make a request to SharePoint to create a VSTO package for this list that is installed to the local desktop as an Outlook add-in (see Figure 11).

ClickOnce is Used to Provide an Offline Experience for External Content Types

Figure 11 ClickOnce is Used to Provide an Offline Experience for External Content Types

Once the VSTO package is installed, the end user opens up the contact item just like any other contact item in Outlook, and the first and last name properties are mapped accordingly as you can see in Figure 12.

The Data from our External Entity is Available in Outlook, with Custom Field Data Preserved in a Pane at the Bottom

Figure 12 The Data from our External Entity is Available in Outlook, with Custom Field Data Preserved in a Pane at the Bottom

Additionally, the extra data is preserved and displayed on the contact form as well, providing an easy place to make updates and save them to the local cache to be synchronized with the external system at a later point. This VSTO package is delivered via ClickOnce, so you get the same benefits of using ClickOnce technology, including the ability to uninstall the add-in using the Add/Remove programs dialog in Windows.

Endless Possibilities

The BCS capabilities in SharePoint 2010 are so many that I could easily write an entire book on the subject. I found it difficult to pick just a few topics to highlight in this article. I introduced a lot of topics and didn’t even start to cover the many other possible areas, such as supplying your own forms using InfoPath to be displayed in SharePoint Workspace, accessing BCS data through the BDC Client Runtime, how you might develop your own VSTO add-in to bring BCS capabilities to Excel 2010, using claims-based security with BCS, or integrating with the Secure Store Service.

There are so many possibilities, I am sure that BCS will serve as the topic for many articles and books to come over the next few years. Just like when you give a mouse a cookie, once you start with BCS, you’ll want to do more and more with this fascinating technology.

Kirk Evans is an Industry Architect for Microsoft’s Communications Sector practice. He works with the largest media companies, telcos, publishers and advertising agencies to create industry-specific solutions. Visit Kirk’s blog at

Thanks to the following technical experts for reviewing this article: Lionel Robinson and Brad Stevenson