ASP.NET Web API using SQL Server

ASP.NET Web API using SQL Server

In this article, I am going to discuss ASP.NET Web API using SQL Server. So here we will create the ASP.NET Web API Service which will perform the CRUD operation on the SQL Server database. We are going to use this service as the base for understanding many of the ASP.NET Web API concepts that we are going to discuss in our upcoming articles. 

The ASP.NET Web API EmployeeService that we are going to build will retrieve the data from the Employees database table. We will be using Entity Framework Database First Approach to retrieve data from the SQL server database. You can use any technology of your choice to retrieve data from the database. For example, you can even use raw ADO.NET. 

Example: ASP.NET Web API using SQL Server

We are going to use the following Employees table in this demo to understand how to create ASP.NET Web API Application using the SQL Server database.

ASP.NET Web API using SQL Server

Please use the following SQL Script to create the WEBAPI_DB database and Employees table and populate the Employees table with sample data.

CREATE DATABASE WEBAPI_DB
GO

USE WEBAPI_DB
GO

CREATE TABLE Employees
(
     ID int primary key identity,
     FirstName nvarchar(50),
     LastName nvarchar(50),
     Gender nvarchar(50),
     Salary int
)
GO

INSERT INTO Employees VALUES ('Pranaya', 'Rout', 'Male', 60000)
INSERT INTO Employees VALUES ('Anurag', 'Mohanty', 'Male', 45000)
INSERT INTO Employees VALUES ('Preety', 'Tiwari', 'Female', 45000)
INSERT INTO Employees VALUES ('Sambit', 'Mohanty', 'Male', 70000)
INSERT INTO Employees VALUES ('Shushanta', 'Jena', 'Male', 45000)
INSERT INTO Employees VALUES ('Priyanka', 'Dewangan', 'Female', 30000)
INSERT INTO Employees VALUES ('Sandeep', 'Kiran', 'Male', 45000)
INSERT INTO Employees VALUES('Shudhansshu', 'Nayak', 'Male', 30000)
INSERT INTO Employees VALUES ('Hina', 'Sharma', 'Female', 35000)
INSERT INTO Employees VALUES ('Preetiranjan', 'Sahoo', 'Male', 80000)
GO
Creating a new ASP.NET Web API Project

Open Visual Studio and select File – New – Project as shown below

Creating a new ASP.NET Web API Project

From the “New Project” window, select the Web option under the “Visual C#” option which is under the “Installed” section. Again from the middle pane, you need to select the “ASP.NET Web Application” and name the project as “EmployeeService“. Finally, click on the “OK” button as shown in the below image.

Web API using SQL Server

Once you click on the OK button. A new dialog window will open with the Name “New ASP.NET Project” for selecting project Templates as per your requirement. From this dialog, we are going to choose the Web API project template, select the Authentication type as No Authentication and then click on the OK button as shown in the below image.

Web API Project Template

Once you click on the OK button, it will create the ASP.NET Web API Project. At this point, you should have the ASP.NET Web API project created with the following file and folder structure.

ASP.NET Web API Project File and Folder Structure

Adding ADO.NET Entity Data Model

We are adding ADO.NET Entity Data Model and using the Entity Framework Database First Approach we are going to communicate with the SQL Server database and will perform the CRUD Operations. The following are the steps to add ADO.NET Entity Data Model to ASP.NET Web API Application.

Right-click on the Models folder and then select Add – New Item from the context menu which will open the Add New Item window as shown below. From this “Add New Item” window select “Data” from the left pane which is inside the Visual C# and Installed section and then from the middle pane select ADO.NET Entity Data Model. In the Name text box, type EmployeeDataModel and then click on the Add button as shown in the below image.

Adding ADO.NET Entity Data Model in ASP.NET Web API Project

Once you click on the Add button, it will open the Entity Data Model Wizard window. From this Entity Data Model Wizard, select the “EF Designer from database” option and click the Next button as shown in the below image. EF Designer from the database option is basically used to use the Database First Approach of Entity Framework.

Entity Framework database First Approach in ASP.NET Web API Application

Once you click on the Next button, it will open Choose Your Data Connection window. From this window, click on the “New Connection” button as shown in the below image to create a new connection that will be going to connect with SQL Server Database and the WEBAPI_DB database.

Creating New Connection in Entity Framework Database First Approach

Once you click on the New Connection button it will open the Connection Properties window. On the “Connection Properties” window, set

  1. Data Source = Microsoft SQL Server (SqlClient)  as we are going to interact with SQL Server database
  2. Server Name = provide the server (Machine name or IP address)
  3. Authentication = Select the authentication type (I am using Windows Authentication)
  4. Select or enter a database name = WEBAPI_DB
  5. Click on the Test Connection button to check if every the provided information is correct

Finally, click on the OK button as shown in the below image.

Connection to SQL in ASP.NET Web API Application

Once you click on the OK button it will navigate back to the Choose Your Data Connection wizard. Here Modify the Connection String name as EmployeeDBContext and click on the Next button as shown in the below image.

ASP.NET Web API using SQL Server

Once you click on the Next button, it will open Choose Your Entity Framework Version window. From this window, select Entity Framework 6.x and click on the Next button as shown in the below image. 

Selecting Entity Framework Version

Once you click on the Next button, it will open the “Choose Your Database Objects and Settings” window. From this window, select the “Employees” table, provide the model namespace as Models and click on the Finish button as shown in the below image. 

Choose Your Database Objects and Settings

Once you click on the Finish Button, the following EDMX file with the Employees model will generate.

Employee Model

The EDMX file is generated within the Models folder with the following structure.

Web API using SQL Server

Adding ASP.NET Web API Controller

Let us add an Empty Web API Controller with the name Employees in our Controllers folder. To do so, Right-click on the Controllers folder in the EmployeeService project and then select Add – Controller option from the context menu which opens the following Add Scaffold window. From this window, select the “Web API 2 Controller – Empty” option which will create an empty Web API Controller, and click on the “Add” button as shown in the below image.

Adding ASP.NET Web API Controller

Once you click on the Add button, it will open the following window. In this window, provide the Controller Name as EmployeesController and click on the Add button as shown in the below image.

ASP.NET Web API using SQL Server

Once you click on the Add button, it will create the Employees Web API controller within the Controllers folder in your project.

Creating ASP.NET Web API Services:

Now, let us create two services i.e. two action methods within the Employees controller. One service is going to return the list of employees and the other action method is going to return one employee’s information based on the employee id. To do so, copy and paste the following code in EmployeesController.cs class file. As you can see in the below code, we have created two action methods with the name Get. The first action method does not take any parameter and returns the list of employees while the second action method takes Employee id as a parameter and returns that employee’s information, and hence the return type is Employee.

using EmployeeService.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace EmployeeService.Controllers
{
    public class EmployeesController : ApiController
    {
        public IEnumerable<Employee> Get()
        {
            using (EmployeeDBContext dbContext = new EmployeeDBContext())
            {
                return dbContext.Employees.ToList();
            }
        }
        public Employee Get(int id)
        {
            using (EmployeeDBContext dbContext = new EmployeeDBContext())
            {
                return dbContext.Employees.FirstOrDefault(e => e.ID == id);
            }
        }
    }
}

At this point when you navigate to /api/employees, you should see all employees and when you navigate to /api/employees/1, you should see all the details of the employee whose Id=1. Here, in this article, I just show you how to retrieve the data from the SQL Server database using Entity Framework Database First approach and in our upcoming articles, I will show you how to Perform the INSERT, UPDATE, and DELETE operations using ASP.NET Web API and SQL Server.

In the next article, I am going to discuss Content Negotiation in ASP.NET Web API with Examples. Here, in this article, I try to explain ASP.NET Web API using SQL Server with Examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this Web API using the SQL Server article.

12 thoughts on “ASP.NET Web API using SQL Server”

  1. Hi, quite a good explanation.

    please the code is not compiling cos the Employee controller class is missing a using directive or assembly reference.

    How do i solve that?

    thanks

  2. Saludos, al ejecutar me salió este mensaje:
    Advertencia de seguridad.
    La ejecucion de esta plantilla de texto puede dañar el equipo.
    No la ejecute si la ha obtenido de una fuente que no es de confianza

Leave a Reply

Your email address will not be published. Required fields are marked *