Problem Statement

As we all know, SharePoint has in-house data storage options like List/Library where we can save the data which in turn gets stored inside SQL Server. Since the data is maintained within SharePoint Context we can seamlessly work with the stored data.However, Often we have to deal with data that is outside Share Point where the data is stored in an external database.In such a case we will have to create a connection with the external database and perform a look up to the external data and perform CRUD against it. For a scenario like this,  we have multiple options that can be used to integrate external data with SharePoint - Business Connectivity Services(BCS), Custom Web Service, WCF Services, Web API and so on.  We can consume all of the  mentioned services from SharePoint. Web API is a highly robust HTTP service solution that enables us to work with external data. 

In this article we will see how to create a Web API and consume it from within SharePoint 2016. The created Web API will use MVC and Entity Framework to perform CRUD operations on the table present in the SQL Server. We will be later consuming this Web API from SharePoint 2016 to invoke the CRUD operations. The article is divided into two main sections:

  • Create a Web API that performs CRUD on SQL Server table using MVC and Entity Framework
  • Consume CRUD Web API from within SharePoint 2016

Once implemented, the consumed Web API will enable CRUD from SharePoint and it will have a UI as shown below:



So let’s get started.

↑ Return to Top



Create a Web API that performs CRUD on SQL Server table using MVC and Entity Framework

We will cover the back end prerequisites first. This involves the creation of a SQL Server table that will hold the information which will be accessed via Web API from SharePoint 2016.

Create the SQL Server table

As the prerequisite let’s create the SQL Server table that we will be using for performing the CRUD operations. We will be creating it inside the database named DB and we will name it as Film. It will contain the below columns that will hold the Film information.  

CREATE TABLE [Film](
    [id] [int] NOT NULL PRIMARY KEY,
    [FilmName] [varchar](50) NULL,
    [Director] [varchar](100) NULL,
    [Year] [varchar](10) NULL,
 )

We have populated some values into the table as well:

 

↑ Return to Top




Create the Web API

Now we can get started with the creation of the Web API. From Web, select ASP.NET Web application. Provide a name for the project and proceed.

From the available templates, select Web API and continue.

This will create the Web API project.



↑ Return to Top



Create Entity Data Model

Once the project structure is in place, right click Models folder and from Add, select New Item.

This will open up the Add New Item window, Select ADO.NET Entity Data Model.

In the Entity Data Model Wizard, Select EF Designer from database and click on Next.

In the next window we have to set up the connection with SQL Server. Click on New Connection.

Provide the Server Name and the Database to which we are going to connect. Click on Test Connection to check the database connectivity.

At the bottom of the window provide the entity name which will be used to create DBContext for interacting with SQL Server. I have named it as FilmEntities. Click on Next.

This will open up the window with the tables present within the database. Let’s select Film Table from the available tables. Provide the Model Name space and continue. We will call it as FilmModel.

This will create the entity model relation as shown below in the edmx file.

 

↑ Return to Top



Add Controller

Once the model is in place, we can create the controller. Right click the Controller folder and add Controller.

From the Add Scaffold window specify, Web API 2 Controller with actions, using Entity Framework option.

 

On Clicking Add, mention the Model Class and the Data Context Class which we had defined earlier. Provide the Controller name here as FilmsController and click on Add.

If you have not build the project yet, you are most likely to get the below error. So build the project once again and specify the Model and DataContext Class values.

Once we click on add in the Add Controller window it will create the scaffolding for the Web API. It will create the CRUD methods in the Controller which will be exposed by the Web API.

We are done with the creation and initial configuration of the Web API. Before we can use it with SharePoint, let’s test the functioning of Web API. Run the project and it will open up the Web API with API URL as “api/films”. Here we can see XML formatted data retrieved from SQL Server using Entity Framework indicating successful creation of the Web API.

The final structure of the Web API project will look like below:

We are done with the first part of the article. In the next section we will see how to host the Web API in IIS and Consume the Web API from SharePoint 2016 to invoke SQL Server CRUD operations.

↑ Return to Top




Consume CRUD Web API from within SharePoint 2016



Lets start with the next major section of the article where we will try to consume the Web API from SharePoint 2016.


Host the Web API in IIS

Before consuming the Web API from SharePoint we will host it in IIS. Open IIS Manager and create a web site.

Specify the Site Name, Physical Path and the port used for hosting the Web API.

Once the site is up and running, go to the Visual Studio Solution and Publish the solution.

 

Select Custom option and specify a name.

Select the publish method as Web Deploy. Specify the Server, Site name and the destination URL.

Upon Clicking Publish the Web API will be hosted in the IIS Site.

↑ Return to Top



Test the Web API

