Developing Client Applications with Office 2010 and Business Connectivity Services for SharePoint Server 2010

Article
10/20/2016

Summary: Walk through the client-side features of Microsoft Business Connectivity Services (BCS) and learn how to use those features to your advantage in various kinds of scenarios.

Provided by: Michael Case, iSoftStone | John Peltonen, 3Sharp | SharePoint 2010 SDK writers

Contents

Introduction to Business Connectivity Services in SharePoint 2010
Walkthrough of Architectural Components of BCS (Client and Server)
Making Data Available to Offline Clients
Understanding Security in BCS
Exploring Three Client-Side Scenarios
- Simple Solutions
- Intermediate Declarative Solutions
- Advanced Code-Based Solutions
Conclusion
Additional Resources

Introduction to Business Connectivity Services in SharePoint 2010

As users try to organize and centralize external business data, they are often frustrated by systems such as customer relationship management (CRM) or enterprise resource planning (ERP) that do not seem to communicate with productivity software. Too often, the important systems that users interact with throughout the day lack integration. To mitigate these difficulties, developers can use the extensible power of Microsoft Office 2010 and related products to integrate line-of-business (LOB) systems into Microsoft Office. That way, users can work with data in familiar Office applications and with other applications that they ordinarily use. For example, suppose that an organization relies on an "event planning" solution with a proprietary back-end database. Instead of forcing users to learn yet another special calendaring application, they could create an Office solution that uses calendaring in Microsoft Outlook 2010, where users do the majority of their scheduling already.

Microsoft Business Connectivity Services (BCS) is a new feature in Microsoft SharePoint 2010 that developers can use to surface data consistently in SharePoint 2010 and the Office 2010 client applications. Users can interact with the data, make changes, and even work with the data offline via a client-side cache. Business Connectivity Services can connect to almost any external system, whether it is an LOB system (such as Microsoft Dynamics, Oracle, or Siebel), a Web 2.0 service, or a custom application.

Note

See Business Connectivity Services Overview on TechNet for a complete introduction to Business Connectivity Services.

What Is the Five-Minute Summary of BCS?

As you might expect, you can use Business Connectivity Services to surface entities from external systems from within Microsoft SharePoint Server 2010. In fact, you actually surface data that a web service, Microsoft .NET Framework assembly, Windows Communication Foundation (WCF) endpoint, or a database provides. External content types can exist in several places; for example, in a special type of list called an external list, on profile pages, in special Business Connectivity Services Web Parts, as columns in other lists or document libraries, and in search. In SharePoint, users can search for and view this data as they would any other first-class SharePoint data. With the appropriate permissions, users can also edit external content types, which in turn are sent to the web service, database, Microsoft .NET Framework assembly, or WCF endpoint. Not only does the architecture let you surface entities from a single external system, it also lets your web service or .NET Framework assembly act as a miniature implementation of service-oriented architecture, surfacing an external content type that is built from a variety of external systems. This connector can also create or update entities in the external systems, based on the user's actions in SharePoint.

The benefit of working toward surfacing this data in SharePoint 2010, instead of surfacing it directly in a custom application, is that these entities are then available to other SharePoint 2010 customizations. For example, developers or power users who build applications can reuse these entities in their workflows, Microsoft InfoPath forms, Web Parts, and so on.

In addition, external content types can be surfaced and taken offline in Office 2010 client applications. Outlook 2010, Microsoft SharePoint Workspace 2010, Access 2010, Word 2010, and InfoPath 2010 all have built-in capabilities to surface data that is cached locally. For example, external content types that describe people can appear as Outlook contents, just as date-driven external content types can appear in the Outlook calendar.

This article describes how you can use Business Connectivity Services to address LOB integration scenarios for Office client users and create powerful productivity solutions.

Walkthrough of Architectural Components of BCS (Client and Server)

This section discusses the major architectural components of Business Connectivity Services, with a particular emphasis on how to integrate with Office by using client-side features.

