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.
 
 
 
 
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 in 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 “Definition” tab.
 
 
 
You must set at least “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 graphical feature in pgAdmin or by SQL scripts.
 
We can choose the “Query Tool” from “Tools” menu to enter SQL scripts and execute.
 
 
 
Create a table “patients” under “public” schema. We set the owner of table to “sarath”
 
 
 
  1. CREATE TABLE public.patients    
  2. (    
  3.   id character varying(50) NOT NULL,    
  4.   name character varying(200) NOT NULL,    
  5.   address character varying(500),    
  6.   city character varying(100),    
  7.   age numeric NOT NULL,    
  8.   gender character varying(10),    
  9.   CONSTRAINT patient_pkey PRIMARY KEY (id)    
  10. );    
  11.     
  12. ALTER TABLE public.patients    
  13.   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 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 new “Models” folder.
 
Patient.cs
  1. namespace PostgresCRUD.Models  
  2. {  
  3.     public class Patient  
  4.     {  
  5.         public string id { getset; }  
  6.         public string name { getset; }  
  7.         public string address { getset; }  
  8.         public string city { getset; }  
  9.         public float age { getset; }  
  10.         public string gender { getset; }  
  11.     }  
  12. }  
Create a Context class “PostgreSqlContext” under new folder “DataAccess”

PostgreSqlContext.cs
  1. using Microsoft.EntityFrameworkCore;  
  2. using PostgresCRUD.Models;  
  3.   
  4. namespace PostgresCRUD.DataAccess  
  5. {  
  6.     public class PostgreSqlContext: DbContext  
  7.     {  
  8.         public PostgreSqlContext(DbContextOptions<PostgreSqlContext> options) : base(options)  
  9.         {  
  10.         }  
  11.   
  12.         public DbSet<Patient> patients { getset; }  
  13.   
  14.         protected override void OnModelCreating(ModelBuilder builder)  
  15.         {  
  16.             base.OnModelCreating(builder);  
  17.         }  
  18.   
  19.         public override int SaveChanges()  
  20.         {  
  21.             ChangeTracker.DetectChanges();  
  22.             return base.SaveChanges();  
  23.         }  
  24.     }  
  25. }  
Create “IDataAccessProvider” interface with below method signatures.
 
IDataAccessProvider.cs
  1. using PostgresCRUD.Models;  
  2. using System.Collections.Generic;  
  3.   
  4. namespace PostgresCRUD.DataAccess  
  5. {  
  6.     public interface IDataAccessProvider  
  7.     {  
  8.         void AddPatientRecord(Patient patient);  
  9.         void UpdatePatientRecord(Patient patient);  
  10.         void DeletePatientRecord(string id);  
  11.         Patient GetPatientSingleRecord(string id);  
  12.         List<Patient> GetPatientRecords();  
  13.     }  
  14. }  
We can implement above interface inside the “DataAccessProvider” class.
 
DataAccessProvider.cs
  1. using PostgresCRUD.Models;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4.   
  5. namespace PostgresCRUD.DataAccess  
  6. {  
  7.     public class DataAccessProvider: IDataAccessProvider  
  8.     {  
  9.         private readonly PostgreSqlContext _context;  
  10.   
  11.         public DataAccessProvider(PostgreSqlContext context)  
  12.         {  
  13.             _context = context;  
  14.         }  
  15.   
  16.         public void AddPatientRecord(Patient patient)  
  17.         {  
  18.             _context.patients.Add(patient);  
  19.             _context.SaveChanges();  
  20.         }  
  21.   
  22.         public void UpdatePatientRecord(Patient patient)  
  23.         {  
  24.             _context.patients.Update(patient);  
  25.             _context.SaveChanges();  
  26.         }  
  27.   
  28.         public void DeletePatientRecord(string id)  
  29.         {  
  30.             var entity = _context.patients.FirstOrDefault(t => t.id == id);  
  31.             _context.patients.Remove(entity);  
  32.             _context.SaveChanges();  
  33.         }  
  34.   
  35.         public Patient GetPatientSingleRecord(string id)  
  36.         {  
  37.             return _context.patients.FirstOrDefault(t => t.id == id);  
  38.         }  
  39.   
  40.         public List<Patient> GetPatientRecords()  
  41.         {  
  42.             return _context.patients.ToList();  
  43.         }  
  44.     }  
  45. }  
We have added all the code for CRUD operations inside above class.
 
We can register the PostgreSqlContext class and DataAccessProvider interface and class inside the Startup class.
 
