Creating a Dynamic Dashboard Using SQL Server Data and Angular Charts

Modern applications require real-time insights presented in a visually appealing and interactive dashboard. Whether it’s sales data, KPIs, inventory status, or user analytics, dynamic dashboards help stakeholders make informed decisions quickly.

This article explains how to build a production-ready dynamic dashboard by combining SQL Server data, ASP.NET Core APIs, and Angular charts, following best practices used in enterprise projects.

1. Understanding the Architecture

A dynamic dashboard involves three main layers:

Data Layer (SQL Server)
- Stores raw transactional or aggregated data
- Provides optimized queries for analytics
API Layer (ASP.NET Core Web API)
- Fetches data from SQL Server
- Provides REST endpoints for the frontend
- Implements filtering, paging, and caching
Frontend Layer (Angular + Charts)
- Consumes API data
- Dynamically updates charts
- Provides interactivity, tooltips, and filtering

Diagrammatically:

SQL Server → ASP.NET Core API → Angular Service → Angular Components → Charts

2. Setting Up SQL Server Data

2.1. Sample database

For demonstration, we will use a Sales Dashboard scenario. Assume we have the following table:

CREATE TABLE Sales (
    SaleId INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(100),
    Quantity INT,
    SaleAmount DECIMAL(10,2),
    SaleDate DATETIME
);

2.2. Sample data

INSERT INTO Sales (ProductName, Quantity, SaleAmount, SaleDate)
VALUES
('Laptop', 10, 50000, '2025-11-01'),
('Mouse', 50, 2500, '2025-11-01'),
('Keyboard', 20, 4000, '2025-11-02'),
('Laptop', 5, 25000, '2025-11-02');

2.3. Writing optimized queries

For dashboards, avoid returning raw rows for large datasets. Instead, use aggregation:

SELECT ProductName, SUM(Quantity) AS TotalQuantity, SUM(SaleAmount) AS TotalAmount
FROM Sales
GROUP BY ProductName
ORDER BY TotalAmount DESC;

3. Creating ASP.NET Core API

3.1. Create a new API project

dotnet new webapi -n DashboardAPI
cd DashboardAPI

3.2. Install EF Core packages

dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

3.3. Create the `Sales` model

public class Sale
{
    public int SaleId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal SaleAmount { get; set; }
    public DateTime SaleDate { get; set; }
}

3.4. Create the `DbContext`

using Microsoft.EntityFrameworkCore;

public class DashboardContext : DbContext
{
    public DashboardContext(DbContextOptions<DashboardContext> options) : base(options) {}

    public DbSet<Sale> Sales { get; set; }
}

3.5. Configure connection string

appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=DashboardDB;Trusted_Connection=True;"
  }
}

Program.cs (or Startup.cs)

builder.Services.AddDbContext<DashboardContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

4. Creating API Endpoints for Dashboard

We need endpoints to provide aggregated data for charts.

4.1. Sales by Product

[ApiController]
[Route("api/[controller]")]
public class SalesController : ControllerBase
{
    private readonly DashboardContext _context;

    public SalesController(DashboardContext context)
    {
        _context = context;
    }

    [HttpGet("product-summary")]
    public async Task<IActionResult> GetSalesByProduct()
    {
        var data = await _context.Sales
            .GroupBy(s => s.ProductName)
            .Select(g => new
            {
                Product = g.Key,
                TotalQuantity = g.Sum(x => x.Quantity),
                TotalAmount = g.Sum(x => x.SaleAmount)
            })
            .OrderByDescending(x => x.TotalAmount)
            .ToListAsync();

        return Ok(data);
    }

    [HttpGet("daily-sales")]
    public async Task<IActionResult> GetDailySales()
    {
        var data = await _context.Sales
            .GroupBy(s => s.SaleDate.Date)
            .Select(g => new
            {
                Date = g.Key,
                TotalAmount = g.Sum(x => x.SaleAmount)
            })
            .OrderBy(x => x.Date)
            .ToListAsync();

        return Ok(data);
    }
}

Using LINQ group-by ensures aggregation is done in the database for performance.

5. Setting Up Angular Project

5.1. Create Angular project

ng new dashboard-app --routing --style=scss
cd dashboard-app

5.2. Install charting library

We will use ng2-charts, which wraps Chart.js:

npm install chart.js@^4.3.0 ng2-charts@^4.0.0

6. Creating Angular Service to Call API

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';

@Injectable({
  providedIn: 'root'
})
export class DashboardService {

  private apiUrl = 'https://localhost:5001/api/sales';

