Implement Business Connectivity Services Using oData, Entity Framework And SharePoint Hosted Add-in

Introduction

Business Connectivity Services(BCS) supports exposing external Line-Of-Business (LOB) applications data to Share Point. There are multiple ways by which we can expose external data to SharePoint through BCS . They are,

.NET Framework
Using Visual Studio we can make use of Custom Business Data Catalog template to create a custom solution that connects to external Line Of Business. It can implement all the CRUD operations by defining the methods programatically.
SQL
Share Point Designer can be used to establish a connection directly to SQL Server and create an External Content Type list in SharePoint
WCF
The WCF service act as an interface from external LOB system to BCS.
OData
OData Service endpoints were introduced in SharePoint 2013 by which we can consume these oData endpoint in SharePoint Hosted Add-ins to perform CRUD between external LOB system and SharePoint. We will see how to implement this in this article.

What are we going to do

We will see how to implement Business Connectivity Services between SQL Server and SharePoint using oData. In order to facilitate this, we will be initially creating an oData WCF data service which will use Entity Framework to perform Data manipulation. This oData Service will be later used to create an External Content Type and thereby establish a BCS connection between SharePoint and SQL Server.

We will be using the below SQL Server table as the External Data Source,

We will then create the BCS Connection and provide an interface that does the entire CRUD operation from SharePoint as shown below,

Prerequisites

Lets create a table in SQL Server which will acts as the source data of the External Line of Business. We will make use of the below SQL Commands to create and populate the film table.

CREATE TABLE [Film](
[id] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
[FilmName] [varchar](50) NULL,
[Director] [varchar](100) NULL,
[Year] [varchar](10) NULL,
)
GO
INSERT INTO [Film]
VALUES('The Shawshank Redemption', 'Frank Darabont', '1994')
INSERT INTO [Film]
VALUES('The Godfather', 'Francis Ford Coppola', '1972')
INSERT INTO [Film]
VALUES('Schindlers List', 'Steven Spielberg', '1993')
INSERT INTO [Film]
VALUES('The Dark Knight', 'Christopher Nolan', '2008')
INSERT INTO [Film]
VALUES('12 Angry Men', 'Sidney Lumet', '1957')
INSERT INTO [Film]
VALUES('Pulp Fiction', 'Quentin Tarantino', '1994')
INSERT INTO [Film]
VALUES('Fight Club', 'David Fincher', '1999')
INSERT INTO [Film]
VALUES('Dunkirk', 'Christopher Nolan', '2017')

Testing the created table yields the below results.

Create oData Service

As the next step let's create the oData Service. We will be creating a WCF service which will act as the oData end point for the SharePoint Hosted Add-in. Lets start with the blank Visual Studio template.

We will be adding two projects to the solution.

WCF Service Application and
SharePoint Hosted Add-in

Let's add the first project - WCF Service Application.

Select the WCF Service Application template and name it as ‘FilmWCFService’.

It will by default contain Service.svc and IService.cs file which we can gracefully delete as we will be adding a WCF Data Service to this project.

Select and delete the above 2 files from the solution.

Add WCF Data Service

Right click the project and add WCF Data Service 5.6.4 as a new item to the project. We have named it as FilmsDataService.svc.

This will add FilmsDataService.svc.cs file which contains the core logic. However we will have to make some changes to the below highlighted code section. We will have to specify the Data Source Class Name there. We still don’t have one yet. In order to create the data source class we will make use of Entity Framework.

Add Entity Framework Connection

So as to create the Data Source Class, we will make use of Entity Framework that will create the Entity Class which we will use with WCF Data Service. Right click the project and add ‘ADO.NET Entity Data Model’.

This will open up the window where we will specify the item name as ‘Film’.

From the Entity Data Model Wizard, select ‘EF Designer from database’. Click on Next.

We now have to create a connection to the data base. Click on ‘New Connection’ to create a new connection.

Specify the Server Name and the database name. Click on Test Connection to check the database connectivity.

If the connection is successful we will get the below success message.

Before moving ahead specify the name for the connection settings. We have named it as ‘FilmsEntities’. Make a note of this, as we will be using this as the data source class name in the WCF Data Service file.

Now select the Entity Framework version that we will be using. If we want to use the latest version we can install it from Nuget Package Manager. For the time being we will use EF 5.0.

As we proceed to the next window, we have the option to select the SQL Server table from which we will fetch the data. Select the ‘Film’ table. Let's not pluralize the table name. If we select this option, if our table name is ‘Film’ it will be pluralized to ‘Films’ in the code behind which can create some confusion which is unnecessary. Let's specify the model name as ‘FilmModel’ and continue.

Thus the Entity Data Model has been created and it shows the SQL Server table structure as below.

Modify the Data Service Code File

Now we have to modify the Data Service class file with the Data Source Class Name of the Entity Model which we created above. Update it with the Entity Class Name ‘FilmEntities’.

We also have to modify the code within ‘IntializeService’ method with the below code block which will enable all the CRUD operations against the Entity Model.

