December 2009

Volume 24 Number 12

SharePoint 2010 and BCS - Using Business Connectivity Services in SharePoint 2010

By Kirk Evans | December 2009

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 1demonstrates the key components that comprise BCS.

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.

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 typein 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 typeand 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.

Modeling the Entity

Let’s start by making changes to the entity itself. Replace the Entity class with the code shown in Figure 3.

Figure 3 Replacement Code for Entity Class

namespace Msdn.Samples.BCS.SocialModel
{    
public partial class Customer
    {        
public int CustomerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }        
//Twitter specific properties
public string TwitterScreenName { get; set; }             
public string TwitterStatus { get; set; }
//CRM specific properties
public DateTime DateLastPurchase { get; set; }
public decimal AmountLastPurchase { get; set; }
    }
}

Switch back to the entity design surface by opening the .bdcm file in the Solution Explorer pane and notice that replacing the code doesn’t affect the entity on the design surface. The entity on the design surface represents the metadata. It’s up to us to provide some of the mapping between our code and the metadata that SharePoint expects. Change the entity name to “Customer” in the modeling design surface. Next, click on the Identifier1 entity identifier, rename it to CustomerID and change its type to System.Int32. We’ve now instructed SharePoint that our Customer entity has an identifier called CustomerID that is an integer, and this entity maps to our Customer class.

Modeling the Methods

Now that we’ve modeled our entity, we need to model its methods. As mentioned earlier, Visual Studio creates two methods for us to provide minimum functionality called ReadItem and ReadList. I found it easier to first delete both of those methods from the BDC Method Details pane and then create new methods with the desired names in their place. This gives you a clean slate to work with to see how the mapping is performed. To do this, open the BDC Method Details pane, click on the ReadList method and click the delete button. Do the same for ReadItem. A note of caution: I have found that you do not want to perform these operations in the entity designer itself, instead being careful to make edits through the BDC Method Details pane or the BDC Explorer pane whenever possible.

We’ll start by modeling the SpecificFinder method, which SharePoint uses to retrieve a list of all items in our list. Now that you have a clean slate to work with, click the “” text in the BDC Method Details pane. Clicking that text will present a drop-down arrow, and clicking it again will present a list of method types to create. Choose the “Create Specific Finder Method” option and it will create a method called ReadItem, its parameters and a method instance. In the same pane, notice that there are three columns: Name, Direction and Type Descriptor, and the return parameter has a Type Descriptor called Customer. Click the Customer type descriptor and look in the properties pane to see that the Type Name property is currently System.String. We need to tell SharePoint that it will return our new Customer type, so change this value to “Msdn.Samples.BCS.Customer, Msdn.Samples.BCS”.

This is a good time to point out the BDC Explorer Pane. This pane provides a hierarchical view of our model, its entities and their methods. Expand the ReadItem method and you will see the “customer” parameter with a child node “Customer” representing the type descriptor. We need to add additional type descriptors as a child to represent our complex type. This is a simple operation, just right-click the “Customer” type descriptor in the BDC Explorer and choose “Add Type Descriptor.” Change the name to CustomerID and its type to System.Int32. Repeat this process, adding type descriptors with the appropriate name and type to reflect the Customer class created earlier. Once you have created all of the type descriptors, click on the CustomerID type descriptor and set its Identifier property to CustomerID and its Read-only property to True. The final product should look like Figure 4.

Figure 4 Using the BDC Explorer Pane to Set Properties of the TypeDescriptors

The next step is to create the method that will return the full list of customers. Since we have already done the work to define the Customer type and its type descriptors, the next step is easy. Go back to the BDC Method Details pane and click the text to “” and select the “Create Finder Method” option. A new method called “ReadList” is created, and its type is a generic list of Customer objects. Furthermore, the type descriptors are already defined for us, so now we can focus on implementing these methods.

Implementing the Methods

Our scenario requires us to aggregate data from an online source—Twitter—and a separate store—a SQL Server database. I implemented a helper class called TwitterHelper to aid in obtaining data from Twitter and stuffing it into our Customer entity type. The GetChannelWithCredentials method helps us to use WCF and its Web programming capabilities to easily call Twitter using a specific set of credentials. The GetCustomersFromTwitter method returns a list of users and their current status and stores the data in a list of Customer objects. The GetCustomerFromTwitterByScreenName method retrieves a single user from Twitter based on the user’s screen name and returns that data in a single Customer object. The code for the TwitterHelper is shown in Figure 5.

