Introduction to Business Connectivity Services in SharePoint Online

Summary:  Learn the basic concepts, best practices, and design patterns necessary to develop applications for Microsoft SharePoint Online using Microsoft Business Connectivity Services (BCS).

Available in SharePoint Online

Applies to: Business Connectivity Services | Open XML | SharePoint Designer 2010 | SharePoint Foundation 2010 | SharePoint Online | SharePoint Server 2010 | Visual Studio

Provided by:  Rodney Farris, Microsoft Corporation

Contents

• What Is Business Connectivity Services?

• External Content Types

• BDC Models

• External Lists

• External Data Columns

• Business Data Web Parts

• External Content Type Picker

• External Item Picker

• Secure Store

• SharePoint Client Object Model

• Conclusion

• Additional Resources

What Is Business Connectivity Services?

Microsoft Business Connectivity Services (BCS), formerly named the Business Data Catalog, enables users to read and write data from external systems—through Web services, databases, and Microsoft .NET Framework—from within Microsoft SharePoint 2010 and Microsoft SharePoint Online. Both SharePoint 2010 and SharePoint Online have features that can use external data directly. Developers can gain access to a rich set of features and rapidly build solutions by using familiar tools such as Microsoft Visual Studio 2010 and Microsoft SharePoint Designer 2010.

If you are new to SharePoint Online, this article is for you. It introduces the basic concepts, best practices, and design patterns necessary to develop applications for SharePoint Online using BCS. The article assumes that you have experience with basic SharePoint concepts and the patterns involved in SharePoint application development.

External Content Types

A core concept of BCS is the external content type. External content types are reusable connectivity definitions stored in SharePoint Online that provide access to external data without needing to know the underlying connection details.

These definitions contain metadata descriptions of connectivity information and data definitions plus the behaviors you want to apply to a certain category of external data. External content types enable you to manage and reuse the metadata and behaviors of a business entity, such as Customer or Order, from a central location, and enable users to interact with that external data and processes in a more meaningful way.

For example, consider a business entity such as Customer. You might want to interact with items of type Customer inside a SharePoint list. Or you might want to enable the user to pick a customer from a list of customers in a Web Part. You can create an external content type once and then reuse it anywhere you need it.

External content types provide the following benefits:

• Reusability   An external content type is a reusable data definition of a business entity. After you create it, you can use it with any of the Presentation Features in BCS to provide a rich user experience to interact with external data.

• Simplified access to external systems   External content types enable information workers to assemble business solutions without having to handle the complexities of those systems, such as connectivity information or programmability interfaces. After an experienced user or a developer creates an external content type, it is available to any user for use in any way they need (provided they have the permissions to perform that operation and access the external data). However, the user does not need to know anything about the location of the external data or how to connect to it.

• Built-in SharePoint behavior   External content types provide SharePoint behaviors (such as lists and Web Parts) and capabilities (such as the ability to search or work offline) to external data and services, so users can work in their familiar work environments without having to hunt for data or learn and interact with different (and proprietary) user interfaces.

• Secure access   External content types adhere to the security put in place by both the external system and SharePoint products and technologies. You can have full control of who accesses what data by configuring security in SharePoint.

• Simplified maintenance   Because external content types can be created once and used by multiple solutions in various scenarios, you can manage them easily. For example, you can manage their access permissions and connection and data definitions in one central location.

BDC Models

A BDC model is the heart of Business Data Connectivity (BDC) service. The data structures, such as Entity (external content type) and Method, that abstract out complex details about an external system are collectively named MetadataObjects and compose the BDC model. The BDC model for an external system is essentially an XML file. When you use SharePoint Designer 2010, this file is generated automatically. However, if you are using Visual Studio or another XML editing utility, you must manually generate the BDC model according to the published BDC schema.

External lists enable writing back to the external system if the external system allows it, and if it is modeled accordingly by the external content type. This implies that users can edit external data directly from within SharePoint Online. Any changes that were made to the items in the list are synchronized automatically with the external system. By using the Refresh data button in the list, you can synchronize and get updated data from the external system automatically.

External Lists

An external list enables accessing data from external systems in the same way that SharePoint list data is accessed. External lists use external content types as their data sources. External lists enable you to use the metadata that is already defined about an external content type to create a SharePoint list that has external data that looks and performs like any other SharePoint list.

External Data Columns

Microsoft Office SharePoint Server 2007 provided the Business Data list column type that is now available to all SharePoint lists (except external lists) in SharePoint Online. The Business Data list column type is now named external data column, and it enables users to add data from external content types to standard SharePoint lists. Just like an external list, the external data column can display data from any external content type that is modeled in the BDC. Figure 1 shows a SharePoint list that was extended with external data columns from the Customer external content type.

Figure 1. SharePoint list with external data columsn added

Business Data Web Parts

BCS provides the following Web Parts for accessing read-only data surfaced by external content types:

• External Data List

• External Data Item

• External Data Item Builder

• External Data Related List

• External Data Connectivity Filter

For more information, see Where Can You Show External Data?

External Content Type Picker

An external content type picker provides a means for selecting a pre-defined external content type from the list of available external content types in SharePoint Online.

External Item Picker

An external item picker provides picking and resolving functionality for external items on the server and in rich client Microsoft Office applications. You can embed a picker in a form or page for scenarios in which a user should be able to pick an external item, such as customer "John" from a list of customers.

Secure Store

Secure Store Service is a shared service that provides storage and mapping of credentials such as account names and passwords. It enables you to securely store data that provides credentials required for connecting to external systems and associating those credentials to a specific identity or group of identities. You can configure Secure Store Service so that multiple users can access an external system by using a single set of credentials on that external system.

For example, if a user named "Fred" has one user account hosted on SharePoint Online and another in a Windows Communication Foundation (WCF) application, the SharePoint Secure Store Service mechanism enables his WCF credentials to be mapped to a group in Secure Store. As a result, if he uses a solution from SharePoint Online to obtain data from the WCF application, SharePoint Online looks up the Secure Store Service database on the server and provides group credentials to the WCF service on behalf of Fred. In this way, Fred is able to access the data secured by the WCF service without needing to provide a separate login.

SharePoint Client Object Model

The SharePoint client object model provides a method for interacting with SharePoint sites from a client application in the browser.

The client object model provides three parallel and comparable representations of the core objects in the SharePoint server object model: a .NET Framework managed model, a Microsoft Silverlight model, and a JavaScript model. These client object models provide remote access to SharePoint data and functionality.

The client object model builds on the SPList class to perform CRUD operations from the client through the SharePoint Web services.

Developing BCS Applications

The following information is useful as you begin to develop BCS applications.

Tools Used for Developing BCS Applications

You have essentially two options for developing applications and components for BCS: Visual Studio 2010 and SharePoint Designer 2010. However, because custom code is not currently allowed for connecting to external data sources from SharePoint Online, the preferred tool is SharePoint Designer 2010.

SharePoint Designer 2010 simplifies development of SharePoint applications and provides a great tool for rapid application development for Visual Studio developers, without the need for code.

Creating External Content Types and Associations

As previously explained, an external content type is a reusable collection of metadata that contains connectivity information and data definitions plus the behaviors you want to apply to a certain category of external data. Creating an external content type is usually the first step in bringing data from external data sources into SharePoint Online by using BCS. After you have an external content type, you can use it with any of the various presentation features of BCS such as external lists, external data columns, and external content controls for Office applications to show external data in SharePoint Online.

Creating an external content type in SharePoint Online involves the following steps:

1. Create a WCF end point that exposes the external data source and the operations you want to allow users to perform.

2. Using SharePoint Designer or Visual Studio, create an external content type that connects to the WCF service using the required credentials.

3. Create a Secure Store Service entry that maps a SharePoint Online user to the user account allowed by the WCF service.

4. Create an external list using the newly created external content type.

For more information on creating external content types by using SharePoint Designer with SharePoint Online, see the following resources:

• Customizing SharePoint Online with SharePoint Designer 2010

• Creating SharePoint Server 2010 External Content Type Associations with SharePoint Designer 2010

The following example demonstrates how to define a BDC model to connect to a WCF service.

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model Name="ExampleApplicationDefinition" 
 xmlns="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <LobSystems>
    <LobSystem Name="ExampleCRM" Type="Wcf">
      <Properties>
        <Property Name="WcfMexDiscoMode" Type="System.String">Disco</Property>
        <Property Name="WcfMexDocumentUrl" Type="System.String">http://example.org/?wsdl</Property>
        <Property Name="WcfProxyNamespace" Type="System.String">ExampleCrmNamespace</Property>
        <Property Name="WsdlFetchAuthenticationMode" Type="System.String">PassThrough</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="ExampleServer">
          <Properties>
            <Property Name="WcfAuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="WcfEndpointAddress" Type="System.String">http://example.org</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Name="Customer" Namespace="example.com" Version="1.0.0.0">
          <Identifiers>
            <Identifier Name="CustomerIdentifier" TypeName="System.Int32" />
          </Identifiers>
          <Methods>
            <Method Name="GetCustomers">
              <Parameters>
                <Parameter Name="CustomerId" Direction="In">
                  <TypeDescriptor Name="Id" TypeName="System.Int32" 
                   IdentifierName="CustomerIdentifier" />
                </Parameter>
                <Parameter Name="Customers" Direction="Return">
                  <TypeDescriptor Name="CustomerArray" 
                   TypeName="ExampleCrmNamespace.Customer[], ExampleCRM" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="Customer" 
                       TypeName="ExampleCrmNamespace.Customer, ExampleCRM">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Id" 
                           TypeName="System.Int32" IdentifierName="CustomerIdentifier" />
                          <TypeDescriptor Name="FirstName" TypeName="System.String" />
                          <TypeDescriptor Name="LastName" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetCustomer" Type="SpecificFinder" 
                 ReturnParameterName="Customers" 
                 ReturnTypeDescriptorPath="CustomerArray[0]" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

