DotNetCurry Logo

Creating an App Scoped External Content Type (ECT) in SharePoint using OData Services and BCS

Posted by: Mahesh Sabnis , on 4/7/2015, in Category SharePoint
Views: 13491
Abstract: In this article, we will see how in SharePoint 2013, the App-Scoped External Content Type (ECT) using BCS allows the end-user to make use of isolated SharePoint App to interact with External Line-Of-Business Applications for manipulating data.

Those working on SharePoint are aware of Business Connectivity Services (BCS). If you are new to BCS, check this nice intro to BCS by Mark. Although BCS was released with SharePoint 2010, SharePoint 2013 made some changes in BCS. Technically, BCS supports exposing external Line-Of-Business (LOB) applications data to SharePoint. This data may be stored in Relational Database like Sql Server or in any other enterprise system e.g. ERP or CRM.

Advantages offered by BCS are that since the data is integrated with SharePoint as standard data element like lists; any office client application e.g. Excel, Outlook, can makes use of this data and work with it without interacting or integrating with external system. Since the external LOB data is available in SharePoint using BCS, this can be manipulated (CRUD Operations) without writing any custom code. SharePoint manage BCS using Business Data Connectivity (BDC) service. This is the SharePoint service application residing on the SharePoint server farm to process business entity requests.

 

The BDC service accesses an external LOB application data using BCS Connector Framework. Following are the connectors provided:

  • SQL: Connects BCS directly to SQL Server Database.
  • WCF: The WCF service act as a contract interface from external LOB system to BCS.
  • .NET Framework: This allows to uses custom code component (an assembly) to act as an interface between external LOB to BCS.
  • OData: The change in BCS in SharePoint 2013 is the OData Service endpoints.

The following diagram, shows the BCS architecture:

bcs-architecture

In a SharePoint environment, BDC services expose the modeled business entities from external LOB systems as External Content Types (ECT). ECT is used for creating External Lists and data columns on SharePoint sites so that it can be used by the end-users to interact with external LOB system data.

External Content Type

ECT is the building block for providing interaction with external LOB system. In earlier versions of SharePoint, ECT was deployed and managed at a farm level. To make this possible, it was mandatory for the user to have farm level privileges. But in SharePoint 2013, we have App Scoped ECT. This means that an ECT can be packaged in the SharePoint App. This restricts the access of the ECT only by that App who created it. This is more flexible because to manage an App-Scoped ECT, farm-level privileges are not needed.

Creating App-Scoped External Content Type using OData and Business Connectivity Service

In the following steps, we will create an App-Scoped ECT using OData and BCS. We will implement this using the WCF Data Service and SharePoint 2013 App.

Creating an OData EndPoint for SharePoint App

Since SharePoint Apps have an isolated nature, to create App-Scoped External Content Type, the OData Connector is the only valid connector that can be used to work with the external data exposed by BCS. We can build OData Endpoint using Visual Studio with WCF Data Services and ADO.NET EF.

We will create Sql Server Data with Table. Open Sql Server 2012/2014 IDE and create Database of the name ‘Company’

Create Database Company

Create the following Table in it:

Create Database Company

Create the following Table in it:

