How to: Create an external list in SharePoint 2010 using BCS via SharePoint designer 2010 - Part 1

Hi All, 

I recently attended a session SharePoint user group (Singapore) and agenda was twofold: 

1. SharePoint Storage Optimization  
2. Introduction to Business Connectivity Services (BCS) in SharePoint 2010. 

The first session was about proper architecture design and intelligent sense storage management that is crucial for successful SharePoint implementation. The demo was given a company named "AvePoint" that specialize in SharePoint storage and identify organization's unique business objectives, usage patterns, deployment size, and distribution. They talked about: 

• Reviewing strategies for SharePoint storage optimization via intelligent content life-cycle management
• Automated Binary Large Object (BLOB) offloading 

Migration-free SharePoint management of legacy data stores such as file share.    

They have different suits for SharePoint 2007 and 2010 such as DocAve File Share Document Connector, DocAve File Share Multimedia Connector, migration tools, testing, data protection etc. Free trial versions available as well. 

The second session was more interesting for me as it deals with SharePoint 2010.  Business Connectivity Services (BCS) is the next generation of Business Data Catalog (BDC, SharePoint 2007) for accessing external data within SharePoint. 

In SharePoint 2007 context, BDC provides the integration feature that enables SharePoint 2007 to incorporate the business data from back-end server applications. For an instance, we are able to connect to display data from our line-of-business (LOB) such as SAP, Siebel via Web Services or database WITHOUT any CODING. The business data could be in SharePoint lists, web parts, search, and custom applications. However, there is problem in this approach: 

• BDC provides a read-only solutions.Therefore, it was not so simple to create a solution that enabled users to make changes and write that data back to the external store.
• There is no offline mode. That is, the ability to take all SharePoint content offline. 
• Lack of Designer.  

In SharePoint 2010,  Business Connectivity Services (BCS, formerly known as the BDC) does all the functionalists which BDC provided with new features: 

• BCS data could be read/write.
• The ability to take all SharePoint content offline using SharePoint Workspace 2010 (formerly Microsoft Groove)
• Auto-sync of lists and libraries (for documents, only actual changes are synched)
• Leverages SharePoint Designer 2010.
• Visual Studio 2010 support for creating .NET assembly connectors and we can write more advanced scenarios.

The bottom line is that Business Connectivity Services (BCS) in SharePoint 2010 is all about connecting to external data (External Content Types). Steve Sofian (SharePoint MVP) showed how to get an external lists (not from SharePoint content database) via SharePoint Designer 2010. The concept of external content types is fundamental to BCS.

Understanding External Content Types:

Basically, ETC describes the schema and data access capabilities of an external data source and its behavior within Office and SharePoint 2010. The external data source could be a web service, database, .NET assembly connector, external systems, Web 2.0 service etc. The data that lives outside of SharePoint and it could be line-of-business applications, web services or something custom. We can leverage these external content types in different ways such as using SharePoint 2010 (external lists) and inside office clients.

Different tools are provided by Microsoft to leverage BCS:

1. "Simple" solutions: Using SP Designer 2010 and browser. It connects to existing back-end integration services or simple databases. 
2. "Advanced" solutions: Using Visual Studio 2010 for advanced scenarios.

Steve showed simple external list using SharePoint Designer 2010  with no code.

Step1: Fire up SharePoint Designer 2010. Navigate to File >> Sites >> Open Site >> Go to the specific site. 

New SharePoint Designer 2010 Beta

  Step2: New SharePoint Designer 2010 has been revamped completely. Earlier version looked like Front page but latest version comes with all SharePoint 2010 artifacts such as Site Navigation, Site Objects etc. 

Step3: Before we go to SharePoint Designer, we have to make to sure that Business Data Connectivity services has been started. We need to go to Central Administration >> Application Management >> Manage Service Application >> Business Data Connectivity and check if this service is activated or not. 

Step4: Switch back to SharePoint Designer and click on "External Content Types". I have downloaded the "AdventureWorkLT" database and downloaded to my local machine.  On right top of SharePoint Designer, we click on "External Content Type" button as shown below.

Step5: We then fill in information about this new content type as follows: 

Name: Contacts

Display Name: Contacts

Namespace: Conotoso

Office Item Type: Contact

Office Sync for External List: Enabled. 

What this setting is trying to do is that we can get this list offline using a tool named SharePoint Workspace 2010 (formerly know Microsoft Groove 2007, part of Office client).

Step6: Add a new data connection. 
We click on "Add Connection" button. We set "SQL Server" as the data source type. Data Source Type could have three options: 
>> .NET
>> SQL Server
>> WCF Service

Step7: We then set up connection properties as follows:

Step8: Expand the tables and look for the "Customer" table. We have to create methods to be associated with this external content type. Lets create all the operations. Whats new in SharePoint 2010 unlike SP 2007 which is read-only methods, we are able to read/write methods which were not possible in SP 2007.

Step9: A wizard will come up where we have to set a few operations properties. Click "Next" button to proceed.  

Step10: We will get some errors and we have to address them. 

Go to LastName field and in Office property choose the "Last Name (LastName)" from the drop down. Furthermore, check the "Show In Picker" as well. The error will disappear. 

Go to "rowguid" field and clear the "Required" option. The warning will disappear.

The next screen we have to set some filter because the table has numerous columns and we do not want to show everything. Firstly, we hit the "Add Filter Parameter" and secondly click on filter property as shown below.    

From the "Filter Configuration" window, we choose the filter type to "Limit" and click OK button.

Once the filter limit is set, we can choose the default value to say 100 records.

Click on "Save" button and external content type will be saved to the BDC Metadata store.

Go to Central Administration >>  Application Management >> Manage Service Application >> Business Data Connectivity >> Manage. We will get "Contacts" as external data source.

Step11: Go to SharePoint 2010 site, Site Actions >> More Options >> In "Filter type" choose "List" >> select "External List" >> Hit on "Create" button.

In list name "BCS Contacts" and select the external content type as shown below. 

Select the "Contacts" external content type and click on "OK" button.

The list is created and it is not from the SharePoint content database but from different database.

Unlike BDC in SharePoint 2007 which is read-only solution, in SharePoint 2010 we can modify/delete via SharePoint UI. 

Cheers, 
--aaroh