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.

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

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:

functionGetFilmData() {
    varuri = '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>");
            });
        });
}

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.

varuri = '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);
        })
}
functionformatItem(item) {
    returnitem.FilmName + '-' + item.Director + '-' + item.Year;
}

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.

function Add() {
    var id = $('#FilmIdToUpdate').val();
    varapiURL = '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!!");
        }
    });
}

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.

function Update() {
    var id = $('#FilmIdToUpdate').val();
    varapiURL = '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!!");
        }
    });
}

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

function Delete() {
    var id = $('#FilmIdToDelete').val();
    varapiURL = '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!!");
        }
    });
}

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





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 src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script type="text/javascript">
        varuri = 'http://localhost:8082/api/films';
        $(document).ready(function() {
            GetFilmData();
        });
        functionGetFilmData() {
            $.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();
        }
        functionformatItem(item) {
            returnitem.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();
            varapiURL = '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();
            varapiURL = '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();
            varapiURL = '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!!");
                }
            });
        }
        functionClearText() {
            $('#FilmIdToUpdate').val('');
            $('#FilmNameUpdated').val('');
            $('#FilmDirectorUpdated').val('');
            $('#FilmYearUpdated').val('');
            $('#FilmIdToDelete').val('');
            $('#FilmId').val('');
        }
    </script>
</body>

</html>

Add the Script to SharePoint

The entire script along with the applied style is available for download in this article with the name ‘SP2016WebAPI.txt’. 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 an MVC CRUD Web API from SharePoint Server 2016.