As mentioned earlier, Business Connectivity Services enables users to read and write data from external systems through web services, databases, and .NET Framework assemblies from within SharePoint 2010 and Office 2010 applications. Both SharePoint 2010 and Office 2010 applications have features that can use external data directly, both online and offline. Developers can access a rich set of features and quickly build solutions by using familiar tools such as Microsoft Visual Studio 2010 and Microsoft SharePoint Designer 2010.

Business Connectivity Services is not intended for applications that process transactions. If you are updating multiple external systems or need transactional control, you should create a web service that supports the transactions that Business Connectivity Services can connect to.

Figure 1 shows a high-level architecture diagram of Business Connectivity Services.

Figure 1. BCS high-level architecture

BCS high-level architecture

Using the BDC Model in SharePoint 2010

Tip

The following section is adapted from BDC Model Infrastructure in the SharePoint 2010 SDK.

A major design goal for the Business Data Connectivity (BDC) service was to enable users to expose data from various external systems (such as CRM, ERP, and databases in Office 2010 applications and SharePoint Server 2010) with minimal coding effort. As a result, BDC provides homogeneous access to the underlying data sources through a declarative BDC metadata model (BDC model) that provides a consistent and simplified client object model.

The BDC model contains data about the APIs of the external system and includes information about the external system, the business entities, the methods that are available, and the relationships between the business entities. The data structures such as Entity (external content type) and Method that abstract complex details about an external system are collectively named MetadataObjects, and compose the BDC model. The BDC model for an external system is basically an XML file. It is autogenerated when you create an external content type in SharePoint Designer; you can also generate it manually in any XML editor.

The BDC model contains data about the external system, not the external data itself. External data exists only in the external system, although a cached copy exists in the Business Connectivity Services client cache if external lists are taken offline.

Business Data Connectivity Service

Tip

The following section is adapted from Business Data Connectivity Service (BDC) Overview in the SharePoint 2010 SDK.

The BDC service is the new version of the Business Data Catalog that was part of Microsoft Office SharePoint Server 2007. The BDC service is implemented as a shared service in Microsoft SharePoint Foundation 2010 and still relies on the Metadata Store. The BDC service enables you to declaratively model external systems by using a BDC model to expose external data in SharePoint Server 2010 and, with Microsoft SharePoint Server 2010 with Enterprise Client Access License, in Microsoft Office 2010 applications as well. The BDC service bridges the gap between SharePoint sites and your business applications, and enables you to bring key data from various external systems to SharePoint lists (via the new external list feature and external data columns), Web Parts, search, user profiles, and custom applications.

The core function of the BDC service is to provide connectivity to various kinds of external systems. It has built-in support for connections to the following data sources:

Databases
WCF and web services
.NET Framework assemblies
Custom data sources

Note

The BDC service provides a pluggable Connector Framework that developers can use to plug in connectors for new kinds of external systems and enable access to these new data source types via the BDC service.

The BDC service models are stored in the Metadata Store. After a BDC service model is made available, any solution can access that store by using the BDC Runtime object model.

The BDC Runtime object model provides programming interfaces that make the experience of programming against varied external systems uniform. Specifically, it enables the developer to call stereotypical, normalized operations, such as Create-An-Entity-Instance, Read-Entity-Instances, and Delete-Entity-Instance that are then converted by the BDC model into the external system-specific invocations.

The following diagram shows how the stereotypical operations can be called to update multiple external systems.

Figure 2. BDC programming model

BDC programming model

BCS Client Runtime

Tip

The following section is adapted from Business Connectivity Services Client Runtime in the SharePoint 2010 SDK.

The Business Connectivity Services Client Runtime is part of the Office Professional Plus 2010 client applications and is part of the rich client components for Office applications. It has the following main functions:

Provides a way to map the business data that is described by the metadata model to the various Office application user interface (UI) elements.
Loads and executes Business Connectivity Services solutions in Microsoft Office applications.
Shows the UI elements contextually when they are required.

