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,

SharePoint

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

SharePoint

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. 

  1. CREATE TABLE [Film](   
  2. [id] [intNOT NULL IDENTITY(1,1) PRIMARY KEY,   
  3. [FilmName] [varchar](50) NULL,   
  4. [Director] [varchar](100) NULL,   
  5. [Year] [varchar](10) NULL,   
  6. )   
  7. GO  
  8.   
  9. INSERT INTO [Film]   
  10. VALUES('The Shawshank Redemption''Frank Darabont''1994')   
  11. INSERT INTO [Film]   
  12. VALUES('The Godfather''Francis Ford Coppola''1972')   
  13. INSERT INTO [Film]   
  14. VALUES('Schindlers List''Steven Spielberg''1993')   
  15. INSERT INTO [Film]   
  16. VALUES('The Dark Knight''Christopher Nolan''2008')   
  17. INSERT INTO [Film]   
  18. VALUES('12 Angry Men''Sidney Lumet''1957')   
  19. INSERT INTO [Film]   
  20. VALUES('Pulp Fiction''Quentin Tarantino''1994')  
  21. INSERT INTO [Film]   
  22. VALUES('Fight Club''David Fincher''1999')  
  23. INSERT INTO [Film]   
  24. VALUES('Dunkirk''Christopher Nolan''2017')  
SharePoint

Testing the created table yields the below results.

SharePoint

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.

SharePoint

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.

SharePoint

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

SharePoint

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.

SharePoint

Select and delete the above 2 files from the solution.

SharePoint

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.

SharePoint

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.

SharePoint

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’.

SharePoint

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

SharePoint

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

SharePoint

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

SharePoint

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

SharePoint

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

SharePoint

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.

SharePoint

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.

SharePoint

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.

SharePoint

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

SharePoint

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’.

SharePoint

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.

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

 

SharePoint

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.

SharePoint

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.

SharePoint

We have specified the name as WCFAppPool.

SharePoint

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.

SharePoint

Create IIS Website

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

SharePoint

Specify the Site name Physical Path and the Application Pool.

SharePoint

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

SharePoint

Specify an Alias and reuse the application pool.

SharePoint

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.

SharePoint

Now lets select the Custom Profile.

SharePoint

Specify the profile name and continue.

SharePoint

Set the Publish Method as ‘File System’.

SharePoint

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

SharePoint

Finally click on Publish.

SharePoint

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

SharePoint

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.

SharePoint

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.

SharePoint

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)]

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

Select the SharePoint Hosted Add-in option and proceed.

SharePoint

Select the version of SharePoint as 2016.

SharePoint

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

SharePoint

SharePoint

Connect to oData Source

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

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

Specify the Service Application name, database name and proceed.

SharePoint

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

SharePoint


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

SharePoint


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.

SharePoint

Now head over to the business connectivity service application.

SharePoint

Select Import option

SharePoint

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

SharePoint

SharePoint

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

SharePoint

Once completed, we will get the below success message.

SharePoint

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

SharePoint

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

SharePoint

Create External List

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

SharePoint

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

SharePoint

This will provision the external list ‘Films’.

SharePoint

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.

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

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

SharePoint

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.

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’.

SharePoint

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

SharePoint

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

SharePoint

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

SharePoint

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.

SharePoint

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’.

SharePoint

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

SharePoint

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

SharePoint

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

SharePoint

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.

SharePoint

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

SharePoint

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.