Now let’s go ahead to the browser and hit the URL http://sitename/api/films. However, we have received the error, Login Failed for User “Domain\machine$”.



In order to resolve this issue we can either grant this user the required access in the SQL Server. Else we can change the application pool account that will be used by the Web API to access SQL Server. 



Currently it is set as Application Pool Identity.

Let’s change it to a different user with SQL Server permissions.

Now checking the Web API URL in browser we can see the data pulled from SQL Server table.

We have a fully functional Web API that connects to SQL Server table using Entity Framework hosted in IIS. Now let’s see how we can consume this from SharePoint. As the first step, we will create a UI for invoking the CRUD operations.

↑ Return to Top




HTML Structure

The HTML structure for the UI that will call the various methods defined in the Web API Controller is as shown below:

Here ‘Search Film’ button will call the GET method issuing the Web API AJAX request. ‘Add Film’ button will call the POST method, ‘Update Film’ will call the Put method and ‘Delete Film’ will call the DELETE method using the Web API URL. We will discuss in detail about these method calls in the upcoming section.

     

<table>
    <tr>
        <td>
            <div>
                <h2>Films Database</h2>
                <table id="Film" cellpadding="2" cellspacing="2" border="1" width="400px">
                    <tr>
                        <td style="background-color: white; color: grey">Film ID</td>
                        <td style="background-color: white; color: grey">Film Name</td>
                        <td style="background-color: white; color: grey">Director</td>
                        <td style="background-color: white; color:grey">Year</td>
                    </tr>
                </table>
            </div>
            </br>
            <div>
                <h2>Search by Film ID</h2>
                <input type="text" size="5" id="FilmId" />
                <input type="button" value="Search Film" onclick="find();" />
                <p id="FilmSearch"></p>
              </div>
        </td>
        <td>
            <div>
                <h2>Add/Update by Film ID</h2>
                <table id="FilmUpdate" cellpadding="2" cellspacing="2" border="1" width="400px">
                    <tr>
                        <td style="background-color: white; color: grey">Film ID</td>
                        <td style="background-color: white; color: grey">Film Name</td>
                        <td style="background-color: white; color: grey">Director</td>
                        <td style="background-color: white; color:grey">Year</td>
                    </tr>
                    <tr>
                      <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmIdToUpdate" />  </td>
                      <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmNameUpdated" /> </td>
                      <td style="background-color: white;color: grey"><input type="text" size="5" id="FilmDirectorUpdated" /></td>
                      <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmYearUpdated" /> </td>
                    </tr>
                    <tr>
                      <td> <input type="button" value="Add Film" onclick="Add();" /> </td>
                      <td> <input type="button" value="Update Film" onclick="Update();" /> </td>
                    </tr>
                </table>
            </div>
            <div>
                <h2>Delete by Film ID</h2>
                <input type="text" size="5" id="FilmIdToDelete" />
                <input type="button" value="Delete Film" onclick="Delete();" />
            </div>
        </td>
    </tr>
</table>

↑ Return to Top




Web API Operations


Web API exposes all the CRUD operations which can be invoked from SharePoint. Lets see in detail each Web API operations. 

Get All Items

We can use the “api/films” Web API URL to get all the list items from the SQL Server table. We will then issue an AJAX call and append the returned data dynamically to the HTML table named ‘Film’. The function to retrieve the film information is as shown below:

function GetFilmData() {
var uri = 'http://localhost:8082/api/films';
$.getJSON(uri)
.done(function (data) {
   $.each(data, function (key, item) {
    $('#Film').append("<tr><td>" + item.id + "</td>" + "<td>" + item.FilmName + "</td>" + "<td>" + item.Director + "<td>" + item.Year + "</td></tr>");
    });
  });
}

Get items in action :

↑ Return to Top




Search for an Item

In order to search for a specific item we will be making use of the Web API URL “api/films/5” where ‘5’ is the id of the item to be searched for. The function to search the film information is as shown below:

var uri = 'http://localhost:8082/api/films';
function find() {
var id = $('#FilmId').val();
$.getJSON(uri + '/' + id)
.done(function (data) {
    $('#FilmSearch').text(formatItem(data));
  })
.fail(function (jqXHR, textStatus, err) {
    $('#Film').text('error' + err);
  })
}
function formatItem(item) {
    return item.FilmName + '-' + item.Director + '-' + item.Year;
}
 Search in action :



↑ Return to Top



Add an Item

In order to add the item we will issue a POST request using the Web API URL “api/films/id”. We accept the input using input fields defined in the HTML structure and create an object based on the input. We will be passing the object as the data attribute while issuing the AJAX request. The function to add the film information is as shown below:

function Add() {
var id = $('#FilmIdToUpdate').val();
var apiURL = 'http://localhost:8082/api/films/'+ id;
var film= new Object();
film.id=  $('#FilmIdToUpdate').val();
film.FilmName = $('#FilmNameUpdated').val();
film.Director= $('#FilmDirectorUpdated').val();
film.Year = $('#FilmYearUpdated').val();
  
$.ajax({
url: apiURL,
type: 'POST',
dataType: 'json',
data : film,
success: function (data, textStatus, xhr) {
    alert("Film Record with ID : " + id +" Added !");
    $("#Film").find("tr:gt(0)").remove();
    GetFilmData();
   },
error: function (xhr, textStatus, errorThrown) {
    alert("An error occurred!!");
   }
 });
}
Add Item in action :

Upon inputting the values and clicking in Add Film, it will add the details to the table.



The SQL Server table as well as the SharePoint UI is updated with the new values.

 

↑ Return to Top


Update an Item

In order to update the item we will issue a PUT request using the Web API URL “api/films/id”. We accept the input using input fields defined in the HTML structure and create an object based on the input. We will be passing the object as the data attribute while issuing the AJAX request. The function to update the film information is as shown below:

function Update() {
var id = $('#FilmIdToUpdate').val();
var apiURL = 'http://localhost:8082/api/films/'+ id;
var film= new Object();
film.id=  $('#FilmIdToUpdate').val();
film.FilmName = $('#FilmNameUpdated').val();
film.Director= $('#FilmDirectorUpdated').val();
film.Year = $('#FilmYearUpdated').val();
  
$.ajax({
url: apiURL,
type: 'PUT',
dataType: 'json',
data : film,
success: function (data, textStatus, xhr) {
     alert("Film Record with ID : " + id +" Updated!");
     $("#Film").find("tr:gt(0)").remove();
     GetFilmData();
     ClearText();
   },
error: function (xhr, textStatus, errorThrown) {
    alert("An error occurred!!");
   }
 });
}
Update in action :

Delete an Item

In order to delete the item we will issue a DELETE AJAX request using the Web API URL “api/films/id”. The id is accepted using input text from the UI. The function to delete the film information is as shown below:

function Delete() {
var id = $('#FilmIdToDelete').val();
var apiURL = 'http://localhost:8082/api/films/'+ id;
  
$.ajax({
url: apiURL,
type: 'DELETE',
dataType: 'json',
success: function (data, textStatus, xhr) {
     alert("Film Name: " + data.FilmName + ", Director: " + data.Director + " . Record Deleted !");
     $("#Film").find("tr:gt(0)").remove();
     GetFilmData();
   },
error: function (xhr, textStatus, errorThrown) {
     alert("An error occurred!!");
   }
 });
}

Delete in action:

On clicking Delete Item, the item will be removed from the SQL Server table as well as the UI.

 

↑ Return to Top




Full Code for invoking Web API CRUD from SharePoint

<head>
 
    <title>Film Database</title>
    <style type="text/css">
        body{background-color:silver;}
        table{
            font-family : TimesNewRoman;
        }
        input[type="text"]{
            background : white;
            border-color : lightgrey;
            size : 38;
        }
        input[type="button"]{
            padding:0.25em;margin:0.25em;
            background:white;
            color:Black;
            border-color : black;
        }
    </style>
</head>
<body>
<table>
    <tr>
        <td>
            <div>
                <h2>Films Database</h2>
                <table id="Film" cellpadding="2" cellspacing="2" border="1" width="400px">
                    <tr>
                        <td style="background-color: white; color: grey">Film ID</td>
                        <td style="background-color: white; color: grey">Film Name</td>
                        <td style="background-color: white; color: grey">Director</td>
                        <td style="background-color: white; color:grey">Year</td>
                    </tr>
                </table>
            </div>
            </br>
            <div>
                <h2>Search by Film ID</h2>
                <input type="text" size="5" id="FilmId" />
                <input type="button" value="Search Film" onclick="find();" />
                <p id="FilmSearch"></p>
            </div>
        </td>
        <td>
            </br> </br> </br> </br> </br>
        </td>
        <td>
            <div>
                <h2>Add/Update by Film ID</h2>
                <table id="FilmUpdate" cellpadding="2" cellspacing="2" border="1" width="400px">
                    <tr>
                        <td style="background-color: white; color: grey">Film ID</td>
                        <td style="background-color: white; color: grey">Film Name</td>
                        <td style="background-color: white; color: grey">Director</td>
                        <td style="background-color: white; color:grey">Year</td>
                    </tr>
                    <tr>
                        <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmIdToUpdate" />  </td>
                        <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmNameUpdated" /> </td>
                        <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmDirectorUpdated" />  </td>
                        <td style="background-color: white; color: grey"> <input type="text" size="5" id="FilmYearUpdated" /> </td>
                    </tr>
                    <tr>
                        <td> <input type="button" value="Add Film" onclick="Add();" /> </td>
                        <td> <input type="button" value="Update Film" onclick="Update();" /> </td>
                    </tr>
                </table>
            </div>
            </br> </br> </br>
            <div>
                <h2>Delete by Film ID</h2>
                <input type="text" size="5" id="FilmIdToDelete" />
                <input type="button" value="Delete Film" onclick="Delete();" />
            </div>
            </br> </br> </br> </br> </br> </br> </br>
        </td>
    </tr>
