Blazor - CRUD Using PostgreSQL And Entity Framework Core

In this article, we will see the CRUD actions inBlazorFramework 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 dates 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 a 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 in to 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 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 letters.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 a Patient app project with the Blazor (ASP.NET Core Hosted) template.

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

We can chooseBlazor (ASP.NET Core Hosted) template.

Our new project will be ready shortly.

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

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

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

Please install “Microsoft.EntityFrameworkCore.Tools” and “Npgsql.EntityFrameworkCore.PostgreSQL” (for PostgreSQL) NuGet packages in the “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 the 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, that we have initialized the “IDataAccessProvider” interface inside this class. We can modify the “ConfigureServices” method inside the Startup class in the Server project.

We have used dependency injection to initialize ourDataAccessProvider 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, wecan modify the Client project. Please modify the “NavMenu” Razor View files under the “Shared” folder with the below code.

NavMenu.cshtml

<p 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>  
</p>  

<p 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>  
</p>  

@functions {  
    bool collapseNavMenu = true;  

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

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

Please add the below code to 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 />
<p class="row">
    <p class="col-md-4">
        <form>
            <p class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@patient.name" />
            </p>
            <p class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@patient.address" />
            </p>
            <p class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" bind="@patient.city" />
            </p>
            <p class="form-group">
                <label for="Age" class="control-label">Age</label>
                <input for="Age" class="form-control" bind="@patient.age" />
            </p>
            <p 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>
            </p>
            <p 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" />
            </p>
        </form>
    </p>
</p>

@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 />

<p class="row">
    <p class="col-md-4">
        <form>
            <p class="form-group">
                <label for="Name" class="control-label">Name</label>
                <input for="Name" class="form-control" bind="@patient.name" />
            </p>
            <p class="form-group">
                <label for="Address" class="control-label">Address</label>
                <input for="Address" class="form-control" bind="@patient.address" />
            </p>
            <p class="form-group">
                <label for="City" class="control-label">City</label>
                <input for="City" class="form-control" bind="@patient.city" />
            </p>
            <p class="form-group">
                <label for="Age" class="control-label">Age</label>
                <input for="Age" class="form-control" bind="@patient.age" />
            </p>
            <p 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>
            </p>
            <p 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" />
            </p>
        </form>
    </p>
</p>

@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 />
<p 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>
    <p 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" />
    </p>
</p>

@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 workflow 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” table 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.