PostgreSQL in Blazor Pivot Table

4 Aug 202624 minutes to read

The Blazor Pivot Table can bind to PostgreSQL data exposed by an ASP.NET Core controller. In this example, SfDataManager uses Adaptors.UrlAdaptor to read and modify order records through HTTP endpoints.

This sample intentionally loads the complete orders table so that the Pivot Table can aggregate the raw records in the application. Use it only for small datasets. For large datasets, use a server-side aggregation design or an OLAP/data-engine solution instead of returning every source row.

Prerequisites

Install or obtain the following:

Software or package Tested version Purpose
Visual Studio 2026 (18.0 or later) IDE with the ASP.NET and web development workload
.NET SDK 10.0 Runtime and build tools
PostgreSQL Server 12 or later Database server
pgAdmin 4 9.6 or later Optional PostgreSQL administration UI
Syncfusion.Blazor.PivotTable 34.2.2 Pivot Table and data components
Syncfusion.Blazor.Themes 34.2.2 Component themes
Npgsql 10.0.3 PostgreSQL provider for .NET

You also need:

Create the Blazor Web App

Create a Blazor Web App named URLAdaptor that targets .NET 10. Select Interactive Server interactivity and enable HTTPS.

You can also create it from a terminal:

dotnet new blazor -n URLAdaptor -f net10.0 -int Server
cd URLAdaptor

The remaining paths in this guide are relative to the URLAdaptor project directory.

Create the PostgreSQL Database

Start PostgreSQL, open pgAdmin or psql, and connect as a role that can create databases.

Run this statement while connected to a maintenance database such as postgres:

CREATE DATABASE "OrderDB";

Reconnect to OrderDB, then create the table and sample data:

CREATE TABLE public.orders (
    orderid INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customername VARCHAR(100) NOT NULL,
    employeeid INTEGER NOT NULL,
    shipcity VARCHAR(100),
    freight NUMERIC(12, 2)
);

INSERT INTO public.orders (customername, employeeid, shipcity, freight)
VALUES
('Alice Johnson', 1, 'New York', 120.50),
('Bob Smith', 2, 'London', 85.20),
('Carol Davis', 1, 'New York', 210.75),
('David Brown', 3, 'Berlin', 95.00),
('Eve Wilson', 2, 'London', 150.25),
('Frank Moore', 4, 'Tokyo', 60.80),
('Grace Taylor', 1, 'New York', 180.40),
('Henry Anderson', 3, 'Berlin', 220.60),
('Ivy Thomas', 2, 'London', 75.10),
('Jack White', 4, 'Tokyo', 130.90);

SELECT * FROM public.orders ORDER BY orderid;

For local development, you can use the role that created the database. For a separate application role, have a database administrator run the following commands in OrderDB, replacing url_adaptor_app and the example password:

CREATE ROLE url_adaptor_app LOGIN PASSWORD 'replace-with-a-strong-password';
GRANT CONNECT ON DATABASE "OrderDB" TO url_adaptor_app;
GRANT USAGE ON SCHEMA public TO url_adaptor_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.orders TO url_adaptor_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO url_adaptor_app;

The sequence grant supports PostgreSQL installations or schemas that use sequence-backed identity columns.

Install the NuGet Packages

Run the following commands in the project directory.

dotnet add package Syncfusion.Blazor.PivotTable --version 34.2.2
dotnet add package Syncfusion.Blazor.Themes --version 34.2.2
dotnet add package Npgsql --version 10.0.3

The project file should contain equivalent package references:

<ItemGroup>
  <PackageReference Include="Syncfusion.Blazor.PivotTable" Version="34.2.2" />
  <PackageReference Include="Syncfusion.Blazor.Themes" Version="34.2.2" />
  <PackageReference Include="Npgsql" Version="10.0.3" />
</ItemGroup>

Configure Secrets and the Connection String

Do not store a database password or license key in appsettings.json. Initialize Secret Manager and add both values:

dotnet user-secrets init
dotnet user-secrets set "ConnectionStrings:PostgreSQL" "Host=localhost;Port=5432;Database=OrderDB;Username=url_adaptor_app;Password=replace-with-your-password"
dotnet user-secrets set "Syncfusion:LicenseKey" "YOUR LICENSE KEY"

