ASP.NET Core Blazor Server Application: Creating Prompt Based Chart Generator for Northwind Database

In this article we will implement a NorthwindReporter, an ASP.NET Core 10 Blazor Server application that lets an end user type a natural-language reporting request (for example, “Regionwise sales” or “Top 5 products by revenue”), pick a chart type, and get back:

• A Chart.js visualization rendered in the browser canvas.
• The raw rows of the result in an HTML data table.
• The actual SQL the LLM produced, so it is transparent.

The reporting engine uses a local Ollama install with the llama3.2 model to translate the English prompt into a single read-only SELECT, a safety validator to make sure that is all it is, and a repository that runs it against a SQL Server “Northwind” database.

Workflow Diagram

The picture below summarizes how a request flows through the components. Four numbered arrows mark the main hops of one Generate click — each is explained right after the picture.

The four numbered steps in the diagram

1. Prompt + Chart Type — the End User fills the prompt textarea (for example “Regionwise sales”) and picks a chart type from the dropdown in the Blazor Browser UI. When the user clicks Generate, the values are sent over the SignalR Circuit to the server-rendered Reporter.razor component. The component then calls ChartReportService.BuildAsync(prompt, chartType), which is the single entry point into the backend pipeline.
2. Ask LLM for SQL — ChartReportService (the orchestrator) delegates first to OllamaService. OllamaService issues a POST /api/generate HTTP request to the locally-running Ollama server at http://localhost:11434, asking the llama3.2 model — primed with the Northwind schema and four worked examples — to return one read-only T-SQL SELECT as Raw SQL Text.
3. Allow only SELECT — validate the statement — the SQL string returned by the LLM is never trusted blindly. The orchestrator hands it to SqlSafetyValidator, which confirms the query starts with SELECT (or WITH), is a single statement, and contains none of the forbidden keywords (INSERT, UPDATE, DELETE, DROP, ALTER, EXEC, sp_, xp_, INTO, OPENROWSET, …). If validation fails the pipeline is short-circuited and an error ChartReport is returned to the page.
4. Execute / Run the Query — once validated, the SQL is sent to NorthwindRepository, which opens a SqlConnection, executes the SQL Command against the local SQL Server Northwind database, reads back the Result Rows (capped by ChartGenerator:MaxRows), and returns them as a QueryResult. The orchestrator then builds the final ChartReport (labels, values, columns, rows, the generated SQL) and hands it back to Reporter.razor. The component re-renders the HTML data table directly via Razor @foreach markup, and invokes Chart.js through JS Interop (chartInterop.render) to draw the canvas in the browser.

Table of Contents

1. Project structure
2. Project file & NuGet packages
3. appsettings.json
4. Program.cs (full walkthrough)
5. Services folder — all classes
   1. OllamaOptions.cs / ChartGeneratorOptions.cs
   2. IOllamaService.cs
   3. OllamaService.cs
   4. SqlSafetyValidator.cs
   5. INorthwindRepository.cs + QueryResult
   6. NorthwindRepository.cs
   7. ChartReportService.cs + ChartReport
6. Components/Pages/Reporter.razor
7. Chart.js — how it is used
8. wwwroot/js/chartInterop.js (JS interop)
9. Changes to the standard Blazor template
   1. Components/App.razor
   2. Components/Layout/NavMenu.razor
   3. Removed sample pages
10. How to run

1. Project structure

NorthwindReporter/
├── Components/
│   ├── App.razor               (modified — added Chart.js + interop scripts)
│   ├── Routes.razor            (unchanged)
│   ├── _Imports.razor          (unchanged)
│   ├── Layout/
│   │   ├── MainLayout.razor    (unchanged)
│   │   └── NavMenu.razor       (modified — single "Reporter" link)
│   └── Pages/
│       ├── Reporter.razor      (NEW — the only page in the app)
│       ├── Error.razor         (template default)
│       └── NotFound.razor      (template default)
├── Services/                   (NEW — all C# logic)
│   ├── OllamaOptions.cs
│   ├── IOllamaService.cs
│   ├── OllamaService.cs
│   ├── SqlSafetyValidator.cs
│   ├── INorthwindRepository.cs
│   ├── NorthwindRepository.cs
│   └── ChartReportService.cs
├── wwwroot/
│   └── js/
│       └── chartInterop.js     (NEW — Chart.js wrapper)
├── appsettings.json            (added ConnectionStrings, Ollama, ChartGenerator sections)
├── Program.cs                  (rewritten — service registrations)
└── image.png                   (workflow diagram)

The sample pages from the template (Counter.razor, Weather.razor, Home.razor) were deleted — the single Reporter.razor page is mapped to both / and /reporter.

2. Project file & NuGet packages

The project targets net10.0 and adds a single package on top of the Blazor template: Microsoft.Data.SqlClient, used by NorthwindRepository to talk to SQL Server.

<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="7.0.1" />
  </ItemGroup>
</Project>

3. appsettings.json

Three custom sections were added: ConnectionStrings, Ollama, and ChartGenerator.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Northwind": "Server=localhost;Database=nw;Trusted_Connection=True;TrustServerCertificate=True;Encrypt=False;"
  },
  "Ollama": {
    "BaseUrl": "http://localhost:11434",
    "Model": "llama3.2",
    "TimeoutSeconds": 120
  },
  "ChartGenerator": {
    "MaxRows": 500
  }
}

