PostgreSQL is one of the most popular databases among developers. It is feature-rich, reliable, powerful, and open-source, an excellent choice for the backend of data-driven applications. Such applications must efficiently store, manage, retrieve, and manipulate data, which makes stable and reliable database connectivity a critical requirement. One of the most convenient ways to ensure connectivity is by using dotConnect ADO.NET providers.
In this guide, we will walk through the process of building an ASP.NET Core Blazor Web App that performs full CRUD (Create, Read, Update, Delete) operations on a PostgreSQL database, with dotConnect acting as the data provider. The application will be developed step by step, with each section representing a fully functional stage of the project.
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and other modern data access technologies for efficient and reliable data management.
Conforms to the latest ADO.NET standards and recent industry innovations for seamless and consistent integration with .NET applications.
Includes many PostgreSQL-specific features and fully supports all unique data types for accurate and complete data representation.
Provides robust security with support for SSL/SSH connections, connecting via proxy servers, embedded servers, and HTTP tunneling.
Features native integration with Visual Studio and complete design-time support for accelerated development.
Includes priority support, detailed documentation, and regular updates for continuous improvement.
You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:
Our first option is connecting to PostgreSQL via the built-in Data Explorer in Visual Studio. Click Tools, then select Connect to Database, and choose PostgreSQL as the data source.
Enter your server details and credentials, and click Connect.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Open the appsettings.json file and add your PostgreSQL connection string:
{
"ConnectionStrings": {
"DefaultConnection": "Host=127.0.0.1;User Id=postgres;Password=postgres;Database=postgres;License key=**********"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}Here, our goal is to establish a connection to the PostgreSQL database and display data in a read-only table. This ensures our database connection and data service are correctly configured and provides the foundation for the other CRUD operations.
In your project, create a new folder named Data. Inside it, create a class file named Actor.cs. This class will act as a model to hold the data for each actor record.
namespace PostgreSqlBlazor.Data;
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public DateTime LastUpdate { get; set; }
}In the Data folder, create a new class ActorService.cs. This service will contain the logic to interact with the database. Note that we will use Dependency Injection to provide this service to our components, so the class is not static.
using Devart.Data.PostgreSql;
namespace PostgreSqlBlazor.Data;
public class ActorService
{
private readonly string _connectionString;
public ActorService(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection")!;
}
public async Task<List<Actor>> GetAllActorsAsync()
{
var actors = new List<Actor>();
using var connection = new PgSqlConnection(_connectionString);
await connection.OpenAsync();
using var command = new PgSqlCommand("SELECT actor_id, first_name, last_name, last_update FROM public.actor ORDER BY actor_id", connection);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
actors.Add(new Actor
{
ActorId = reader.GetInt32(reader.GetOrdinal("actor_id")),
FirstName = reader.GetString(reader.GetOrdinal("first_name")),
LastName = reader.GetString(reader.GetOrdinal("last_name")),
LastUpdate = reader.GetDateTime(reader.GetOrdinal("last_update"))
});
}
return actors;
}
}In Program.cs, register the ActorService as a scoped service so it can be injected into our components.
// Program.cs
using PostgreSqlBlazor.Components;
using PostgreSqlBlazor.Data;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddRazorComponents()
.AddInteractiveServerComponents();
// Register the ActorService
builder.Services.AddScoped<ActorService>();
var app = builder.Build();
// ...In the Components/Pages folder, create a new Razor Component named Actors.razor. This page will fetch and display the data.
@page "/actors"
@rendermode InteractiveServer
@using PostgreSqlBlazor.Data
@inject ActorService ActorSvc
<PageTitle>Actors</PageTitle>
<h1>Actor List</h1>
@if (actors == null)
{
<p><em>Loading...</em></p>
}
else
{
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Last Update</th>
</tr>
</thead>
<tbody>
@foreach (var actor in actors)
{
<tr>
<td>@actor.ActorId</td>
<td>@actor.FirstName</td>
<td>@actor.LastName</td>
<td>@actor.LastUpdate.ToString("yyyy-MM-dd HH:mm")</td>
</tr>
}
</tbody>
</table>
}
@code {
private List<Actor>? actors;
protected override async Task OnInitializedAsync()
{
actors = await ActorSvc.GetAllActorsAsync();
}
}In Components/Layout/NavMenu.razor, add a link to your new page.
<div class="nav-item px-3">
<NavLink class="nav-link" href="actors">
<span class="bi bi-person-fill-gear" aria-hidden="true"></span> Actors
</NavLink>
</div>You have now built a functional, read-only Blazor application that connects to a PostgreSQL database and displays the data in a table. This confirms that your core setup, including dependency injection and data retrieval, is working correctly.
Now we will enhance the application by adding the ability to create new records. This involves adding an EditForm to the UI and implementing the corresponding logic to execute an INSERT SQL command.
Modify Actors.razor to add a dedicated EditForm for creating new actors.
@page "/actors"
@rendermode InteractiveServer
@using PostgreSqlBlazor.Data
@inject ActorService ActorSvc
@inject IJSRuntime JSRuntime
<PageTitle>Actors</PageTitle>
<h1>Actors</h1>
<h3>Add New Actor</h3>
<EditForm Model="@newActor" OnValidSubmit="HandleInsert" FormName="NewActorForm">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="mb-3">
<label for="newFirstName" class="form-label">First Name</label>
<InputText id="newFirstName" @bind-Value="newActor.FirstName" class="form-control" />
</div>
<div class="mb-3">
<label for="newLastName" class="form-label">Last Name</label>
<InputText id="newLastName" @bind-Value="newActor.LastName" class="form-control" />
</div>
<button type="submit" class="btn btn-primary">Insert Actor</button>
</EditForm>
<hr />
@* The table for displaying actors will be updated in later steps. *@
Add the InsertActorAsync method to your ActorService.cs file.
// ... (add this async method inside the ActorService class)
public async Task InsertActorAsync(Actor actor)
{
using var connection = new PgSqlConnection(_connectionString);
await connection.OpenAsync();
using var command = new PgSqlCommand("INSERT INTO public.actor (first_name, last_name, last_update) VALUES (:first_name, :last_name, NOW())", connection);
command.Parameters.AddWithValue(":first_name", actor.FirstName);
command.Parameters.AddWithValue(":last_name", actor.LastName);
await command.ExecuteNonQueryAsync();
}Modify the @code block in Actors.razor to handle the submission form for adding new actors.
@code {
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new();
await LoadActors();
}
}
The application can now add new records to the database. Users can input data into the form, press the Insert Actor button, and see the table update immediately with the new entry.
We need to implement the ability to edit existing records. This requires adding Edit buttons to our table and logic to populate the form for editing. We will reuse the same EditForm.
Modify Actors.razor to include a conditional EditForm for updating actors and add Edit and Delete buttons to each row in the table.
<!-- ... (Existing 'Add New Actor' form and <hr />) ... -->
@if (actorToEdit.ActorId != 0)
{
<h3>Edit Actor</h3>
<EditForm Model="@actorToEdit" OnValidSubmit="HandleUpdate" FormName="EditActorForm">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="mb-3">
<label for="editFirstName" class="form-label">First Name</label>
<InputText id="editFirstName" @bind-Value="actorToEdit.FirstName" class="form-control" />
</div>
<div class="mb-3">
<label for="editLastName" class="form-label">Last Name</label>
<InputText id="editLastName" @bind-Value="actorToEdit.LastName" class="form-control" />
</div>
<button type="submit" class="btn btn-success">Save</button>
<button type="button" class="btn btn-secondary" @onclick="ClearForm">Cancel</button>
</EditForm>
<hr />
}
@if (actors == null)
{
<p><em>Loading...</em></p>
}
else
{
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>First Name</th>
<th>Last Name</th>
<th>Last Update</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach (var actor in actors)
{
<tr>
<td>@actor.ActorId</td>
<td>@actor.FirstName</td>
<td>@actor.LastName</td>
<td>@actor.LastUpdate.ToString("yyyy-MM-dd HH:mm")</td>
<td>
<button class="btn btn-sm btn-warning" @onclick="() => SetActorForUpdate(actor)">Edit</button>
<button class="btn btn-sm btn-danger" @onclick="() => HandleDelete(actor)">Delete</button>
</td>
</tr>
}
</tbody>
</table>
}
Add the UpdateActorAsync method to ActorService.cs.
// ... (add this async method inside the ActorService class)
public async Task UpdateActorAsync(Actor actor)
{
using var connection = new PgSqlConnection(_connectionString);
await connection.OpenAsync();
using var command = new PgSqlCommand("UPDATE public.actor SET first_name = :first_name, last_name = :last_name, last_update = NOW() WHERE actor_id = :actor_id", connection);
command.Parameters.AddWithValue(":first_name", actor.FirstName);
command.Parameters.AddWithValue(":last_name", actor.LastName);
command.Parameters.AddWithValue(":actor_id", actor.ActorId);
await command.ExecuteNonQueryAsync();
}Update the @code block in Actors.razor to manage the state for both inserting and updating.
@code {
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new();
await LoadActors();
}
private void SetActorForUpdate(Actor actor)
{
actorToEdit = new Actor
{
ActorId = actor.ActorId,
FirstName = actor.FirstName,
LastName = actor.LastName
};
}
private async Task HandleUpdate()
{
await ActorSvc.UpdateActorAsync(actorToEdit);
ClearForm();
await LoadActors();
}
private void ClearForm()
{
actorToEdit = new();
}
}
The application now supports editing data. Users can click Edit on an actor, change their details in the form, and save the changes back to the database.
Finally, we will implement the deletion functionality. This involves adding a Delete button and using Blazor's JavaScript Interop feature to show a confirmation dialog.
Add the Delete button to each row in the Actor table.
<!-- ... (Inside the table foreach loop) ... -->
<td>
<button class="btn btn-sm btn-warning" @onclick="() => SetActorForUpdate(actor)">Edit</button>
<button class="btn btn-sm btn-danger" @onclick="() => HandleDelete(actor)">Delete</button>
</td>
Add the DeleteActorAsync method to ActorService.cs.
// Data/ActorService.cs
// ... (add this async method inside the ActorService class)
public async Task DeleteActorAsync(int actorId)
{
using var connection = new PgSqlConnection(_connectionString);
await connection.OpenAsync();
using var command = new PgSqlCommand("DELETE FROM public.actor WHERE actor_id = :actor_id", connection);
command.Parameters.AddWithValue(":actor_id", actorId);
await command.ExecuteNonQueryAsync();
}Inject the IJSRuntime service and add the HandleDelete method to the @code block. This method uses JavaScript to prompt the user for confirmation before deleting.
@code {
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new();
await LoadActors();
}
private void SetActorForUpdate(Actor actor)
{
actorToEdit = new Actor
{
ActorId = actor.ActorId,
FirstName = actor.FirstName,
LastName = actor.LastName
};
}
private async Task HandleUpdate()
{
await ActorSvc.UpdateActorAsync(actorToEdit);
ClearForm();
await LoadActors();
}
private void ClearForm()
{
actorToEdit = new();
}
private async Task HandleDelete(Actor actor)
{
bool confirmed = await JSRuntime.InvokeAsync<bool>("confirm", $"Are you sure you want to delete {actor.FirstName} {actor.LastName}?");
if (confirmed)
{
await ActorSvc.DeleteActorAsync(actor.ActorId);
await LoadActors();
}
}
}
You have completed the Blazor application with full CRUD functionality to read, create, update, and delete records in a PostgreSQL database through a modern web interface.
In this article, we built an ASP.NET Core Blazor Web App and implemented the core CRUD operations against a PostgreSQL database using a simple, fully working example.
By using dotConnect for PostgreSQL, we could simplify database connectivity, avoid common issues, and ensure smooth communication between the application and the database. It helped us reliably retrieve, insert, update, and delete data without adding unnecessary complexity.
Try dotConnect for PostgreSQL in your own projects. Start a free trial, improve connectivity, boost performance, and strengthen the security of your application!
Host, User Id, Password, Database, and (if required) License Key. Then create a PgSqlConnection with this string and call Open() inside a try/catch block to verify the connection and handle any errors.
SslMode=Require and provide the certificate file paths (for example, CACert, Cert, and Key) in the connection string. Then create a PgSqlConnection with this string and call Open() to establish an encrypted SSL/TLS session.
DbContext and entity classes, or run Scaffold-DbContext with a dotConnect connection string (including License Key) to scaffold the DbContext and entities from an existing database.
I'm a technical content writer who loves breaking complex tech topics into clear and helpful content that's enjoyable to read. With a solid writing background and growing skill in software development and database tools, I create content that's accurate, easy to follow, and genuinely useful. When I'm not writing, you'll probably find me learning something new or sweating it out at the gym.
