Blazor - CRUD Using PostgreSQL And Entity Framework Core

In this article, we will see the CRUD actions in Blazor Framework with a PostgreSQL database and Entity Framework Core. We will create a sample patient app with all CRUD actions.

I already wrote an article in C# Corner about Blazor with CosmosDB.

• Single Page Application With Blazor And CosmosDB

This time, we are using PostgreSQL as a database. PostgreSQL is a powerful, open source object-relational database system. The origin of PostgreSQL date back to 1986 as a part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. It is very easy to use and high-performance database and absolutely free!

Please refer to this URL to get more details about PostgreSQL.

Step 1 - Create a database in PostgreSQL

Before creating a new database, I will create a login role in PostgreSQL. I am using pgAdmin3 to administer PostgreSQL.

Please log into pgAdmin3 with default credentials and create a new “Login Role”.

I am creating this login role as “Super User”.

 

After clicking the OK button, our login role will be created successfully.

Now, we can create a new database from the database tab.

 

Please give a valid database name. We can set any login role as “Owner” to this database. I am giving all privileges to this database.

 

Our new database is created now.

If you check the database details in pgAdmin3, you can see the below details.

Step 2 - Create a new "patients” tables for our Patient App

We can create a new “patients” table in our database. For that, we can use the SQL editor option in pgAdmin3.

 

Please use the below script to create a new table inside the database.

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;

Please note that in PostgreSQL, all the column names are created in lowercase letter.s Even if you write the script in uppercase letters, objects will be created in lowercase only.

Our table is ready. Now, we can create our Blazor project.

Step 3 - Create Patient app project with Blazor (ASP.NET Core Hosted) template.

Please refer to my previous article to check the prerequisites for the Blazor project.

 

We can choose Blazor (ASP.NET Core Hosted) template.

 

Our new project will be ready shortly.

Please note that by default, our solution has 3 projects.

 

“Client“ project contains all the client-side scripts and library files; “Server” project contains our Web API part (controllers and other business logic); and “Shared” project contains all the commonly shared classes like models.

We will use Entity Framework Core to establish a connection between PostgreSQL and our .NET Core application.

Please install “Microsoft.EntityFrameworkCore.Tools” and “Npgsql.EntityFrameworkCore.PostgreSQL” (for PostgreSQL) NuGet packages in “Server” project.

 

 

In our solution, some classes and Razor pages are automatically created at the project creation time. We can remove the unwanted files now. We can create a Patient model class now in the Shared project. Please create a Models folder and create a Patient class inside this folder.

Patient.cs

namespace BlazorPostgresCRUD.Shared.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; }
    }
}

Now we can create the interface “IDataAccessProvider”. This interface will implement the CRUD actions inside the DataAccessProvider class.

IDataAccessProvider.cs.

using System.Collections.Generic;

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

We can create “DomainModelPostgreSqlContext” under new “DataAccess” folder in our Server project. This class will provide the PostgreSQL context to our application.

DomainModelPostgreSqlContext.cs

using BlazorPostgresCRUD.Shared.Models;
using Microsoft.EntityFrameworkCore;

namespace BlazorPostgresCRUD.Server.DataAccess
{
    public class DomainModelPostgreSqlContext : DbContext
    {
        public DomainModelPostgreSqlContext(DbContextOptions<DomainModelPostgreSqlContext> 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();
        }

    }
}

We can create the “DataAccessPostgreSqlProvider” class now. This class will implement the “IDataAccessProvider” interface. This class provides all the CRUD actions to our Web API Controller (We will create this controller soon).

DataAccessPostgreSqlProvider.cs

using BlazorPostgresCRUD.Shared.Models;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Linq;

namespace BlazorPostgresCRUD.Server.DataAccess
{
    public class DataAccessPostgreSqlProvider : IDataAccessProvider
    {
        private readonly DomainModelPostgreSqlContext _context;
        private readonly ILogger _logger;

        public DataAccessPostgreSqlProvider(DomainModelPostgreSqlContext context, ILoggerFactory loggerFactory)
        {
            _context = context;
            _logger = loggerFactory.CreateLogger("DataAccessPostgreSqlProvider");
        }

        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.First(t => t.id == id);
            _context.patients.Remove(entity);
            _context.SaveChanges();
        }

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

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

    }
}

We have defined all the CRUD actions inside this class.