If you use the local postgres role instead, change Username and Password accordingly.

For deployment, provide these settings through a secrets manager or environment variables:

ConnectionStrings__PostgreSQL
Syncfusion__LicenseKey

See the Npgsql connection-string reference for SSL, pooling, timeout, and certificate options required by your PostgreSQL environment.

Create the Shared Order Model

Create Models/Order.cs:

using System.ComponentModel.DataAnnotations;

namespace URLAdaptor.Models;

public class Order
{
    [Key]
    public int? OrderID { get; set; }

    [Required]
    public string? CustomerName { get; set; }

    [Required]
    public int? EmployeeID { get; set; }

    public decimal? Freight { get; set; }
    public string? ShipCity { get; set; }
}

The nullable key allows an inserted record to omit OrderID; PostgreSQL generates it and the API returns it after insertion.

Create the API Controller

Create the Controllers folder and add Controllers/OrderController.cs:

using System.ComponentModel.DataAnnotations;
using System.Text.Json.Serialization;
using Microsoft.AspNetCore.Mvc;
using Npgsql;
using NpgsqlTypes;
using Syncfusion.Blazor.Data;
using URLAdaptor.Models;

namespace URLAdaptor.Controllers;

[ApiController]
[Route("api/[controller]")]
public class OrderController : ControllerBase
{
    private readonly string connectionString;
    private readonly ILogger<OrderController> logger;

    public OrderController(
        IConfiguration configuration,
        ILogger<OrderController> logger)
    {
        connectionString = configuration.GetConnectionString("PostgreSQL")
            ?? throw new InvalidOperationException(
                "The PostgreSQL connection string is not configured.");
        this.logger = logger;
    }

    [HttpPost]
    public async Task<ActionResult<object>> Read(
        [FromBody] DataManagerRequest request,
        CancellationToken cancellationToken)
    {
        _ = request;
        List<Order> orders = [];

        const string sql = """
            SELECT orderid, customername, employeeid, shipcity, freight
            FROM public.orders
            ORDER BY orderid
            """;

        try
        {
            await using NpgsqlConnection connection = new(connectionString);
            await connection.OpenAsync(cancellationToken);
            await using NpgsqlCommand command = new(sql, connection);
            await using NpgsqlDataReader reader =
                await command.ExecuteReaderAsync(cancellationToken);

            while (await reader.ReadAsync(cancellationToken))
            {
                orders.Add(new Order
                {
                    OrderID = reader.GetInt32(0),
                    CustomerName = reader.GetString(1),
                    EmployeeID = reader.GetInt32(2),
                    ShipCity = reader.IsDBNull(3) ? null : reader.GetString(3),
                    Freight = reader.IsDBNull(4) ? null : reader.GetDecimal(4)
                });
            }

            return Ok(new { result = orders, count = orders.Count });
        }
        catch (NpgsqlException exception)
        {
            logger.LogError(exception, "Unable to read orders from PostgreSQL.");
            return Problem("The order data could not be loaded.");
        }
    }

