BCS with SharePoint Online

Introduction

Business Connectivity Services (BCS) is a feature of SharePoint which allows to connect to external data sources and perform CRUD (Create Read Update Delete) operations. The data sources can be one of the following:

• SQL Server
• WCF Service
• .NET Type

In this blog I will walk you through the detailed steps involved in implementation of BCS in SharePoint Online with WCF service as a data source. Below are the major steps required for implementation:

• Implementation of WCF REST Web Service
• Web Service integration with SharePoint Online(SP-O) through BCS

Implementation of WCF REST Web Service

Windows Communication Foundation (WCF) is a framework for building service-oriented applications. Using WCF, you can send data as asynchronous messages from one service endpoint to another. A service endpoint can be part of a continuously available service hosted by IIS, or it can be a service hosted in an application.

I will not explain a WCF service implementation here, since there are lots of blogs out there on WCF REST services implementation. Create new WCF service project from Visual Studio and add below CRUD operations:

• Create Item
• Read Item
• Read All Items
• Update Item
• Delete item

Let’s say CUSTOMER is a table in Database with below columns which are pretty much self-explanatory:

• customerID
• FirstName
• LastName

Operations supported by WCF service would more likely be as below:

        public List<Customer> ReadList()

        {

//Read all items Code goes here         

        }

        public bool Update(int customerID, string FirstName,string LastName)

        {

// Update item Code goes here           

        }

        public bool Create(int customerID, string FirstName,string LastName)

        {

// New Item Code goes here       

        }

        public void Delete(int customerID)

        {

//Delete item Code goes here            

        }

        public Customer ReadItem(int customerID)

        {

//Get Item  by ID Code goes here        

        }

You can follow this blog for WCF REST service implementation. Post implementation of WCF serice publish it to IIS and make a DNS entry of it so that we can use it over internet and can access it from SharePoint online site.

So assuming that our web service is ready we will move forward to the next step.

WCF Service Integration with SP-O through BCS

The External Content Type (ECT) is a core concept of BCS through which we can integrate external data with SharePoint. Open your site in SharePoint designer and follow some easy steps to create ECT.

    1.Open SharePoint designer, select external content types and click Add connections. Select WCF service as a Data Source.

    2.Fill the details in WCF connection dialog box as follows:

2.1 For the Service Metadata URL setting, type the URL of the service endpoint created in STEP1, appending the ?wsdl string to the end of the URL. e.g.https://www.abcd.com:41003/WCFCustomer.svc?wsdl.

2.2 In the Metadata Connection Mode field, select WSDL.

2.3 In the Service Endpoint URL field, type the service endpoint URL. For example, type http://www.abcd.com:41003/WCFCustomer.svc

2.4 In the Name field, type WCFCustomer.

Note: Our web service does not have authentication, so we can connect with user’s identity. Otherwise we need to create a Secure Store Application ID entry from SharePoint Admin center. Secure Store Application ID entry will have credentials which can be used to authenticate user to access a web service.

    3.SharePoint designer will validate the connection and it will get displayed in Data Source Explorer. Expand the connection and we will be able to see all the methods that we have implemented in a web service.

Note: User must have Edit and Execute permissions in Metadata store, otherwise it will give an error saying Access denied by BDC. We assign metadata store permissions to user through SharePoint admin center in BCS by select ting “Manage BDC Models and External Content Types”

    4. We need to define supported operations for the external content type.

1. Right-click the ReadItem method in the Data Source Explorer, and then click New Read Item Operation on the context menu.
2. In the Read Item wizard that opens, click Next on the Operation Properties page to keep the default Operation Name and Operation Display Name values for the operation.
3. On the Input Parameters Configuration page, notice that there is an error in the Error Messages group box about an identifier field for this external content type that should be specified. To do this, select the CustomerId field in the Data Source Elements group box, and then select the Map to Identifier check box in the Properties group box. This sets the CustomerID field as an identifier for this external content type, not only for this parameter. The error message disappears. Following figure shows the Read Item operation input parameters.

  Figure: Read Item operation input parameters

4. Click Next to continue to the Return Parameter Configuration page.
5. On the Return Parameter Configuration page expand the ReadItem node in the Data Source Elements group box.
6. Click the CustomerID parameter in the Data Source Elements group box.
7. Select the Map to Identifier check box in the Properties group box. Notice that field is automatically marked Read-Only.
8. Map the FirstName field and LastName field to Office properties. Select the FirstName field in the Data Source Elements group box, and then select the First Name (FirstName) property from the drop-down list for the Office Property setting in the Properties group box. Repeat this process for the LastName field, mapping it to the Last Name (LastName) Office property. Following figure shows the Read Item operation return parameter.
9. Click Finish to complete the creation of the Read Item operation.

Figure: Read Item operation return parameter

Similarly create External Content Type Operations for ReadAll, Create, Update and Delete operations.

    5. ECT is ready for use post implementation of all ECT operations. Click on ‘Create List and Forms’ in SharePoint designer and fill the details to create specific list and forms. It will create a list as well as forms which will be an InfoPath form.

Create an external list from SharePoint site using this external content type and that’s it. 

Hope it helps somebody to quick start with BCS and SharePoint Online. Any comments, questions are welcome. :)