You can install the driver by using the Windows installer.
After you receive the license key, add it to your connection strings to connect to the data source.
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.
Why dotConnect for PostgreSQL?
Advanced ORM support
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and other modern data access technologies for efficient and reliable data management.
Full ADO.NET compliance
Conforms to the latest ADO.NET standards and recent industry innovations for seamless and consistent integration with .NET applications.
PostgreSQL-specific data types
Includes many PostgreSQL-specific features and fully supports all unique data types for accurate and complete data representation.
Secure connection options
Provides robust security with support for SSL/SSH connections, connecting via proxy servers, embedded servers, and HTTP tunneling.
IDE integration
Features native integration with Visual Studio and complete design-time support for accelerated development.
Priority support & frequent updates
Includes priority support, detailed documentation, and regular updates for continuous improvement.
Download and activate dotConnect for PostgreSQL
You can start using dotConnect for PostgreSQL immediately with a 30-day free trial. Choose one of the following installation options:
30-day free trial version
Check PostgreSQL database objects
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.
Connection setup
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": "*"
}Connect and retrieve PostgreSQL data
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.
Create the data model (Actor.cs)
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; }
}Create the data service (ActorService.cs)
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;
}
}Register the service
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();
// ...Create the Actors page (Actors.razor)
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();
}
}Add navigation link
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.
Insert PostgreSQL data
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.
Update the UI (Actors.razor)
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. *@
Update the data service (ActorService.cs)
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();
}Update the UI logic (Actors.razor)
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.
Update PostgreSQL data
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.
Update the UI (Actors.razor)
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>
}
Update the data service (ActorService.cs)
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 UI logic (Actors.razor)
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.
Delete PostgreSQL data
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.
Update the UI (Actors.razor)
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>
Update the data service (ActorService.cs)
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();
}Update the UI logic (Actors.razor)
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.
Video tutorial
Conclusion
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!
FAQ
How do you install and activate dotConnect for PostgreSQL in a .NET project?
Install dotConnect for PostgreSQL either by running the Windows installer (EXE) or by adding the Devart.Data.PostgreSql NuGet package to your .NET project. Then, retrieve your activation key from the Devart Customer Portal and specify it in your connection string by using the License Key parameter to activate the provider and connect successfully.
How do you create a connection to PostgreSQL using dotConnect in C#?
Define a connection string that includes
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.
How do you enable SSL/TLS for secure PostgreSQL connections with dotConnect?
Add
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.
Can you connect to PostgreSQL using Entity Framework Core and dotConnect?
Yes, you can connect to PostgreSQL using Entity Framework Core and dotConnect. You can either use Entity Developer to visually create an EF Core model from the database and generate the
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.
Is it possible to connect to PostgreSQL using Visual Studio Server Explorer with dotConnect?
Yes. In Visual Studio Server Explorer, you can add a new Data Connection, select dotConnect for PostgreSQL as the data provider, enter your PostgreSQL connection details, test the connection, and then browse and work with database objects directly in the IDE.
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.
Try the 30-day trial of the full product. No limits. No card required.
Start free trial