    [HttpPost("Insert")]
    public async Task<ActionResult<Order>> Insert(
        [FromBody] CrudModel<Order> request,
        CancellationToken cancellationToken)
    {
        if (request.Value is not Order order ||
            string.IsNullOrWhiteSpace(order.CustomerName) ||
            !order.EmployeeID.HasValue)
        {
            return BadRequest("CustomerName and EmployeeID are required.");
        }

        const string sql = """
            INSERT INTO public.orders
                (customername, freight, shipcity, employeeid)
            VALUES
                ($1, $2, $3, $4)
            RETURNING orderid
            """;

        try
        {
            await using NpgsqlConnection connection = new(connectionString);
            await connection.OpenAsync(cancellationToken);
            await using NpgsqlCommand command = new(sql, connection);
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Varchar, Value = order.CustomerName });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Numeric, Value = order.Freight ?? (object)DBNull.Value });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Varchar, Value = order.ShipCity ?? (object)DBNull.Value });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Integer, Value = order.EmployeeID.Value });

            object? generatedKey = await command.ExecuteScalarAsync(cancellationToken);
            order.OrderID = Convert.ToInt32(generatedKey);
            return Ok(order);
        }
        catch (PostgresException exception)
            when (exception.SqlState == PostgresErrorCodes.CheckViolation ||
                  exception.SqlState == PostgresErrorCodes.NotNullViolation ||
                  exception.SqlState == PostgresErrorCodes.ForeignKeyViolation)
        {
            logger.LogWarning(exception, "PostgreSQL rejected an inserted order.");
            return BadRequest("The order violates a database constraint.");
        }
        catch (NpgsqlException exception)
        {
            logger.LogError(exception, "Unable to insert an order.");
            return Problem("The order could not be inserted.");
        }
    }

    [HttpPost("Update")]
    public async Task<ActionResult<Order>> Update(
        [FromBody] CrudModel<Order> request,
        CancellationToken cancellationToken)
    {
        if (request.Value is not Order order ||
            !order.OrderID.HasValue ||
            string.IsNullOrWhiteSpace(order.CustomerName) ||
            !order.EmployeeID.HasValue)
        {
            return BadRequest(
                "OrderID, CustomerName, and EmployeeID are required.");
        }

        const string sql = """
            UPDATE public.orders
            SET customername = $1,
                freight = $2,
                employeeid = $3,
                shipcity = $4
            WHERE orderid = $5
            """;

        try
        {
            await using NpgsqlConnection connection = new(connectionString);
            await connection.OpenAsync(cancellationToken);
            await using NpgsqlCommand command = new(sql, connection);
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Varchar, Value = order.CustomerName });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Numeric, Value = order.Freight ?? (object)DBNull.Value });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Integer, Value = order.EmployeeID.Value });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Varchar, Value = order.ShipCity ?? (object)DBNull.Value });
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Integer, Value = order.OrderID.Value });

            int affectedRows = await command.ExecuteNonQueryAsync(cancellationToken);
            return affectedRows == 0 ? NotFound() : Ok(order);
        }
        catch (PostgresException exception)
            when (exception.SqlState == PostgresErrorCodes.CheckViolation ||
                  exception.SqlState == PostgresErrorCodes.NotNullViolation ||
                  exception.SqlState == PostgresErrorCodes.ForeignKeyViolation)
        {
            logger.LogWarning(exception, "PostgreSQL rejected an updated order.");
            return BadRequest("The order violates a database constraint.");
        }
        catch (NpgsqlException exception)
        {
            logger.LogError(exception, "Unable to update order {OrderID}.", order.OrderID);
            return Problem("The order could not be updated.");
        }
    }

    [HttpPost("Delete")]
    public async Task<ActionResult<object>> Delete(
        [FromBody] CrudModel<Order> request,
        CancellationToken cancellationToken)
    {
        if (!int.TryParse(request.Key?.ToString(), out int orderID))
        {
            return BadRequest("A numeric order key is required.");
        }

        const string sql =
            "DELETE FROM public.orders WHERE orderid = $1";

        try
        {
            await using NpgsqlConnection connection = new(connectionString);
            await connection.OpenAsync(cancellationToken);
            await using NpgsqlCommand command = new(sql, connection);
            command.Parameters.Add(new NpgsqlParameter
                { NpgsqlDbType = NpgsqlDbType.Integer, Value = orderID });

            int affectedRows = await command.ExecuteNonQueryAsync(cancellationToken);
            return affectedRows == 0
                ? NotFound()
                : Ok(new { key = orderID });
        }
        catch (NpgsqlException exception)
        {
            logger.LogError(exception, "Unable to delete order {OrderID}.", orderID);
            return Problem("The order could not be deleted.");
        }
    }
}

public class CrudModel<T> where T : class
{
    [JsonPropertyName("action")]
    public string? Action { get; set; }

    [JsonPropertyName("keyColumn")]
    public string? KeyColumn { get; set; }

    [JsonPropertyName("key")]
    public object? Key { get; set; }

    [JsonPropertyName("value")]
    public T? Value { get; set; }

    [JsonPropertyName("added")]
    public List<T>? Added { get; set; }