CREATE TABLE [dbo].[ExpensesDetails](
    [ReportId] [int] IDENTITY(1,1) NOT NULL,
    [SubmittedBy] [varchar](100) NOT NULL,
    [RegionName] [varchar](50) NOT NULL,
    [SalesTarget] [decimal](18, 0) NOT NULL,
    [SalesAchieved] [decimal](18, 0) NOT NULL,
    [SalesDifference] [decimal](18, 0) NOT NULL,
    [Expenses] [int] NOT NULL,
    [Approved] [bit] NULL,
 CONSTRAINT [PK_ExpensesDetails] PRIMARY KEY CLUSTERED 
(
    [ReportId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Creating WCF Data Service

Step 1: Open Visual Studio 2013 (the article is developed using Visual Studio 2013 Community Edition), and create a new blank solution, name it as ‘MyBCS’. In this solution, add a new WCF Service Application of the name ‘WCF_DataServ’. In this project, we will have IService1.cs and Service1.svc, these files contain standard code for implementing WCF Service. Delete these files.

Step 2: Since we need to create a WCF Data Service, in this project add a new WCF Data Service of the name ExpenseService.svc.

Step 3: In the project, add a new ADO.NET Entity Framework of name CompanyEDMX. This will open a wizard, from this wizard select the option for Generate from Database. Select Company database and ExpensesDetails table. After completing the wizard, the following table mapping will be displayed:

table-mapping

Step 4: Change the code for ExpenseService class as shown: (Highlighted)

public class ExpenseService : DataService<CompanyEntities>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
      
        config.UseVerboseErrors = true;
         config.SetEntitySetAccessRule("ExpensesDetails", EntitySetRights.All);
         config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
    }
}

Please read more about WCF 4.5 data services from here.

Since we will be using WCF Data Service in SharePoint App, we need to host it in IIS. To do so, create a new Application Pool on IIS targeted to .NET 4.0 and Create a Web Site in it. The detailed steps for hosting WCF service on IIS are provided over here Hosting WCF 4.0 Service on IIS 7.5 with SSL. (Note: Please ignore the SSL part from the article). The WCF service can be published on IIS in the Web Site created in it OR alternatively we can directly set the web server path from the Project Properties of the WCF Service as shown here:

wcf-hosting

View the service using a browser. The service details will be displayed as shown here:

wcf-dataservice

The data from the service can be displayed by adding the /ExpensesDetails in the URL as shown here:

service-data

Note: If this data is not displayed correctly and if Page Not Found types of errors are displayed then there may be an issue with the connectivity from WCF hosted on IIS and Sql Server database. This can be sorted out using ‘config.UserVerboseErrors=true’ statement in the WCF Data Service class. To overcome these errors, we need to set the Company Database access rights to the NT Authority\System account.

Creating SharePoint 2013 App

Step 1: In the same solution created above, add a new SharePoint 2013 App project, name it as ‘MyBCS’. In this project, right-click > select Add and select Content Types for External Data Source as shown here:

external-data-source

This will start a SharePoint Customization Wizard. In this window, enter the URL for the OData Service published on IIS, in the What OData Service URL do you want to use to create the external data source? comboBox. Also provide the data source name in the What do you want to name to your data source? textbox as shown in the figure. Here ExpensesDetailsExternal name is entered.

sharepoint-custom

Clicking on Next, the following window will display data entities in the OData service. This window will provide the option of selecting the data entities to generate external content type. This Window also has a checkbox which will be used to create list instances for the selected data entities. This will create a SharePoint List.

ect-datasource

Clicking on Finish will add new project nodes in the project:

external-content-type

In the above diagram, the newly created file nodes are displayed. The most important file here is ExpensesDetails.ect, this file contains BDC Data model for the service. The ExpensesDetails, an external list instance is also added in the app web.

Step 2: To view the list created, click on the ‘AppManifest.xml’ file and in the startup page, set the URL for the List to the following:

app-manifest

Run the application and the ExpensesDetails list will be display as below:

expenses-details

That’s it, the List is created. Now the list operations can be performed i.e. Insert, Update and Delete, list items. From the Ribbon on the top of the page, select ITEMS and click on New Item. A New Item page will be displayed:

item-page

This is a pre-created page for the SharePoint List instance. Now the end-user can enter data in the List and after clicking on the Save, it will be saved in SQL Server using underline BCS Service. The added data will be shown in the list as can be seen here:

added-data

This means that without writing any custom code, the end-user can perform operations with the List and hence can interact with external data.

Creating the SharePoint App to interact with the App-Scoped ECT List for performing Read/Write Operations

We have already created a SharePoint List instance using App-Scoped ECT. But now the challenge is to provide interactive UI to the end-user to perform CRUD operations with the list. To implement this, we will make use of REST API and jQuery with Knockout.js.

Step 1: Open AppManifest.xml and from the General Tab and change the Start Page to Default.aspx:

general-tab

Step 2: In the SharePoint App project, add the latest jQuery and Knockout JavaScript libraries.

Step 3: Open App.js file from the Scripts folder and replace the already available logic with the following:

/// <reference path="jquery-2.1.1.min.js" />
/// <reference path="knockout-3.2.0.js" />


'use strict';

var SPHostUrl;
var SPAppWebUrl;

// read URL parameters
function retrieveQueryStringParameter(param) {
    var params = document.URL.split("?")[1].split("&");
    var strParams = "";
    for (var i = 0; i < params.length; i = i + 1) {
        var singleParam = params[i].split("=");
        if (singleParam[0] == param) {
            return singleParam[1];
        }
    }
}

//The App and WEB URL
SPAppWebUrl = decodeURIComponent(retrieveQueryStringParameter("SPAppWebUrl"));
SPHostUrl = decodeURIComponent(retrieveQueryStringParameter("SPHostUrl"));


$(document).ready(function () {

    var viewmodel = new expensesDetailsModel();

    ko.applyBindings(viewmodel);

    viewmodel.loadReports();

});


//The Knockout ViewModel
var expensesDetailsModel = function () {

    var self = this;
    //S1: Array to store the Expenses reports
    self.Reports = ko.observableArray([]);
    self.errorMessage = ko.observable();

    //S2: Observables for Storing Expense Details
    self.ReportId = ko.observable(0);
    self.SubmittedBy = ko.observable("");
    self.RegionName = ko.observable("");
    self.SalesTarget = ko.observable(0.0);
    self.SalesAchieved = ko.observable(0.0);
    self.SalesDifference = ko.observable(0.0);
    self.Expenses = ko.observable(0.0);
    self.Approved = ko.observable("");
    //Ends Here


    //S3:Function to Load All Expenses Records
    self.loadReports = function(){

        var url = _spPageContextInfo.webServerRelativeUrl + "/_api/lists/getbytitle('ExpensesDetails')/items";
         
        $.ajax({
            url: url,
            type: "GET",
            headers: {
                "Accept": "application/json;odata=verbose",
                "X-RequestDigest": $("#__REQUESTDIGEST").val()
            }
        }).done(function (resp) {
            alert("Success");
            self.Reports(resp.d.results);

        }).error(function (err) {

            self.errorMessage("Some Error Occurs " + err.status + "  " + err.success);
        });
    }

    //S4: Fundtion to Clear values of Obsevable
    self.clearEntries = function () {

      self.SubmittedBy("");
      self.RegionName("");
      self.SalesTarget(0.0);
      self.SalesAchieved(0.0);
      self.SalesDifference(0.0);
      self.Expenses(0.0);
      self.Approved(false);
    }

    //S5:Function to Submit the new Expense Record
    self.submitReport = function () {

        var url = _spPageContextInfo.webServerRelativeUrl + "/_api/lists/getbytitle('ExpensesDetails')/items";

        //Get the Difference between
        //Actual Sales arget and Achieved Sales Target
        var diff = self.SalesAchieved() - self.SalesTarget();

        diff = diff.toString();

        //The Rule for Approving the Expenses
        if (diff > 5000 && self.Expenses() < 5000) {
            self.Approved(true);
        }
        else {
            self.Approved(false);
        }

        self.SalesDifference(diff);

        var itemType = "SP.Data.ExpensesDetailsListItem";

        //The Expense Details Object

        var expensesDetails = {
            "__metadata": { "type": itemType },
            "SubmittedBy": self.SubmittedBy(),
            "RegionName": self.RegionName(),
            "SalesTarget": self.SalesTarget(),
            "SalesAchieved": self.SalesAchieved(),
            "SalesDifference": self.SalesDifference(),
            "Expenses": self.Expenses(),
            "Approved" : self.Approved()
        };

        $.ajax({
            url: url,
            type: "POST",
            data: ko.toJSON(expensesDetails),
            headers: {
                "X-RequestDigest": $("#__REQUESTDIGEST").val(),
                "accept": "application/json;odata=verbose",
                "content-type": "application/json;odata=verbose",
              "content-length": expensesDetails.length
            }
        }).done(function (resp) {
            alert("Report Added Successfully");
            //self.ReportId(resp.d.results);

            self.loadReports();

        }).error(function (err) {
            self.errorMessage("Some Error Occurs " + err.status + "  " + err.statusText);
        });
    }
};

In the above code expensesDetailsModel defines the knockout View-Model so that it can be bound with the View which we will be creating in the next steps. The Reports observable array will be used to store the expenses report data received from the OData Service to the App-Scoped ECT List instance of name ‘ExpensesDetails’. The View-Model defines several observables so that they can be bound with textboxes on the view. The loadReports function is used to make an Ajax call to the ECT List and read data from it. This data is then stored in the Reports Observable array. The function clearEntries is used to clear all observables. The function submitReport is used to add new Expense details. submitReport contains logic for approving the expenses report. If the difference between the Actual Sales Target and Sales Achieved is more than 5000 and Expenses are less than 5000, then the report will be approved else it will be rejected.

Step 4: Open Default.aspx and add the following HTML in the <asp:Content> with ID as PlaceHolderMain

<table id="tblMain">
<tr>
    <td>
        <table id="tblRecordView">
            <tr>
                <td>
                   Submitted By:
               </td>
                <td>
                    <input type="text" id="submittedBy" class="c1" data-bind="value: $root.SubmittedBy"/>
                </td>
            </tr>
             <tr>
                <td>
                   Region:
               </td>
                <td>
                    <input type="text" id="regionName" class="c1" data-bind="value: $root.RegionName"/>
                </td>
            </tr>
            <tr>
                <td>
                   Actual Sales Target:
               </td>
                <td>
                    <input type="text" id="actualSalesTarget" class="c1" data-bind="value: $root.SalesTarget"/>
                </td>
            </tr>

            <tr>
                <td>
                   Achieved Sales Target:
               </td>
                <td>
                    <input type="text" id="achievedSalesTarget" class="c1" data-bind="value: $root.SalesAchieved"/>
                </td>
            </tr>
            <tr>
                <td>
                  Difference Sales Target:
               </td>
                <td>
                    <input type="text" id="differenceSalesTarget" class="c1" data-bind="value: $root.SalesDifference"/>
                </td>
            </tr>
            <tr>
                <td>
                  Expenses Made:
               </td>
                <td>
                    <input type="text" id="expensesMade" class="c1" data-bind="value: $root.Expenses"/>
                </td>
            </tr>

             <tr>
                <td>
                  Status (Approved/Rejected):
               </td>
                <td>
                    <input type="text" id="status" class="c1" data-bind="value: $root.Approved"/>
                </td>
            </tr>
           <tr>
               <td>
                   <input type="button" id="new" value="New" data-bind="click:$root.clearEntries"/>
               </td>
               <td>
                   <input type="button"
                        id="submitReport" value="Submit Report"  data-bind="click: $root.submitReport"/>
               </td>
           </tr>
        </table>
    </td>

    <td>
        <div id="dvListView">
            <table id="tblListView">
                <thead>
                    <tr>
                        <td>ReportId</td>
                        <td>Submited By</td>
                        <td>Region Name</td>
                        <td>Actual Sales Target</td>
                        <td>Achieved Sales Target </td>
                        <td>Difference </td>
                        <td>Expenses</td>
                        <td>Status(Approved/Rejected)</td>
                    </tr>
                </thead>
                <tbody data-bind="foreach:Reports">
                    <tr>
                        <td><span data-bind="text:ReportId"></span></td>
                        <td><span data-bind="text:SubmittedBy"></td>
                        <td><span data-bind="text:RegionName"></td>
                        <td><span data-bind="text:SalesTarget"></td>
                        <td><span data-bind="text:SalesAchieved"></td>
                        <td><span data-bind="text:SalesDifference"></td>
                        <td><span data-bind="text:Expenses"></td>
                        <td><span data-bind="text:Approved"></td>
                    </tr>
                </tbody>
            </table>
        </div>
         
    </td>
</tr>
</table>
<div>
<span data-bind="text:errorMessage"></span>
</div>

The above HTML markup has the necessary data binding with knockout observable.

Step 5: Run the Application

ect-bcs

Enter data in the TextBoxes and click on the Submit Report Button, the result will be as shown here:

ect-app

This shows the newly added Expenses report.

Conclusion

In SharePoint 2013, the App-Scoped External Content Type (ECT) using BCS allows the end-user to make use of isolated SharePoint App to interact with External Line-Of-Business Applications for manipulating data.

Download the entire source code of this article (Github)

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!