Business Connectivity Services by André Vala

A few weeks ago I had an article of mine published on PROGRAMAR magazine, issue number 28, a
Portuguese developer-focused magazine. Here is the translated
version of the article.

SharePoint 2010 is a complex platform with an impressive set of
out-of-the-box features which allow it to fit a great variety of
situations. One of the new and most powerful features is called
Business Connectivity Services and this article is
an introduction to this technology and its potential.

What are Business Connectivity Services and what are
they for?

Business Connectivity Services (BCS) is the name of the
technology that allows SharePoint 2010 and Office 2010 to read from
and write information to external systems. It’s an evolution of the
Business Data Catalog (BDC) technology introduced in
SharePoint 2007, and on which several improvements were made,
namely:

  • Possibility not only to read, but also to write information to
    the external data source;
  • Support for more complex authentication scenarios;
  • Support for multiple data sources;
  • Integration with Office client applications;
  • New and better ways to present the data;
  • Tools specifically focused on creating and manipulating
    models;
  • Extensibility through .Net assemblies.

The main goal of this technology is to allow the integration of
information from external systems and present it on SharePoint and
Office client applications with the minimal effort possible and,
ideally, without writing a single line of code. There are, in fact,
some scenarios where you can use BCS technology only through
configuration, but the true flexibility lies in its extensibility
which allows it to fulfill almost any integration need.

Architecture

BCS technology is not limited to a service or API inside
SharePoint 2010. In fact, it’s a set of components, services and
tools as presented in the schema below.

Business Connectivity Services

Figure 1 – Business Connectivity
Services Architecture

Business Data Connectivity (BDC) Service

The Business Data Connectivity Service is one
of the core components of BCS. It’s the component which stores, in
its central metadata repository, the descriptions of the
information which we want to access as well as the external systems
that holds it.

Metadata Store

The metadata repository is the database used by the Business
Data Connectivity Service
to store the descriptions of the
information and the external systems that hold it. This repository
does not store any of the information from the external systems,
only the metadata required to retrieve it.

Connectors

The connectors are the pieces that allow the
Business Data Connectivity Service to connect to the
external data sources described in the models stored in its
Metadata Store. Three connectors are included in the
product:

  • Database Connector – allows the connection to
    SQL Server databases.
  • WCF/Web Services Connector – allows the
    connection to WCF services or web services.
  • .Net Assembly Connector – allows connections
    using a custom developed .Net assembly, allowing access to
    virtually any external data source, including simultaneous
    connections to multiple data sources.

The connector mechanism is extensible and allows custom
connectors to be developed, for cases when the out-of-the-box
connectors are not enough.

BDC Client Runtime

Office 2010 applications are also able to expose information
from external systems through BCS. That is possible because Office
2010 includes the BDC Client Runtime, a component
that, in the client application context, performs the same tasks
that the BDC Service performs in the server context. That
is, it accesses the metadata repository and, through the
definitions stored in it, it accesses the external information
itself.

Client Data Cache

To improve performance when accessing information, as well as to
support offline access to information, the BCS use a cache to store
the retrieved external data. This cache is based on a SQL
Server 2005 Compact Edition
database and has an automatic
synchronization mechanism that allows all changes performed on the
data in offline mode to be replicated as soon as the external
system becomes available once again.

Core Concepts

Now that you know the architecture for Business Connectivity
Services, it’s important to understand what kind of metadata is
stored in the Metadata Store by the Business Data Connectivity
Service.

Model

The metadata used by the BDC Service and stored in the Metadata
Store consists of XML files which describe Models,
usually named BDC Metadata Models. In SharePoint
2007, these metadata files were called application definition
files
.

A model contains, in declarative form, all the necessary
information so that the BCS are able to connect to an external
system and retrieve the data stored in it.

Lob System

Regarding BCS, the Lob System (or
Line-of-Business System) refers to the external system
which stores the data we want to access. This system can be a
relational database, or any other system that exposes that data
through web services or WCF services.

External Content Type

The External Content Type (ECT) is the core and
most important concept of the BCS, since it describes a business
entity, that is, it describes the structure and behavior of the
data we want to access. Examples of ECTs are Client,
Invoice or Employee.