Figure 5 The TwitterHelper Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.ServiceModel.Channels;
using System.Xml.Linq;
namespace Msdn.Samples.BCS.SocialModel
{
    [ServiceContract]
public interface ITwitterFriends
    {
        [OperationContract]
        [WebGet(UriTemplate="/statuses/friends.xml")]
        Message GetAllFriends();
        [OperationContract]
        [WebGet(UriTemplate = "/users/show.xml?screen_name={screenName}")]
        Message GetFriendByID(string screenName);
    }
public class TwitterHelper
    {
public static List<Customer> GetCustomersFromTwitter()
        {
var cf = GetChannelWithCredentials();
using(cf)
            {
                ITwitterFriends proxy = cf.CreateChannel();
                Message m = proxy.GetAllFriends();
List<Customer> customers =
                        (from user in XDocument.Load(m.GetReaderAtBodyContents()).Root.Elements("user")
select new Customer
                         {
                             TwitterScreenName = user.Element("screen_name").Value, 
                             TwitterStatus = user.Element("status").Element("text").Value
                         }).ToList();
return customers;
            }
        }
public static Customer GetCustomerFromTwitterByScreenName(string screenName)
        {
var cf = GetChannelWithCredentials();
using (cf)
            {
                ITwitterFriends proxy = cf.CreateChannel();
                Message m = proxy.GetFriendByID(screenName);
                XElement user = XDocument.Load(m.GetReaderAtBodyContents()).Root;
                Customer c = new Customer
                {
                    TwitterScreenName = user.Element("screen_name").Value,                    
                    TwitterStatus = user.Element("status").Element("text").Value
                };
return c;
            }
        }
private static WebChannelFactory<ITwitterFriends> GetChannelWithCredentials()
        {
            WebHttpBinding binding = new WebHttpBinding(WebHttpSecurityMode.TransportCredentialOnly);
            binding.MaxReceivedMessageSize = 999999;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Basic;
            binding.Security.Transport.Realm = "Twitter API";
            Uri location = new Uri("https://www.twitter.com");
            WebChannelFactory<ITwitterFriends> cf = new WebChannelFactory<ITwitterFriends>(binding, location);
            cf.Credentials.UserName.UserName = "your_twitter_screenname_here";
            cf.Credentials.UserName.Password = "your_password_here";
return cf;
        }
    }
}

Our database table is quite simple; here is its schema:

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[TwitterScreenName] [nvarchar](100) NOT NULL,
[DateLastPurchase] [date] NOT NULL,
[AmountLastPurchase] [decimal](18,0) NOT NULL)

To query the data easily, I added a LINQ to SQL class into my project and pointed it at the Customer database table to generate a dbml file. My first method, GetCustomer, uses LINQ to SQL to query the database for a single customer record and then obtains the current status for that user from Twitter. The GetCustomer and GetCustomerList methods are detailed in Figure 6. The final step is to press F5 to deploy our external content type and start debugging, enabling you to set breakpoints in the code to ensure that everything is working properly.

Figure 6 The GetCustomer and GetCustomerList Methods

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Msdn.Samples.BCS.SocialModel
{
public partial class CustomerService
    {
private const string CONNECTION_STRING = @"Data Source=moss2010demo\sqlserver;Initial Catalog=CustomerCRM;Integrated Security=True";
public static Customer GetCustomer(int customerID)
        {            
//Pull from database, then pull from Twitter
using (CustomerCRMDataContext db = new CustomerCRMDataContext(CONNECTION_STRING))
            {
                Customer customer = (from c in db.Customers
where c.CustomerID == customerID
select new Customer
                     {
                         CustomerID = c.CustomerID,
                         FirstName = c.FirstName,
                         LastName = c.LastName,
                         AmountLastPurchase = c.AmountLastPurchase,
                         DateLastPurchase = c.DateLastPurchase,
                         TwitterScreenName = c.TwitterScreenName
                     }).FirstOrDefault();
                Customer friend = TwitterHelper.GetCustomerFromTwitterByScreenName(customer.TwitterScreenName);
                customer.TwitterStatus = friend.TwitterStatus;                
return customer;
            };
        }
public static IEnumerable<Customer> GetCustomerList()
        {
//Pull all friends from Twitter, 
// then only return friends that also have CRM records
List<Customer> friends = TwitterHelper.GetCustomersFromTwitter();
List<Customer> customers = new List<Customer>();
using (CustomerCRMDataContext db = new CustomerCRMDataContext(CONNECTION_STRING))
            {
foreach (Customer friend in friends)
                {
var cust = db.Customers.FirstOrDefault(c => c.TwitterScreenName == friend.TwitterScreenName);
if (null != cust)
                    {
                        customers.Add(new Customer
                        {
                            CustomerID = cust.CustomerID,
                            FirstName = cust.FirstName,
                            LastName = cust.LastName,
                            AmountLastPurchase = cust.AmountLastPurchase,
                            DateLastPurchase = cust.DateLastPurchase,
                            TwitterScreenName = cust.TwitterScreenName,
                            TwitterStatus = friend.TwitterStatus
                        });
                    }
                }
return customers;
            };                        
        }
    }
}

An interesting point that I have mentioned already is the ability to make changes in an offline client and synchronize them back to the external data store. This can be done by implementing an Updater method. To do this, simply add a new Updater method to the BDC Method Details pane. A sample body for the Updater method might look like the code shown in Figure 7:

Figure 7 An Update Method to Synchronize Changes

public static void Update(Customer customer)
{
//Update only the CRM database information
using (CustomerCRMDataContext db = new CustomerCRMDataContext(CONNECTION_STRING))
    {
var cust = db.Customers.Single(c => c.CustomerID == customer.CustomerID);
    cust.AmountLastPurchase = customer.AmountLastPurchase;
    cust.DateLastPurchase = customer.DateLastPurchase;
    db.SubmitChanges();
    };
}

As you can see, the tooling in Visual Studio 2010 makes it very easy to create writeable external content types.

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

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

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.

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

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.

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 blogs.msdn.com/kaevans.

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