For more information on creating BDC models, see the following resources:

• Walkthrough: Creating Your Own BDC Model

• Sample BDC Model: Connecting to Different External Systems

• Code Snippets: Using the SharePoint Client Object Model with External Lists

Using the Client Object Model to Work with BCS

To programmatically work with external data in SharePoint Online, you must use the client object model. Because the client object model APIs execute remotely, they are not subject to the same restrictions that apply to sandboxed or server-side solutions.

Using the client object model for BCS is just like working with data that is native to SharePoint. The following code example shows how to retrieve the items in a specific list.

using System;
using Microsoft.SharePoint.Client;

class Program
{
    static void Main()
    {
        ClientContext clientContext = new ClientContext("http://intranet.contoso.com");
        List list = clientContext.Web.Lists.GetByTitle("Customer Info");
        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml = "<View/>";
        ListItemCollection listItems = list.GetItems(camlQuery);
        clientContext.Load(list);clientContext.Load(listItems);
        clientContext.ExecuteQuery();
        foreach (ListItem listItem in listItems)
            Console.WriteLine("Id: {0} Title: {1}", listItem.Id, oListItem["Title"]);
    }
}

Updating client objects by using the client object model is fairly simple. You retrieve the objects, alter properties, call the Update method for each object that you change, and then call the ExecuteQuery method. The following example modifies items in the Client API Test List, increasing the estimate for all development items by 50 percent (a common operation).

using System;
using Microsoft.SharePoint.Client;

class Program
{
    static void Main(string[] args)
    {
        ClientContext clientContext = new ClientContext("http://intranet.contoso.com");
        List list = clientContext.Web.Lists.GetByTitle("Client API Test List");
        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml =
            @"<View>
                <Query>
                  <Where>
                    <Eq>
                      <FieldRef Name='Category'/>
                      <Value Type='Text'>Development</Value>
                    </Eq>
                  </Where>
                </Query>
                <RowLimit>100</RowLimit>
              </View>";
        ListItemCollection listItems = list.GetItems(camlQuery);
        clientContext.Load(
             listItems,
             items => items.Include(
                 item => item["Category"],
                 item => item["Estimate"]));
        clientContext.ExecuteQuery();
        foreach (ListItem listItem in listItems)
        {
            listItem["Estimate"] = (double)listItem["Estimate"] * 1.5;
            listItem.Update();
        }
        clientContext.ExecuteQuery();
    }
}

Deleting client objects is just as easy. However, there is one very important dynamic around deleting client objects from a client object collection. You cannot iterate through the collection, deleting objects. As soon as you delete the first object, it causes the iterator of the client object collection to malfunction. The iterator may throw an exception, or it may quietly finish but not visit all items in the collection. Instead, you must materialize the collection into a List<T> using the ToList method, and then iterate through that list, deleting the client objects.

The following example deletes the test items from our Client API Test List. It shows using the ToList method to materialize the collection before you iterate through it.

using System;
using System.Linq;
using System.Collections.Generic;
using Microsoft.SharePoint.Client;

class Program
{
    static void Main(string[] args)
    {
        ClientContext clientContext = new ClientContext("http://intranet.contoso.com");
        List list = clientContext.Web.Lists.GetByTitle("Client API Test List");
        CamlQuery camlQuery = new CamlQuery();
        camlQuery.ViewXml =
            @"<View>
                <Query>
                  <Where>
                    <Eq>
                      <FieldRef Name='Category'/>
                      <Value Type='Text'>Test</Value>
                    </Eq>
                  </Where>
                </Query>
                <RowLimit>100</RowLimit>
              </View>";
        ListItemCollection listItems = list.GetItems(camlQuery);
        clientContext.Load(
             listItems,
             items => items.Include(
                 item => item["Title"]));
        clientContext.ExecuteQuery();
        foreach (ListItem listItem in listItems.ToList())
            listItem.DeleteObject();
        clientContext.ExecuteQuery();
    }
}

For more information on using the client object model, see the following resources:

• Using the SharePoint Foundation 2010 Managed Client Object Model

• Code Snippets: Using the SharePoint Client Object Model with External Lists

• Code Snippet: Get Item Data from an External List on the Client

Conclusion

BCS is a powerful tool for aggregating and using data from systems external to SharePoint. Beginning with the service update in November 2011, developers have the ability to use BCS in their SharePoint Online applications.

Additional Resources

For more information, see the following resources:

• SharePoint Online Developer Resource Center

• Customizing SharePoint Online with SharePoint Designer 2010

• SharePoint Online: An Overview for Developers

• SharePoint Online General Reference