A Practical Guide to SharePoint 2013

A Practical Guide to SharePoint 2013
A Practical Guide to SharePoint 2013 - Book by Saifullah Shafiq

Tuesday, July 1, 2014

SharePoint Business Connectivity Services - Part 1

Business Connectivity Services is a centralized infrastructure in SharePoint 2013 and Office 2013 that supports integrated data solutions. SharePoint 2013 acts as an interface that you can use to surface data from external data sources. Enterprises do not store their data in one location. Enterprises use myriad of data sources and applications with each application connected to a separate data source. Before Business Connectivity Services, surfacing data from different sources was not easy and required lot of effort and custom programming.


SharePoint 2013 provides an easy way to connect SharePoint interface with Line of Business applications and this make analysis of data very easy and it especially helps the decision makers in the company to perform analysis on the data and thus they can take informed decisions in a timely manner. SharePoint provides data connectors out-of-the-box to connect to external data sources. SharePoint also allows developers to write new connectors to connect to the data sources that cannot be reached with out-of-the-box connectors. Developers can also connect to external sources through web services. Data can be published as an OData source or many other types of external data. The connectors are the communication bridge between SharePoint and the external system that hosts the external data. Reaching external data is one part. Second part is how you display data in SharePoint. SharePoint has several ways to present the external data. Most common way is to use an external list. External lists look and feel like regular SharePoint lists, except that they can only display external data. You can also use out-of-the-box custom list to display external data. In this case, you would external data column. It is a special column that you can add to any list and it is used to display data form an external data source. External data column is a special content type. SharePoint 2013 also includes Business Data Web Parts for presenting and interacting with external data and apps for SharePoint.

Once you have connected to the external data, you can perform different operations on the data. You can create, Read, Update, Delete, and Query on the data. This is a cool feature because you can not only read from the external source but you can also write back using Business Connectivity Services.  You can also integrate results from the external source into SharePoint search.

Business Connectivity Services provide an easy way to surface data in one location. The biggest challenge faced by the companies today is dealing with data stored at multiple locations and to access this data companies have to use different systems and mechanisms. It is a big challenge and requires lot of investment and resources. Business Connectivity Services, or BCS for short, has  solved this problem. One way is to pull data from external resources and store it inside SharePoint and the other is to keep data where it is but use Business Connectivity Services or Search to look at the data. Most companies have their data stored in File Shares in the form of documents. SharePoint Search allows users to index documents stored in the File Shares. Next chapter discusses search in detail and shows you how you can set up Search for searching file shares. You can also use Business Connectivity Services to search for information stored in external data sources. Companies take data from external sources, and some times these external sources are controlled by third party companies, and make it available to the employees through custom interfaces. This data consists of both structured and unstructured data. With BCS, companies have a way to integrate external data into SharePoint.

Some uses of BCS include applications that are crucial to companies’ success, for example, customer support systems, tracking systems (HR, etc), KPIs (dashboards), etc. For example, help desk is a popular application and almost all companies use this application in form or the other. Help desk data may be stored in separate databases. Queries may be stored in one database, knowledge based articles and tips may be stored in another database. To provide a unified system to the help desk technicians so that they can easily look at the queries and can find solutions in the knowledge base database, companies can use BCS. With BCS, companies can retrieve data, filter and sort it and then present it for interaction in an external list in SharePoint. Another popular usage is HR system. Resumes are stored in different formats. Some companies store resumes in the form of files whereas some companies prefer to store candidate data in the database and they use metadata system to identify the candidates easily. Storing data in the database lets companies automate several processes like setting up search agents and automatically connecting with the right candidates when needed. With BCS, connecting to these external databases is easy. A complete HR management system can be developed in SharePoint using Business Connectivity Services.

Business Connectivity Services have an advantage over other solutions and the biggest advantage is its ease of implementation. Custom solutions can be developed
using .NET or Javascript but implementing a BCS solution is very easy and does not require any programming unless your requirements are overly complex and advanced.

Business Connectivity Services is a centralized infrastructure that supports integrated data solutions. Infrastructure itself is unaware of any external system. External data is wrapped up in a definition called an external content type. External content types are centrally stored and secured and can be shared by many Business Connectivity Services solutions.

Business Connectivity Services has server-side components and client-side components. Both components use the same configuration data. For the server-side, the configuration data is stored in an external content type. External content types are stored in the Business Data Connectivity (BDC) Metadata Store database. For the client-side, the configuration data is stored in a BDC model on the client in the BDC client-side cache. The BDC model is just a version of the external content type that is exported to an XML file. The XML file is imported into the Office client. There is a lot more that can be discussed in this chapter, for example, how BCS works exactly but that is beyond the scope of this discussion. Below, you will setup an external content type, assign permissions and create an external list to surface data from the external database. You will also see learn to use Business Data List web part.

1. First, you need to setup a database that will act as an external data source. Create a database in SQL Server and name it “Users”. Add a table with the following fields and name it “Profiles”. Add some records in the table.

Figure 1: Users table
2. Open SharePoint site in SharePoint Designer. Click External Content Types under Site Objects. 

3. Click External Content Type button in the ribbon to create new content type.

Figure 2: Create new external content type
4. Type name of the new content type in the Name field. Call it NewECT. Click in the Display Name field and type NewECT.

5. In the field External System, click the link Click here to discover external data sources ….

6. Click Add Connection button.

7. Select SQL Server in the Data Source Type drop down that appears and click OK.

8. You will be prompted to enter database details.

Figure 3: SQL Server Connection

In the Database Server field, enter the name of your database server. In the Database Name, enter Users (that is the database you created above). Keep the first option (Connect with User’s Identity) selected. Click OK.

9. In Data Source Explorer tab, expand Users node, expand Tables node. Right-click Profiles and select Create All Operations.

10. Click Next.

11. At least one identifier should be specified. In the Errors and Warnings box, you will see an error that asks you to select at least one field from the table as an identifier. This identifier uniquely identifies an item in the external system to ensure proper runtime behavior.  By default field ID is selected and its properties are shown in the Properties box. Check Map to Identifier check box. Uncheck Required and Read-Only check boxes since ID field in our database is not unique. It should be a unique field and should be autogenerated. If you opt to keep ID field unique, then leave these check boxes checked.

We want to show one field in the external item picker. Select FirstName from the Data Source Elements and in the Properties, check Show In Picker check box.