The BCS Client Runtime acts as a connector between Business Connectivity Services, the solutions, and the Microsoft Office host applications. The BCS Client Runtime loads every time that a host Office application loads, and then raises, listens to, and acts on specific events on the host application. The BCS Client Runtime interprets the declarative solutions on Microsoft Office applications and integrates business data with Microsoft Office applications.

The BCS Client Runtime requires certain application artifacts that tell it how to show business data in the Office 2010 application. These artifacts are part of the Business Connectivity Services solution package.

An in-memory Microsoft SQL Server Compact Edition database on the client maintains the metadata and the cached entities that are of interest to the user. This architecture enables solution developers to focus on business needs instead of handling complex interactions with different Microsoft Office application APIs to perform common and repetitive tasks.

Figure 3 shows how the BCS Client Runtime integrates with BDC in Business Connectivity Services.

Figure 3. BCS Client Runtime integration with BDC

BCS Client Runtime integration with BDC

Making Data Available to Offline Clients

After you design the BDC model and expose the data on the SharePoint server through external lists, you can take the data offline and surface them in the Office 2010 client applications. This is enabled by the BCS Client Runtime. The BCS Runtime manages the client-side cache, calls into the external data sources, and surfaces external data in the Office 2010 client applications.

Although the Business Connectivity Services client and server runtimes are designed to be very similar, and are typically symmetrical to one another, they are not directly connected. You must configure each separately. As you will see later, in most cases the client-side cache, including the Metadata Store, is configured automatically when users choose to synchronize external lists with Outlook 2010 or SharePoint Workspace 2010. Part of that configuration includes uploading a local copy of the metadata model. Additionally, if the metadata model is referencing a .NET Framework assembly as an external connector, the .NET Framework assembly is copied to the client computer and run locally.

Note

In this case, it is very important to make sure that your .NET Framework assembly does not reference any assemblies that are hosted only on the SharePoint server and are not available on client computers.

Business Connectivity Services (BCS) Client Cache

Tip

The following section is adapted from Understanding Business Connectivity Services Client Cache to Optimize Your Solutions in the SharePoint 2010 SDK.

Business Connectivity Services uses a cache to store a copy of the external data that is required by the Business Connectivity Services solutions that are deployed on Microsoft Office 2010 client applications. The cache enables external data to be automatically copied on the client and managed for real-time access, with automatic data synchronization between the cache and the external application. The following are the significant benefits of using a cache on the client to store data locally from the external application:

Increased application throughput After the data is stored in the cache, it can be used by accessing the cached copy instead of re-fetching the original data from the external business application.
Support for disconnected scenarios Users can continue to work with Office 2010 business applications to manipulate external data seamlessly and efficiently, even when connectivity to the external system is slow, intermittent, or unavailable.

The client cache is implemented as a per-user Microsoft SQL Server 2005 Compact Edition database. Do not edit the database directly in any way; it is not supported by Microsoft. Instead, use the cache object model to access the cache.

Cache subscriptions define what is populated in the cache and are automatically generated during deployment of a Business Connectivity Services solution. Cache subscriptions are represented as an XML file (subscription.xml) and can be found in the deployment folder. Although a subscription is auto-generated at deployment-time, you can modify a subscription programmatically by using the object model. Editing the XML file has no effect. Subscriptions cannot be deleted.

A subscription contains the following items:

Queries The queries that must be executed to get the required external items into the cache.
Explicit Identities Any additional external items that you want to bring from the external data source by explicitly providing their identities.
Associations Any related external items from the external data source that interests you.

The unit of storage in the Business Connectivity Services client cache is an external item. The identifiers and instance data are stored in separate locations. The identifiers of all external items, belonging to all the subscriptions that are installed on the client for the user, are stored in one central location. The external item data for each external content type in a subscription such as ID, Name, Address, and so on, are stored in a separate location. For example, if the user has two subscriptions, Customer and Accounts, and each one has 10 external items, then all 20 identifiers are centrally stored in one location in the cache. The Customer data such as ID, Name, and Address that belong to the subscription are stored in a separate location. The Accounts data is stored in a yet another location in the cache. If the user modifies the subscription to add a query that returns five of the 10 Customer external items used by the first query in the subscription, the cache does not duplicate the data for those external items again.