The ECT definition specifies the structure and behavior of the
entity, that is:

  • The fields that compose an instance of the entity and their
    respective data types. Example: Name, Address or
    Country.
  • The mapping of these fields to objects used by Office client
    applications. Example: the field Name of the entity maps
    to the FullName field in Outlook.
  • The methods that must be invoked by the BCS to read, create,
    update and delete instances of the entity. These methods might
    correspond, for instance, to stored procedures, SQL queries or web
    services.

In the context of a Model, we can define several entities, that
is, several External Content Types.

Methods

The methods are abstractions of the external
system’s API and they allow BDC Service to know which stored
procedures or web services to call when manipulating the data. The
definition of a method is created in the context of an entity and
is always based on one of the stereotypes supplied by the BCS.
There are about 20 stereotypes, such as Updater (to update
an item), Finder (to list items), SpecificFinder
(to retrieve a specific item), Deleter (to delete an item)
among others.

Filters

Filters describe the parameters that can be
passed to the methods in the definition of each entity. There are
18 types of filters that can be used in methods and which let the
BDC Service know which information should be passed to them.
Examples of filters are the UserName, which passes the
username of the current user, or the Limit, which defines
the maximum number of items that are returned in a single method
call.

Associations

An association is a relationship between two
entities (External Content Types). However, since there is no
guarantee that the data source is a relational database, the
association requires that a special method exists. This method
allows the BCS to retrieve elements of the related entity from
elements of the source entity.

Model Sample

Fortunately, in most cases, we don’t need to manually edit the
model since the tools supplied by Microsoft allow us to visually
perform most of the configurations. However, just as an example, a
model file looks like the one shown below.

2

Figure 2 – Partial sample of a BDC
Model

 

Presenting External Data

But all this complexity has a purpose – to allow the
presentation and manipulation of the data stored in external
systems – so there are several alternatives to do so.

External List

The External List is a new type of list in
SharePoint 2010 which, being bound to an External Content Type,
allows the visualization and manipulation of the data exposed
through that ECT as if it was stored in a regular SharePoint list.
In reality, the data still resides in the external system and it’s
read and manipulated in real time.

Business Connectivity Services

Figure 3 – External List

The greatest advantage of External Lists is the fact that they
look and feel exactly like regular lists and, additionally,
SharePoint’s object model treats them as such, allowing developers
to read and write list items as if they were stored in
SharePoint.

On the other hand, not all works exactly as in traditional
lists. In particular:

  • Workflows
  • Alerts
  • Folders
  • Attachments
  • RSS Feeds
  • Export to Excel

Before an ECT can be used in an External List, it has to define,
at least, a Finder method (to list items) and a
SpecificFinder method (to retrieve a specific item). This
allows the External List to present the list of items and the
detail for each one. Additionally, if the ECT has an
Updater method (updates an item), a Deleter
method (deletes an item) and a Creator method (creates a
new item), the External List will make the corresponding actions
available.

External Data Column

The External Data Column was already around in
SharePoint 2007 and, although it has been slightly improved in
SharePoint 2010, its objective remains the same – allow the use of
external data as a list column. It works similarly to a lookup
column, allowing the user to select one of the items return by the
ECT.

4

Figure 4 – External Data Column

One of the advantages of External Data Columns is the
possibility to used them in Word 2010, allowing the user to select
one item exposed through the BCS and use that data inside the
documents.

Just like for External Lists, to use an ECT in an External Data
Column, the ECT must define, at least, the Finder and
SpecificFinder methods.

Business Data Web Parts

Business Data Web Parts are a set of web parts
that are able to connect to external data sources through an ECT
and present that information in SharePoint. These web parts were
also present in SharePoint 2007 but were improved in SharePoint
2010, now being able to cache the external information for better
performance.

Business Connectivity Services

Figure 5 – Business Data Web
Parts

The Business Data Web Parts use XSLT to present the information,
which grants them enormous flexibility regarding its graphical
appearance as well as the possibility to be edited in SharePoint
Designer 2010.

