Business Connectivity Service (External Content types) using SharePoint Designer

Business Connectivity services

Simple definition is SharePoint able to communicate with external systems, Such as Microsoft SQL Server, Siebel, and SAP, or a Web service. Users can view, add, update, or delete business data by using external lists or Business Data Web Parts in SharePoint.

Benefits of BCS:

1.       Provide CRUD operations.

2.       Using SharePoint designer 2010 its need simple Steps required to complete its Configuration, so no programing knowledge is required.

3.       Visual studio is available to Write Code of Piece to Create Custom BCS.

4.       SharePoint Foundation, standard and enterprise Level

5.       Data is crawled using Search service and available in search results

Some Terms to Remember 

1.       External Content type

 It’s represent the schema for external object/External entity/ external data.

2.       External List

It’s Instant of External Object inside the SharePoint. Its behave similar like a SharePoint List

How to configure External Contact Type using SharePoint Designer 2010

Open site in SharePoint Designer and click on External contact types.

1.    Click to create new External Content type.

Provide the Name, Display Name; Select Office Item Type based on c its Contain (Generic List, Appointment, Contact, Task, and Post)

Connecting to Data Sources

1.       Click to discover External data Sources and define operations

2.      Click on add Connection button

3.       Choose Data source Option. (For Now choose SQL-Server)

4.       After that Provide Server name, Database name and Authentication type

Authentication Options:

·         Connect with user’s identity 

Use when u has SQL-Server and SharePoint on the same machine

·         Connect with Impersonated Windows Identity

Secure Store Service with windows ID (For Example Active Directory) will Use for it

·         Connect with impersonated Custom Identity

Secure Store Service with Custom ID (For Example SQL-Server ID’s) will Use for it.

It will take some time to explore Data source.  It will show Structure of provided Database.

5.       Select Required Table or View and Right click to generate operations. For now Click on “ Create All Operation”

Setting up operations and properties

Press Next to parameter and identifiers (if Required to change , by default it populate based on database Structure )

Press Next to Set Filter (its use full when you have large amount of data), for now leave it blank and press Finish button.

Now you can see you Connection have all operation without any error. Press Save button at top left of the SharePoint Designer.

Creating External Lists and Verifying the Operations

1.       Open the site in browser

2.       Click on site actions , Press more Options

3.       Select the external List as template and press Create.

4.       Provide List Name, Description and select external content type. and press Create

After pressing create button you can see default all item View for that list and you will see all items from the Database table.

Add new item.

Verify from the Database

You can test by adding, updating and deleting the items from both ends, it will synchronize the data between SQL –Server table and SharePoint List.

Sample Database Script:

CREATE TABLE [dbo].[Product](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Title] [nvarchar](max) NOT NULL,

[Description] [nvarchar](max) NOT NULL,

[QTY] [int] NULL,

[Price] [float] NOT NULL,

[CategoryID] [int] NOT NULL,

)