SharePoint 2010 : How to create External Content type

By | April 10, 2010

English:

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
SPS2010_BDC-0000

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

 SPS2010_BDC-0001

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

SPS2010_BDC-0002

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

SPS2010_BDC-0003

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.

 SPS2010_BDC-0004

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

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.

SPS2010_BDC-0006

Follow the wizard to create an operation.

SPS2010_BDC-0007

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

 SPS2010_BDC-0008

SPS2010_BDC-0009

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

SPS2010_BDC-0010

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

SPS2010_BDC-0011

 

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.

9 thoughts on “SharePoint 2010 : How to create External Content type

  1. paras

    but during add new item i dont need a feld called (reportId)wich is auto incremented feild and is (not null) in sqlserver2008… rether than i simply wana other felds to show during add items..
    and this field must not come over there in pop up for add new item…….. during insertion or adding it must automatically increments. but it coming in my add new pop up….n i don’t wanna put identity manually…it must happen in database automatically…………………………any suggestions….???

    Reply
  2. Pingback: SharePoint 2010 BCS with "Item Level" security and PDF forms | SP ZAP - PDF sharepoint

  3. Pingback: » SharePoint 2010: External Content Types for DBA’s

  4. Anony Mouse

    The best part is when I am in Sharepoint Designer 2010 and click on the External Content Types, I get the message:
    “business data connectivity metadata store is currently unavailable”.

    Reply
  5. Uday Kiran

    Tell me one thing If I want auto populate the values  in the fields from  one or more tables and relationships,How I can achieve this???

    Reply
  6. felipe

    Does anyone know how to edit the column type once the External Content is created.

    Reply
  7. François

    Hi,

    Thanks for this great article!
    Do you know if Enterprise licence is needed for External Contype Use?

    Thanks for your help!

    François

    Reply

Leave a Reply