Welcome to our free SharePoint Power User Training tutorial. This tutorial is based on Webucator's SharePoint 2016 Power User Training course.
In this lesson, you will learn to work with business connectivity services.
Business Connectivity Services is a free service that comes with the Foundation version of SharePoint. The service enables administrators to create external lists linked to data outside of SharePoint.
Normal SharePoint lists expose data that is stored inside of a SQL content database that SharePoint creates and maintains.
Business Connectivity Services can expose data that is external to SharePoint, i.e., stored in a database separate from the SharePoint content database. An External Content Type is used to define the external data to the SharePoint system, kind of like a window.
Once the External Content Type is defined, you can create an External List using a special list template that comes with SharePoint and Business Connectivity Services. The External List works through the "window" the External Content Type provides to expose the data as if it were a normal list. The user is usually not even aware the data is really from outside the SharePoint site's normal content.
SharePoint Designer 2013 is a great tool for defining External Content Types (at the time of this writing, Microsoft was not planning on releasing a newer version of SharePoint Designer for 2016 but fully supports using the 2013 version of SharePoint Designer). Its graphical driven no-code approach makes if fairly easy to expose external data. Visual Studio is another tool that can be used to define External Content Types and has an advantage with more complex data configurations but it requires developer skills to create.
The following walk-through will show you how to use SharePoint Designer 2013 to create an External Content Type mapped to the Employees table from the Northwind SQL database.
You will need access to the Northwind database stored on a Microsoft SQL server for this walk-through. Additionally, you will need metadata store permissions to the Business Connectivity service in your farm.
Once the External Content Type is defined the next step would be to create an External List that links to the data through the External Content Type. This can be done on any site in the Farm.
The following walk-through will show you how to create an external list named "Employees" that is linked to the External Content Type defined in the previous walk-through.
You can use this page to remove fields you don't want to expose through SharePoint. Additionally, you can set properties of each field such as the Display Name, which is used to create labels for displaying the data.
You can also create the External List using the browser as was done in the walk-though earlier in this lesson.