ASP.NET MVC - REST Web API SQL Server Connection Using Entity Framework Database First Approach

One of the key advantages of using REST Web API as a mode of data exchange between client-server machines is that it can connect any type to a data source, and client machines do not need to bother about it. Depending on the business requirements, any sort of database; e.g. SQL server, MySQL, CSV files, Excel file etc., can be connected with the underlying development platform to exchange data with the client machines.

 

Today, I shall be demonstrating the creation of a REST Web API connection with SQL server Entity Framework Database First approach using ASP.NET REST Web API platform.

 

 

Prerequisites

 

Following are some prerequisites before you proceed any further in this tutorial.

1. Knowledge of REST Web API.
2. Knowledge of ASP.NET MVC5.
3. Knowledge of C# Programming.

The example code is being developed in Microsoft Visual Studio 2019 Professional. I have taken the data sample from AdventureWorks for SQL server 2014.

 

Let's begin now.

 

Step 1

 

Create new Web API project and name it "RESTWebApiGetMethod".  

 

Step 2

 

Although you can create your entity framework model inside any hierarchy of the project,  I prefer "Models" folder, to keep code cleaner. So, right click on your "Models" folder and then click "Add->ADO.NET Entity Data Model" as shown below i.e.

 

 

Step 3

 

Now, from "Entity Data Model Wizard" choose "EF Designer from database", since I am creating entity framework database first approach model. Then click "Next" as shown below i.e.

 

 

Step 4

 

On the "Choose Your Data Connection" window, click "New Connection" button and create your SQL server connection as shown below i.e.:

 

 

Step 5

 

Now, on "Choose Your Data Connection" window, click "Yes, include sensitive data in connection string" option and click "Next" as shown below i.e.:

 

 

Step 6

 

Now, on "Choose Your Database Objects and Settings" window, choose your target database objects. In my case, I have select only the store procedures. Then click "Finish" as shown below i.e.:

 

 

Step 7

 

Create "Controllers\WebApiController.cs" file.

 

Step 8

 

Create a global parameter in "Controllers\WebApiController.cs" file to access DbContext of your SQL server connection and name it "databaseManager".

 

Step 9

 

Now, create "Get" method without parameter inside "Controllers\WebApiController.cs" file and replace the following code in it i.e.:

...
        public HttpResponseMessage Get()
        {
            // Initialization
            HttpResponseMessage response = null;
            DataTable responseObj = new DataTable();
            string json = string.Empty;
...
            // Loading Data from SQL server.
            var data = this.databaseManager.GetProductByPriceGreaterThan1000().ToList()
...
            // Process data
...
            // Create HTTP Response.
...
            // Info.
            return response;
        }
...

In the above code, I am simply using my entity framework DbContext global variable to access a strore procedure from my SQL server database.

 

Step 10

 

If you execute the attached solution in REST Web API client, in my case I am using Firefox plugin i.e. "RESTED" and you will be able to see  the following response i.e.:

 

 

Conclusion

 

In this article, you learned to create REST Web API connection with SQL server Entity Framework Database First approach using ASP.NET REST Web API platform. You learned about creating GET type method with input request parameter. You learned to load data from SQL server using entity framework database first approach and finally, you  learned to test REST Web API using any REST client to see your REST Web API in action before consumption.