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.
Fig.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 the 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.
Fig.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.
Fig.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.
Fig.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.
Fig.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:
- 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.
- 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. |
Once 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.
Fig.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:
- 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.
- Create a new document in that library, using the New button from the ribbon.
- 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.
Fig.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.
Fig.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.
Fig.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.
Fig.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… |
|
|
| Development model |
|
|
| Suited for… |
|
|
| Limitations |
|
|
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.
| Feature | SharePoint Foundation 2010 | SharePoint Server 2010 Standard | SharePoint Server 2010 Enterprise |
|---|---|---|---|
| BDC Service |
X |
X |
X |
| Connector Framework |
X |
X |
X |
| External List |
X |
X |
X |
| External Data Column |
X |
X |
X |
| Secure Store Service |
X |
X |
|
| External Data Search |
X |
X |
|
| Profile Pages |
X |
X |
|
| Business Data Web Parts |
X |
||
| Office Client integration |
X |
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
