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

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:

1.	Open SharePoint Designer 2010. Following installation, the application should be added to a SharePoint grouping on your computer’s Programs menu.
2.	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/.
3.	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.
4.	In the External Content Types Ribbon at the top of the screen, select New External Content Type.
5.	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.
6.	Next to the External System header, click the Click Here to Discover New External Data Sources and Define External Content Types link.
7.	In the Data Source Explorer view, click the Add Connection button.
8.	In the External Data Source Type Selection dialog box, select SQL Server as the Data Source Type and click OK.
9.	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.
10.	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.
11.	Right-click the Customer table, and a list of available operations loads. Begin by selecting New Read Item Operation.
12.	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.
13.	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.
14.	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.
15.	In the Data Source Explorer of SharePoint Designer, right-click the Customer table and select New Read List Operation.
16.	In the Operation Properties portion of the wizard, type Customer Read List as the Operation Name and the Operation Display Name. Click Next.
17.	In the Filter Parameters section, no changes are necessary. Click Next.
18.	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.
19.	Click the Save icon in the upper-left corner of SharePoint Designer (or use the CTRL-S keys) to save the External Content Type.

Note

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.