Whenever an operation such as Read, Create, Update, or Delete is performed on an external item in the client application, calls are not routed directly to the external application (LobSystemInstance). Instead, the operations are stacked in the operation queue, which is a location in the cache, and then executed one-by-one depending on the availability of the external application.

A process known as BCS Sync (BCSSync.exe) runs on the client and provides automatic cache refresh and data synchronization of the external items. To populate the cache for a subscription, the BCS Sync process makes several calls to the external application through the Finder and SpecificFinder stereotyped operations. The Finder stereotyped operation returns a collection of external items that match a filter condition and the SpecificFinder stereotyped operation returns a specific external content type given its identifier. For each query defined in a subscription the BCS Sync process calls the Finder stereotyped operation to retrieve all external items that match the query condition, and if necessary calls the SpecificFinder stereotyped operation to retrieve the details for each external content type returned.

If there are enabled subscription associations, the BCS Sync process then calls the AssociationNavigator stereotyped operation to populate the cache with related external items. The AssociationNavigator stereotyped operation returns the external item identifiers that are associated with an external content type. The BCS Sync process calls the AssociationNavigator stereotyped operation for each external items in the cache for the subscription, and if necessary calls the SpecificFinder stereotyped operation to retrieve the details for each related external item identifier returned. Because of the number of additional calls required, it is not recommended that you use subscription associations. Instead create two subscriptions, one for each related external content type.

The sync process tries to refresh the cache from the external application at certain intervals. However, the exact time that the cache is refreshed cannot be predicted and depends on several factors such as the cache refresh interval (ExpireAfter) set by the user and the availability of the external application.

Tip

To learn more about the Business Connectivity Services cache, see Understanding Business Connectivity Services Client Cache to Optimize Your Solutions.

Understanding Security in BCS

There are three levels of security in Microsoft Business Connectivity Services (BCS).

First, each external content type can be secured, independent of external system privileges. This level of security is handled directly in the BDC model and can be managed at run time in SharePoint Designer and the SharePoint Administration site.

Second, the client-side cache itself must be protected on the client computer, especially on shared computers. It is encrypted by the Windows Encrypting File System and stored in isolated storage on a per-user basis to prevent tampering.

Third, access to the external system must be managed and provided to the users.

Using Secure Store Service to Simplify Authentication

Tip

The following section is adapted from How to: Use Secure Store Service to Connect to an External System in the SharePoint 2010 SDK.

It is very common for Microsoft Business Connectivity Services (BCS) solutions to try to authenticate to an external system in which the current user has a different identifier or has a different authentication account. In such cases, you can use the Secure Store Service to store and map user credentials that are required by the external system. The Secure Store Service enables multiple users to access an external system by using a single set of credentials on that external system.

For example, a user named Fred has one account on the server that is running SharePoint Server 2010 and another account for a CRM application. The Secure Store Service mechanism enables his CRM credentials to be stored with his user profile in SharePoint Server 2010. As a result, if Fred uses a Business Connectivity Services solution in SharePoint Server to obtain data from the CRM application, SharePoint Server looks up his credentials in the Secure Store Service database and provides his credentials to CRM. In in this manner, Fred automatically logs on to the CRM application without having to log on to the CRM application separately.

To provide similar functionality on Microsoft Office clients, Business Connectivity Services provides a Secure Store Service provider that uses the Windows Credential Store.

Exploring Three Client-Side Scenarios

There are basically three ways to put together client-side solutions where external data is hosted in Office 2010 client applications. They range from very simple solutions to solutions that run almost completely with code.

Simple Solutions

One common use of Business Connectivity Services (and SharePoint in general) is a composite application. Specifically, an application that is built from multiple components, uses data from multiple sources, and built to address a business need by business users. Microsoft Business Connectivity Services (BCS) offers business users, power users, and developers the ability to create sophisticated applications without having to write code or complex XML files.

This section addresses some of the options and scenarios that are available to anyone.

Outlook and SharePoint Workspace

