Business Connectivity Services (BCS) in SharePoint 2010 provides new ways to connect and integrate with external data in SharePoint. Business Connectivity services also allows users to create external content types, external lists based on the external data. This opens up a new dimension on how you view external data in SharePoint. To get to know more about BCS, you can have a look at the Business Connectivity Services poster - http://bit.ly/bcs_poster
Lets get straight to work!
Here is a simple Customers table that you would like to bring to SharePoint.
Ideally you want to create a Customers List in SharePoint which can bring this Customers data into SharePoint and be in sync with the external data.
SharePoint Designer to the Rescue
If you are thinking ‘What, SharePoint Designer? Are you crazy!’ – Well, my dear friend, you are in for a surprise with what SharePoint Designer 2010 can offer you!
The first and foremost thing you would notice is the Office Ribbon integration:
No more folder views and is replaced by the Navigation pane:
With SharePoint Designer 2010, creating External Content Types is very simple!
Click on the External Content Types in the navigation pane. This will open the External Content Types tab.
Select New External Content Type from the ribbon.
This will create the new external content type
Go ahead and change the Name and Display Name to External Customers:
To create external connections and operations, click on click here to discover external data source an…
Now you can add connections:
SharePoint Designer allows to create external data sources connected to:
1) SQL Server
2) .NET Type
3) WCF Services
Lets select SQL Server as our external data source is in the SQL Server
Enter your server details. The Database Name will be Customers.
Now, I can see the Customers database and the Customers table that I am looking for:
Right click and create the operations. Create All Operations will create the necessary Create, Read, Update, Delete operations.
Go through the Wizard and complete it.
And here are the operations created:
Now, you can create External Lists pretty easily from the Ribbon:
Fill in your List details:
And our external list is created!
No pain, no hassles, pretty simple step-by-step procedure!
Integrating with Office Outlook
To go one step, further, you might actually want to use these Customers in your Outlook so that you can store them as contacts. Integrating external data to Office is just few clicks away with SharePoint Designer!
In the SharePoint Designer, Choose the Office Item Type as Contact for the external content type.
This will enable Outlook Contacts integration with the external content type.
Rest is to map the appropriate data source fields with Office properties.
Double click on Read Item and map the fields:
Select the appropriate Office field from the Office Property for each of the data source element.
Once mapped, save the changes in SharePoint Designer.
Open the external list in the browser.
Under the List tab in the Ribbon, click on the Connect To Outlook button. This will send the external list information to Outlook. As we have configured the external content type as Contact Office Item, they would appear as Outlook Contacts.
A new Outlook add-in will be installed:
Below is a screenshot of the external data as Outlook Contacts:
Sync to SharePoint Workspace
You can also sync this external list with SharePoint Workspace and take the data offline!
Here is a screenshot of the external list in SharePoint Workspace
