Business Connectivity Services (BCS) is still a great addition to any version of SharePoint On-Premises and also SharePoint Online. Business Connectivity Services is used to make your External Data such as Microsoft SQL Server Databases, Oracle, Dynamics, Salesforce.com and many more data sources available within Microsoft SharePoint. Business Connectivity Services is made up of a suite of components such as External Lists, Business Data Web Parts, External Data Column, User Profiles, Search and throughout the Microsoft Office applications such as Word, Excel, Access, Outlook and Visio.
Not all of these components are available to you unless you use the Enterprise version of SharePoint, but nonetheless, the External List is perhaps the most useful component and that is available for all versions of SharePoint including SharePoint Foundation from SharePoint 2010 onwards.
Business Connectivity Services External List
The Business Connectivity Services External List looks and behaves like a typical SharePoint List to any user. Users can view external data within the list as well as Create, Update and Delete items if you allow that. A major benefit of the Business Connectivity Services External List is that the external data is displayed but not imported. If you consider that you may have for example 100,000 contacts in an ERP or CRM system. You perhaps wouldn’t want to duplicate those contacts by importing them into a list and therefore duplicating the storage and overinflating the SharePoint Content Database. The External List links to the external data and displays it to the user. Through the use of filters and pagination, users get great performance and don’t suffer from record synchronization issues.
Once the connection to the external database has been created, users with permissions to do so within SharePoint can add the External List app. They simply select the External Content Type that they would like to use within the External List. The External List is then created and will display the data from your external database. The user can create and customize views, apply filters, and sorting in the same way as they would in a standard SharePoint List. There are a few limitations due to the data being linked to rather than imported. The limitations include the inability to assign item level permissions to the content, display RSS feeds, Create Quick Edit Views, and add custom columns. However, the benefits of linking to the data outweigh the limitations.
External Data Column
The Business Connectivity Services External Data Column is also a very powerful component of Business Connectivity Services. The External Data Column can be added to any SharePoint Internal List or Library. It provides a lookup to the database thus allowing the end user who is creating SharePoint content, to select a record and store it as meta data against the SharePoint Document or List Item. If you imagine that you are a user creating a Quotation for a Product within a SharePoint Document Library, it would be useful for instance to store the name of the company that the quotation is for. A standard SharePoint Single line of text column would suffice, but users would likely make typo’s, or use abbreviations which would mean that searching and filtering by the column would be inconsistent. Managed Meta Data is an alternative, but if this data already resides within an external data source, you may as well link directly to it.
Note: PremierPoint Solutions offer a great tool that enables you to import data via BCS into your Managed Meta Data Term Store: https://www.premierpointsolutions.com/
The below screenshot shows how you can select a row from an external database to store against the SharePoint Document or SharePoint List Item.
The fields from the selected row can be displayed in the List or Library view allowing users to sort, edit and filter the documents based upon the external data as shown below:
Search
Business Connectivity Services can also be configured as a Content Source within the Enterprise SharePoint Farm Search Configuration. Upon configuring the Business Connectivity Services Content Source, Users will be able to perform a search within the SharePoint Search Centre and results from the External Data Source will be included in the SharePoint Search Results. This is extremely powerful for an organization since a huge amount of time is wasted by employees trying to find content and then duplicating content if it cannot be found. A profile page can be created as the landing page from a search result. e.g. consider you search on ‘Lightning Tools’ as a customer. When you click the search result, you would be the Lightning Tools company name and address details as well as any other data from related tables whereby their is an association. Therefore you may also see Orders, and Order Details if Lightning Tools had placed an order with your organization.
If you are using a hybrid Search between SharePoint On-Premises and SharePoint Online, this becomes extremely powerful since you will be able to surface external data on-premises within the cloud via hybrid search.
Business Data Web Parts
One of the original components of the Business Data Catalog in SharePoint 2007 which has been carried forward into Business Connectivity Services for SharePoint 2010 onwards is the BDC Web Parts. The BDC Web Parts provide a read only view of external data within a web part that may be added to a SharePoint page to create a dashboard of data. The Business Data Web Parts including the Business Data List Web Part, Business Data Related List Web Part, and Business Data Item Web Part can be joined to show Summary/Detail or Master/Detail scenarios of Data. For example, you could display a few columns from the Customers table such as Company Name, Contact Name and Country. But upon selecting the row, more detail is shown to include Telephone, Address and Email Address. You could also have the scenario whereby you display Customer –> Orders, or Employee –> Holidays.
Microsoft Office Client
As mentioned in the opening paragraph of this blog post, Business Connectivity Services is available through most of the Microsoft Office Client suite. You can export data from an External List into Microsoft Excel which will then maintain a External List to Excel link. You can import data into Outlook as Contacts, Tasks, Calendar Items or Posts. You can also look up External Data from within a Microsoft Word Document using Quickparts.
Connecting To External Data
In order to create the connection to the External Data Source, you need to create what is known as an External Content Type. The External Content Type describes the table of data that you are connecting to. Each of the fields are strongly typed, and a number of methods make up the operation of Business Connectivity Services. For example, The Finder method returns all data and can contain filters. A Specific Finder returns a single row of data, and then there is a Creator, Updater, and Deleter method. If you wish to connect to other table in a one-to-many relationship, you will also create an association.
There are several ways to create the External Content Type;
- SharePoint Designer can be used to create an External Content Type for Microsoft SQL, a .NET Assembly or a WCF Service. the .NET Assembly and WCF Service would need to be coded, but you could connect to Microsoft SQL or Microsoft SQL Azure without writing code.
- Visual Studio enables you to create a .NET Assembly, or WCF Service External Content Type. You can connect to just about any External Data using this method if you are prepared to write the code. Using Visual Studio you can also create an Add-In/App which is deployed to a site in SharePoint 2013 or Office 365. The Add-In will only operate as an External List, and therefore you would not get any of the other functionality such as the Search, External Data Column etc.
- A third Party tool such as BCS Meta Man can create the External Content Type code free for around 30+ external data sources.
This post is one of many in a series of posts. Please check back for:
- Creating a Business Connectivity Services External Content Type in SharePoint Designer
- Working with Business Connectivity Services External Lists
- Business Connectivity Services Permissions
- Configuring Business Connectivity Services Search
- Setting up a Hybrid Business Connectivity Services Search with Office 365 SharePoint Online.
<Brett Lonsdale/>