The most basic kind of client-side solution that is available to users is to take an external list offline, either in SharePoint Workspace or Outlook. In both scenarios, SharePoint Server handles all of the setup issues for the user by creating a subscription in the client-side cache and configuring Outlook or SharePoint Workspace to read that list out of the cache.

This is a full-fidelity read/write scenario that enables users to pull external data down to the client, take it offline, modify it, and have their changes automatically applied to the external system when they reconnect.

Essentially, users are not really syncing the External List directly with Outlook or SharePoint Workspace. Behind the scenes, a cache subscription to the external content type is created. Then, SharePoint Workspace or Outlook is configured to expose the external content type instances in the cache through their respective user interfaces. From a user perspective, the process is completely transparent. They click to install the .vsto package and the content appears in one of these two applications. So, the next question is when should this data appear in SharePoint Workspace and when should it appear in Outlook.

There seems to be a bit of an inclination to always put data inside Outlook, but in this case the Office team was very thoughtful. External content types that you have defined as having an Outlook data type (Contact, Appointment, Task or Post) are ones that Outlook can host. SharePoint Workspace, as it is designed to take a SharePoint site offline, works with any external content type, regardless of data type definition.

How the Two Applications Present Data to the User

SharePoint Workspace shows the external data the same way it shows any content of that data type. Contacts appear as contacts, appointments as calendar items, and so on. Behind the scenes, SharePoint Workspace syncs a special Personal Store (PST) file that is created for the list with the cache. This can potentially provide a source of minor confusion to the user as the items are in a separate PST, not in the Outlook primary offline store (OST) and therefore are not merged with whatever items the user manages directly in Outlook.

Figure 4. Outlook displaying contacts from external database

Outlook displaying contacts from external database

SharePoint Workspace enables the user, when creating the connection, to decide which view to use to display the data in Workspaces. If the client computer has InfoPath installed, any custom InfoPath forms that are hosted on SharePoint for create and update are also pulled down and hosted locally in SharePoint Workspace.

In either case, these two applications surface the data from the same subscription in the cache. In fact, you can have the same list appear in both places. In this case, each application pulls from exactly the same data in the cache!

To learn more about surfacing external data in Outlook and SharePoint Workspace, see the following SDK articles:

Exposing External Data in Word 2010

Microsoft Word can display bulleted lists and tables. But these do not have inherent connections to external content types. Instead, Word surfaces a single, distinct instance of an external content type through content controls.

If you are not familiar with content controls, take a few minutes to study them. Content controls are a very powerful feature of Word that enables you to delineate sections of the document (anything from words and paragraphs to table rows and cells). They were initially designed to mark specific data points in the document and can be bound to custom XML files embedded in the docx.

For more information about content controls, see the following articles on MSDN:

One powerful feature for users in Office SharePoint Server 2007 was the ability to surface document library columns (and other document properties) directly in the surface of the document by using these content controls. SharePoint 2010 enhances this capability to include document library columns that surface External List data. In fact, Word 2010 provides a unique BCS picker content control that is specifically designed to enable users to select the external item directly from the document’s surface.

Unlike Outlook andSharePoint Workspace, which query the cache directly, the BCS picker in Word first looks to the SharePoint list for external content data. If offline, the picker looks into the cache to see whether the user has initiated a subscription for that external content type through Outlook or SharePoint Workspace. If the user has not explicitly taken the external list offline in Outlook or SharePoint Workspace, the picker does not show any choices. Of course, if the external content type has already been selected while connected to SharePoint, it is still shown in the document.

To learn more about surfacing external data in Word 2010 documents, see How to: Show External Data in Word Using Content Controls.

Synchronizing Copies of External Lists to Local Databases Using Access 2010

Microsoft Access 2010 can synchronize copies of external lists to local databases. This can be a very powerful feature when using data from external systems to drive your local Access 2010 applications. Access 2010 already has built-in capabilities to synchronize SharePoint lists that have been extended to support read-only External Lists. Access 2010 does not use the local BCS runtime or cache to synchronize with lists. However, you can import a BDC model into Access 2010 to create read-only tables.