config.UseVerboseErrors = true;
config.SetEntitySetAccessRule("Film", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

Thus we have completed the creation of WCF Data Service.Before hosting it let's test it by running Visual Studio Debugger. If the WCF Service creation was successful it will open up the IE and show us the below feeds.

Host the oData WCF Data Service

If we want to consume the created oData WCF Service we will have to host it in the IIS. Lets see how we can host it in IIS.

Create Application Pool

So to Host the WCF service we will create a IIS Web Site. Before creating the website let's provision an app pool for the IIS web site. Right click and Add Application Pool.

We have specified the name as WCFAppPool.

Go to the advanced settings of the App Pool and ensure that the identity is Local System. This is done to avoid permission issues at the SQL Server.

Create IIS Website

As the next step, Spin up IIS Manager and Add a Website.

Specify the Site name Physical Path and the Application Pool.

Now right click the web site and application to it. We will be hosting the WCF Service here.

Specify an Alias and reuse the application pool.

Click on OK. This will complete the IIS Set up. Now we have to publish the WCF Service to IIS.

Publish WCF Data Service

Right click the project and click on Publish.

Now lets select the Custom Profile.

Specify the profile name and continue.

Set the Publish Method as ‘File System’.

In order to specify the Target location, browse to the Local IIS application we had created recently in IIS.

Finally click on Publish.

Visual Studio has completed the publishing of the WCF Data Service to IIS.

If we head over to IIS we can see the svc file, right click on it and select browse to view the WCF data feed.

Debug the WCF Data Service

When we try to browse the WCF service in the browser we may encounter the below error which is not self explanatory.

So as to get more detailed error messages, we can add the below code lines in the Data Service class file for enhanced error messages.Add reference to System.ServiceModel and add the below code block as shown below to give detailed error messages in the browser.

[System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]

In case we get the below page not found error, one of the reasons could be due to insufficient permissions at the application pool.

To overcome that we can modify the Application Pool identity to ‘LocalSystem’.

Finally, once we have resolved any issues with the hosted WCF Service, we will get the Data Feed as shown below.

If we append ‘/Films’ to svc endpoint we will get the detailed feeds from the database as shown below.

Add SharePoint Hosted Add-in Solution

Thus we have completed the first section of the implementation. Now let's get to the second section where we will consume the oData Service from SharePoint Hosted Add-in to implement Business Connectivity Services. Right click the existing solution and add a new project.

Select the SharePoint Add-in template and click on OK.

Select the SharePoint Hosted Add-in option and proceed.

Select the version of SharePoint as 2016.

This will create the project structure for SharePoint Hosted Add-in.

Connect to oData Source

Right click the SharePoint Hosted Project and Select Content Types for External Data Source

Specify the oData Service URL we had created earlier and hosted in IIS. Specify a name for the oData end point.

Select the Film Data entity that we will use for CRUD operations. Select the check box that will create a List instance for the selected Film data entity.

Upon Clicking on FInish, external Content Type will be created. Film.ect is the major file that contains the external content type information.

Thus we have created the SharePoint Hosted Add-in. Now we are ready to create/deploy external Content Type to SharePoint. Before deploying the solution, lets create Business Connectivity Service Application in SharePoint, else we will get the below error.

From Central Administration -> Manage Service Application, create a Business Connectivity Service Application.

Specify the Service Application name, database name and proceed.

Once the service application has been created we will get the below success message.

Ensure that the service is in started state from the Services in Server page.

Deploy External Content Type to SharePoint

We have two options to deploy the External Content Type to SharePoint.

Upload the ECT file to BCS Service Application and create an External List from Site Contents
Deploy the SharePoint Add-in directly to SharePoint Site

We will cover both steps in this walkthrough

Upload ECT File to Service Application

Download the below highlighted Film.ect file that hold the external content type information to the desktop.

Now head over to the business connectivity service application.

Select Import option

This will open the Import BDC Model window. Specify the ECT file location ad click on Import.

It will start the import of the ect file to the service application.

Once completed, we will get the below success message.

From the ribbon, select ‘ Set Medata store permissions’ option and add the required users and permissions.

Thus we have uploaded and assigned permissions to the ECT file in the Service Application.

Create External List

As the last step, head over to the site contents and select ‘External List’ option.

Specify the External List name as ‘Films’ and browse to the ECT file we had uploaded to the Service Application. Finally select ‘Create’.

This will provision the external list ‘Films’.

Clicking on it will take us inside the list where we can see the data retrieved from the External Line of Business using the oData Service.

Let's try to create a new item in the SharePoint external list and see if it gets updated in the External System .

After clicking in Save, item will be created in SharePoint. Now head over to SQL Server and we can see that the item has been updated here as well.

Similarly we can try to delete a record from SQL Server and see if the BCS updates the deletion in SharePoint.

Heading over to SharePoint, we can see that the item has been deleted from here as well.

Deploy External Content Type as a SharePoint Hosted Add-in

Another way to achieve BCS is to deploy the SharePoint Hosted Add-in Solution to SharePoint.

Before doing that let's set the start page of the Add-in to the List location. To do that we will change it from ‘FilmsBCSAdd-in/Pages/Default.aspx’ to ‘appWebUrl/lists/Film’.

Now let's Publish the add-in. Right Click the solution and click on Publish .

This will open up the page from where we can Package the add-in.

After packaging the add-in, location of ‘.app’ file will open up.

Copy the location and go to the App Catalog. From here click on Upload and browse to the ‘.app’ location in the file system. After uploading it to the App Catalog it will look like below.

Now if we head over to Site Contents and Select ‘Apps from your organization’ we can see the newly uploaded app. Click on it and select ‘Trust it’.

This will add the Add-in to the site contents.

On clicking it we will be navigated to the external list and looking at the address bar we can see the App web URL.

Let's try to edit an item and update it.

We have updated the Film Name in SharePoint. Let's see if it has been updated at the same time in SQL Server External System.

We can see that it has been updated here as well.

We saw in detail how to implement the entire CRUD operations against external Line of Business from SharePoint using Business Connectivity Services.

Summary

Thus we had a detailed demo on how to implement Business Connectivity Services using oData,Entity Framework and SharePoint Hosted Add-in in SharePoint Server 2016.