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)
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.