Startup.cs
  1. using Microsoft.AspNetCore.Builder;  
  2. using Microsoft.AspNetCore.Hosting;  
  3. using Microsoft.EntityFrameworkCore;  
  4. using Microsoft.Extensions.Configuration;  
  5. using Microsoft.Extensions.DependencyInjection;  
  6. using Microsoft.Extensions.Hosting;  
  7. using PostgresCRUD.DataAccess;  
  8.   
  9. namespace PostgresCRUD  
  10. {  
  11.     public class Startup  
  12.     {  
  13.         public Startup(IConfiguration configuration)  
  14.         {  
  15.             Configuration = configuration;  
  16.         }  
  17.   
  18.         public IConfiguration Configuration { get; }  
  19.   
  20.         // This method gets called by the runtime. Use this method to add services to the container.  
  21.         public void ConfigureServices(IServiceCollection services)  
  22.         {  
  23.             services.AddControllers();  
  24.   
  25.             var sqlConnectionString = Configuration["PostgreSqlConnectionString"];  
  26.   
  27.             services.AddDbContext<PostgreSqlContext>(options => options.UseNpgsql(sqlConnectionString));  
  28.   
  29.             services.AddScoped<IDataAccessProvider, DataAccessProvider>();  
  30.         }  
  31.   
  32.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
  33.         public void Configure(IApplicationBuilder app, IWebHostEnvironment env)  
  34.         {  
  35.             if (env.IsDevelopment())  
  36.             {  
  37.                 app.UseDeveloperExceptionPage();  
  38.             }  
  39.   
  40.             app.UseRouting();  
  41.   
  42.             app.UseAuthorization();  
  43.   
  44.             app.UseEndpoints(endpoints =>  
  45.             {  
  46.                 endpoints.MapControllers();  
  47.             });  
  48.         }  
  49.     }  
  50. }  
We can create a controller “PatientsController” under “Controllers” folder.
 
PatientsController.cs
  1. using Microsoft.AspNetCore.Mvc;  
  2. using PostgresCRUD.DataAccess;  
  3. using PostgresCRUD.Models;  
  4. using System;  
  5. using System.Collections.Generic;  
  6.   
  7. namespace PostgresCRUD.Controllers  
  8. {  
  9.     [Route("api/[controller]")]  
  10.     public class PatientsController : ControllerBase  
  11.     {  
  12.         private readonly IDataAccessProvider _dataAccessProvider;  
  13.   
  14.         public PatientsController(IDataAccessProvider dataAccessProvider)  
  15.         {  
  16.             _dataAccessProvider = dataAccessProvider;  
  17.         }  
  18.   
  19.         [HttpGet]  
  20.         public IEnumerable<Patient> Get()  
  21.         {  
  22.             return _dataAccessProvider.GetPatientRecords();  
  23.         }  
  24.   
  25.         [HttpPost]  
  26.         public IActionResult Create([FromBody]Patient patient)  
  27.         {  
  28.             if (ModelState.IsValid)  
  29.             {  
  30.                 Guid obj = Guid.NewGuid();  
  31.                 patient.id = obj.ToString();  
  32.                 _dataAccessProvider.AddPatientRecord(patient);  
  33.                 return Ok();  
  34.             }  
  35.             return BadRequest();  
  36.         }  
  37.   
  38.         [HttpGet("{id}")]  
  39.         public Patient Details(string id)  
  40.         {  
  41.             return _dataAccessProvider.GetPatientSingleRecord(id);  
  42.         }  
  43.   
  44.         [HttpPut]  
  45.         public IActionResult Edit([FromBody]Patient patient)  
  46.         {  
  47.             if (ModelState.IsValid)  
  48.             {  
  49.                 _dataAccessProvider.UpdatePatientRecord(patient);  
  50.                 return Ok();  
  51.             }  
  52.             return BadRequest();  
  53.         }  
  54.   
  55.         [HttpDelete("{id}")]  
  56.         public IActionResult DeleteConfirmed(string id)  
  57.         {  
  58.             var data = _dataAccessProvider.GetPatientSingleRecord(id);  
  59.             if (data == null)  
  60.             {  
  61.                 return NotFound();  
  62.             }  
  63.             _dataAccessProvider.DeletePatientRecord(id);  
  64.             return Ok();  
  65.         }  
  66.     }  
  67. }  
We have completed the API side coding part. We can use Postman tool to test our API application.
 
Create a new patient record using POST request.
 
 
 
We can use GET request to retrieve 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 entity framework.

Conclusion


In this post, we have seen how to install PostgreSQL in 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 PostgreSQL database. We have used Entity Framework Core for data mapping. We have successfully checked all the CRUD operations using Postman tool.