The web parts included in this set are:

  • Business Data List – lists instances (items)
    of an entity (ECT).
  • Business Data Item – presents the details of
    an instance (item) of an entity (ECT).
  • Business Data Item Builder – uses query string
    parameters to create an instance (item) of an entity (ECT) which
    can then be passed on to other web parts, such as the Business Data
    Item web part.
  • Business Data Related List – lists instances
    (items) of a related entity (ECT). It’s especially useful to
    present data in Master/Detail scenarios.
  • Business Data Connectivity Filter – filters
    the data retrieved from an ECT before it is consumed by another web
    part, such as the Business Data List web part.
  • Business Data Actions – presents the available
    actions for an instance (item) of an entity (ECT).

 

Search

One of the largest benefits offered by the BCS is the
possibility to index and search external data exposed through the
ECTs as if it is stored in SharePoint lists.

For an ECT to be indexed it has to define, at least, the
IDEnumerator method and the SpecificFinder
method. The first will allow SharePoint to retrieve the IDs of all
the items and the second to retrieve each item’s detail.
Additionally, the model must have the ShowInSearchUI
property so that SharePoint can use it for indexing.

But this is for the data to be indexed. To allow the users to
see detailed information about a specific item when they click on a
search result, we also need to configure the Profile
Page
for each indexed ECT.

A Profile Page is just a page in SharePoint
with a few predefined web parts which receives the item identifier
through a query string parameter and presents detailed information
about that item, including items of related ECTs (through
Associations).

Profile Pages are configured in Central Administration,
accessing the Business Data Connectivity Service management page.
The only information we need to supply is the URL address of the
website where these pages will automatically be created, and
SharePoint will do the rest for us.

At this point, it suffices to tell the Search Service that it
must index a new Content Source of type Line of Business
Data
and then start a Full Crawl. After the crawl
finishes, the external data is available to be searched and each
item’s information will be presented in its respective Profile
Page.

User Profiles

Using the BCS, SharePoint 2010 can use external data sources to
complement User Profile information. To do that, you just need to
able to map User Profiles to items of an ECT, using a field on each
side (such as the username).

It is not possible to configure an ECT as a main data source for
the profile synchronization, but you can complement an Active
Directory synchronization with data retrieved from an ECT.

Office Client Integration

The integration of external data in Office 2010 applications is
also one of the new SharePoint 2010 features in what regards the
Business Connectivity Services. Until now, this kind of
functionality was only possible through considerably complex custom
developments.

With the BCS it’s possible to present external data in Office
applications, use it in offline scenarios and, in specific cases,
update the data directly in the external data source. However, not
all the Office 2010 applications have native support for such
integration. At the moment, only Outlook 2010, Word 2010, Access
2010, InfoPath 2010 and SharePoint Workspace 2010 are able to do
it, being that each one uses this technology in a different
way.

 

Outlook 2010

Outlook 2010 is one of the Office applications that profits the
most from the client features of the BCS. To be able to view the
data exposed through an ECT in Outlook 2010, two steps are required
when configuring that ECT:

  1. Defining what is the type of data exposed by the ECT among the
    types of data supported by Outlook: Contacts, Tasks, Appointments
    or Posts. This configuration can be performed in SharePoint
    Designer or directly in the Model’s XML.
  2. Mapping the ECT fields with the Outlook fields used in that
    type of data. For instance, specify which fields of the ECT map to
    the fields Last Name, First Name, E-mail
    Address
    and others, in Outlook.

 

If there is an External List which exposes the data from the
ECT, it will have a Connect to Outlook button in its
ribbon. By pressing that button SharePoint will analyze the ECT
specification and include it in a Click Once installation
package. That package will be immediately deployed to the user’s
Outlook 2010 as an Office Add-In.

6Once the package is installed, the list will appear
in Outlook’s interface allowing the user to interact with the
external data as if it was a list of regular contacts, tasks,
appointments or posts. In case the ECT defines the required
methods, you can even use Outlook to update the data from the
external data source. All the fields exposed by the ECT which are
not mapped to fields of the Outlook object are shown in a separate
section of that object’s detail and can also be updated.

 

7