  constructor(private http: HttpClient) { }

  getSalesByProduct(): Observable<any[]> {
    return this.http.get<any[]>(`${this.apiUrl}/product-summary`);
  }

  getDailySales(): Observable<any[]> {
    return this.http.get<any[]>(`${this.apiUrl}/daily-sales`);
  }
}

7. Creating Angular Components for Charts

7.1. Product Sales Chart

import { Component, OnInit } from '@angular/core';
import { ChartOptions, ChartType } from 'chart.js';
import { DashboardService } from '../services/dashboard.service';

@Component({
  selector: 'app-product-sales-chart',
  templateUrl: './product-sales-chart.component.html'
})
export class ProductSalesChartComponent implements OnInit {
  public barChartOptions: ChartOptions = { responsive: true };
  public barChartLabels: string[] = [];
  public barChartData: any[] = [{ data: [], label: 'Sales Amount' }];
  public barChartType: ChartType = 'bar';

  constructor(private dashboardService: DashboardService) {}

  ngOnInit(): void {
    this.dashboardService.getSalesByProduct().subscribe(data => {
      this.barChartLabels = data.map(d => d.product);
      this.barChartData[0].data = data.map(d => d.totalAmount);
    });
  }
}

Template:

<div style="display:block">
  <canvas baseChart
          [data]="barChartData"
          [labels]="barChartLabels"
          [options]="barChartOptions"
          [chartType]="barChartType">
  </canvas>
</div>

7.2. Daily Sales Line Chart

import { Component, OnInit } from '@angular/core';
import { ChartOptions, ChartType } from 'chart.js';
import { DashboardService } from '../services/dashboard.service';

@Component({
  selector: 'app-daily-sales-chart',
  templateUrl: './daily-sales-chart.component.html'
})
export class DailySalesChartComponent implements OnInit {
  public lineChartOptions: ChartOptions = { responsive: true };
  public lineChartLabels: string[] = [];
  public lineChartData: any[] = [{ data: [], label: 'Daily Sales' }];
  public lineChartType: ChartType = 'line';

  constructor(private dashboardService: DashboardService) {}

  ngOnInit(): void {
    this.dashboardService.getDailySales().subscribe(data => {
      this.lineChartLabels = data.map(d => new Date(d.date).toLocaleDateString());
      this.lineChartData[0].data = data.map(d => d.totalAmount);
    });
  }
}

8. Adding Interactivity

Filter by date range: Use Angular forms and query parameters.
Hover tooltips: Chart.js provides default tooltips.
Dynamic updates: Use setInterval or WebSocket for live dashboards.

Example: Refresh every 30 seconds

ngOnInit(): void {
  this.loadData();
  setInterval(() => this.loadData(), 30000);
}

loadData(): void {
  this.dashboardService.getSalesByProduct().subscribe(...);
}

9. Best Practices for Dynamic Dashboards

Do aggregation in SQL to avoid sending large raw datasets to Angular.
Use async pipes to avoid manual subscriptions.
Cache API responses if data does not change frequently.
Use Web API pagination for very large datasets.
Use separate components per chart for maintainability.
Lazy load modules to improve Angular app startup time.
Use responsive charts to support desktop and mobile.

10. Security Considerations

Protect API endpoints with JWT authentication or Azure AD.
Validate all query parameters in the API.
Avoid exposing sensitive business data in charts.
Use HTTPS for API calls.

11. Performance Optimizations

Server-side aggregation reduces payload size.
Client-side caching avoids unnecessary API calls.
Change detection strategy: Use OnPush for chart components.
Lazy load chart libraries if your app has multiple dashboards.

12. Example Production Scenario

Imagine an e-commerce analytics dashboard:

Product sales and revenue charts updated every minute.
Daily and monthly KPIs displayed with charts and cards.
Angular charts dynamically react to filter changes without page reload.
ASP.NET Core APIs fetch data efficiently using EF Core aggregations and stored procedures.
SQL Server stores millions of transactions but only aggregates are sent to Angular.

This approach is scalable, maintainable, and performant for enterprise applications.

Conclusion

Building a dynamic dashboard using SQL Server and Angular charts requires careful planning across three layers: database, API, and frontend. Key takeaways:

Use aggregated queries in SQL for performance.
Provide clean REST APIs from ASP.NET Core.
Use ng2-charts with Chart.js for interactive visualization.
Apply security, caching, and refresh strategies for production-grade dashboards.
Modular Angular components make maintenance and scalability easier.

With these practices, you can build dashboards that provide real-time insights to business users with minimal latency and high responsiveness.