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

Sarathlal Saseendran

5y165.6k10250Article

10 Comments

Join the conversation! Your thoughts help the community grow.

Jamil Moughal3y ago
Thanks for sharing
Dheerendra Dwivedi4y ago
I Created Procedure in PostgreSQL .My question that "How to call procedure while i am using EF "?
John DAntonio5y ago
What would be the best way to make this accept multiple json objects?
alok singh5y ago
How to use this api in flutter ? can u help me out
prabhat chandra6y ago
Hi please confirm which approach is this DBFirst or model first approach please confirm as soon as possible
Rutuparna Panda6y ago
When I am try to send post request I am getting an exception:{ "Message": "An error has occurred.", "ExceptionMessage": "An error occurred when trying to create a controller of type 'UserController'. Make sure that the controller has a parameterless public constructor.", "ExceptionType": "System.InvalidOperationException", "StackTrace": " at System.Web.Http.Dispatcher.DefaultHttpControllerActivator.Create(HttpRequestMessage request, HttpControllerDescriptor controllerDescriptor, Type controllerType)\r\n at System.Web.Http.Controllers.HttpControllerDescriptor.CreateController(HttpRequestMessage request)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()", "InnerException": { "Message": "An error has occurred.", "ExceptionMessage": "Type 'CP_API.Controllers.UserController' does not have a default constructor", "ExceptionType": "System.ArgumentException", "StackTrace": " at System.Linq.Expressions.Expression.New(Type type)\r\n at System.Web.Http.Internal.TypeActivator.Create[TBase](Type instanceType)\r\n at System.Web.Http.Dispatcher.DefaultHttpControllerActivator.GetInstanceOrActivator(HttpRequestMessage request, Type controllerType, Func`1& activator)\r\n at System.Web.Http.Dispatcher.DefaultHttpControllerActivator.Create(HttpRequestMessage request, HttpControllerDescriptor controllerDescriptor, Type controllerType)" } }
Ben Hayat6y ago
The link to download the code is broken and goes back to this page
- Ben Hayat6y ago
  Hi; I'm logged in as you can see my name shows with my comment. If I'm not logged in, I get a page to log in. But when I click on the link, it refreshes the page. I'm using FireFox. Can you try it with FF to see if you get same behavior? I've tried other download links from other posts, and they work. Something is not right with this one.
- Ben Hayat6y ago
  Ok, I logged out and logged back in and works. Something is up with the site and login cache. BTW, thank you for this great article. More on PosgreSQL articles for EF and C# developers are welcomed.
- Sarathlal Saseendran6y ago
  Please login first and then try to download. Link is working fine.

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.