Figure 6 – Additional information
(unmapped) from the ECT in Outlook’s interface

Like all the other Office applications, Outlook takes advantage
of a data caching and synchronization mechanism allowing the user
to work offline and automatically synchronizing the data as soon as
the external system becomes available.

Word 2010

Word 2010 is another of the Office applications which has native
support for the BCS. However, the use cases for this technology in
Word are different from the ones available in Outlook. Using BCS in
Word 2010 is limited to inserting external data in documents
through Quick Parts.

For those who never heard of them, Quick Parts are a
Word feature which allows the creation of dynamic fields in the
middle of a document’s text. These fields can be automatically
filled with data from the document’s content type, in SharePoint.
This includes data from an External Data Column that exists in the
Document Library where the document is stored.

Here’s how it works:

  1. In a Document Library, create an External Data Column
    configuring it to expose the data of a specific ECT and defining
    the fields of the ECT which are exposed.
  2. Create a new document in that library, using the New
    button from the ribbon.
  3. In Word, through the Insert ribbon, insert one (ore
    more) Quick Part, selecting the Document
    Property(s)
    that match the external data you wish to include
    in the document.

8

Figure 7 – Quick parts with external
data

Word will allow the user to select an item from the ECT, using
the Entity Data Picker, and will automatically populate
all the related fields

Figure 8 – External Data Picker

Access 2010

Access 2010 is able to import a Business Data Connectivity (BDC)
model an present the external data as regular database tables.
However, the created tables are read-only, which means you cannot
write back to the external data source.

InfoPath 2010

When an External List is created, the corresponding forms for
viewing, adding and editing the external data are also generated.
By default, these forms are generated as regular ASP.NET pages but,
using SharePoint Designer or the External List ribbon, it’s
possible to create smarter forms using InfoPath. The forms are also
automatically generated but they can later be modified using
InfoPath.

It is also possible to drag an External Data Picker to an
InfoPath form and define an External List as a data source, thus
allowing reading from and writing to external data sources.

SharePoint Workspace 2010

SharePoint Workspace 2010 is the evolution of Groove 2007 and is
positioned as the tool for offline access to data stored in
SharePoint 2010, including External Lists. Just like for the other
types of lists, you just need to click the Sync to SharePoint
Workspace
button so that their content is downloaded to the
user’s machine becoming available when she is disconnected from the
server.

Regarding BCS, what SharePoint Workspace does is download the
ECT definition associated with the External List and store it
locally, as well as the list forms for inserting, editing and
viewing the data which were generated for that External List. Just
like the other described

Office applications, SharePoint Workspace uses the local cache
to ensure the availability of the external data even when the
external system is not available.

Solutions and Tools

One of the most frequent complaints of who used Business
Data Catalog
in SharePoint 2007 was the lack of proper tools
that allowed for a good experience when creating and manipulating
metadata (models). Microsoft took notice of those complaints and
supplied us with two fantastic tools to use with Business
Connectivity Services:

  • SharePoint Designer 2010
  • Visual Studio 2010

 

SharePoint Designer 2010

SharePoint Designer 2010 is a free and mandatory tool for any
SharePoint power user or developer. It includes a large set of
features mainly focused on creating no code solutions, that is,
SharePoint customizations that do not require any custom code.

Figure 9 – Using SharePoint Designer
to manage ECTs

Regarding Business Connectivity Services, SharePoint Designer
2010 allows:

  • Creating and manipulating External Content Types, including
    changing configurations, creating new methods and mapping ECTs with
    Office objects. When creating ECTs you are only able to connect to
    SQL Server databases, web services whose schemas are supported by
    BCS or existing .NET assemblies.
  • Creating and configuring External Lists based on existing
    ECTs.
  • Generating and editing InfoPath forms to support External
    Lists.
  • Using external data in workflows.
  • Creating web part pages and profile pages.

These features allow the use of BCS without any custom coding
and fulfill the most common and simple requirements.

Visual Studio 2010

With Visual Studio 2010 we can create more complex solutions for
cases in which SharePoint Designer 2010’s features are not enough.
Additionally, with Visual Studio 2010 we can create reusable
components which can then be incorporated in solutions through
SharePoint Designer.

