Business Intelligence in SharePoint 2010 with Business Connectivity Services : External Content Types (part 2) - Defining the External Content Type

2/11/2011 9:09:01 AM

Defining the External Content Type

The next set of exercises step through how to create the most basic External Content Type. The example uses the Customers table of the AdventureWorks database.

There are several operations available for an External Content Type; however, two specific ones—Read List and Read Item—are required before it can be consumed by SharePoint.

A Read List operation provides all the rows of the table that meet the criteria defined in the operation. In the Customer example, a Read List operation can enable viewing of all the records from the Customers table in one SharePoint list, with it looking and acting like a SharePoint list. Although the data continues to live in the AdventureWorksLT database on SQL Server, it can be consumed in SharePoint, with the benefit of SharePoint views, and column sorting and filtering.

A Read Item operation allows for the isolation of an individual record from the list, based upon some unique identifier, such as a Customer ID. The Read Item operation also enables you to join together a regular SharePoint list with the records of the Customer table. In the forthcoming examples, a Customer Service Issues list, stored in SharePoint, can capture data about calls fielded by the Customer Service department. The list contains a mixture of SharePoint data and external data.

There are other operations available as well for External Content Types, including

  • Create Operation— Add a new record to a table.

  • Update Operation— Modify an existing record.

  • Delete Operation— Remove a record from a table.

  • Association— Define a relationship between two related tables.

Each of these operations is discussed in later sections of this chapter.

To begin building the external content types, complete the following steps:

Open SharePoint Designer 2010. Following installation, the application should be added to a SharePoint grouping on your computer’s Programs menu.

In SharePoint Designer 2010, click the Open Site button from the File Ribbon. Enter the web address for the site created in the previous section. For example, enter http://mss2010.mydomain.com/sites/BCSSample/.

In the Site Objects window, shown on the left side bar in Figure 2, select External Content Types.

Figure 2. Connect to the Adventureworkslt database.

In the External Content Types Ribbon at the top of the screen, select New External Content Type.

In the External Content Type Information section, click the link next to Name called New External Content Type. The link will be replaced with a text box to enter the name. Type BCS Customer. The Display Name updates automatically.

Next to the External System header, click the Click Here to Discover New External Data Sources and Define External Content Types link.

In the Data Source Explorer view, click the Add Connection button.

In the External Data Source Type Selection dialog box, select SQL Server as the Data Source Type and click OK.

In the SQL Server connection dialog box, enter the SQL Server connection information. The Database Server should be the name of the SQL Server and instance where you installed the AdventureWorks LT database. For example, type mySQLServer.myDomain.com. For Database Name, type AdventureWorksLT. For the connection options, select Connect with User’s Identity. Click OK.

After a connection to the AdventureWorksLT database is established, the database schema loads under the Data Source Explorer. Expand the AdventureWorksLT node. Note that tables, views, and Routines (SQL Server stored procedures) are all available for use. Expand the Tables node as well.

Right-click the Customer table, and a list of available operations loads. Begin by selecting New Read Item Operation.

The Read Item Wizard is divided into three parts. In the first part—Operation Properties—type Customer Read Item as both the Operation Name and the Operation Display Name. Click Next.

In the Input Parameters, the CustomerID field, by virtue of it being defined as a unique identifier for the table in the database, is automatically recognized as the identifier for the external content type. Notice when the CustomerID field is selected, the Map to Identifier box is selected. No changes are necessary on this screen. Click Next.

The last portion of the wizard—Return Parameter—defines which columns from the database will be available for consumption and is shown in Figure 3. No changes are necessary on this screen. Click Finish.

Figure 3. Creating the external content type read item operation.

In the Data Source Explorer of SharePoint Designer, right-click the Customer table and select New Read List Operation.

In the Operation Properties portion of the wizard, type Customer Read List as the Operation Name and the Operation Display Name. Click Next.

In the Filter Parameters section, no changes are necessary. Click Next.

In the Return Parameters section, select CustomerID field in the Data Source Elements section, and select the box in the Properties section labeled Show in Picker. Repeat this for the FirstName, LastName, EmailAddress, and Phone fields. Click Finish.

Click the Save icon in the upper-left corner of SharePoint Designer (or use the CTRL-S keys) to save the External Content Type.


Saving the external content type uploads the information to the BCS service application in SharePoint Central Administration. The external content type can later be removed or modified by going to Central Administration, Application Management, Manage Service Applications, and Business Data Connectivity.

  •  Recovering from a Disaster in an Exchange Server 2010 Environment : Recovering from Database Corruption
  •  Recovering from a Disaster in an Exchange Server 2010 Environment : Recovering Exchange Server Application and Exchange Server Data
  •  Recovering from a Disaster in an Exchange Server 2010 Environment : Recovering from a Complete Server Failure
  •  Sharepoint 2007: Add a Column to a List or Document Library
  •  Sharepoint 2007: Create a New Document Library
  •  Sharepoint 2007: Open the Create Page for Lists and Libraries
  •  Exchange Server 2010 : Developments in High Availability (part 3) : Backup and restore
  •  Exchange Server 2010 : Developments in High Availability (part 2) : Configuring a Database Availability Group & Managing database copies
  •  Exchange Server 2010 : Developments in High Availability (part 1) : Exchange database replication & Database Availability Group and Continuous Replication
  •  High Availability in Exchange Server 2010 : Exchange Server database technologies
  •  SharePoint 2010 : Cataloging the Best Scripts to Automate SharePoint Administration
  •  SharePoint Administration with PowerShell (part 2)
  •  SharePoint Administration with PowerShell (part 1)
  •  Sharepoint 2007: Approve or Reject a File or List Item
  •  Exchange Server 2007 : Configure the Client Access Server - Enable POP3 and IMAP4
  •  Exchange Server 2007 : Configure the Client Access Server - Enable and Configure Outlook Anywhere
  •  Exchange Server 2007 : Configure the Client Access Server - Create and Apply ActiveSync Mailbox Policies
  •  SharePoint 2010 : Understanding Windows PowerShell Concepts (part 3)
  •  SharePoint 2010 : Understanding Windows PowerShell Concepts (part 2)
  •  SharePoint 2010 : Understanding Windows PowerShell Concepts (part 1)
    Top 10
    Qooq - The First Culinary Tablet Made For The Kitchen
    HP Envy 23 TouchSmart - All-in-One Desktop PC
    Kobo Aura HD - An Excellent Ebook Reader
    Razer Edge Pro - It Combines Tablet, Laptop And Gaming PC In One
    Samsung Galaxy S4 Review (Part 8)
    Samsung Galaxy S4 Review (Part 7)
    Samsung Galaxy S4 Review (Part 6)
    Samsung Galaxy S4 Review (Part 5)
    Samsung Galaxy S4 Review (Part 4)
    Samsung Galaxy S4 Review (Part 3)
    Most View
    Canon EOS M – Is The Final Big Player?
    Windows 7 : Networking and HomeGroup Sharing - Sharing Between PCs (part 1) - HomeGroup Sharing
    KWA 150 SE – The Most Expensive Amplifier Of ModWright
    MSI FM2-A85XA-G65 Motherboard Review (Part 1)
    Nikon D600 Digital SLR Camera - Full-Framed Temptress
    Tips & Tricks Of November 2012 (Part 1)
    Popular GPS Apps Shootout (Part 1)
    Cambridge Audio Azur 751R - The Importance Of Being Earnest (Part 1)
    Google vs Apple vs Microsoft (Part 5)
    Roll Your Own Home Server (Part 1)