    [JsonPropertyName("changed")]
    public List<T>? Changed { get; set; }

    [JsonPropertyName("deleted")]
    public List<T>? Deleted { get; set; }

    [JsonPropertyName("params")]
    public IDictionary<string, object>? Params { get; set; }
}

The URL Adaptor uses value for insert and update records and key for the delete identifier. The remaining CrudModel<T> properties support the complete DataManager request shape even though this sample does not use batch operations.

The controller returns:

Endpoint Success Client error Missing row Database error
POST /api/Order 200 with { result, count } 400 for an invalid body Not applicable 500 problem response
POST /api/Order/Insert 200 with the inserted record and generated key 400 Not applicable 500 problem response
POST /api/Order/Update 200 with the updated record 400 404 500 problem response
POST /api/Order/Delete 200 with the deleted key 400 404 500 problem response

Register Services and Endpoints

Replace Program.cs with:

using Syncfusion.Blazor;
using URLAdaptor.Components;

var builder = WebApplication.CreateBuilder(args);

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

var app = builder.Build();

string licenseKey = builder.Configuration["Syncfusion:LicenseKey"]
    ?? throw new InvalidOperationException(
        "The Syncfusion license key is not configured.");
Syncfusion.Licensing.SyncfusionLicenseProvider.RegisterLicense(licenseKey);

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error", createScopeForErrors: true);
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseAntiforgery();
app.MapStaticAssets();
app.MapControllers();
app.MapRazorComponents<App>()
    .AddInteractiveServerRenderMode();

app.Run();

The controller actions do not opt into antiforgery validation. If you add an antiforgery policy to these cookie-authenticated endpoints, configure SfDataManager to send the matching request token. For a public deployment, also add authentication, authorization, rate limiting, and an appropriate production logging policy.

Configure Imports and Static Assets

Add these namespaces to Components/_Imports.razor:

@using Syncfusion.Blazor
@using Syncfusion.Blazor.Data
@using Syncfusion.Blazor.PivotView
@using URLAdaptor.Models

The .NET 10 Blazor Web App template already includes _framework/blazor.web.js in Components/App.razor; do not add it a second time.

Add the Syncfusion theme in the <head> element:

<link rel="stylesheet"
      href="@Assets["_content/Syncfusion.Blazor.Themes/bootstrap5.3.css"]" />

Add the Syncfusion script before the closing </body> tag:

<script src="@Assets["_content/Syncfusion.Blazor.Core/scripts/syncfusion-blazor.min.js"]"></script>

Keep the existing framework script:

<script src="@Assets["_framework/blazor.web.js"]"></script>

See Blazor component themes for other supported themes.

Configure the Pivot Table

Replace Components/Pages/Home.razor with:

@page "/"

<SfPivotView TValue="Order"
             Width="1000"
             Height="300"
             ShowFieldList="true">
    <PivotViewDataSourceSettings TValue="Order"
                                 ExpandAll="false"
                                 EnableSorting="true">
        <SfDataManager Url="/api/Order"
                       InsertUrl="/api/Order/Insert"
                       UpdateUrl="/api/Order/Update"
                       RemoveUrl="/api/Order/Delete"
                       Adaptor="Adaptors.UrlAdaptor">
        </SfDataManager>
        <PivotViewColumns>
            <PivotViewColumn Name="EmployeeID" />
        </PivotViewColumns>
        <PivotViewRows>
            <PivotViewRow Name="CustomerName" />
        </PivotViewRows>
        <PivotViewValues>
            <PivotViewValue Name="Freight" Caption="Freight" />
        </PivotViewValues>
    </PivotViewDataSourceSettings>
    <PivotViewGridSettings ColumnWidth="120" />
    <PivotViewCellEditSettings AllowEditing="true"
                               AllowAdding="true"
                               AllowDeleting="true"
                               Mode="EditMode.Normal" />
    <PivotViewEvents TValue="Order"
                     BeginDrillThrough="BeginDrillThrough" />
</SfPivotView>

@code {
    private static void BeginDrillThrough(
        BeginDrillThroughEventArgs args)
    {
        foreach (var column in args.GridObj.Columns)
        {
            column.Visible = true;
            column.IsPrimaryKey = column.Field == nameof(Order.OrderID);
        }
    }
}