Figure
7 – Quick parts with external
data

Word will allow the user to select an item from the ECT,
using the Entity Data Picker, and will
automatically populate all the related fields

9

Figure
8 – External Data
Picker

Access
2010

Access 2010 is able to import a Business Data
Connectivity (BDC) model an present the external data as regular
database tables. However, the created tables are read-only, which
means you cannot write back to the external data
source.

InfoPath
2010

When an External List is created, the
corresponding forms for viewing, adding and editing the external
data are also generated. By default, these forms are generated as
regular ASP.NET pages but, using SharePoint Designer or the
External List ribbon, it’s possible to create smarter forms using
InfoPath. The forms are also automatically generated but they can
later be modified using InfoPath.

It is also possible to drag an External Data
Picker to an InfoPath form and define an External List as a data
source, thus allowing reading from and writing to external data
sources.

SharePoint Workspace
2010

SharePoint Workspace 2010 is the evolution of
Groove 2007 and is positioned as the tool for offline access to
data stored in SharePoint 2010, including External Lists. Just like
for the other types of lists, you just need to click the
Sync to SharePoint Workspace button so that
their content is downloaded to the user’s machine becoming
available whe she is disconnected from the
server.

10

Regarding BCS, what SharePoint Workspace does is download
the ECT definition associated with the External List and store it
locally, as well as the list forms for inserting, editing and
viewing the data which were generated for that External List. Just
like the other described

Office
applications, SharePoint Workspace uses the local cache to ensure
the availability of the external data even when the external system
is not available.

Solutions and Tools

One of the
most frequent complaints of who used Business Data Catalog
in SharePoint 2007 was the lack of proper tools that allowed for a
good experience when creating and manipulating metadata (models).
Microsoft took notice of those complaints and supplied us with two
fantastic tools to use with Business Connectivity
Services:

·
SharePoint Designer 2010

·
Visual Studio 2010

SharePoint Designer 2010

SharePoint Designer 2010 is a free and mandatory tool for
any SharePoint power user or developer. It includes a large set of
features mainly focused on creating no code solutions, that is,
SharePoint customizations that do not require any custom
code.

11

Figure 9 – Using SharePoint Designer
to manage ECTs

Regarding Business Connectivity Services, SharePoint Designer
2010 allows:

  • Creating and manipulating External Content Types, including
    changing configurations, creating new methods and mapping ECTs with
    Office objects. When creating ECTs you are only able to connect to
    SQL Server databases, web services whose schemas are supported by
    BCS or existing .NET assemblies.
  • Creating and configuring External Lists based on existing
    ECTs.
  • Generating and editing InfoPath forms to support External
    Lists.
  • Using external data in workflows.
  • Creating web part pages and profile pages.

These features allow the use of BCS without any custom coding
and fulfill the most common and simple requirements.

Visual Studio 2010

With Visual Studio 2010 we can create more complex solutions for
cases in which SharePoint Designer 2010’s features are not enough.
Additionally, with Visual Studio 2010 we can create reusable
components which can then be incorporated in solutions through
SharePoint Designer.

12

Figure 10 – Visual editor for BDC
Models in Visual Studio 2010

Some of the use cases for Visual Studio 2010 are:

  • Creating and manipulating External Content Types using the new
    Business Data Connectivity Model project template. This
    template includes a set of designers that allow for the visual
    editing of the Model and respective ECTs, and also the development
    of .NET code solutions to access virtually any type of external
    data source.
  • Creating reusable components for BCS using the multiple API
    extensibility points, such as Code Actions which can be
    used inside Outlook, External Data Parts which can be used
    in declarative task panes in Outlook, custom workflow activities
    and others.
  • Creating Office application add-ins with BCS support, using the
    BCS object model.
  • Creating custom workflows that leverage data from external
    lists or use the BCS object model.

 

Types of Solution for each Tool

The table below will help you select the best tool for each
need.

