How to: Create an External Content type using SharePoint Designer 2010 (Business Connectivity Services)

Community

Email Notifications

How to: Create an External Content type using SharePoint Designer 2010 (Business Connectivity Services)

Here we are going to do a lab that creates an external content type using SharePoint 2010. Assume your company has a database with product details. You need to retrieve those details from the database and show them in the SharePoint site. Here you need to create, update and delete those records within SharePoint environment. So basically we are talking about creating CRUD methods for the external database inside the SharePoint UI.

First I will breakdown entire process into few points where we can very easily follow.

Create an External Content Type in SharePoint Designer 2010.
Create CRUD methods.
Deploy the external content type to SharePoint server.
Create an external list
Test the lab

OK… will start SharePoint designer and connect to our site. Once you connected to the server you get all the site objects on your left pane. Select External Content Types and you get all the external content types listed on the right pane.

Create new external content type by clicking External Content Type button on the top left corner

It will open the new external content type screen. Here you can change the key information of your external content type such as Name, Display Name , etc.

Now we have to create the data source to connect to our external database. Go to the bottom of the screen and click ‘Click here to discover external data sources and define operations’ link.

Now you’re in the screen where you can manage connections to external data sources and operations for the external content type you are going to create.

Click Add Connection to add our data source and select SQL Server from the dropdown menu. Press OK button

Type your Database Server and Database Name. Select the appropriate security method to connect and press OK.

Here you can see your data source has been added to the list.

Now we need to add methods to perform our CRUD actions. So will select the Product table which we need to extract data from and right click. Here you can see all the operations you can add to the particular table. Select New Read List Operation from the menu.

Now you can see the pop up window where you can configure read list operation. Change the names as you want and click Next.

Next you can add filter parameters to filter your data. Otherwise it will make large result with unwanted data. Since our lab database doesn't have much data, we skip that part. Press Next.

Here you can see all you columns in the table and select the appropriate columns to show in the external list. You can change their Display Names as well. Make sure to select Map to Identifier check box in the primary key. Click Finish

Now you can see the Read List operation in the External Content Type Operations.

Create the read item operation the way we did to read list operation.

Create operation.

I have removed Product ID column from create because it’s an auto number field.

Update operation.

Delete operation.

Now we have created all the methods we want and you can see that in the External Content Type Operations

Now save the SharePoint Designer and it will automatically deploy the external content type into SharePoint server. If you go to the central administration site you can see the external content type has been deployed. Application Management – Manage Service Applications – Business Connectivity Services.