Insight from Agora Consultants

Connecting to external data in SharePoint 2010 with CRUD Operations

There may have various data sources in one enterprise using by different applications, SharePoint as a portal it will have need to integrate business data from back-end server applications. This is BDC came out in SharePoint 2007.

Connecting to external data in SharePoint with BDC in SharePoint 2007 is difficult due to the lack of designer. Although it’s not hard to create a read-only solution to display data in Business Data List Web Part, it was not simple to create a solution that allow users to make changes and write data back to the external sources.

With Business Connectivity Services (BCS) in SharePoint, life becomes easier to connect to the external data sources with the CRUD (Create, Read, Update, and Delete) methods.

SharePoint Designer 2010 provides a UI to create External Content Types with CRUD operations really easy. We can now simply create an external data List using the following 3 steps.

1. Create a External Content Types

Launch SharePoint Designer, open the site and click ‘External Content Types’, create a New External Content Type, When you click create new External Content Types, SharePoint Designer will start a Wizard:


As showed above, first just give the new External Content Type a Name, and then we click the External System link to define to external data source we are going to use and the operations which will be used to manipulate the data in the List.

2. Define External Data Source and Operations

First we click ‘Add Connection’ to add Data Source. In my case, I connect to SQL Database and use the table ‘Customer’ as my data source.

Then we define what kind of Operations to create for External List, it requires at least ‘Read Item’ operation. For simplicity, just right click table name ‘Customer’, in the popup menu, click ‘Create All Operations’, this will give you another Wizard to setup the parameters for those methods.


After all the steps done, then this will be the final version for my External Content Type looks like:


3. Create External List


To use this External Content Type, click ‘Create Lists & Form’ and select ‘Create New External List’, type List Name ‘Customer’ and click ‘OK’.

Now you have a List call ‘Customer’ in SharePoint site which you can display data from back end database Customer Table, Edit the record and write back to Table. Really easy without any coding, all in steps 1, 2, 3.

SharePoint 2010 External List is very easy and powerful to display the external data. But for Editing data, although we can use SharePoint feature to validate required field, field type, but for more complicated rules, for example, format data, related fields dynamic dropdown items, these will not be easy to implemented in External List.

Comments are closed