Creating External Content Types and External Lists Through SharePoint Designer 2010
SharePoint 2010 defines the structure of the external data to which it will connect through external content types. External content types are similar to SharePoint content types that define the structure of the data stored in SharePoint. However, external content types define the structure of the data not stored in SharePoint. In addition external content types define the data access capabilities for the external data source.
For those who worked on Business Data Connectivity in SharePoint 2007, you can consider an external content type to be equivalent to a BDC entity. However, external content types contain much more information compared to a BDC entity.
External lists are lists based on external content types. They help you interact with the external data in the same way as the normal SharePoint list works.
External lists do not physically store data. They are just a medium to interact with the external data.
Follow the next steps to create an external content type and list in SharePoint Designer.
Try It Yourself: Create an External Content Type and List in SharePoint Designer
You were introduced to SharePoint Designer 2010 in Hour 1, “Introducing SharePoint 2010.” Although this hour limits the use of SharePoint Designer to creating external content types and external lists, Hour 19, “Working with SharePoint Designer 2010,” is dedicated to the topic of SharePoint Designer 2010.
1. Open Microsoft SharePoint Designer 2010. Click Open Site, enter the URL of the SharePoint site where you want to create the external content type, and click Open as shown in Figure 14.1. Enter the appropriate username and password if it prompts for credentials.
Figure 14.1. SharePoint Designer 2010
2. The site opens up in the SharePoint Designer as shown in Figure 14.2.
Figure 14.2. SharePoint 2010 site in SharePoint Designer 2010
3. Click the External Content Types link in the Site Objects pane on the left. The existing external content types if any are displayed.
If the Business Data Connectivity Service is not running, you get the error “The Business Data Connectivity Metadata Store is currently unavailable.” To fix this go to SharePoint 2010 Central Administration. Go to Application Management and open Manage Services on Server under the Service Applications section. In the Manage Services on Server screen make sure that the Business Data Connectivity Service has been started. Then go back to the Application Management screen. Click Manage Service Applications and create a new Business Data Connectivity service if none exists. Restart the SharePoint Designer, and you should be able to open the External Content Types screen.
4. Click External Content Type in the New section of the toolbar at the top as shown in Figure 14.3.
Figure 14.3. Creating a new external content type
5. The New External Content Type creation screen appears. Specify the Name and Display Name as AdventureWorks_Product. Keep the Office Item Type set to Generic List.
The Office Item Type allows you to choose a base type for the external content type. This enables you to use the content type in Office applications. For example, you could have an external contact type based on the Office item type Contact or Appointment, which you could then use in Outlook.
6. Click the Click Here to Discover External Data Sources and Define Operations link. This takes you to the screen that allows you to define data sources for the external content type as shown in Figure 14.4.
Figure 14.4. Creating a new data source in SharePoint Designer 2010
7. Click Add Connection. The External Data Source Type Selection dialog opens as shown in Figure 14.5. Select SQL Server as the Data Source Type and click OK.
Figure 14.5. The External Data Source Type Selection dialog
8. You now are to enter the details for the SQL Server Connection. Enter the details as shown in Figure 14.6. Replace the Database Server Name with the appropriate value.
Figure 14.6. The SQL Server Connection dialog
9. Once the connection is successful, the database tables and views can be visible in the Data Source Explorer window as shown in Figure 14.7. Expand the Tables node and find the Product table. Right-click the Product table and click Create All Operations.
Figure 14.7. Data Source Explorer
10. The All Operations dialog displays as shown in Figure 14.8. You can click Finish or click Next to change the settings for this data source. You want to go with the default settings, so click Finish.
Figure 14.8. The All Operations screen
11. The various operations for the data source are created. This can be seen in the right section of the SharePoint Designer, as shown in Figure 14.9.
Figure 14.9. External content type creation
12. Finally save the content type. Go back to the External Content Types screen, and you can see AdventureWorks.Product. Select it and click create External List in the New section of the toolbar at the top.
13. In the Create External List screen enter AdventureWorks.Products as the name of the list and click OK. The list is created.
14. Before you can view the external list in the browser you need to give the site users permissions on the external content type, or else the call to the various operations for the external content type will fail. For this browse the SharePoint 2010 Central Administration. Go to Application Management and click Manage Service Applications. Click the Business Data Connectivity Service. You should be able to the see your external content type AdventureWorks.Product. Open the pop-up menu for the AdventureWorks.Product and click Set Permissions as shown in Figure 14.10. Add the appropriate users and select Execute permission. This permission needs to be given to all users, regardless of their access level; otherwise, they cannot even read the data. For site administrators, select all the permissions—that is, Edit, Execute, Selectable in Clients, and Set Permissions.
Figure 14.10. Setting permissions on an external content type
You can give permissions to all users by selecting the group NT AuthorityAuthenticated Users. If you are giving permissions to all users just give the Execute permission. For high-privilege users, add them separately in addition to the NT AuthorityAuthenticated Users and explicitly give them all permissions.
15. Browse to the SharePoint site. Go to Site Actions, View All Site Content. You can see the AdventureWorks.Products list. Open the list and you see the data shown in Figure 14.11.
Figure 14.11. AdventureWorks.Products external list
16. Add and edit items, and you can see these reflected in the database.
When adding or editing the items in the list, make sure you add data that complies with the database constraints. For example, the ProductSubCategoryID and ProductModelID have foreign key constraints and you need to add one of the valid values that you can pick up by looking at the existing records.