ASP.NET MVC: REST Web API SQL Server Connection using Entity Framework Database First Approach

One of the key advantage of using REST Web API as a mode of data exchange between client-server machines is that it can connect any type to data source and client machines do not need to bother about it. Depend 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 creation of 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.

Download Now!

Let's begin now.

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

2) Although you can create your entity framework model inside any hierarchy of the project. But, 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.

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.

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

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.

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.

7) Create "Controllers\WebApiController.cs" file.

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

9) Now, create "Get" method without parameter inside "Controllers\WebApiController.cs" file and replace 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.

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 following response i.e.

Conclusion

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