Capability SharePoint Designer 2010 Visual Studio 2010
Connect to… WCF/WS, SQL Server and existing .NET assemblies Any data source through .NET assemblies
Development model Discover and configure Create and publish
Suited for…
  • Simple models with native interfaces (External Lists, Outlook,
    SharePoint Workspace, InfoPath, Search) and simple associations
    based on foreign keys
  • Complex models with custom connectivity logic for aggregation,
    transformation, security, etc.
  • Custom interfaces via Office customizations
  • Reusable client and server controls that connect to external
    data
Limitations
  • The data source must expose interfaces with supported
    format
  • No support for advanced stereotypes (bulk operations)
  • No support for generic or polymorphic services
  • Only foreign key associations
  • Visual designer only works for models based on .NET
    objects
  • Separate development and packaging for client and server
    components

Use Cases

One of the most common questions regarding the use of Business
Connectivity Services is what are the use cases of this technology
or, when should I use BCS.

Some of the most common uses cases for BCS are:

  • Presenting information stored in a SQL Server database. Using
    BCS it’s possible to present and, if necessary, modify the
    information using External Lists without developing a single line
    of custom code. It’s like an instantaneous back-office.
  • Enriching the domain user’s Profiles with information from the
    HR management system or the ERP system. As mentioned before, BCS
    allows you to fulfill this requirement by configuring an ECT as an
    additional data source for SharePoint’s profile
    synchronization.
  • Synchronizing contacts stored in a line-of-business system or
    ERP. Using BCS it’s possible to define an ECT which exposes these
    contacts through an External List and connect it to Outlook where
    they can be managed as regular contacts. This scenario allows the
    users to access to the contacts even when they’re out of the
    office, in offline mode.
  • Presenting information from distinct data sources. Using the
    .NET assemblies connector and developing an ECT with Visual Studio
    2010, we can build scenarios with aggregation of data from multiple
    data sources exposed as a single entity.
  • Indexing and searching information stored in a line-of-business
    or ERP system. BCS allows SharePoint’s search service to index
    contents exposes through ECTs and searching them as if the
    information is stored in SharePoint.

There are several other scenarios where BCS can be useful,
sometimes just as one of the components of the solution.

Features per SharePoint Version

The infrastructure used by the Business Connectivity Services is
available in all SharePoint versions, including SharePoint
Foundation 2010. However, not all is included in the free version.
The table below helps to clarify which features are included in
each version of SharePoint 2010.

Funcionalidade

SharePoint Foundation
2010

SharePoint Server 2010
Standard

SharePoint Server 2010
Enterprise

BDC Service

ü

ü

ü

Connector Framework

ü

ü

ü

External List

ü

ü

ü

External Data Column

ü

ü

ü

Secure Store Service

ü

ü

External Data Search

ü

ü

Profile Pages

ü

ü

Business Data Web Parts

ü

Office Client integration

ü

 

The support for Business Connectivity Services in Office
applications requires Microsoft Office 2010 Professional Plus, or
higher.

Useful Links

Here are a few useful links for who is now starting and wants to
know more about Business Connectivity Services.

Microsoft Business Connectivity Services Team
Blog

Official blog of the team that developed BCS, with several
articles with multiple levels of complexity. Mandatory to all the
interested in this technology.
http://blogs.msdn.com/b/bcs/

BCS Team Channel

The YouTube channel where the product team published a few
videos.
http://www.youtube.com/user/MOSSBCSTeam

Connecting to a .NET Framework Source Using Business
Connectivity Services in Office 2010

Visual How To about developing an ECT using Visual
Studio 2010 to retrieve data from an external source.

http://msdn.microsoft.com/en-us/library/ff394331(office.14).aspx

Microsoft Business Connectivity Services

SharePoint 2010 SDK’s section dedicated to Business Connectivity
Services.
http://msdn.microsoft.com/en-us/library/ee556826.aspx

Business Connectivity Services Resource
Center

TechNet Resource Center dedicated to Business Connectivity
Services.
http://technet.microsoft.com/en-us/sharepoint/ee518675.aspx

Business Connectivity Services: Technical
Articles

MSDN technical articles related to Business Connectivity
Services.
http://msdn.microsoft.com/en-us/library/gg481768.aspx

Come to the European SharePoint Conference.

Share this on...

Rate this Post:

Share: