Integration of Web API with database

in my previous post I have describe how to create a Web API project with ASP.NET Core in VS2019 (WebApiDemoProject) and used Swagger which can show an UI to user to interact with backend.

In this part  I’m going to explain, creating API with GET, POST, PUT and DELETE (http mehthods.) These are basic CRUD operations which we need while creating APIs. Also, I’ll explain how to connect the database using the Scaffold DB context and creating a model to process the API request and response.

Scaffolding a database produces an Entity Framework model from an existing database. The resulting entities are created and mapped to the tables in the specified database.

The code can be found in  the GitHub.

The basic things we need to understand before start writing WebAPI’s,

  • Database.
  • HTTP Methods.
  • Router.
  • HTTP Status Code.
  • Model.

WebAPI acts as a middleware between the client-side and server-side. For example, When the user hits a button, it will call an API from the client-side,  Get user details. Now API will process the request and get the data from the database based on logic.


The database is a collection of data in an organized way so that users can easily access, manage, and update.

HTTP Methods

  1. Get: To get data from a database, we use Get Method.
  2. Post: To insert data to a database in the form of a payload in the request body to create/update.
  3. Put: It is similar to Post, mainly meant for updates.
  4. Delete: To delete data from the database.


In the Web API controller, we have different types of action methods [get, post, put, delete ] the router takes this incoming request and routes this request to the appropriate action method in the API controller.

WEB API supports two types of routing

  1. Convention-based Routing – It should have at least one route template using that template it identifies controller and action method to execute.
  2. Attribute Routing – We can define routing directly at the Controller or action level using route attributes Route[()].

In this article, we are using Attribute Routing to define the route of an action method.



Here User is Controller Name and GetUserById is an API name.

HTTP Status Code

These are most commonly used status codes are listed here,

  • 200 OK: When request successfully returned the response.
  • 201 Created: When record inserted to database successfully.
  • 202 Accepted: When a request is accepted for processing.
  • 204 No content: When response returned with no content.
  • 400 BadRequest: When there is a problem with request sending from the client-side.
  • 401 Unauthorized: When authentication or authorization fails.
  • 500 Internal Server Error: When the server doesn’t fulfill the request[ exceptions or errors in API code].


Model is a class that has a set of properties with getters and setters, that represents the data to process the API request.

Let’s start with coding,

Create a Model Folder and Model class file as shown below.

Right click on project => Add => NewFolder [Folder Name : Model]

Right click on Folder => Add => New File [File name : Controller Name ]

Sample Model

GetUserModel is a model name:

public class GetUserModel 
public int UserID { get; set; }
 public string Name { get; set; }
 public string Address { get; set; } 
public string Email { get; set; } 
public string PhoneNumber { get; set; }
 public bool IsActive { get; set; } 

Scaffold-DbContext Command

Select menu Tools -> NuGet Package Manager -> Package Manager Console and run the following command:

PM> Scaffold-DbContext "Server=(LocalDb)\MSSQLLocalDB;Initial Catalog=DemoDatabase;Integrated Security=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir DataModel

Here I’m trying to connect the local database.

Server=.\SQLExpress; refers to local SQLEXPRESS database server.

Database= DemoDatabase

Specifies the database name ” DemoDatabase” for which we are going to create classes.

Trusted_Connection=True (Security=True)

Specifies the Windows authentication. It will use Windows credentials to connect to the SQL Server.


It is the provider’s name. We use a provider for the SQL Server, so it is Microsoft.EntityFrameworkCore.SqlServer.


Parameter specifies the directory where we want to generate all the classes which is the Models folder in this case.


Let’s start creating the API.

Remove the default ValueController.cs under Controllers and create a new Controller with name UserController.cs and paste the following code to  this file:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using WebAPIDemoProject.DataModel;
using static WebAPIDemoProject.Models.UserModel;

namespace WebAPIDemoProject.Controllers
    public class UserController : ControllerBase
        DemoDatabaseContext db = new DemoDatabaseContext();
        // GET: api/GetUserById
        public ActionResult GetUserById(int id)
                var user = db.Set<Users>().Where(w => w.Id == id).Select(c=> new GetUserModel { 
                return Ok(user);
            catch (Exception ex)
                return BadRequest(ex.StackTrace);

        // We can write both Post and Put(update) in same API
        // POST: api/PostUser
        public ActionResult PostUser(GetUserModel userModel)
                var userIsExist = db.Set<Users>().FirstOrDefault(w => w.Id == userModel.UserID);

                //Update block
                if (userIsExist!=null) 
                    userIsExist.Name = userModel.Name;
                    userIsExist.PhoneNumber = userModel.PhoneNumber;
                    userIsExist.Email = userModel.Email;
                    userIsExist.Address = userModel.Address;
                else// User not exist creaing new user
                    var user = new Users();
                    // user.Id =;  auto increament in Database
                    user.Name = userModel.Name;
                    user.PhoneNumber = userModel.PhoneNumber;
                    user.Email = userModel.Email;
                    user.Address = userModel.Address;
                    user.IsActive = user.IsActive;
                return Ok();
            catch (Exception ex)
                return BadRequest(ex.StackTrace);

        // DELETE: api/DeleteUserById
        public ActionResult DeleteUser(int id)
                var user = db.Set<Users>().FirstOrDefault(w => w.Id == id);
                if (user != null)
                    user.IsActive = false;
                    return Ok();

                    return Ok("User not Exist");
            catch (Exception ex)
                return BadRequest(ex.StackTrace);

 Explaining the code above:

Try-Catch block

Always add try-catch block if API fails in the middle of the process you can return BadRequest with exception details.


DemoDatabaseContext db = new DemoDatabaseContext();

DemoDatabaseContext is database context name. DemoDatabaseContext is used to access database tables.


We have this outside controller class. This means we are setting path: api/User

Linq (Language Integrated Query)

It is used to get data from a database based on conditions. In the below example, we are trying to get user details using userId.

DemoDatabaseContext db = new DemoDatabaseContext();

Gets user details by user Id.

API Name



For every API we have routing. Each name should be unique. API path : api/User/GetUserById

Post API / Put API

Both post and put are done in the same API.

Delete API

In this, we are just setting IsActive to false.


Testing API from Visual Studiow:

In the menu select WebApiDemoProject (instead of IIS Express) and run the application. then swagger UI is loaded up as follow:

Swagger UI is loaded


In this post, I have described HTTP Methods, Router., HTTP Status Code. Model and Database. I have explained how to create a Model from the Database by using Scaffold-DbContext.

I have also showed how to create a new Controller to connect to the database to Get, and Post Data. This was the Integration of WebApi with Database.

The code can be found in  the My GitHub.

This post is a part of APIs step by step

Back to home page

Leave a Reply

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