facebook google plus twitter
Webucator's Free Advanced SharePoint 2013 Tutorial

Lesson: Business Connectivity Services

Welcome to our free Advanced SharePoint 2013 tutorial. This tutorial is based on Webucator's SharePoint 2013 Power User Training course.

In this lesson, you will learn about business connectivity services in SharePoint.

Lesson Goals

  • Learn the basics of Business Connectivity Services.
  • Learn how to create an External Connection using SharePoint Designer.
  • Learn how to create an External Content Type using SharePoint Designer.
  • Learn how to define database operations using SharePoint Designer.
  • Learn how to create a list using the External List template.
  • Learn how to create an External List using SharePoint Designer.

Business Connectivity Services Basics

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 Basics

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. Business Connectivity Services Basics

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.

Creating an External Content Type with SharePoint Designer

SharePoint Designer 2013 is a great tool for defining External Content Types. 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.

  1. Open your site in SharePoint Designer.
  2. Create a new External Content Type mapped to the Northwind database Employees table.

    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.

    1. Click the External Content Types link in the Navigation pane of SharePoint Designer. External Content Types
    2. Click the External Content Type button in the External Content Types tab toolbar inside the New group. External Content Types
    3. Click the New external content type link next to the Name label in the External Content Type Information area of the New External Content Type tab editor.
    4. Type "<<your name goes here>>-EmployeesECT" in the Name field, your name is to make sure the External Content Type is unique within your farm. External Content Types
    5. Click the Click here to discover external data sources and define operations link next to the External System label.
    6. Click the Add Connection button on the Operation Designer editor.
    7. Select SQL Server in the Data Source Type and click the OK button on the External Data Source Type Selection dialog. External Content Types
    8. Type the name of a SQL server that has a copy of the Northwind database in the Database Server field, "northwind" in the Database Name field, "<<your name goes here>>-Northwind" in the Name (optional) field and click the OK button to save and close the SQL Server Connection dialog. External Content Types
    9. Expand the northwind database by clicking the "+" icon in the Data Source Explorer tab. External Content Types
    10. Click the "+" icon next to the Tables item in the Data Source Explorer tab to expand the tables list.
    11. Right-click the Employees table and choose the Create All Operations option. External Content Types
    12. Click the Next button on the first page of the All Operations dialog. External Content Types
    13. Uncheck the check box next to the Photo field in the list of the Employees table fields. External Content Types
    14. Click the Next button on the second page of the All operations dialog.
    15. Click the Finish button on the last page of the All operations dialog. External Content Types
    16. Click the Save icon in the upper-left corner of SharePoint Designer's window to save the External Content Type to the Business Connectivity Service of the SharePoint farm. External Content Types

Creating a List from an External Content Type

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.

  1. Switch back the browser window with your site open.
  2. Create a new list named "Employees" using the External List template that is linked to the <<your name goes here>>-EmployeesECT External Content Type.
    1. Click the Settings menu link and choose the Add an app option.
    2. Select the External List template. Lists from External Content Types
    3. Type "Employees" in the Name field.
    4. Click the Select External Content Type icon in the Data source configuration area. Lists from External Content Types
    5. Choose the <<your name goes here>>-EmployeesECT that matches your name and click the OK button. Lists from External Content Types
    6. Click the Create button to finish creating the new external list.
  3. Click the new Employees link in the Quick Launch and verify it shows data from the Northwind database Employees table.

Working with External Content Types and Lists

Duration: 15 to 25 minutes.
  1. Navigate to your team site.
  2. Open your site in SharePoint Designer.
  3. Create a new External Content Type mapped to the Northwind database Products table.
    1. Click the External Content Types link in the Navigation pane of SharePoint Designer. Exercise - Business Connectivity Services
    2. Click the External Content Type button in the External Content Types tab toolbar inside the New group. Exercise - Business Connectivity Services
    3. Click the New external content type link next to the Name label in the External Content Type Information area of the New External Content Type tab editor. Exercise - Business Connectivity Services
    4. Type "<<your name goes here>>-ProductsECT" in the Name field; your name is to make sure the External Content Type is unique within your farm.Exercise - Business Connectivity Services
    5. Click the Click here to discover external data sources and define operations link next to the External System label. Exercise - Business Connectivity Services
    6. Click the Add Connection button on the Operation Designer editor. Exercise - Business Connectivity Services
    7. Select SQL Server in the Data Source Type and click the OK button on the External Data Source Type Selection dialog. Exercise - Business Connectivity Services
    8. Type the name of a SQL server that has a copy of the Northwind database in the Database Server field, "northwind" in the Database Name field, "<<your name goes here>>-Northwind" in the Name (optional) field and click the OK button to save and close the SQL Server Connection dialog. External Content Types
    9. Expand the northwind database by clicking the "+" icon in the Data Source Explorer tab. Exercise - Business Connectivity Services
    10. Click the "+" icon next to the Tables item in the Data Source Explorer tab to expand the tables list.
    11. Right-click the Products table and choose the Create All Operations option. Exercise - Business Connectivity Services
    12. Click the Next button on the first page of the All operations dialog. Exercise - Business Connectivity Services
    13. Click the Next button on the second page of the All operations dialog. Exercise - Business Connectivity Services

      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.

    14. Click the Finish button on the last page of the All operations dialog. Exercise - Business Connectivity Services
    15. Click the Save icon in the upper-left corner of SharePoint Designer's window to save the External Content Type to the Business Connectivity Service of the SharePoint farm. Exercise - Business Connectivity Services
  4. Create a new SharePoint list from the <<your name goes here>>-ProductECT content type.
    1. Click the Create Lists & Form button on the External Content Types tab toolbar. Exercise - Business Connectivity Services

      You can also create the External List using the browser as was done in the walk-though earlier in this lesson.

    2. Type "Products" in the List Name field and click the OK button. Exercise - Business Connectivity Services
  5. Open the Products list in the browser and edit an item.
    1. Switch back to your browser window and click the Contoso Home Site link to refresh the page so the Products link will show in the Quick Launch. Exercise - Business Connectivity Services
    2. Click the Products link in the site's Quick Launch menu. Exercise - Business Connectivity Services
    3. Verify the Products list shows the data from the Northwind database Products table.
    4. Click the ellipsis link to the right of the first Products item with the ProductID of "1" and choose Edit Item from the drop-down options. Exercise - Business Connectivity Services
    5. Change the UnitPrice field to "20.0000" and click the Save button. Exercise - Business Connectivity Services
    6. Verify the Products item shows "20.0000" for the UnitPrice field back in the main view of all items. Exercise - Business Connectivity Services
  6. Close SharePoint Designer.