Create And Consume MVC CRUD Web API In SharePoint 2016 - Part Two

SharePoint can consume a Web Service, WCF Service, or Web API when we have to integrate a .NET Service with SharePoint. In the previous article, we saw how to create the MVC CRUD Web API using Entity Framework. In this article, we will see how to host the Web API in IIS and consume the Web API for performing CRUD operations from within 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 Website.



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.

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 or 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 the 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.

HTML Structure

The HTML structure for the UI that will call the various methods is defined in the Web API Controller, 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.
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:


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.


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 shown below.


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





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 shown below.