Exercise 1: Creating External Content Types with SharePoint Designer 2010

In this task you will connect to the customers list in the Adventure Works database. You will start by defining the External Content Type in SharePoint Designer 2010 and finish by creating a new External List using the External Content Type.

Task 1 – Define the External Content Type

In this task, you will create a new External Content Type using SharePoint Designer 2010.

1. Open SharePoint Designer and load the existing External Content Types.
   1. In Internet Explorer navigate to https://intranet.contoso.com/sites/BCSOffice
   2. Click Site Actions -> Edit Site in SharePoint Designer

   3. In SharePoint Designer locate the Navigation pane and click External Content Types

      Figure 1

      External Content Types List

2. Create a new CustomerExternal Content Type
   1. Click the External Content Type in the New group in the External Content Types ribbon tab.
   2. Click the Name link and rename the External Content Type to Customer
   3. Click the link by the External System label to define the external data source

   4. In the Operation Designer, click the Add Connection button

      Figure 2

      Define Data Source Window

   5. In the External Data Source Type Selection dialog, choose SQL Server and click OK
   6. In the Database Server text box enter demo2010a
   7. In the Database Name text box enter AdventureWorksLT2008
   8. In the Name text box enter AdventureWorks Lite

   9. Click OK to create the data source

      Figure 3

      Define SQL Server Connection

3. Add the read and write operations to the new External Content Type
   1. In the Data Source Explorer tab, expand AdventureWorks Lite and locate the Customer table.

   2. Right click the Customer table and select Create All Operations

      Figure 4

      Create All Operations

   3. In the All operations dialog, click Next to move to the first step

      Note:

      This is the point where you will define the relationship between the data in the SQL database and the operations SharePoint needs to manage the external data.

   4. In the Parameters Configuration page wizard page select the Company Name item

   5. Once the Company Name item is select, check the Show In Picker check box

      Figure 5

      Parameters Configuration

   6. Click Next to move to the next page

      Note:

      Applying a filter of type Limit will ensure performance by limiting the amount of data SharePoint deals with on each read.

   7. Click the Add Filter Parameter button

   8. In the Properties section, click the Click to Add link.

      Figure 6

      Filter Configuration

   9. In the Filter Configuration dialog, choose a Filter Type of Limit and click OK

      Figure 7

      Limit Filter Configuration

   10. In the Default Value control in the Properties section, enter 100
   11. Click Finish to complete the operations definition
4. Save the new CustomerExternal Content Type
   1. In the Quick Access Toolbar click the Save button

   2. Click the Summary View button in the External Content Types ribbon tab

      Figure 8

      Completed Customer External Content Type

Task 2 – Create an External List

In this task, you will use the External Content Type from the previous task to create a new External List that will provide access to the database information from within SharePoint.

1. Create the new External List using the CustomerExternal Content Type.
   1. Click the Create Lists & Form button in the External Content Types ribbon tab

   2. In the List Item text box enter External Customers

      Figure 9

      Create External List

   3. Click OK to create the new list

Exercise 1 Verification

In order to verify that you have correctly performed all steps of exercise 1, proceed as follows:

Test the External Content Type and External List

Test the External Content Type and External List by accessing the list in SharePoint and updating an item in the database.

1. Verify the External Customers list is available in the SharePoint site
   1. In Internet Explorer navigate to https://intranet.contoso.com/sites/BCSOffice
   2. Click the External Customers link in the Navigation bar

   3. Verify the customers are visible in the list page

      Figure 10

      External Customers List

2. Edit one of the customers to verify the Update operation works
   1. Select the first item in the list

   2. Click the drop down arrow on the item and select Edit Item

      Figure 11

      Edit Menu Option

   3. In the dialog, change the FirstName to John

   4. Click Save to commit the changes

      Figure 12

      Edit External Customer

   5. Verify the change is reflected in the customers list

      Figure 13

      Updated Customer Record

      Note:

      If you would like to verify the updated data in the database, open SQL Management Studio and perform a query against the SalesLT.Customer table in the AdventureWorksLT2008 database.