Blazor with PostgreSQL using ADO.NET (Enterprise-Ready Guide)

In this article, we will explore how to connect a Blazor (Server or WebAssembly) application with a PostgreSQL database using ADO.NET (Npgsql).

This approach is especially useful when:

• You prefer Stored Procedures or Raw SQL

• You want to avoid ORM overhead

• You are building banking or enterprise-grade systems

🧱 Architecture Overview

Blazor UI
↓
Service Layer
↓
Repository (ADO.NET)
↓
PostgreSQL (Npgsql)

• The UI layer has no direct knowledge of database logic

• The Repository layer is responsible only for SQL / Stored Procedures

🛠️ Prerequisites

• .NET 9or .NET 10

• Blazor Server or Blazor WebAssembly

• PostgreSQL 13+

• NuGet Package:

📦 Install Npgsql

dotnet add package Npgsql

🔌 Connection String Configuration

{
"ConnectionStrings": {
"PostgresDb": "Host=localhost;Port=5432;Database=bankdb;Username=postgres;Password=postgres"
}
}

📁 Model (Entity)

public class Customer
{
public int Id { get; set; }
public string CustomerName { get; set; }
public string AccountNo { get; set; }
public decimal Balance { get; set; }
}

🧠 Repository Layer using ADO.NET

using Npgsql;
using System.Data;


public class CustomerRepository
{
private readonly IConfiguration _configuration;


public CustomerRepository(IConfiguration configuration)
{
_configuration = configuration;
}


private NpgsqlConnection GetConnection()
{
return new NpgsqlConnection(
_configuration.GetConnectionString("PostgresDb"));
}


public List<Customer> GetAllCustomers()
{
var customers = new List<Customer>();


using var conn = GetConnection();
using var cmd = new NpgsqlCommand("SELECT * FROM customers", conn);


conn.Open();
using var reader = cmd.ExecuteReader();


while (reader.Read())
{
customers.Add(new Customer
{
Id = reader.GetInt32("id"),
CustomerName = reader.GetString("customer_name"),
AccountNo = reader.GetString("account_no"),
Balance = reader.GetDecimal("balance")
});
}


return customers;
}
}

✔ Pure ADO.NET ✔ No EF Core ✔ High performance and predictable behavior

🧪 Stored Procedure Example (PostgreSQL)

CREATE OR REPLACE FUNCTION get_customer_by_id(p_id INT)
RETURNS TABLE (
id INT,
customer_name TEXT,
account_no TEXT,
balance NUMERIC
)
AS $$
BEGIN
RETURN QUERY
SELECT * FROM customers WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

📞 Calling Stored Procedures from ADO.NET

public Customer GetCustomerById(int id)
{
using var conn = GetConnection();
using var cmd = new NpgsqlCommand("get_customer_by_id", conn);


cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("p_id", id);


conn.Open();
using var reader = cmd.ExecuteReader();


if (reader.Read())
{
return new Customer
{
Id = reader.GetInt32("id"),
CustomerName = reader.GetString("customer_name"),
AccountNo = reader.GetString("account_no"),
Balance = reader.GetDecimal("balance")
};
}


return null;
}

🔄 Service Layer

public class CustomerService
{
private readonly CustomerRepository _repo;


public CustomerService(CustomerRepository repo)
{
_repo = repo;
}


public List<Customer> GetCustomers()
{
return _repo.GetAllCustomers();
}
}

🖥️ Blazor Component (UI)

@page "/customers"
@inject CustomerService customerService


<h3>Customer List</h3>


<table class="table">
<thead>
<tr>
<th>Name</th>
<th>Account</th>
<th>Balance</th>
</tr>
</thead>
<tbody>
@foreach (var c in customers)
{
<tr>
<td>@c.CustomerName</td>
<td>@c.AccountNo</td>
<td>@c.Balance</td>
</tr>
}
</tbody>
</table>


@code {
List<Customer> customers = new();


protected override void OnInitialized()
{
customers = customerService.GetCustomers();
}
}

🧩 Dependency Injection Configuration

builder.Services.AddScoped<CustomerRepository>();
builder.Services.AddScoped<CustomerService>();

✅ Why Use ADO.NET with Blazor?

✔ Ideal for financial applications

✔ Heavy stored procedure usage

✔ Full control over SQL execution

✔ No hidden ORM behavior

🏁 Conclusion

Blazor + PostgreSQL + ADO.NET is a proven, enterprise-grade combination. If you want maximum control, performance, and predictability without relying on an ORM, this architecture is an excellent choice.