Blazor Pivot Table – PostgreSQL Database Connection using UrlAdaptor

Project Overview

This repository demonstrates a production-ready pattern for binding a PostgreSQL database to the Syncfusion Blazor Pivot Table. The sample reads order records stored in a PostgreSQL table and loads them into the Pivot Table so they can be aggregated and analyzed within the Blazor UI.

The implementation focuses on a clean data-access flow where the Pivot Table works against data loaded from PostgreSQL through the sample application, rather than requiring direct database access from the client.

Key Features

• PostgreSQL Integration: Order records are persisted in a PostgreSQL table (public.orders) and read through the Npgsql ADO.NET provider.
• Syncfusion Blazor Pivot Table: Field list, drill-through, and configurable rows, columns, and values for multidimensional analysis.
• Data Binding Support: The sample uses Syncfusion's Blazor data-binding capabilities to connect the Pivot Table to the project data flow.
• Interactive Pivot Analysis: Explore aggregated values and inspect underlying records from the drill-through experience.
• Primary Key Configuration: The BeginDrillThrough event marks OrderID as the primary key column so drill-through operations target the correct record.
• Configurable Development Port: Server port managed via Properties/launchSettings.json.

Repository

Sample Location / Source Code: https://github.com/SyncfusionExamples/syncfusion-blazor-pivot-table-postgresql-database-binding-sample

Prerequisites

Component	Version	Purpose
Visual Studio 2022 / VS Code	Latest	Development IDE with Blazor workload
.NET SDK	net10.0 or compatible	Runtime and build tools
PostgreSQL	13 or later	Relational database server
Npgsql NuGet package	Latest	PostgreSQL ADO.NET data provider
Syncfusion.Blazor.PivotTable	Latest	Pivot Table UI component
Syncfusion.Blazor.Themes	Latest	Styling for the Pivot Table component
Syncfusion.Blazor.Data	Latest	Data adaptors including URL Adaptor support
Newtonsoft.Json	Latest	JSON serialization for CRUD models

Note: Install the latest version of the required Syncfusion Blazor NuGet packages and the latest available version of Npgsql from NuGet. Do not pin to a specific patch version.

PostgreSQL Setup

1. Start the PostgreSQL Server

Ensure the PostgreSQL service is running on your machine. The sample expects the server to be reachable at:

Host=localhost
Port=5432

2. Create the Database

Connect to PostgreSQL using psql, pgAdmin, or any SQL client and create the database:

CREATE DATABASE "OrderDB";

3. Create the Table

Switch to the OrderDB database and create the orders table used by the sample:

CREATE TABLE public.orders
(
    orderid        SERIAL PRIMARY KEY,
    customername   VARCHAR(100),
    employeeid     INTEGER,
    shipcity       VARCHAR(100),
    freight        NUMERIC(10, 2)
);

4. Insert Sample Data (Optional)

Seed the table with a few rows so the Pivot Table has data to render on first load:

INSERT INTO public.orders (customername, employeeid, shipcity, freight) VALUES
('Toms',   1, 'New York',     35.30),
('Ravi',   2, 'London',       80.20),
('Sven',   1, 'Berlin',       52.10),
('Sara',   3, 'Madrid',       18.40),
('Paul',   2, 'Tokyo',        64.75);

5. Configure the Connection String

The connection string is defined in Controllers/OrderController.cs:

string ConnectionString = @"Host=localhost;Port=5432;Database=yourDB;Username=postgres;Password=password@123;";

Update the following values to match your local PostgreSQL installation:

Property	Default	Description
Host	localhost	PostgreSQL server host
Port	5432	PostgreSQL server port
Database	yourDB	Target database name
Username	postgres	Database user with read/write privileges
Password	password@123	Password for the database user

Project Structure

File/Folder	Purpose
/Models	Contains the Order data model representing an order record
/Data	Data access logic for interacting with PostgreSQL
/Components/Pages/Home.razor	Pivot Table page with data binding, cell editing, and drill-through setup
/Components/App.razor	Root component that references Syncfusion stylesheet and syncfusion-blazor.min.js
/Program.cs	Service registration for ASP.NET Core and Syncfusion Blazor
/Properties/launchSettings.json	Port configuration for the local development server
/URLAdaptor.csproj	Project file with Syncfusion Blazor packages and Npgsql NuGet references

Application Flow

The sample follows a straightforward data access flow:

PostgreSQL Database  (public.orders table)
        ↓
Sample data access logic
        ↓
Syncfusion Blazor Pivot Table

1. The sample reads the order rows from the PostgreSQL orders table.
2. The resulting data is loaded into the Pivot Table for aggregation and analysis.
3. The Pivot Table renders the grouped results based on the configured fields.

Configuration Steps

Database Configuration

Create the OrderDB database and the orders table as described in the PostgreSQL Setup section, then verify connectivity with psql or pgAdmin.

Connection String Setup

Update the ConnectionString field at the top of OrderController.cs to match your PostgreSQL credentials:

string ConnectionString = @"Host=localhost;Port=5432;Database=OrderDB;Username=postgres;Password=<your-password>;";

Dependency Injection Registration

Program.cs registers the services required by the sample:

builder.Services.AddSyncfusionBlazor();
builder.Services.AddRazorComponents()
    .AddInteractiveServerComponents();

The project uses the standard Blazor service registration pattern for the Pivot Table and its supporting components.

Getting Started / Quick Start

1. Clone the repository

   git clone https://github.com/SyncfusionExamples/syncfusion-blazor-pivot-table-postgresql-database-binding-sample
   cd "syncfusion-blazor-pivot-table-postgresql-database-binding-sample"
   cd "URLAdaptor"

   Repository: https://github.com/SyncfusionExamples/syncfusion-blazor-pivot-table-postgresql-database-binding-sample

2. Restore packages and build

   From the repository root, change into the Blazor project folder and restore the dependencies:

   cd URLAdaptor
   dotnet restore
   dotnet build

3. Start PostgreSQL and seed the database

   Ensure PostgreSQL is running and that the OrderDB database and orders table exist (see PostgreSQL Setup).

4. Run the application

   From inside the URLAdaptor project folder, start the application with dotnet run:

   cd URLAdaptor
   dotnet run

5. Open the application

   Navigate to the local URL displayed in the terminal (typically https://localhost:7169 or http://localhost:5145).

CRUD Operations

The sample is intended for interactive pivot analysis, including drill-through and editing experiences handled by the Syncfusion Blazor Pivot Table and its data-binding setup.

Drill-Through to Source Records

1. Double-click an aggregated value cell in the pivot view.
2. A drill-through grid opens showing the underlying PostgreSQL records.
3. The BeginDrillThrough event handler marks the OrderID column as IsPrimaryKey = true so that operations from the drill-through grid target the correct record.
4. Close the drill-through window to return to the pivot view.

Data Model

The Order model represents a single row in the PostgreSQL orders table:

public class Order
{
    [Key]
    public int? OrderID { get; set; }
    public string? CustomerName { get; set; }
    public int? EmployeeID { get; set; }
    public decimal? Freight { get; set; }
    public string? ShipCity { get; set; }
}

Field	Type	Pivot Role	Description
OrderID	int	Primary Key	Unique identifier for the order
CustomerName	string	Row	Customer name used as a pivot row field
EmployeeID	int	Column	Employee ID used as a pivot column field
Freight	decimal	Value	Freight amount used as the pivot value (aggregated measure)
ShipCity	string	Dimension	Ship city used as an additional pivot field

Static Assets

Components/App.razor references the Syncfusion stylesheet and script bundle:

<link href="_content/Syncfusion.Blazor.Themes/bootstrap5.css" rel="stylesheet" />
<script src="_content/Syncfusion.Blazor.Core/scripts/syncfusion-blazor.min.js" type="text/javascript"></script>

If CSS or scripts fail to load, check the browser developer tools (F12) for 404 errors on _content/Syncfusion.* paths.

Troubleshooting

Connection Error

• Verify Host, Port, Database, Username, and Password in OrderController.cs match your PostgreSQL installation.
• Confirm the PostgreSQL service is running and reachable on localhost:5432:
  • Windows: Get-Service postgresql* and Start-Service postgresql-x64-<version>
  • macOS (Homebrew): brew services start postgresql
  • Linux: sudo systemctl start postgresql
• Test the connection from psql using the same credentials:

  psql -h localhost -p 5432 -U postgres -d OrderDB

• Ensure the database user has read/write privileges on the public.orders table.

Application Not Starting

• Confirm the development port in Properties/launchSettings.json is available.
• If needed, change the port numbers in launchSettings.json and restart the application.

Full Documentation

For step-by-step guidance on integrating PostgreSQL with the Syncfusion Blazor Pivot Table, including UrlAdaptor configuration, CRUD operations, and database connectivity, see the official PostgreSQL database binding documentation.