How to: Create an External Content Type Based on a SQL Server Table

  • Article

Applies to: SharePoint Server 2010

Creating external content types that connect to Microsoft SQL Server is very easy with Microsoft Business Connectivity Services (BCS). The External Content Type Designer in Microsoft SharePoint Designer 2010 supports Microsoft SQL Server databases. You can simply discover your database, point to the table, view, or stored procedure that will perform the operations you want, and then return the required data and use it to create your external content type without writing any code or XML.

In this topic, you will learn how to create an external content type named Customer that is based on the Customers table in the Northwind SQL Server 2000 sample database.

Prerequisites

  • To keep this procedure simple, Microsoft SQL Server 2005 or later installed on the server running SharePoint Server. If Microsoft SQL Server is installed on a different machine, you will need to configure Secure Store service. See How to: Use Secure Store Service to Connect to an External System for more information.

  • The Northwind SQL Server 2000 database installed on the server running Microsoft SQL Server.

  • Microsoft SharePoint Designer 2010 installed on the client computer.

Creating an External Content Type Based on a SQL Server Table

SharePoint Designer 2010 requires a SharePoint site to store the files and artifacts it generates (such as external lists) when you use the External Content Type Designer to create external content types. Therefore, as a first step, you need to create a SharePoint site on the server running SharePoint Server to store the artifacts of the external content type and open it in SharePoint Designer on the client.

To create and open a SharePoint site in SharePoint Designer

  1. Browse to the server running SharePoint Server and create a new Web site.

  2. Type the URL of the new Web site as http:// yourservername/sdksamples.

  3. Select any site template, for example, Blank Site.

  4. Click OK to create the site.

  5. On the client computer, start SharePoint Designer, and open the site you created. The site should open in the main window of SharePoint Designer.

Notice the new navigation pane in SharePoint Designer. This pane is designed to accommodate all of the SharePoint objects (such as lists and libraries, external content types, master pages, and workflows) that you can create and edit in SharePoint Designer.

To define the general external content type settings

  1. To create an external content type, start by clicking External Content Types.

  2. On the Ribbon, click the External Content Type button, as shown in the following figure. This displays the External Content Type Designer for creating an external content type.

    Create new external content type

  3. On the New External Content Type page, specify the Name, Display Name, Namespace, and the Office Item Type, as shown in the following figure. The Office Item Type you select here determines the Outlook behavior you want to attach to the external content type. In this example, the Customer external content type will behave like a native Contact Item when taken offline in Outlook. The Offline Sync for external list field determines whether to enable users to take external lists based on this external content type offline in Microsoft Outlook or SharePoint Workspace. In this example, accept the default Enabled setting.

    Customer external content type information

To add a connection to the external system

  1. To specify the data source for the external content type, click Click here to discover external data sources and define operations.

  2. Click the Add Connection button, select SQL Server in the External Data Source Type Selection dialog box and then click OK.

  3. In the SQL Server Connection dialog box, specify the name of the SQL server and type Northwind as the database name as shown in the figure below, and then click OK.

    SQL Server connection properties

  4. After validating the connection to the server running SQL Server you specified in the previous step, the Data Source Explorer shows the database objects in the Northwind database. Expand the database to view the tables, views, and the stored procedures it contains. For this example, select the Customer table.

Now you can define the operations that can be performed on the Customer external content type.

To define the supported operations and their parameters and filters

  1. You must define the methods on the external system that will perform the operation you want and their parameters. For a SQL Server database table, this is very straightforward because SharePoint Designer can auto-generate the basic methods for you. In this example, you will create all the basic operations for the Customer external content type: Create, Read Item, Update, Delete, and Read List.

    In the Data Source Explorer, right-click the Customers table, and then click Create All Operations, as shown in the following figure. This auto generates the methods for creating, deleting, reading, and updating records in the Customers table in the Northwind database. You will now be able to select the columns you want to expose from the Customers table and define parameters.

    Create all operations for Customers

    Note

    Instead of auto generating all basic operations, you can also create the operations individually by using the various options on the shortcut menu. You might want to do this for a database table if you do not want to allow users to perform all the basic operations or if the database does not support certain operations. Also, the Create All Operations command is available only for SQL Server tables and views. On views that span multiple tables, ensure that write operations are supported, or Create All Operations will fail. For data sources other than SQL Server tables (and views) (such as Web services or SQL Server stored procedures), you need to create each operation separately in SharePoint Designer

  2. Next, select the columns you want to expose and the input and return parameters for the operations, and filter parameters.

    To configure parameters, click Next. The Parameters Configuration dialog box displays all the columns in the Customers Table. Notice that by default, all columns are selected. If you do not want all of the columns, deselect the columns that you do not need. In this example, let’s accept the default.

    1. Notice that the CustomerID is the currently selected field. Deselect the Required check box as we do not want users to enter CustomerID in the Update form. However, leave the ReadOnly checkbox enabled. This enables update capability on the external content type. If the ID field is not set to ReadOnly, then you cannot update the items in the external system.

    2. Next do the following two important configurations. If your external content type maps to an Outlook item type, map the fields from your external content type to Outlook item fields. For example, for an Outlook Contact Item, map the FirstName from the database to the FirstName column in an Outlook Contact by mapping it to FirstName in the Office Property field. This maps the FirstName field from the database to the First Name in the Outlook Contact Item and enables the FirstName field to appear in the First Name field in the Outlook Contact Item form. In this example, first map the ContactName to the FullName field as shown in the figure. Then repeat this step for the Address, Phone and Fax fields.

      Map properties to Outlook Contact item fields

    3. For each field, decide whether it should be available in the External Content Type Picker. If a field should be available in the picker, select the field and select the Show in Picker check box. For this example, select The Show In Picker checkbox for the ContactName and Company Name fields.

    When you are finished, click Next.

  3. In the Filters Parameters Configuration dialog, you define filters for your data. Filters help limit the external items (rows) returned from the operation from the external data source. It is always strongly recommended that you add a limit filter to limit the number of rows coming back from the external system during a Read operation. To do this, click Add Filter Parameter. In the Properties pane, click (Click to Add). In the Filter Configuration dialog box, name the new filter LimitFilter. In the Filter Type field, select Limit. Then click OK.

    Configure limit filter

    In the Default Value field, enter 1000 as the number of rows you want returned from the database. Note that this will limit the number of rows returned by an operation to only 1000 rows. Click Next.

    Note

    Ensure that the default value you enter for the limit filter is less than 2000. For database connections that have more than 2000 rows, Business Data Connectivity (BDC) service throttles the results and therefore will fail to return any data from the external system. If you must return more than 2000 rows, then an administrator must increase the default throttling numbers on the server. For more information about throttling limits and how to increase them, check out the Administrators Guide on TechNet.

  4. Click Finish. This generates all the basic operations, as shown in the following figure.

    External content type generated operations

  5. Click the Save button on the Ribbon to store the external content type definition in the BDC metadata store on the server running SharePoint Server. You have now successfully created an external content type.

The external content type should be immediately available to all Office client and server products for use by the Business Connectivity Services presentation features and by custom solutions through the Business Connectivity Services object model. For more information, see How to: Create External Lists in SharePoint to create an external list for the Customer external content type you just created.