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

When we are given the task of creating an HTTP service, we have numerous options available such as Web Service, WCF Service, Web API, etc. We can consume all of the aforementioned services from SharePoint. 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 later be consuming this Web API from SharePoint 2016 to invoke the CRUD operations. The article is divided into two series.

• Create a Web API that performs CRUD on a SQL Server table using MVC and Entity Framework (current article).
• Consume CRUD Web API from within SharePoint 2016 (upcoming).

So, let’s get started.

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 Film. It will contain the below columns that will hold the Film information.

SQL Query

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.



Create the Web API

Now, we can get started with the creation of the Web API. From the 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.



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.



Add Controller

Once the Model is in place, we can create the Controller. Right click the Controllers 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 built 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 the successful creation of 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 article, 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.

Here is a sneak peak of what we are going to do in the next article.



Summary

Thus, we saw how to create a Web API that performs CRUD in a SQL Server table using Entity Framework. In the next article, we will see how to host it and consume it from within SharePoint 2016.