CRUD Operations In PostgreSQL With EF Core And ASP.NET Core Web API

Introduction

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley. PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

 

We can download and install the official certified PostgreSQL database from EnterpriseDB (EDB) site.

 

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

 

 

 

You can select pgAdmin 4 for administering the database.

 

 

 

Please provide a password for the database superuser.

 

 

 

Choose the default port and locale and start installing the database.

 

After few minutes, PostgreSQL will be installed on your machine.

 

We can open pgAdmin 4 tool now.

 

Please set a master password for pgAdmin. We must give this password, later we open the pgAdmin.

 

 

 

 

We can see a default server PostgreSQL 12. You must give your superuser password to open this server.

 

 

 

By default, there is one login role “postgres” and some group roles available. We can create one more group role.

 

 

 

You can set a password to your group role in the “Definition” tab.

 

 

 

You must set at least the “Can login” option to “Yes” inside the “Privileges” tab. Otherwise, you can’t do database operations with this group role.

 

 

 

We can create a new database now. I have chosen the owner name as “sarath” which was created recently.

 

 

 

In Postgres, each table comes under schemas. By default, there is a “public” schema for each database.

 

 

 

We can create a new table inside the public schema. You can either create a table using the graphical feature in pgAdmin or by SQL scripts.

 

We can choose the “Query Tool” from the “Tools” menu to enter SQL scripts and execute.

 

 

 

Create a table “patients” under the “public” schema. We set the owner of the table to “sarath”

 

 

CREATE TABLE public.patients
(
  id character varying(50) NOT NULL,
  name character varying(200) NOT NULL,
  address character varying(500),
  city character varying(100),
  age numeric NOT NULL,
  gender character varying(10),
  CONSTRAINT patient_pkey PRIMARY KEY (id)
);

ALTER TABLE public.patients
  OWNER TO sarath;

Create ASP.NET Core Web API using Visual Studio

We can create a simple Web API with ASP.NET Core 3.1 SDK using the default template.

 

We are using “Npgsql.EntityFrameworkCore.PostgreSQL” NuGet library for entity operations. We can install this library.

 

We can create a model class “Patient” under the new “Models” folder.

 

Patient.cs

namespace PostgresCRUD.Models
{
    public class Patient
    {
        public string id { get; set; }
        public string name { get; set; }
        public string address { get; set; }
        public string city { get; set; }
        public float age { get; set; }
        public string gender { get; set; }
    }
}

Create a Context class “PostgreSqlContext” under the new folder “DataAccess”

PostgreSqlContext.cs

using Microsoft.EntityFrameworkCore;
using PostgresCRUD.Models;

namespace PostgresCRUD.DataAccess
{
    public class PostgreSqlContext: DbContext
    {
        public PostgreSqlContext(DbContextOptions<PostgreSqlContext> options) : base(options)
        {
        }

        public DbSet<Patient> patients { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }

        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();
            return base.SaveChanges();
        }
    }
}

Create “IDataAccessProvider” interface with the below method signatures.

 

IDataAccessProvider.cs

using PostgresCRUD.Models;
using System.Collections.Generic;

namespace PostgresCRUD.DataAccess
{
    public interface IDataAccessProvider
    {
        void AddPatientRecord(Patient patient);
        void UpdatePatientRecord(Patient patient);
        void DeletePatientRecord(string id);
        Patient GetPatientSingleRecord(string id);
        List<Patient> GetPatientRecords();
    }
}

We can implement the above interface inside the “DataAccessProvider” class.

 

DataAccessProvider.cs

using PostgresCRUD.Models;
using System.Collections.Generic;
using System.Linq;

namespace PostgresCRUD.DataAccess
{
    public class DataAccessProvider: IDataAccessProvider
    {
        private readonly PostgreSqlContext _context;

        public DataAccessProvider(PostgreSqlContext context)
        {
            _context = context;
        }

        public void AddPatientRecord(Patient patient)
        {
            _context.patients.Add(patient);
            _context.SaveChanges();
        }

        public void UpdatePatientRecord(Patient patient)
        {
            _context.patients.Update(patient);
            _context.SaveChanges();
        }

        public void DeletePatientRecord(string id)
        {
            var entity = _context.patients.FirstOrDefault(t => t.id == id);
            _context.patients.Remove(entity);
            _context.SaveChanges();
        }

        public Patient GetPatientSingleRecord(string id)
        {
            return _context.patients.FirstOrDefault(t => t.id == id);
        }

        public List<Patient> GetPatientRecords()
        {
            return _context.patients.ToList();
        }
    }
}

We have added all the code for CRUD operations inside the above class.

 

We can register the PostgreSqlContext class and DataAccessProvider interface and class inside the Startup class.

 

Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using PostgresCRUD.DataAccess;

namespace PostgresCRUD
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();

            var sqlConnectionString = Configuration["PostgreSqlConnectionString"];

            services.AddDbContext<PostgreSqlContext>(options => options.UseNpgsql(sqlConnectionString));

            services.AddScoped<IDataAccessProvider, DataAccessProvider>();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

We can create a controller “PatientsController” under the “Controllers” folder.

 

PatientsController.cs

using Microsoft.AspNetCore.Mvc;
using PostgresCRUD.DataAccess;
using PostgresCRUD.Models;
using System;
using System.Collections.Generic;

namespace PostgresCRUD.Controllers
{
    [Route("api/[controller]")]
    public class PatientsController : ControllerBase
    {
        private readonly IDataAccessProvider _dataAccessProvider;

        public PatientsController(IDataAccessProvider dataAccessProvider)
        {
            _dataAccessProvider = dataAccessProvider;
        }

        [HttpGet]
        public IEnumerable<Patient> Get()
        {
            return _dataAccessProvider.GetPatientRecords();
        }

        [HttpPost]
        public IActionResult Create([FromBody]Patient patient)
        {
            if (ModelState.IsValid)
            {
                Guid obj = Guid.NewGuid();
                patient.id = obj.ToString();
                _dataAccessProvider.AddPatientRecord(patient);
                return Ok();
            }
            return BadRequest();
        }

        [HttpGet("{id}")]
        public Patient Details(string id)
        {
            return _dataAccessProvider.GetPatientSingleRecord(id);
        }

        [HttpPut]
        public IActionResult Edit([FromBody]Patient patient)
        {
            if (ModelState.IsValid)
            {
                _dataAccessProvider.UpdatePatientRecord(patient);
                return Ok();
            }
            return BadRequest();
        }

        [HttpDelete("{id}")]
        public IActionResult DeleteConfirmed(string id)
        {
            var data = _dataAccessProvider.GetPatientSingleRecord(id);
            if (data == null)
            {
                return NotFound();
            }
            _dataAccessProvider.DeletePatientRecord(id);
            return Ok();
        }
    }
}

We have completed the API side coding part. We can use the Postman tool to test our API application.

 

Create a new patient record using a POST request.

 

 

 

We can use GET request to retrieve the patient record.

 

 

We can modify the existing patient record by PUT request.

 

 

 

 

You can perform the DELETE request also in the same way.

 

We have successfully executed all CRUD operations in PostgreSQL along with ASP.NET Core Web API using the entity framework.

Conclusion

In this post, we have seen how to install PostgreSQL on a windows machine and we have created a simple Web API application with ASP.NET Core 3.1 framework. We have created a DataContext class and DataAccessProvider class to access the PostgreSQL database. We have used Entity Framework Core for data mapping. We have successfully checked all the CRUD operations using the Postman tool.