Intermediate Declarative Solutions

Intermediate declarative solutions require code but involve some user customization as well. Declarative solutions can frequently take advantage of an existing advanced code-based solution. However, they can also stand on their own as extremely powerful customizations of native application functionality. In fact, there are some cases in which advanced code-based solutions can take advantage of an existing declarative solution. As a developer, make sure that you know how to create declarative solutions and simple solutions.

At its most basic level, a declarative solution enhances a simple solution; for example, you could customize a Word 2010 template with some BCS Picker controls hosted in a SharePoint document library, or replace the default SharePoint list forms with customized InfoPath forms.

To learn more about upsizing native SharePoint forms to InfoPath forms, see How to: Upsize the Default SharePoint Forms of an External List to Microsoft InfoPath Forms.

There is a declarative solution specific to Outlook 2010 that is much more complex than a simple customization to existing functionality. It enables developers to surface related Business Connectivity Services data in task panes alongside external content types that are surfaced as native Outlook data types. The solution is somewhere between a simple no-code solution and a complex managed code solution that is written in Visual Studio. Although you can create this type of solution without writing any code, it requires some sophisticated application design, data structure, and XML editing.

Outlook declarative solutions enable developers to design and build solutions that let Outlook users interact with contextually relevant external data in Outlook as a contact, appointment, post, or task. Remember that a simple solution already enables power users and developers to surface external content types as native Outlook contacts, appointments, posts, or tasks. Developers can use an Outlook declarative solution to extend this style of solution; that is, they can declaratively define custom ribbons and task panes in Outlook to enable users to interact with related external content types.

To learn more about how to create an Outlook declarative solution, see Walkthrough: Creating an intermediate Declarative Outlook Solution

Advanced Code-Based Solutions

Just because the BCS Client Runtime supports surfacing data in Outlook, SharePoint Workspace, Word 2010, and Access 2010 does not mean that these are the only applications that can surface external content types. You can use Microsoft Visual Studio 2010 to create solutions that range from rich end-to-end solutions with code components running on the client and on the server, to reusable Business Connectivity Services components.

Code-based, end-to-end .NET Framework solutions, created in a tool such as Visual Studio, can use any element of the public Business Connectivity Services object model and can enable users to interact with external data. The main components that you can create are Microsoft Silverlight applications, custom server components (for example Web Parts), and Microsoft Officeadd-ins. Code-based solutions can register with the BDC service by using the BDC object model to present data in SharePoint, in an Office 2010 application such as Excel 2010, or in a custom application. You can design code-based solutions to function only while online to retrieve data directly from the external system, or design them to function both online and offline by retrieving data from the BCS client cache. A Visual Studio 2010 BDC project type is available to help create .NET Framework connectivity assemblies, and application-specific Office add-in project templates are available to create Office add-ins quickly.

In addition to developing end-to-end BCS solutions, you can create reusable BCS components for power users to implement intermediate declarative solutions. The following list contains some examples:

External content types and BDC models
Web services and .NET Framework connectivity assemblies to aggregate or transform data from external systems
Custom Web Parts
Custom workflow activities
Code-behind for InfoPath forms
Custom field types to show data structures that are not flat
Custom external data parts for Outlook declarative solutions

To learn more about how to create an advanced code-based solution, see Creating Advanced Code-Based Solutions Using Business Connectivity Services.

Conclusion

Using Microsoft Business Connectivity Services (BCS) with Office 2010 and SharePoint 2010 makes it possible to surface data from external systems directly in the applications that users already know. Methods to surface the data range from very simple, no-code client-side scenarios, to rich programmatic offerings. Your custom Office 2010 solution consumes data from a line-of-business system and enables further productivity enhancements through the client-side programmability of Microsoft Office 2010. The solution also uses the client-side runtime and cache so that users can work with the data online or offline.

For more information about how to develop your own client applications by using Business Connectivity Services, see Microsoft Business Connectivity Services (BCS) in SharePoint 2010.

Additional Resources

For more information, see the following resources: