Cheap Windows Hosting Review

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

Posted on by Pablo Francesco

This article will discuss how to use ADO.NET (Npgsql) to link a Blazor (Server or WebAssembly) application with a PostgreSQL database.

This method works particularly well when:

  • You favor raw SQL or stored procedures.
  • You wish to stay away from ORM overhead.
  • You are developing enterprise-level or financial 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.

Best ASP.NET Core 10.0 Hosting

The feature and reliability are the most important things when choosing a good ASP.NET Core 10.0 hosting. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core , their servers are optimized for PHP web applications such as the latest ASP.NET Core 10.0 version. The performance and the uptime of the ASP.NET Core  hosting service are excellent, and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its data centers are equipped with top equipment like cooling system, fire detection, high-speed Internet connection, and so on. That is why HostForLIFE.eu guarantees 99.9% uptime for ASP.NET Core . And the engineers do regular maintenance and monitoring works to assure its ASP.NET Core  hosting are security and always up.

hostforlifebanner