Connecting C# to PostgreSQL in .NET Blazor Using EF Core

Blazor is a modern front-end web framework based on HTML and C# that helps developers build web applications faster. Developers can build reusable components that interact nicely with each other to help create a beautiful web UI.

PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language. It's highly extensible and, being open-source, it's a good choice for integrating data into your applications.

This tutorial will use the Code-First approach to integrate PostgreSQL with a Blazor application, allowing you to design and manage your database directly from your C# code.

Why dotConnect for PostgreSQL?

dotConnect for PostgreSQL is the ideal data provider for all PostgreSQL-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.

Prerequisites

What you'll need to complete this tutorial:

• PostgreSQL server: The database server we will interact with.
• Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• Blazor: This is a feature of ASP.NET for building interactive web UIs using C# instead of JavaScript. It's real .NET running in the browser on WebAssembly.
• dotConnect for PostgreSQL: This tool is a high-performance ADO.NET data provider for PostgreSQL with enhanced ORM support and database connectivity features.

Download and activate dotConnect for PostgreSQL

Visit the dotConnect for PostgreSQL download page. You can choose the free trial or the full version of dotConnect for PostgreSQL. The trial version gives you 30 days to explore all features and doesn't require a license key for activation, but it can't be used for deploying commercial projects.

Download and install the trial version

To use the trial version, download and install dotConnect for PostgreSQL on your machine. No license key is required for the trial, and you can start exploring the product immediately.

Purchase and access the full version

If you're ready to purchase the full version, click Buy Now to add it to your cart and complete the purchase.

After purchasing the full version, go to Your Licenses in the user menu. There you'll have a list of your licenses, including your dotConnect for PostgreSQL purchase.

To view your license details and find the activation key, click Details next to your dotConnect for PostgreSQL license.

Download the full version

Click Download Product Versions to view the list of available product versions. Select the most recent version and download the installer.

Run the downloaded file and follow the on-screen instructions to install dotConnect for PostgreSQL on your machine. This process installs the software and activates all features associated with your license key.

Create a Blazor Web App project

Next, let's create a Blazor project. Open Visual Studio and select Create a new project. In the search box, enter Blazor Web App, select the matching search result, and click Next.

Give your project a name—we'll call ours PostgreSQLBlazor. Click Next.

Select .NET 8 as the target framework, leave default values for all other options, and click Create.

Install packages

We're going to install dotConnect for PostgreSQL to our project via the NuGet Package Manager in Visual Studio. On the menu bar, go to Tools > NuGet Package Manager and select Manage NuGet Packages for Solution.

The NuGet - Solution tab opens. Click Browse and search for Devart.Data.PostgreSQL.EF Core. Select the matching result, then select your project, and click Install.

Next, open the Package Manager Console and run this command to install the EF Core tools needed for the application:

Install-Package Microsoft.EntityFrameworkCore.Tools

Run the application by pressing F5 to open the scaffolded webpage.

Create a connection instance

In your project, create a new folder named Models and add a new class named Actor. The Actor class will have the following structure:

public class Actor
{
    [Key]
    public int ActorId { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public DateTime LastUpdate { get; set; }
}

Next, we need to scaffold our DbContext. So, in the Models folder, create a new class named ActorDbContext and copy this code into it:

public class ActorDbContext : DbContext
{
    public ActorDbContext(DbContextOptions<ActorDbContext> options) : base(options)
    {

    }
    public DbSet<Actor> Actors { get; set; }
}

Your project should look like this now:

Create a PostgreSQL table

We need to set up a few things to create a table based on the DbContext we have defined.

First, in your Blazor project, create a class named DatabaseConfiguration and copy the below code into it:

public static class DatabaseConfiguration
{
    public static string GetConnectionString()
    {
        string databasePath = "" +
            "Server=127.0.0.1;" +
            "Port=5432;" +
            "UserId=TestUser;" +
            "Password=TestPassword;" +
            "Database=devart_test;" +
            "Schema=public;";
        string licenseKey = "your-license-key";
        return $"{databasePath};LicenseKey={licenseKey};";
    }
}

DatabaseConfiguration has a single method, GetConnectionString, which returns a concatenated string of the connection string of your PostgreSQL database and the license key for your dotConnect for PostgreSQL purchase (you can find it on the Devart customer portal).

The PostgreSQL connection string contains the following properties:

• Host
• Port
• UserId
• Password
• Database
• Schema

Note: This approach is for tutorial purposes only. Ideally, you should store your activation key more securely, such as using Environment Secrets. Don't forget to replace UserId and Password values with your actual credentials.

Next, create a class named MyDbContextFactory and add the following code:

public class MyDbContextFactory : 
IDesignTimeDbContextFactory<ActorDbContext>
{
    public ActorDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ActorDbContext>();
        string connectionString = DatabaseConfiguration.GetConnectionString();
        optionsBuilder.UsePostgreSql(connectionString);

        return new ActorDbContext(optionsBuilder.Options);
    }
}

The MyDbContextFactory class is implementing the IDesignTimeDbContextFactory<ActorDbContext> interface. This interface creates instances of our DbContext (ActorDbContext) at design time.

So far, your project should look like this:

Next, in Program.cs, add these two lines of code so that your project knows the correct database string to reference:

string connectionString = DatabaseConfiguration.GetConnectionString();
builder.Services.AddDbContext<ActorDbContext>(options => 
    options.UsePostgreSql(connectionString));

Everything is set up now, so let's create the first migration.

From the Package Manager Console, run this piece of code:

Add-Migration InitialCreate -Context ActorDbContext

This command creates a Migrations folder in the project based on the current state of our ActorDbContext.

We must update the database to finish this process and create the Actor table. So go ahead and run the below command in the Package Manager Console as well:

Update-Database

If all goes well, you get a response saying “Applying migration 20240910145215_InitialCreate.” This means your migration has been applied, and your Actor table has been created.

Before moving on, let's create a Services folder that will be the hub for most business logic.

In the Services folder, create an interface named IActorService and add the below code:

public interface IActorService
{
    Task<List<Actor>> GetActorsAsync();
    Task<Actor> GetActorByIdAsync(int actorId);
    Task AddActorAsync(Actor actor);
    Task UpdateActorAsync(Actor actor);
    Task DeleteActorAsync(int actorId);
}

Then, still in the Services folder, create a new class that will implement the interface we created, call it ActorService, and copy this code into it:

public class ActorService : IActorService
{
    private readonly ActorDbContext _context;

    public ActorService(ActorDbContext context)
    {
        _context = context;
    }

    public async Task<List<Actor>> GetActorsAsync()
    {
        return await _context.Actors.ToListAsync();
    }

    public async Task<Actor> GetActorByIdAsync(int actorId)
    {
        var actor = await _context.Actors.FindAsync(actorId);
        if (actor == null)
        {
            throw new Exception("Actor not found");
        }
        return actor;
    }

    public async Task AddActorAsync(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Add(actor);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateActorAsync(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Entry(actor).State = EntityState.Modified;
        await _context.SaveChangesAsync();
    }

    public async Task DeleteActorAsync(int actorId)
    {
        var actor = await _context.Actors.FindAsync(actorId);
        if (actor != null)
        {
            _context.Actors.Remove(actor);
            await _context.SaveChangesAsync();
        }
    }
}

Now, register both the interface and its implementation by adding the code below to Program.cs:

builder.Services.AddScoped<IActorService, ActorService>();

At this point, your project should look like this:

Insert PostgreSQL data

Let's insert some data into our Actor table. From your project, go to the Pages folder under Components and add a Razor component named Actor.

In the Actor.razor file, add this piece of code:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation

<h3>Actor</h3>

<button class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

We've created a button that navigates to the add-actor page. So, let's create it too.

In the Pages folder under Components, add another Razor component named AddActor and copy this piece of code into it:

@page "/add-actor"
@rendermode InteractiveServer
@using PostgreSQLBlazor.Models
@inject PostgreSQLBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Add New Actor</h3>

<div class="mb-3">
    <label for="firstName" class="form-label">First Name</label>
    <input type="text" class="form-control" id="firstName" @bind="newFirstName" />
</div>
<div class="mb-3">
    <label for="lastName" class="form-label">Last Name</label>
    <input type="text" class="form-control" id="lastName" @bind="newLastName" />
</div>
<button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
<button class="btn btn-primary" @onclick="InsertActor">Save</button>

@code {
    private string newFirstName;
    private string newLastName;

    private async Task InsertActor()
    {
        var newActor = new Models.Actor
            {
                FirstName = newFirstName,
                LastName = newLastName,
                LastUpdate = DateTime.UtcNow
            };
        await ActorService.AddActorAsync(newActor);

        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Close the modal and navigate back to the actor page
        Navigation.NavigateTo("/actor");
    }
}

We'll need to add the previous Actor component to the navigation bar to have it displayed.

Go to the Layouts folder under Components, open NavMenu.razor, and replace the code with the one below:

<div class="top-row ps-3 navbar navbar-dark">
    <div class="container-fluid">
        <a class="navbar-brand" href="">PostgreSQLBlazor</a>
    </div>
</div>

<input type="checkbox" title="Navigation menu" class="navbar-toggler" />

<div class="nav-scrollable" onclick="document.querySelector('.navbar-toggler').click()">
    <nav class="flex-column">
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="bi bi-house-door-fill-nav-menu" aria-hidden="true"></span> Home
            </NavLink>
        </div>
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="actor">
                <span class="bi bi-list-nested-nav-menu" aria-hidden="true"></span> Actors
            </NavLink>
        </div>

    </nav>
</div>

Now run the application. Click Actors > Add New Actor, enter the actor's details, and click Save.

The Actors page opens.

Read PostgreSQL data

Let's modify our application to show the created actor in the database. Replace the code in Actor.razor with the below:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation
@inject PostgreSQLBlazor.Services.IActorService ActorService

<h3>Actor Table</h3>

@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</td>
                </tr>
            }
        </tbody>
    </table>
}

<button class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private List<Models.Actor> actors;

    protected override async Task OnInitializedAsync()
    {
        // Load the actors when the component is initialized
        actors = await ActorService.GetActorsAsync();
    }


    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

Run the application and click Actors. The actor is displayed in the table.

Update PostgreSQL data

Let's assume that our actor Seth wants to change their last name. Our application should be able to handle that.

So, in the Pages folder, create a new Razor component named Update and add the below code to the file:

@page "/update/{actorId:int}"
@rendermode InteractiveServer
@using PostgreSQLBlazor.Models
@inject PostgreSQLBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Update Actor</h3>

@if (actor == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <div class="mb-3">
        <label for="firstName" class="form-label">First Name</label>
        <input type="text" class="form-control" id="firstName" @bind="actor.FirstName" />
    </div>
    <div class="mb-3">
        <label for="lastName" class="form-label">Last Name</label>
        <input type="text" class="form-control" id="lastName" @bind="actor.LastName" />
    </div>
    <button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
    <button class="btn btn-primary" @onclick="UpdateActor">Save</button>
}

@code {
    [Parameter]
    public int actorId { get; set; }

    private Models.Actor actor;

    protected override async Task OnInitializedAsync()
    {
        // Load the actor to be updated
        actor = await ActorService.GetActorByIdAsync(actorId);
    }

    private async Task UpdateActor()
    {
        // Update the actor using the service
        await ActorService.UpdateActorAsync(actor);

        // Redirect back to the actor list page
        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Navigate back to the actor list page
        Navigation.NavigateTo("/actor");
    }
}

Then, in Actor.razor, modify the table element in the else block with this code:

<table class="table">
    <thead>
        <tr>
            <th>ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Last Update</th>
            <th>Actions</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</td>
                <td>
                    <button class="btn btn-warning btn-sm" @onclick="() => NavigateToUpdate(actor.ActorId)">Update</button>
                </td>
            </tr>
        }
    </tbody>
</table>

Then, in the @code block, add this method:

private void NavigateToUpdate(int actorId)
{
    Navigation.NavigateTo($"/update/{actorId}");
}

This will help you navigate to the page we created with the particular actor's ID.

Run the application and go to Actors. Click Update to open the page with the actor's details.

Change the actor's details and click Save to update the actor.

The actor is displayed in the table with updated details.

Delete PostgreSQL data

Let's try to remove an actor from our database. For reference, we have an actor, “Johnny Blaze,” which was added behind the scenes and which we'll remove now.

In the Actor.razor file, add this line of code under the Update button:

<button class="btn btn-danger btn-sm" @onclick="() => 
DeleteActor(actor.ActorId)">Delete</button>

Then add this method to the @code block:

private async Task DeleteActor(int actorId)
{
    await ActorService.DeleteActorAsync(actorId);

    actors = await ActorService.GetActorsAsync();
}

This method takes that particular actor's ID and calls our DeleteActorAsync service.

Run the application and go to Actors. To delete an actor, click Delete.

We've removed the “Johnny Blaze” actor from the table.

Video tutorial: How to Connect a .NET Blazor Application to a PostgreSQL Database

Conclusion

In this tutorial, we explored how to connect a Blazor application to a PostgreSQL database using dotConnect for PostgreSQL. We also saw how to create tables, insert data into a table, read from a table, and update or delete records.