Now, we can create our Web API controller. Please create a new API Class Controller.

 

Please add the below code to this Controller class.

PatientsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using BlazorPostgresCRUD.Shared.Models;
using Microsoft.AspNetCore.Mvc;

namespace BlazorPostgresCRUD.Server.Controllers
{
    public class PatientsController : Controller
    {
        private readonly IDataAccessProvider _dataAccessProvider;

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

        [HttpGet]
        [Route("api/Patients/Get")]
        public IEnumerable<Patient> Get()
        {
            return _dataAccessProvider.GetPatientRecords();
        }

        [HttpPost]
        [Route("api/Patients/Create")]
        public void Create([FromBody]Patient patient)
        {
            if (ModelState.IsValid)
            {
                Guid obj = Guid.NewGuid();
                patient.id = obj.ToString();
                _dataAccessProvider.AddPatientRecord(patient);
            }
        }

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

        [HttpPut]
        [Route("api/Patients/Edit")]
        public void Edit([FromBody]Patient patient)
        {
            if (ModelState.IsValid)
            {
                _dataAccessProvider.UpdatePatientRecord(patient);
            }
        }

        [HttpDelete]
        [Route("api/Patients/Delete/{id}")]
        public void DeleteConfirmed(string id)
        {
            _dataAccessProvider.DeletePatientRecord(id);
        }
    }
}

Please note, we have initialized “IDataAccessProvider” interface inside this class. We can modify the “ConfigureServices” method inside the Startup class in Server project.

 

We have used dependency injection to initialize our DataAccessProvider class and added DBContext to our service. I have simply hard coded the PostgreSQL connection string in this class. You may try with a separate JSON configuration file if needed.

Our Shared project and Server project are ready. Now, we can modify the Client project. Please modify the “NavMenu” Razor View files under “Shared” folder with the below code.

NavMenu.cshtml

<div class="top-row pl-4 navbar navbar-dark">
    <a class="navbar-brand" href="">Patient App</a>
    <button class="navbar-toggler" onclick=@ToggleNavMenu>
        <span class="navbar-toggler-icon"></span>
    </button>
</div>

<div class=@(collapseNavMenu ? "collapse" : null) onclick=@ToggleNavMenu>
    <ul class="nav flex-column">

        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match=NavLinkMatch.All>
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>

        <li class="nav-item px-3">
            <NavLink class="nav-link" href="/listpatients">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Patient Details
            </NavLink>
        </li>
    </ul>
</div>

@functions {
bool collapseNavMenu = true;

void ToggleNavMenu()
{
    collapseNavMenu = !collapseNavMenu;
}
}

We can create “ListPatients” Razor View under “Pages” folder now.

 

 

Please add the below code in this Razor page.

ListPatients.cshtml

@using BlazorPostgresCRUD.Shared.Models
@page "/listpatients"
@inject HttpClient Http

<h1>Patient Details</h1>
<p>
    <a href="/addpatient">Create New Patient</a>
</p>
@if (patientList == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class='table'>
        <thead>
            <tr>
                <th>Name</th>
                <th>Address</th>
                <th>City</th>
                <th>Age</th>
                <th>Gender</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var patient in patientList)
            {
                <tr>
                    <td>@patient.name</td>
                    <td>@patient.address</td>
                    <td>@patient.city</td>
                    <td>@patient.age</td>
                    <td>@patient.gender</td>
                    <td>
                        <a href='/editpatient/@patient.id'>Edit</a>
                        <a href='/deletepatient/@patient.id'>Delete</a>
                    </td>
                </tr>
            }
        </tbody>
    </table>
}
@functions {
Patient[] patientList;

protected override async Task OnInitAsync()
{
    patientList = await Http.GetJsonAsync<Patient[]>("/api/Patients/Get");
}

}

Also, add the below three Razor Views in our Client project.

AddPatients.cshtml

@using BlazorPostgresCRUD.Shared.Models
@page "/addpatient"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Create Patient</h2>
<hr />
<div class="row">
    <div class="col-md-4">
        <form>
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@patient.name" />
            </div>
            <div class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@patient.address" />
            </div>
            <div class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" bind="@patient.city" />
            </div>
            <div class="form-group">
                <label for="Age" class="control-label">Age</label>
                <input for="Age" class="form-control" bind="@patient.age" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@patient.gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
            <div class="form-group">
                <input type="button" class="btn btn-default" onclick="@(async () => await CreatePatient())" value="Save" />
                <input type="button" class="btn" onclick="@Cancel" value="Cancel" />
            </div>
        </form>
    </div>
