How to Create Web API (CRUD) in Asp.net Core With PostgreSQL

In this Tutorial, We will learn How to Create Web API (CRUD) in Asp.net Core With PostgreSQL. It is web service that allows clients to access and manipulate data stored in a database. The API provides a set of endpoints that clients can use to perform CRUD(Create, Read, Update and Delete ) operation on the data.

Table of Contents

Install Required Software

NET Core SDK from here
Visual Studio or Visual Studio Code from here
PostgreSQL database from here

Create ASP.NET Core Web API Project

Open Visual Studio and Create a new project.
Select ASP.NET Core Web Application and give it a name.
Choose the API template ad select Create

Install NuGet Packages

Npgsql.EntityFrameworkCore.PostgreSQL
Microsoft.EntityFrameworkCore.Tools

Also check previous article How to Implement Model Validation using fluent Validation in Asp.net Core

Create the Model

Now you need to create a model that represents the data you want to store in the PostgreSQL database.

Create Folder Models of your project and Create Class Users with properties like Firstname, Username, Password etc.

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations;
using WebAPI_Postgresql.Models;

namespace WebAPI_Postgresql.Models
{
    public class Users
    {
        [Key]

        public int Id { get; set; }
        public string FirstName { get; set; }
        public string Username { get; set; }
        public string password { get; set; }

    }
}

Create the Database Context

The Database context is responsible for communicating with the PostgreSQL database.

Create a new Class UsersdbContext that inherits from DbContext.
Add a DbSet property for each table you want to access.

using Microsoft.EntityFrameworkCore;

namespace WebAPI_Postgresql.Models
{
    public class UsersdbContext :DbContext
    {
        public UsersdbContext(DbContextOptions<UsersdbContext> options)
            :base(options)
        { 
        

        } 
        public virtual DbSet<Users> Users { get; set; }
    }
}

Configure PostgreSQL

PostgreSQL with your ASP.NET Core Web API, you need to configure the connection string in the appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "connection": "Server=localhost;Port=5432;Database=PostgreSQL15;User Id=postgres;Password=Admin;"

  },
  "AllowedHosts": "*"
}

Configure Program.cs

using Microsoft.EntityFrameworkCore;
using WebAPI_Postgresql.Models;
using WebAPI_Postgresql.Controllers;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();

builder.Services.AddDbContext<UsersdbContext>(option=> option.UseNpgsql(builder.Configuration.GetConnectionString("connection")));

var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();
app.Run();

Migrations

Run migrations

add-migration 'initial'
Update-Database

Create the Controller

Create the controller that will handle HTTP request to the API. You can do this by create a new class in the controllers folder of your project.

Now implement the CRUD (Create, Read, Update, Delete) operations in your controller. This can be done by using the database context and the HttpGet, HttpPost, HttpPut and HttpDelete attributes.

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using WebAPI_Postgresql.Models;

namespace WebAPI_Postgresql.Controllers
{
        [Route("api/[controller]")]
        [ApiController]
        public class UsersController : ControllerBase
        {
            private UsersdbContext _users;
            
            public UsersController(UsersdbContext users)
            {
                _users = users;
                
            }
            // GET: api/<UsersController>
            [HttpGet]
            public IActionResult Get()
            {
                var user = _users.Users;
                return Ok(user);
            }


            public Users GetById(int id)
            {
                var users = _users.Users.Find(id);
                if (users == null) { throw new KeyNotFoundException("User Not Found"); }
                return users;
            }


        // GET api/<UsersController>/5
        [AllowAnonymous]
            [HttpGet("{id}")]
            public IActionResult Get(int id)
            {
                var emp = _users.Users.Find(id);
                return Ok(emp);
            }

        // POST api/<UsersController>
        [HttpPost]
            public IActionResult Post([FromBody] Users model )
            {

                var userExist = _users.Users.Any(e => e.Username == model.Username);
                if (userExist == true)
                {
                    return Ok(new { Message = "User Already Created" });
                    
                }

                _users.Add(model);
                _users.SaveChanges();

                return Ok(new { Message = "User Created" });
            }

        // PUT api/<UsersController>/5
        [HttpPut("{id}")]
            public IActionResult Put([FromBody] Users model)
            {
            
            _users.Users.Attach(model);
            _users.Entry(model).State = EntityState.Modified;        
                

               // _users.Users.Update(model);
                _users.SaveChanges();
                          
                return Ok(new { Message = "User Updated" });
            }

        // DELETE api/<UsersController>/5
        [HttpDelete("{id}")]
            public IActionResult Delete(int id)
            {
                var user = GetById(id);               

                _users.Users.Remove(user);
                _users.SaveChanges();

                return Ok(new { Message = "User Deleted" });
               
            }
        }
    }