SharePoint 2010: How to create External Content type

External Content type allows you to make seamless interface between SharePoint 2010 and external data storage (ie. SQL server). Using External Content type, you can create an External List which stores the data to predefined storage. You can imagine of having a table with SharePoint list interface.

To create an External Content type, you need SharePoint Designer 2010. If you curious enough, the External Content type is enhancement of Business Data Connector (BDC) in previous version. It will now depend on Business Connectivity Services (BCS) – which more or less same as BDC.

Imagine that you have a table in SQL Database and you want to create SharePoint list which directly stores the data in that table.

1. Open SharePoint Designer and click on External Content Types

2. Define the new External Content Type name. Then click on “External System” to start to connect to external Data Storage.

 

3. Define connection to external data storage. (ie. DB name,  and security)

In my case, I use SQL server as my data storage.

There are 3 security connection options in SQL Server Data Source:
– Connect with User’s Identity   => it will use running user’s credential to access to DB server. This implies that you need to manage user’s access rights in DB server.
– Connect with Impersonated Windows Identity => it will use impersonated Windows Identity ; usually the Application Pool identity.
– Connect with Impersonated Custom Identity => it will use specified user/password combination ; you need to define the Custom Identity separately.

 

Now, we already have the External List with the tables and views.

4. Select any table for the External Content types. In this scenario, I select RequestTbl for my data storage. Right click on the selected table, and SPD2010 will show you the available operations.
The External Content Types requires at least “Read Item” and “Read List” operation.  In every operation you will need at least 1 identifier.

Follow the wizard to create an operation.

Don’t forget that every operation requires at least 1 identifier. You can choose ID field from the table.

 

You can also define the default view filter, for example, by default this external content type will show only the first 100 items.

5. Publish the new content type by click “Save” button.

Now you already have new External Content Type. You can also create external content type to call .NET CLR and WCF Service. Next , how to use the external content type in a list.