</table>
  
<script type="text/javascript">
  
    var uri = 'http://localhost:8082/api/films';
    $(document).ready(function () {
        GetFilmData();
    });
  
    function GetFilmData() {
        $.getJSON(uri)
                .done(function (data) {
                    $.each(data, function (key, item) {
                        $('#Film').append("<tr><td>" + item.id + "</td>" + "<td>" + item.FilmName + "</td>" + "<td>" + item.Director + "<td>" + item.Year + "</td></tr>");
                    });
                });
        ClearText();
    }
  
    function formatItem(item) {
        return item.FilmName + '-' + item.Director + '-' + item.Year;
    }
  
    function find() {
        var id = $('#FilmId').val();
        $.getJSON(uri + '/' + id)
                .done(function (data) {
                    $('#FilmSearch').text(formatItem(data));
                    ClearText();
                })
                .fail(function (jqXHR, textStatus, err) {
                    $('#Film').text('error' + err);
                })
    }
  
    function Delete() {
        var id = $('#FilmIdToDelete').val();
        var apiURL = 'http://localhost:8082/api/films/'+ id;
        $.ajax({
            url: apiURL,
            type: 'DELETE',
            dataType: 'json',
            success: function (data, textStatus, xhr) {
                alert("Film Name: " + data.FilmName + ", Director: " + data.Director + " . Record Deleted !");
                $("#Film").find("tr:gt(0)").remove();
                GetFilmData();
            },
            error: function (xhr, textStatus, errorThrown) {
                alert("An error occurred!!");
            }
        });
    }
  
    function Add() {
        var id = $('#FilmIdToUpdate').val();
        var apiURL = 'http://localhost:8082/api/films/'+ id;
        var film= new Object();
        film.id=  $('#FilmIdToUpdate').val();
        film.FilmName = $('#FilmNameUpdated').val();
        film.Director= $('#FilmDirectorUpdated').val();
        film.Year = $('#FilmYearUpdated').val();
  
        $.ajax({
            url: apiURL,
            type: 'POST',
            dataType: 'json',
            data : film,
            success: function (data, textStatus, xhr) {
                alert("Film Record with ID : " + id +" Added !");
                $("#Film").find("tr:gt(0)").remove();
                GetFilmData();
            },
            error: function (xhr, textStatus, errorThrown) {
                alert("An error occurred!!");
            }
        });
    }
  
    function Update() {
        var id = $('#FilmIdToUpdate').val();
        var apiURL = 'http://localhost:8082/api/films/'+ id;
        var film= new Object();
        film.id=  $('#FilmIdToUpdate').val();
        film.FilmName = $('#FilmNameUpdated').val();
        film.Director= $('#FilmDirectorUpdated').val();
        film.Year = $('#FilmYearUpdated').val();
  
        $.ajax({
            url: apiURL,
            type: 'PUT',
            dataType: 'json',
            data : film,
            success: function (data, textStatus, xhr) {
                alert("Film Record with ID : " + id +" Updated!");
                $("#Film").find("tr:gt(0)").remove();
                GetFilmData();
            },
            error: function (xhr, textStatus, errorThrown) {
                alert("An error occurred!!");
            }
        });
    }
  
    function ClearText(){
        $('#FilmIdToUpdate').val('');
        $('#FilmNameUpdated').val('');
        $('#FilmDirectorUpdated').val('');
        $('#FilmYearUpdated').val('');
        $('#FilmIdToDelete').val('');
        $('#FilmId').val('');
    }
</script>
</body>
</html>

↑ Return to Top



Add the Script to SharePoint

The entire script along with the applied style is available for download in the above section. Copy it to a text file and upload it to the SharePoint repository ‘Site Assets’. Copy the file path and assign it to the Content Link section of a Content Editor Web Part. Once done, click on Apply. We can see the CRUD web part available in the page.

 

 

Summary

 Thus we saw how to create and consume a MVC CRUD Entity Framework Web API from SharePoint Server 2016.

↑ Return to Top



See Also


The article was first published and can be viewed in detail from the below links as well :