Business Connectivity Services, Part I

This article is the first in a three-part series about SharePoint 2010 Business Connectivity Services (BCS). In this series of articles, I’ll walk you through some foundational BCS concepts and components, discuss advanced data access techniques with BCS, demonstrate how to build custom user interface components, and how to work around BCS limitations in the sandbox.

When you look at the new functionality BCS offers in SharePoint 2010, you'll see that Microsoft made significant investments to improve the capabilities of the Business Data Catalog (BDC), which shipped with SharePoint Server 2007. The BDC did not go away; it still exists as a foundational component of the larger BCS. BCS capabilities are now available in SharePoint Foundation and Office Client applications instead of just in the SharePoint Server product.

New components including External Lists and enhancements to BDC Web Parts make it easier than ever before for non-developers to create business applications that integrate with external systems. SharePoint Designer and Visual Studio 2010 offer new and improved tooling support for developers to create BCS solutions. Many new data access methods and extension points provide the ability to integrate external systems with the SharePoint platform in ways never before possible. BCS has grown by leaps and bounds and will certainly be part of many more SharePoint applications than it has been in the past.

Foundational BCS Concepts and Components

First, you need to understand what the main components in BCS are and what they do. Once you understand the components, you can combine them to create SharePoint applications that integrate with external data. Figure 1 illustrates the components that make up BCS. This diagram is not all inclusive, but it does demonstrate how the components discussed in this series of articles relate to one another.

In this article, I focus on BCS Models, External Content Types (ECTs), External Lists, and BDC Web Parts and show you how to use them to implement functionality common to line-of-business applications. Future articles in the series will focus on the other BCS components.

BCS Models, previously known as application definitions in SharePoint Server 2007, contain metadata about external systems. The metadata in these XML files describe how to connect to, authenticate, and perform operations on external systems. Both SharePoint Designer and Visual Studio 2010 provide tooling support to create BCS Models.

External Content Types, also referred to as entities, are defined in BCS Models. Typically, an ECT represents a table or view in a database, a web service method, or another discreet entity within an external system. ECTs are not the same as the Content Types that were introduced in SharePoint Server 2003. They are stored within the BDC Metadata Store, not in the SharePoint content database. You cannot create traditional Content Types or ECTs that inherit from another ECT. ECTs are managed in SharePoint Designer or the SharePoint Central Administration web site.

External Lists provide a user interface to interact with the data External Content Types represent. External Lists look like regular SharePoint lists and include the forms necessary to display and edit the data they are tied to. You can enhance External Lists by creating custom views in the web browser, or by using SharePoint Designer to create InfoPath forms to edit the data.

External Lists do not support all the functionality that regular SharePoint lists, also known as internal lists, provide. Figure 2 compares the functionality between internal and external lists. As you can see, certain business requirements may dictate using one type of list versus another. Understanding these differences is critical during a project's architecture phase. For example, if a business application requires a list that provides alerting functionality and workflow integration, an external list will not work.

Modeling External Content Types with SharePoint Designer

SharePoint Designer now includes tooling support to create External Content Types, External Lists, and custom InfoPath forms for the web pages associated with External Lists. When you open a SharePoint site in SharePoint Designer you'll notice the External Content Types category in the Navigation pane on the left side.

Creating an External Content Type is simple. First, in SharePoint Designer, click the External Content Type button in the New group in the Ribbon. Then provide an internal name and a display name for the ECT. Providing a display name for an ECT is critical because the display name is visible throughout the out-of-the-box SharePoint BCS components such as BDC Web Parts, the ECT Picker, and SharePoint Central Administration. Note that taking the time to create friendly display names that accurately reflect the data they represent in an external system will help end users understand where the data is coming from and empower them to build applications in SharePoint.

Next, click the link to discover external data sources and define operations. SharePoint Designer then presents you with a new page that lets you connect to databases, web or WCF services, and any custom connectors deployed to your SharePoint server.

In this article, I demonstrate how to connect to a database. Clicking the Add Connection link starts a wizard that creates the connection information that lets you connect to an external system (Figure 3). Remember that all the information in an ECT is stored inside a BCS Model in an XML format. SharePoint Designer automatically creates a BCS Model for you when you create a new ECT.

Selecting Connect with User’s Identity uses the PassThrough AuthenticationMode setting in the BCS Model. This setting specifies that the credentials for the user currently accessing the ECT will be passed to the database server for authentication. Figure 4 describes the other authentication options available in SharePoint Designer.

Note that the RevertToSelf AuthenticaionMode setting is not available in the initial connection wizard in SharePoint Designer. The RevertToSelf AuthenticationMode setting uses the service account associated with the SharePoint web application where the ECT is called from to authenticate to an external system. Using this setting is not recommended because it provides the SharePoint web application service account access to the external system.

In case you're wondering why this is a dangerous approach, consider the following. The service account associated with a SharePoint web application has permissions to write to SharePoint content databases. The BCS supports ECTs that execute update and edit operations on external data sources. Therefore, when RevertToSelf is enabled on the farm, if a user has access to create ECTs he could create an ECT that performs update and edit operations in the SharePoint content databases. Potentially, this could allow an ECT to be created that adds user accounts as administrators in SharePoint site collections.

The recommended approach is to use the Secure Store Service (SSS) to map credentials for individual users, or all users, to a service account specifically used to access an external system. If you do need to enable this setting, you can edit the connection properties for the ECT after the wizard is complete and select BDC Identity for the Authentication Mode (Figure 5).