Section	Purpose
ConnectionStrings:Northwind	SQL Server connection used by NorthwindRepository. Default targets a local SQL Server, database nw, Windows authentication.
Ollama:BaseUrl	Where the local Ollama service is listening (default port 11434).
Ollama:Model	Which model to call. The app uses llama3.2; switch to any other Ollama-hosted model without code changes.
Ollama:TimeoutSeconds	HTTP client timeout for the LLM call.
ChartGenerator:MaxRows	Hard cap on how many rows the repository reads back, no matter what the LLM produced.

4. Program.cs — full walkthrough

Program.cs is the application entry point. It builds the WebApplication, wires up dependency injection, configures the HTTP pipeline, and starts listening.

using NorthwindReporter.Components;
using NorthwindReporter.Services;

var builder = WebApplication.CreateBuilder(args);

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

builder.Services.Configure<OllamaOptions>(builder.Configuration.GetSection("Ollama"));
builder.Services.Configure<ChartGeneratorOptions>(builder.Configuration.GetSection("ChartGenerator"));

builder.Services.AddHttpClient<IOllamaService, OllamaService>();
builder.Services.AddSingleton<ISqlSafetyValidator, SqlSafetyValidator>();
builder.Services.AddSingleton<INorthwindRepository, NorthwindRepository>();
builder.Services.AddScoped<IChartReportService, ChartReportService>();

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error", createScopeForErrors: true);
    app.UseHsts();
}
app.UseStatusCodePagesWithReExecute("/not-found", createScopeForStatusCodePages: true);
app.UseHttpsRedirection();

app.UseAntiforgery();

app.MapStaticAssets();
app.MapRazorComponents<App>()
    .AddInteractiveServerRenderMode();

app.Run();

Line-by-line

• WebApplication.CreateBuilder(args) — creates the host builder, reads appsettings.json, environment variables and command-line args.
• AddRazorComponents().AddInteractiveServerComponents() — registers Blazor Server services (the SignalR circuit infrastructure that powers @rendermode InteractiveServer).
• Configure<OllamaOptions>(...) and Configure<ChartGeneratorOptions>(...) — bind the Ollama and ChartGenerator sections of appsettings.json to the option classes so they can be injected via IOptions<T>.
• AddHttpClient<IOllamaService, OllamaService>() — registers a typed HttpClient for OllamaService. The framework pools the client correctly for us.
• AddSingleton<ISqlSafetyValidator, ...> — the validator is stateless, one instance is enough.
• AddSingleton<INorthwindRepository, ...> — the repository only holds the connection string; the actual SqlConnection is created per call.
• AddScoped<IChartReportService, ...> — scoped to the Blazor circuit (one per user session), which lines up with how Blazor Server resolves services for components.
• UseExceptionHandler / UseHsts — production safety defaults.
• UseStatusCodePagesWithReExecute("/not-found", ...) — pretty 404 page from the template.
• UseHttpsRedirection / UseAntiforgery — standard middleware.
• MapStaticAssets() — serves files from wwwroot (Bootstrap CSS, our chartInterop.js, etc.) with built-in fingerprinting.
• MapRazorComponents<App>().AddInteractiveServerRenderMode() — mounts the root component App.razor and enables Server interactivity, so any component with @rendermode InteractiveServer gets a SignalR circuit.
• app.Run() — starts Kestrel and listens.

5. All classes from the Services folder  

5.1 OllamaOptions.cs & ChartGeneratorOptions.cs

Simple POCOs bound to configuration. Both live in the same file.

Services/OllamaOptions.cs

namespace NorthwindReporter.Services;

public class OllamaOptions
{
    public string BaseUrl { get; set; } = "http://localhost:11434";
    public string Model { get; set; } = "llama3.2";
    public int TimeoutSeconds { get; set; } = 120;
}

public class ChartGeneratorOptions
{
    public int MaxRows { get; set; } = 500;
}

5.2 IOllamaService.cs

The contract used by callers — exactly one method, takes a user prompt and asks the LLM to come back with SQL.

Services/IOllamaService.cs

namespace NorthwindReporter.Services;

public interface IOllamaService
{
    Task<string> GenerateSqlAsync(string userPrompt, CancellationToken ct = default);
}

5.3 OllamaService.cs

The implementation. Three responsibilities:

1. Hold a strict system prompt that teaches the model the Northwind schema and the rules for the SQL it must produce.
2. POST the prompt to Ollama’s /api/generate endpoint.
3. Strip any Markdown / “SQL:” prefix / trailing semicolons the model may have added.

Services/OllamaService.cs

using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Options;

namespace NorthwindReporter.Services;

public class OllamaService : IOllamaService
{
    private readonly HttpClient _http;
    private readonly OllamaOptions _options;
    private readonly ILogger<OllamaService> _log;

    private const string NorthwindSchema = @"
Northwind tables and columns (SQL Server):
- Categories(CategoryID, CategoryName, Description)
- Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
- Employees(EmployeeID, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, ReportsTo)
- Shippers(ShipperID, CompanyName, Phone)
- Suppliers(SupplierID, CompanyName, ContactName, City, Region, Country)
- Products(ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
- Orders(OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
- [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount)   -- NOTE the brackets, table name has a space
- Region(RegionID, RegionDescription)
- Territories(TerritoryID, TerritoryDescription, RegionID)
- EmployeeTerritories(EmployeeID, TerritoryID)

Useful expressions:
- Line total: ([Order Details].UnitPrice * [Order Details].Quantity * (1 - [Order Details].Discount))
- 'Sales agent' = Employees (join Orders.EmployeeID = Employees.EmployeeID), label = FirstName + ' ' + LastName
- 'Region-wise sales' typically uses Orders.ShipRegion or Orders.ShipCountry, OR Employees->EmployeeTerritories->Territories->Region for employee-region
";

    private const string SystemPrompt = @"You are a senior T-SQL author for the Northwind sample database on Azure SQL Server.

You will receive a natural-language reporting request. You MUST return ONE single read-only SELECT query that answers it.

STRICT RULES:
1. Output ONLY the SQL. No prose, no markdown fences, no comments, no semicolons at the end. Just the SELECT.
2. The query MUST start with SELECT (optionally preceded by WITH ... cte AS (...) but ending in a SELECT).
3. NEVER use INSERT, UPDATE, DELETE, MERGE, TRUNCATE, DROP, ALTER, CREATE, EXEC, EXECUTE, sp_, xp_, GRANT, REVOKE, BACKUP, RESTORE.
4. The first column of the SELECT must be the CATEGORY/LABEL (string), the second column must be the NUMERIC VALUE (e.g. total sales, count). Additional columns are allowed for the data table.
5. Use TOP 50 if the request implies a 'top N' or ranking, otherwise cap with TOP 100.
6. Always include ORDER BY on the numeric value DESC unless a time dimension is requested (then order chronologically).
7. Use ROUND(SUM(...), 2) for money so the chart is clean.
8. Remember [Order Details] needs square brackets.
9. Use proper joins, never cross-joins.

EXAMPLES:
... (four worked examples for regionwise sales, sales-agent sales, top-N products, category-wise sales)
";

    public OllamaService(HttpClient http, IOptions<OllamaOptions> options, ILogger<OllamaService> log)
    {
        _http = http;
        _options = options.Value;
        _log = log;
        _http.BaseAddress = new Uri(_options.BaseUrl.TrimEnd('/') + "/");
        _http.Timeout = TimeSpan.FromSeconds(_options.TimeoutSeconds);
    }

    public async Task<string> GenerateSqlAsync(string userPrompt, CancellationToken ct = default)
    {
        var fullPrompt = $"{SystemPrompt}\n\nSCHEMA:\n{NorthwindSchema}\n\nUser request: {userPrompt}\n\nSQL:";

        var payload = new
        {
            model = _options.Model,
            prompt = fullPrompt,
            stream = false,
            options = new { temperature = 0.1, num_ctx = 4096 }
        };

        var body = new StringContent(JsonSerializer.Serialize(payload), Encoding.UTF8, "application/json");
        var resp = await _http.PostAsync("api/generate", body, ct);

        if (!resp.IsSuccessStatusCode)
        {
            var err = await resp.Content.ReadAsStringAsync(ct);
            throw new InvalidOperationException($"Ollama returned {(int)resp.StatusCode}: {err}");
        }

        var json = await resp.Content.ReadAsStringAsync(ct);
        using var doc = JsonDocument.Parse(json);
        var raw = doc.RootElement.GetProperty("response").GetString() ?? string.Empty;

        return CleanSql(raw);
    }

    private static string CleanSql(string raw)
    {
        var s = raw.Trim();
        s = Regex.Replace(s, "^```[a-zA-Z]*\\s*", "", RegexOptions.Multiline);
        s = Regex.Replace(s, "```\\s*$", "", RegexOptions.Multiline);
        s = Regex.Replace(s, "^\\s*SQL\\s*:\\s*", "", RegexOptions.IgnoreCase);
        s = s.TrimEnd().TrimEnd(';').Trim();
        return s;
    }
}

Why a deterministic prompt? 

Temperature is pinned at 0.1 and the system prompt contains four hand-written exemplars. Together that pushes the model toward predictable T-SQL with a guaranteed label-first / numeric-second column layout — exactly what the charting layer expects.

5.4 SqlSafetyValidator.cs

A second line of defense. Even though the LLM is told to only emit SELECT, we never trust the model. Before any SQL is sent to the database, this validator scans it.

Services/SqlSafetyValidator.cs

using System.Text.RegularExpressions;

namespace NorthwindReporter.Services;

public interface ISqlSafetyValidator
{
    void EnsureSafe(string sql);
}

public class SqlSafetyValidator : ISqlSafetyValidator
{
    private static readonly string[] ForbiddenKeywords =
    {
        "insert", "update", "delete", "merge", "truncate", "drop", "alter",
        "create", "exec", "execute", "sp_", "xp_", "grant", "revoke",
        "backup", "restore", "shutdown", "bulk", "openrowset", "opendatasource",
        "into "
    };

    public void EnsureSafe(string sql)
    {
        if (string.IsNullOrWhiteSpace(sql))
            throw new InvalidOperationException("Generated SQL is empty.");

        var stripped = Regex.Replace(sql, "/\\*.*?\\*/", " ", RegexOptions.Singleline);
        stripped = Regex.Replace(stripped, "--.*?$", " ", RegexOptions.Multiline);

        var lower = stripped.ToLowerInvariant().Trim();

        var startsOk = lower.StartsWith("select") || lower.StartsWith("with ");
        if (!startsOk)
            throw new InvalidOperationException("Only SELECT (or WITH ... SELECT) queries are allowed.");

        if (stripped.TrimEnd(';').Contains(';'))
            throw new InvalidOperationException("Multiple SQL statements are not allowed.");

        foreach (var kw in ForbiddenKeywords)
        {
            if (Regex.IsMatch(lower, "\\b" + Regex.Escape(kw.TrimEnd()) + (kw.EndsWith(" ") ? "\\s" : "\\b")))
                throw new InvalidOperationException($"Disallowed keyword in SQL: {kw.Trim()}");
        }
    }
}

What the validator enforces

• The SQL is not empty.
• It starts with SELECT or WITH (after comments are stripped).
• It is a single statement — no semicolons in the middle.
• It contains no DDL/DML keywords (INSERT, UPDATE, DELETE, DROP, …), no stored-proc invocation (EXEC, sp_, xp_), no INTO (blocks SELECT ... INTO some_new_table), and no OPENROWSET / BULK.

5.5 INorthwindRepository.cs + QueryResult record

The contract used by the orchestrator and a simple record to carry rows back.

Services/INorthwindRepository.cs

namespace NorthwindReporter.Services;

public record QueryResult(IReadOnlyList<string> Columns, IReadOnlyList<IReadOnlyList<object?>> Rows);

public interface INorthwindRepository
{
    Task<QueryResult> ExecuteAsync(string sql, int maxRows, CancellationToken ct = default);
}

5.6 NorthwindRepository.cs

The only class that talks to SQL Server. It opens a connection, runs the validated SQL with Microsoft.Data.SqlClient, reads rows up to maxRows, and returns the columns plus the raw values for downstream layers to format.

Services/NorthwindRepository.cs

using Microsoft.Data.SqlClient;

namespace NorthwindReporter.Services;

public class NorthwindRepository : INorthwindRepository
{
    private readonly string _connectionString;

    public NorthwindRepository(IConfiguration config)
    {
        _connectionString = config.GetConnectionString("Northwind")
            ?? throw new InvalidOperationException("ConnectionStrings:Northwind is not configured.");
    }

    public async Task<QueryResult> ExecuteAsync(string sql, int maxRows, CancellationToken ct = default)
    {
        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync(ct);

        await using var cmd = new SqlCommand(sql, conn);
        cmd.CommandTimeout = 60;

        await using var reader = await cmd.ExecuteReaderAsync(ct);

        var columns = new List<string>(reader.FieldCount);
        for (var i = 0; i < reader.FieldCount; i++)
            columns.Add(reader.GetName(i));

        var rows = new List<IReadOnlyList<object?>>();
        while (await reader.ReadAsync(ct) && rows.Count < maxRows)
        {
            var row = new object?[reader.FieldCount];
            for (var i = 0; i < reader.FieldCount; i++)
                row[i] = reader.IsDBNull(i) ? null : reader.GetValue(i);
            rows.Add(row);
        }

        return new QueryResult(columns, rows);
    }
}

5.7 ChartReportService.cs + ChartReport record

This is the orchestrator the Blazor page actually calls. It chains the three other services together and shapes the result into something the UI can render directly.

Services/ChartReportService.cs

using System.Globalization;
using Microsoft.Extensions.Options;

namespace NorthwindReporter.Services;

public record ChartReport(
    bool Success,
    string? Error,
    string Sql,
    string ChartType,
    string Title,
    string ValueColumn,
    IReadOnlyList<string> Labels,
    IReadOnlyList<double> Values,
    IReadOnlyList<string> Columns,
    IReadOnlyList<IReadOnlyList<object?>> Rows);

public interface IChartReportService
{
    Task<ChartReport> BuildAsync(string prompt, string chartType, CancellationToken ct = default);
}

public class ChartReportService : IChartReportService
{
    private static readonly HashSet<string> AllowedChartTypes = new(StringComparer.OrdinalIgnoreCase)
    { "bar", "line", "pie", "doughnut", "polarArea", "radar" };

    private readonly IOllamaService _ollama;
    private readonly ISqlSafetyValidator _validator;
    private readonly INorthwindRepository _repo;
    private readonly ChartGeneratorOptions _options;
    private readonly ILogger<ChartReportService> _log;

    public ChartReportService(
        IOllamaService ollama,
        ISqlSafetyValidator validator,
        INorthwindRepository repo,
        IOptions<ChartGeneratorOptions> options,
        ILogger<ChartReportService> log)
    {
        _ollama = ollama;
        _validator = validator;
        _repo = repo;
        _options = options.Value;
        _log = log;
    }

    public async Task<ChartReport> BuildAsync(string prompt, string chartType, CancellationToken ct = default)
    {
        var resolvedType = AllowedChartTypes.Contains(chartType) ? chartType : "bar";
        resolvedType = resolvedType.Equals("polararea", StringComparison.OrdinalIgnoreCase)
                       ? "polarArea" : resolvedType.ToLowerInvariant();

        if (string.IsNullOrWhiteSpace(prompt))
            return Fail(resolvedType, prompt, "", "Please enter a prompt.");

        string sql;
        try
        {
            sql = await _ollama.GenerateSqlAsync(prompt, ct);
            _validator.EnsureSafe(sql);
        }
        catch (Exception ex)
        {
            _log.LogWarning(ex, "SQL generation/validation failed");
            return Fail(resolvedType, prompt, "", $"Could not produce a safe SQL query: {ex.Message}");
        }

        QueryResult data;
        try
        {
            data = await _repo.ExecuteAsync(sql, _options.MaxRows, ct);
        }
        catch (Exception ex)
        {
            _log.LogError(ex, "SQL execution failed");
            return Fail(resolvedType, prompt, sql, $"Database error: {ex.Message}");
        }

        if (data.Rows.Count == 0)
            return new ChartReport(false, "Query returned no rows.", sql, resolvedType, prompt, "",
                Array.Empty<string>(), Array.Empty<double>(), data.Columns, data.Rows);

        if (data.Columns.Count < 2)
            return new ChartReport(false,
                "Query returned only one column — chart needs at least a label and a numeric value.",
                sql, resolvedType, prompt, "", Array.Empty<string>(), Array.Empty<double>(),
                data.Columns, data.Rows);

        var numericIdx = FindNumericColumn(data);
        if (numericIdx < 0)
            return new ChartReport(false, "No numeric column found in the result; cannot chart.",
                sql, resolvedType, prompt, "", Array.Empty<string>(), Array.Empty<double>(),
                data.Columns, data.Rows);

        var labelIdx = numericIdx == 0 ? 1 : 0;
        var labels = new List<string>(data.Rows.Count);
        var values = new List<double>(data.Rows.Count);
        foreach (var row in data.Rows)
        {
            labels.Add(row[labelIdx]?.ToString() ?? "(null)");
            values.Add(ToDouble(row[numericIdx]));
        }

        return new ChartReport(true, null, sql, resolvedType, prompt, data.Columns[numericIdx],
            labels, values, data.Columns, data.Rows);
    }

    private static ChartReport Fail(string chartType, string title, string sql, string error) =>
        new(false, error, sql, chartType, title, "",
            Array.Empty<string>(), Array.Empty<double>(),
            Array.Empty<string>(), Array.Empty<IReadOnlyList<object?>>());

    private static int FindNumericColumn(QueryResult data)
    {
        for (var col = 0; col < data.Columns.Count; col++)
        {
            foreach (var row in data.Rows)
            {
                var v = row[col];
                if (v is null) continue;
                if (v is byte or sbyte or short or ushort or int or uint or long or ulong
                       or float or double or decimal)
                    return col;
                break;
            }
        }
        return -1;
    }

    private static double ToDouble(object? value) => value switch
    {
        null => 0,
        double d => d,
        float f => f,
        decimal m => (double)m,
        long l => l,
        int i => i,
        short s => s,
        byte b => b,
        _ => double.TryParse(value.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out var d)
              ? d : 0
    };
}

How it shapes the result

• Chart type is constrained to bar, line, pie, doughnut, polarArea, radar. Anything else falls back to bar.
• Empty prompt, Ollama errors, validator errors, and SQL errors are all turned into a ChartReport with Success = false and an Error message — the page just shows that message.
• Auto-detection: FindNumericColumn picks the first column that has at least one numeric value; the label is whichever non-numeric column comes first. That means the LLM does not need to produce columns in any specific order — the orchestrator finds them.
• Labels and Values are flattened into two parallel lists, exactly what Chart.js wants.
• The full Columns/Rows are also returned so the page can show the HTML data table.

6. Components/Pages/Reporter.razor

This is the single page in the app. It is routed at both / and /reporter, and uses @rendermode InteractiveServer, so all event handlers run on the server over the SignalR circuit and Chart.js is invoked through JS interop.

Components/Pages/Reporter.razor

@page "/"
@page "/reporter"
@rendermode InteractiveServer
@using NorthwindReporter.Services
@inject IChartReportService Reports
@inject IJSRuntime JS
@implements IAsyncDisposable

<PageTitle>Chart Status Generator</PageTitle>

<div class="row mb-3">
    <div class="col-12">
        <h2 class="mb-0">Northwind Chart Status Generator</h2>
        <small class="text-muted">Type a request in plain English. Llama 3.2 (local) writes the SQL,
            Azure SQL runs it, Chart.js draws it.</small>
    </div>
</div>

<div class="card mb-3">
    <div class="card-body">
        <div class="row g-3">
            <div class="col-md-8">
                <label for="prompt" class="form-label fw-bold">Your prompt</label>
                <textarea id="prompt" class="form-control" rows="3" @bind="_prompt"
                          placeholder="e.g. Regionwise sales, top 5 products by revenue, ..."></textarea>
                <div class="mt-2">
                    <span class="text-muted small me-2">Try:</span>
                    @foreach (var sample in _samples)
                    {
                        <button type="button" class="btn btn-sm btn-outline-secondary me-1 mb-1"
                                @onclick="() => _prompt = sample">@sample</button>
                    }
                </div>
            </div>
            <div class="col-md-4">
                <label for="chartType" class="form-label fw-bold">Chart type</label>
                <select id="chartType" class="form-select"
                        @bind="_chartType" @bind:after="OnChartTypeChangedAsync">
                    <option value="bar">Bar</option>
                    <option value="line">Line</option>
                    <option value="pie">Pie</option>
                    <option value="doughnut">Doughnut</option>
                    <option value="polarArea">Polar Area</option>
                    <option value="radar">Radar</option>
                </select>
                <button class="btn btn-primary w-100 mt-3" @onclick="GenerateAsync" disabled="@_busy">
                    @if (_busy)
                    {
                        <span class="spinner-border spinner-border-sm me-2"></span>
                    }
                    Generate
                </button>
            </div>
        </div>
    </div>
</div>

@if (_report is not null && !string.IsNullOrEmpty(_report.Error))
{
    <div class="alert alert-danger">@_report.Error</div>
}

<div class="card mb-3" style="@(_report?.Success == true ? "" : "display:none")">
    <div class="card-header d-flex justify-content-between align-items-center">
        <span class="fw-bold">@_report?.Title</span>
        <small class="text-muted">
            @if (_report?.Success == true)
            {
                @($"{_report.ChartType} · {_report.Values.Count} points · value: {_report.ValueColumn}")
            }
        </small>
    </div>
    <div class="card-body">
        <div style="position: relative; height: 420px;">
            <canvas id="reportChart"></canvas>
        </div>
    </div>
</div>

@if (_report is not null && !string.IsNullOrWhiteSpace(_report.Sql))
{
    <div class="card mb-3">
        <div class="card-header">
            <span class="fw-bold">Generated SQL</span>
            <small class="text-muted">— produced by Ollama / llama3.2</small>
        </div>
        <div class="card-body">
            <pre class="mb-0" style="white-space: pre-wrap; font-size: 0.85rem;">@_report.Sql</pre>
        </div>
    </div>
}

@if (_report is not null && _report.Columns.Count > 0 && _report.Rows.Count > 0)
{
    <div class="card mb-3">
        <div class="card-header">
            <span class="fw-bold">Data</span>
            <small class="text-muted">— @_report.Rows.Count row@(_report.Rows.Count == 1 ? "" : "s")</small>
        </div>
        <div class="card-body p-0">
            <div class="table-responsive">
                <table class="table table-striped table-sm mb-0">
                    <thead class="table-light">
                        <tr>
                            @foreach (var c in _report.Columns)
                            {
                                <th>@c</th>
                            }
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var row in _report.Rows)
                        {
                            <tr>
                                @foreach (var cell in row)
                                {
                                    <td>@(cell?.ToString() ?? "")</td>
                                }
                            </tr>
                        }
                    </tbody>
                </table>
            </div>
        </div>
    </div>
}

@code {
    private string _prompt = "";
    private string _chartType = "bar";
    private bool _busy;
    private ChartReport? _report;

    private readonly string[] _samples =
    {
        "Regionwise sales",
        "Sales agent wise sales",
        "Top 5 products by revenue",
        "Category-wise sales",
        "Monthly sales for 1997",
        "Sales by country",
        "Sales by shipper",
        "Top 10 customers by total order value",
        "Yearly sales totals",
        "Quarterly sales for 1997",
        "Top 10 best-selling products by quantity",
        "Average order value by country",
        "Number of orders per employee",
        "Sales by category for 1997",
        "Top 5 suppliers by product revenue",
        "Freight cost by shipper",
        "Sales by ship region",
        "Discontinued vs active product sales"
    };

    private async Task OnChartTypeChangedAsync()
    {
        if (_report?.Success != true) return;
        _report = _report with { ChartType = _chartType };
        await JS.InvokeVoidAsync("chartInterop.render",
            "reportChart", _report.ChartType, _report.Labels, _report.Values,
            _report.ValueColumn, _report.Title);
    }

    private async Task GenerateAsync()
    {
        if (_busy) return;
        _busy = true;
        _report = null;
        StateHasChanged();

        try
        {
            _report = await Reports.BuildAsync(_prompt, _chartType);
        }
        catch (Exception ex)
        {
            _report = new ChartReport(false, $"Unexpected error: {ex.Message}", "", _chartType, _prompt, "",
                Array.Empty<string>(), Array.Empty<double>(),
                Array.Empty<string>(), Array.Empty<IReadOnlyList<object?>>());
        }
        finally
        {
            _busy = false;
        }

        if (_report.Success)
        {
            await JS.InvokeVoidAsync("chartInterop.render",
                "reportChart", _report.ChartType, _report.Labels, _report.Values,
                _report.ValueColumn, _report.Title);
        }
        else
        {
            await JS.InvokeVoidAsync("chartInterop.destroy", "reportChart");
        }
    }

    public async ValueTask DisposeAsync()
    {
        try { await JS.InvokeVoidAsync("chartInterop.destroy", "reportChart"); }
        catch { /* circuit may already be gone */ }
    }
}

Important pieces explained

Directive / element	What it does
@page "/" @page "/reporter"	Two route templates point to the same component.
@rendermode InteractiveServer	Enables the SignalR-based interactive mode — clicks and binds run on the server.
@inject IChartReportService Reports	DI inside the component. Reports.BuildAsync is the single entry point into the C# pipeline.
@inject IJSRuntime JS	Hook into Blazor’s JS interop. Used to invoke the Chart.js wrapper.
@bind="_prompt"	Two-way binding of the textarea to the C# field.
@bind="_chartType" @bind:after="OnChartTypeChangedAsync"	The chart-type <select> binds two-way to _chartType and then invokes the handler — so switching the dropdown re-renders the chart immediately without re-running the query.
@onclick="() => _prompt = sample"	Each sample chip just sets the prompt text.
GenerateAsync	Sets the busy flag, calls Reports.BuildAsync, then invokes chartInterop.render for the chart (the data table is rendered by the Razor markup directly because _report.Columns / _report.Rows are bound in @foreach).
OnChartTypeChangedAsync	If a report is already on screen, it just updates the chart type on the existing data and asks Chart.js to redraw — no second call to Ollama or SQL Server.
DisposeAsync	Cleans up the Chart.js chart when the component goes away (e.g. user navigates).

7. Chart.js — how it is used

Chart.js is a small, browser-only charting library. It draws into an HTML <canvas> using configuration objects (“types and data”), not declarative markup. In this project Chart.js is used purely on the client; the Blazor server only sends data to the browser.

Loading Chart.js

Chart.js is pulled from a CDN inside the App.razor shell:

<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.4/dist/chart.umd.min.js"></script>

This exposes the global Chart constructor in the browser.

Chart configuration used

Inside chartInterop.js, each chart is built with this shape:

new Chart(canvas.getContext('2d'), {
    type: chartType,                       // "bar" | "line" | "pie" | "doughnut" | "polarArea" | "radar"
    data: {
        labels: [...labels],
        datasets: [{
            label: valueColumn,
            data:  [...values],
            backgroundColor: ...,
            borderColor:     ...,
            borderWidth: 1,
            fill: chartType === 'radar',
            tension: 0.25
        }]
    },
    options: {
        responsive: true,
        maintainAspectRatio: false,
        plugins: {
            legend: { display: isCategorical || isRadar },
            title:  { display: !!title, text: title }
        },
        scales: (isCategorical || isRadar) ? {} : { y: { beginAtZero: true } }
    }
});

Why some choices are made

• maintainAspectRatio: false + a fixed-height wrapper (420px) — so the chart fills the card cleanly on any screen.
• For pie / doughnut / polarArea we color each slice from a 20-color palette and show the legend.
• For bar / line we use one accent color, hide the legend (the title is enough), and force beginAtZero on the Y axis so revenue charts are honest.
• For radar we enable fill so the polygon is shaded.
• Reuse: the wrapper keeps a Map of canvasId → Chart. Each render destroys the previous chart on the same canvas, otherwise switching chart types leaks WebGL/Canvas state.

8. wwwroot/js/chartInterop.js — JS interop wrapper

Blazor talks to JavaScript through IJSRuntime.InvokeVoidAsync("namespace.function", args…). The wrapper exposes two functions on a global window.chartInterop object so the Blazor component can call chartInterop.render(...) and chartInterop.destroy(...).

wwwroot/js/chartInterop.js

window.chartInterop = (function () {
    const charts = new Map();

    function palette(n) {
        const base = [
            '#4e79a7', '#f28e2b', '#e15759', '#76b7b2', '#59a14f',
            '#edc948', '#b07aa1', '#ff9da7', '#9c755f', '#bab0ac',
            '#1f77b4', '#ff7f0e', '#2ca02c', '#d62728', '#9467bd',
            '#8c564b', '#e377c2', '#7f7f7f', '#bcbd22', '#17becf'
        ];
        const out = [];
        for (let i = 0; i < n; i++) out.push(base[i % base.length]);
        return out;
    }

    function render(canvasId, chartType, labels, values, valueColumn, title) {
        const canvas = document.getElementById(canvasId);
        if (!canvas) return;

        const existing = charts.get(canvasId);
        if (existing) { existing.destroy(); charts.delete(canvasId); }

        const colors = palette(values.length);
        const isCategorical = ['pie', 'doughnut', 'polarArea'].includes(chartType);
        const isRadar = chartType === 'radar';

        const cfg = {
            type: chartType,
            data: {
                labels: labels,
                datasets: [{
                    label: valueColumn || 'Value',
                    data: values,
                    backgroundColor: isCategorical ? colors : colors[0] + 'cc',
                    borderColor: isCategorical ? colors : colors[0],
                    borderWidth: 1,
                    fill: isRadar,
                    tension: 0.25
                }]
            },
            options: {
                responsive: true,
                maintainAspectRatio: false,
                plugins: {
                    legend: { display: isCategorical || isRadar },
                    title: { display: !!title, text: title }
                },
                scales: (isCategorical || isRadar) ? {} : { y: { beginAtZero: true } }
            }
        };

        const chart = new Chart(canvas.getContext('2d'), cfg);
        charts.set(canvasId, chart);
    }

    function destroy(canvasId) {
        const existing = charts.get(canvasId);
        if (existing) { existing.destroy(); charts.delete(canvasId); }
    }

    return { render, destroy };
})();

9. Changes to the standard Blazor template

9.1 Components/App.razor

The Blazor Web App template ships an App.razor that hosts the page shell. Two <script> tags were added at the bottom — first to pull Chart.js, then our wrapper:

Components/App.razor (additions in bold)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <base href="/" />
    <ResourcePreloader />
    <link rel="stylesheet" href="@Assets["lib/bootstrap/dist/css/bootstrap.min.css"]" />
    <link rel="stylesheet" href="@Assets["app.css"]" />
    <link rel="stylesheet" href="@Assets["NorthwindReporter.styles.css"]" />
    <ImportMap />
    <link rel="icon" type="image/png" href="favicon.png" />
    <HeadOutlet />
</head>
<body>
    <Routes />
    <ReconnectModal />
    <script src="@Assets["_framework/blazor.web.js"]"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.4/dist/chart.umd.min.js"></script>
    <script src="js/chartInterop.js"></script>
</body>
</html>

9.2 Components/Layout/NavMenu.razor

The sidebar that ships with the template has links to “Home”, “Counter”, and “Weather”. The template was simplified to a single Reporter link:

Components/Layout/NavMenu.razor

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

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

<div class="nav-scrollable" onclick="document.querySelector('.navbar-toggler').click()">
    <nav class="nav flex-column">
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="bi bi-bar-chart-fill-nav-menu" aria-hidden="true"></span> Reporter
            </NavLink>
        </div>
    </nav>
</div>

9.3 Removed sample pages

The following files generated by the template were deleted because they are not part of the reporting feature:

• Components/Pages/Home.razor
• Components/Pages/Counter.razor
• Components/Pages/Weather.razor

The Error.razor and NotFound.razor pages are kept as-is.

Code for this article can be downloaded from this link.

10. How to run

1. Install SQL Server locally and restore / create the Northwind sample database (name nw as shown in appsettings.json, or change the connection string to your actual database name).
2. Install Ollama and pull the model: ollama pull llama3.2. Confirm it is listening at http://localhost:11434.
3. Edit appsettings.json if your SQL Server instance or auth differs.
4. From the project folder, run: dotnet run --project NorthwindReporter.
5. Open the URL Kestrel prints. Type a prompt (or click a sample chip), pick a chart type, hit Generate.
6. Switch the chart-type dropdown — the chart re-renders instantly without going back to the database.

 The Following View Shows the Result of the Application.