</div>
@functions {

Patient patient = new Patient();

protected async Task CreatePatient()
{
    await Http.SendJsonAsync(HttpMethod.Post, "/api/Patients/Create", patient);
    UriHelper.NavigateTo("/listpatients");
}

void Cancel()
{
    UriHelper.NavigateTo("/listpatients");
}
}

EditPatient.cshtml

@using BlazorPostgresCRUD.Shared.Models
@page "/editpatient/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Edit</h2>
<h4>Patient</h4>
<hr />
<div class="row">
    <div class="col-md-4">
        <form>
            <div class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@patient.name" />
            </div>
            <div class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@patient.address" />
            </div>
            <div class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" bind="@patient.city" />
            </div>
            <div class="form-group">
                <label for="Age" class="control-label">Age</label>
                <input for="Age" class="form-control" bind="@patient.age" />
            </div>
            <div class="form-group">
                <label for="Gender" class="control-label">Gender</label>
                <select for="Gender" class="form-control" bind="@patient.gender">
                    <option value="">-- Select Gender --</option>
                    <option value="Male">Male</option>
                    <option value="Female">Female</option>
                </select>
            </div>
            <div class="form-group">
                <input type="button" value="Save" onclick="@(async () => await UpdatePatient())" class="btn btn-default" />
                <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
            </div>
        </form>
    </div>
</div>
@functions {

[Parameter]
string id { get; set; }

Patient patient = new Patient();

protected override async Task OnInitAsync()
{
    patient = await Http.GetJsonAsync<Patient>("/api/Patients/Details/" + id);
}

protected async Task UpdatePatient()
{
    await Http.SendJsonAsync(HttpMethod.Put, "api/Patients/Edit", patient);
    UriHelper.NavigateTo("/listpatients");

}

void Cancel()
{
    UriHelper.NavigateTo("/listpatients");
}

}

DeletePatient.cshtml

@using BlazorPostgresCRUD.Shared.Models
@page "/deletepatient/{id}"
@inject HttpClient Http
@inject Microsoft.AspNetCore.Blazor.Services.IUriHelper UriHelper

<h2>Delete</h2>
<p>Are you sure you want to delete this Patient with id :<b> @id</b></p>
<br />
<div class="col-md-4">
    <table class="table">
        <tr>
            <td>Name</td>
            <td>@patient.name</td>
        </tr>
        <tr>
            <td>Address</td>
            <td>@patient.address</td>
        </tr>
        <tr>
            <td>City</td>
            <td>@patient.city</td>
        </tr>
        <tr>
            <td>Age</td>
            <td>@patient.age</td>
        </tr>
        <tr>
            <td>Gender</td>
            <td>@patient.gender</td>
        </tr>
    </table>
    <div class="form-group">
        <input type="button" value="Delete" onclick="@(async () => await Delete())" class="btn btn-default" />
        <input type="button" value="Cancel" onclick="@Cancel" class="btn" />
    </div>
</div>
@functions {

[Parameter]
string id { get; set; }

Patient patient = new Patient();

protected override async Task OnInitAsync()
{
    patient = await Http.GetJsonAsync<Patient>
("/api/Patients/Details/" + id);
}

protected async Task Delete()
{
    await Http.DeleteAsync("api/Patients/Delete/" + id);
    UriHelper.NavigateTo("/listpatients");
}

void Cancel()
{
    UriHelper.NavigateTo("/listpatients");
}

}

Our entire application is ready now. We can check the work flow one by one.

Please run the application and create new Patient information now.

 

After creating the record, it will be automatically displayed in the List View.

Now, we can edit the information.

 

I have edited the City field. If you check the pgAdmin3 SQL query, you can now get the data.

 

We can delete the data now.

 

We have seen all four CRUD actions in this sample Patient app.

In this article, we saw how to create a database in PostgreSQL and we created a new login role as well. Later, we created a “patients” tables with 6 columns. Then, we created a Blazor application with Entity Framework Core. We used the dependency injection to initialize the DataAccessProvider class inside our project.

We can see more Blazor projects with new exciting features in upcoming articles.