By default, the RevertToSelf AuthenticationMode setting is disabled in a BDC Service Application. When you try to save an ECT that uses this setting, SharePoint Designer will refuse to save the BCS Model and will provide the warning Figure 6 shows.

To enable the RevertToSelf authentication mode, set the RevertToSelfAllowed property on the BDC service application to true. The following code demonstrates how to do this in Windows PowerShell:

$BDCServiceApplication = Get-SPServiceApplication -Name "Business Data Connectivity Service"$BDCServiceApplication.RevertToSelfAllowed = $true;$BDCServiceApplication.Update();

After you've defined a connection to an external system the next step is creating the operations to interact with the data it contains. In this example, I’ll create read operations to return customer information from the AdventureWorks database.

Clicking the Operations Design View button in the Views tab in the Ribbon opens the Operation Designer page. First, select the table or view of data you wish to query. Then, right click it and add the appropriate operations (Figure 7). In this example, I added the Read Item and Read List operations. Note that the Read List operation is a friendly name for the Finder method in a BCS Model and the ReadItem operation is a friendly name for the SpecificFinder method in a BCS Model.

To create an External list based on an ECT, both the Read Item and Read List operations must be defined in an ECT. This dependency exists because the web pages that support External Lists rely upon these operations.

After you select an operation to create, SharePoint Designer presents a wizard to configure the operations. The wizard lets you name the operations and configure the input and output properties associated with each operation. You can select which columns of data to return from the external system, set their display names, set Filters on the queries, and define which column(s) represent the identifier for an ECT.

In this example, I created a Filter to limit the data returned from the external system based on the City column in the ECT (Figure 8). I assigned a default value of Beverly Hills. If no filter value is supplied, the default value is used when the operation is executed.

Just as with ECT display names, it’s important to define the Operation display name for each operation you create because they also show up in the out-of-the-box SharePoint web pages. In this example, I changed the display name of the Read List Operation to Customer Data.

Creating External Lists

After the ECT is created, you can create External Lists based upon it. You can create External Lists in SharePoint Designer and in the web browser. To create an External List in SharePoint Designer, click the Create Lists & Form button in the Lists & Forms group in the Ribbon. SharePoint Designer provides a dialog to create the list. If you check the Create InfoPath Form, checkbox SharePoint Designer will generate InfoPath forms to view and edit the list data (Figure 9).

To create an external list in the web browser, navigate to the create page for the SharePoint site and select External List. You're presented with a dialog to create the list. Name the list, enter a description, and then click the button (Figure 10) to open the ECT Picker dialog.

As I mentioned before, display names for ECTs appear in many places in the out-of-the-box SharePoint web pages. The External Content Types column in the ECT Picker (Figure 11) displays the display name for ECTs.

Select the ECT you wish to base the External List upon and click OK. Finally, click Create to make the list. Once the list is created you'll see the data coming from the external system in the list (Figure 12).  Note that the Operation Display Name (Customer Data) is displayed in the breadcrumb trail at the top of the page.

Creating Associations Between Entities

Most data has a relationship associated with it. For example, customers typically have orders. In this example, I created a new ECT representing Order data in the AdventureWorks system. I then created an association operation for the Orders ECT. When you create an association operation in SharePoint Designer you select which ECT to associate with (Figure 13) and define which column of data defines the connection between the two ECTs.

The Input Parameters Configuration dialog lets you specify which column is used to define the association between the two ECTs. In this example, I used the CustomerID column because the Customer Data and Order Data ECTs are related by this column (Figure 14).

Once an ECT has an association defined, you can use BDC Web Parts to navigate between associated data in the ECTs. In this example, I added a BDC List Web Part to a SharePoint page and configured it to display data from the Customers ECT. Then I added a Business Data Related List Web Part to the Page and configured it to display data from the Order Data ECT and associate it with the Customer Data ECT (Figure 15). To use an ECT in a BDC Web Part, you don't have to create an External List based on the ECT. As long as the ECT is registered with a SharePoint site you can use it with BDC Web Parts. Also note that the Association Display Name you create for an association operation is displayed in the Relationship dropdown list.

After both BDC Web Parts were configured I connected them (Figure 16). By connecting the BDC Web Parts I enabled the ability to navigate from customers to orders in the web page.

When a customer is selected in the BDC List Web Part the BDC sends the CustomerID to the BDC Related List Web Part, which in turn uses the CustomerID as the input parameter in the association operation defined on the Order Data ECT. The association operation executes and returns the Orders for the selected customer (Figure 17). Note that the association method executes each time a new customer is selected. The data is not cached anywhere in the web browser.

The Customer Data ECT is based on a view in the AdventureWorks database and the Order Data ECT is based on a table in the AdventureWorks database. As we know, tables and views do not have relationships defined between them in a database; however the BDC is still capable of creating an association between the two ECTs. This is possible as long as two ECTs are defined in the same BCS Model, and the column used to associate them shares the same data type.

Making Life Easier

As you've seen, creating ECTs and displaying the information in SharePoint sites is easier than ever. Without writing a single line of code I created an External List to display data from an external system and used BDC Web Parts to navigate data that didn't even have a relationship defined in the database.

These foundational components of the BCS provide a core set of functionality used in many SharePoint applications. In the next article in this series, I’ll dive deeper into the BCS components and discuss advanced data access techniques with the BCS.