BeginDrillThrough marks OrderID as the edit grid’s primary key. The URL Adaptor sends the edited record, including OrderID, to the update endpoint and sends the primary-key value in key to the delete endpoint.

The editing APIs shown here are tested with the Syncfusion version listed in the prerequisites. See editing in the Blazor Pivot Table and the BeginDrillThroughEventArgs API when using another package version.

Build and Verify the Application

Confirm that PostgreSQL is running and that the configured role can read and modify public.orders. Then restore, build, and run the application:

dotnet restore
dotnet build
dotnet run

Open the HTTPS URL shown in the terminal. The Pivot Table should display CustomerName as rows, EmployeeID as columns, and the sum of Freight as values.

Before testing the UI, you can verify the read endpoint with PowerShell. Replace the URL with the HTTPS address printed by dotnet run:

Invoke-RestMethod `
  -Method Post `
  -Uri "https://localhost:7001/api/Order" `
  -ContentType "application/json" `
  -Body "{}"

A browser address-bar request sends GET, so it is not a valid test for this POST endpoint.

Test CRUD Operations

Double-click an aggregated value cell to open its underlying records.

  • To insert, select Add, enter CustomerName, EmployeeID, and optional values, then save. PostgreSQL generates OrderID, and the insert response returns it.
  • To update, select a row, select Edit, change a value, and save. The edited record supplies OrderID.
  • To delete, select a row, select Delete, and confirm. The edit grid supplies OrderID as the request key.

After each operation, the Pivot Table refreshes its aggregated values. If it does not, inspect the write response in the browser Network panel and confirm that it returned a success status and the documented JSON body.

Representative payloads are:

{
  "action": "insert",
  "keyColumn": "OrderID",
  "value": {
    "customerName": "Karen Lee",
    "employeeID": 3,
    "shipCity": "Berlin",
    "freight": 110.00
  }
}
{
  "action": "update",
  "keyColumn": "OrderID",
  "value": {
    "orderID": 1,
    "customerName": "Alice Johnson",
    "employeeID": 1,
    "shipCity": "Boston",
    "freight": 125.00
  }
}
{
  "action": "remove",
  "keyColumn": "OrderID",
  "key": 1
}

Troubleshooting

Symptom Likely cause Resolution
Pivot Table shows no data The API returned an error or is unreachable Inspect the browser Network panel and send a POST request with {} to /api/Order.
405 Method Not Allowed The endpoint was tested with GET Use POST and confirm AddControllers() and MapControllers() are present.
NpgsqlException or connection refused PostgreSQL is stopped or the connection string is wrong Start PostgreSQL and verify the host, port, database, credentials, SSL settings, and role permissions.
relation "orders" does not exist The script ran in the wrong database or schema Run the table script in OrderDB and confirm public.orders exists.
permission denied for table orders The application role lacks table privileges Grant SELECT, INSERT, UPDATE, and DELETE on public.orders.
Insert fails while reads work The role lacks identity-sequence privileges Grant USAGE and SELECT on sequences in the public schema.
Insert or update returns 400 Required values are missing or a constraint rejected the row Inspect the response and server log; supply CustomerName and EmployeeID.
Update or delete returns 404 The supplied OrderID no longer exists Refresh the Pivot Table and retry with a current record.
Editing sends no usable key The edit grid did not mark OrderID as its primary key Confirm the shared model has [Key] and the BeginDrillThrough handler runs.
CORS error The API and Blazor app are on different origins Keep the relative URLs or configure a restricted CORS policy for the Blazor origin.
Antiforgery validation fails A policy requires a token that the adaptor does not send Configure the client and server to exchange the token, or use an appropriate non-cookie API authentication design.
Aggregates are incorrect Freight has an incompatible type Keep PostgreSQL freight numeric and the .NET property decimal?.

Complete Sample Repository

A complete, working sample implementation is available in the GitHub repository.

Summary

This guide provides a small-dataset URL Adaptor implementation with parameterized SQL, generated-key handling, validation, logging, and CRUD endpoints. Use a server-side aggregation architecture for production-scale datasets.