SQLSugar实现大屏数据可视化系统

最新推荐文章于 2025-12-20 23:32:06 发布

原创最新推荐文章于 2025-12-20 23:32:06 发布 · 414 阅读

15 ·

CC 4.0 BY-SA版权

文章标签：

#信息可视化

数据库ORM 专栏收录该内容

14 篇文章

订阅专栏

2025博客之星年度评选已开启 10w+人浏览 1.6k人参与

引言

随着数字化转型的深入，大屏数据可视化系统在各行各业得到了广泛应用。无论是生产监控看板、业务数据驾驶舱，还是智慧城市大屏，都需要实时、直观地展示海量数据。本文将以SQLSugar为核心，分享如何构建一个高性能、高可用的大屏数据可视化系统。

一、系统架构设计

1.1 整体架构

┌─────────────────────────────────────────────────────────┐
│ 大屏展示层 │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ ECharts │ │ AntV │ │ Three.js │ │ D3.js │ │
│ │ 图表库 │ │ 图形库 │ │ 3D渲染 │ │ 自定义 │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
├─────────────────────────────────────────────────────────┤
│ API网关层 │
│ ┌────────────────────────────────────────────────────┐ │
│ │ WebSocket / SignalR │ │
│ │ HTTP REST API / GraphQL │ │
│ └────────────────────────────────────────────────────┘ │
├─────────────────────────────────────────────────────────┤
│ 业务服务层 │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 实时数据 │ │ 统计服务 │ │ 预测服务 │ │ 报警服务 │ │
│ │ 服务 │ │ │ │ │ │ │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
├─────────────────────────────────────────────────────────┤
│ SQLSugar数据访问层 │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 实时库 │ │ 统计库 │ │ 缓存层 │ │ 归档库 │ │
│ │ 读写分离 │ │ OLAP │ │ Redis │ │ 历史数据 │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
├─────────────────────────────────────────────────────────┤
│ 数据源层 │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ 业务系统 │ │ IoT设备 │ │ 日志文件 │ │ 外部API │ │
│ │ 数据库 │ │ │ │ │ │ │ │
│ └──────────┘ └──────────┘ └──────────┘ └──────────┘ │
└─────────────────────────────────────────────────────────┘

1.2 技术栈选型

组件	技术选型	说明
后端框架	.NET 6/8	高性能、跨平台
ORM框架	SQLSugar 5.x	高性能ORM，支持多种数据库
前端框架	Vue 3 + TypeScript	响应式、组合式API
可视化库	ECharts 5 + AntV G6	丰富图表类型，高性能渲染
实时推送	SignalR	WebSocket双向通信
缓存	Redis 7.x	内存数据库，高性能缓存
消息队列	RabbitMQ	异步处理，削峰填谷
数据库	PostgreSQL 14 / SQL Server 2022	主数据库，支持JSON和时序数据

二、数据库设计与优化

2.1 数据模型设计

// Models/DashboardModels.cs
using SqlSugar;
using System.ComponentModel.DataAnnotations;

namespace BigScreen.Data.Models
{
    /// <summary>
    /// 大屏配置实体
    /// </summary>
    [SugarTable("dashboard_configs", "大屏配置表")]
    [SugarIndex("idx_dashboard_name", nameof(Name), OrderByType.Asc)]
    public class DashboardConfig
    {
        [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true, ColumnDescription = "主键ID")]
        public long Id { get; set; }

        [SugarColumn(ColumnName = "name", ColumnDescription = "大屏名称", Length = 100)]
        [Required(ErrorMessage = "大屏名称不能为空")]
        public string Name { get; set; }

        [SugarColumn(ColumnName = "code", ColumnDescription = "大屏编码", Length = 50, IsNullable = false)]
        public string Code { get; set; }

        [SugarColumn(ColumnName = "description", ColumnDescription = "描述", Length = 500)]
        public string Description { get; set; }

        [SugarColumn(ColumnName = "layout_config", ColumnDataType = "jsonb", ColumnDescription = "布局配置")]
        public DashboardLayout Layout { get; set; }

        [SugarColumn(ColumnName = "data_sources", ColumnDataType = "jsonb", ColumnDescription = "数据源配置")]
        public List<DataSourceConfig> DataSources { get; set; } = new();

        [SugarColumn(ColumnName = "widgets", ColumnDataType = "jsonb", ColumnDescription = "组件配置")]
        public List<WidgetConfig> Widgets { get; set; } = new();

        [SugarColumn(ColumnName = "theme", ColumnDescription = "主题配置", Length = 50)]
        public string Theme { get; set; } = "default";

        [SugarColumn(ColumnName = "refresh_interval", ColumnDescription = "刷新间隔(秒)")]
        public int RefreshInterval { get; set; } = 30;

        [SugarColumn(ColumnName = "is_active", ColumnDescription = "是否启用")]
        public bool IsActive { get; set; } = true;

        [SugarColumn(ColumnName = "created_at", ColumnDescription = "创建时间")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        [SugarColumn(ColumnName = "updated_at", ColumnDescription = "更新时间")]
        public DateTime UpdatedAt { get; set; } = DateTime.Now;

        [Navigate(NavigateType.OneToMany, nameof(Widget.DashboardId))]
        public List<Widget> WidgetList { get; set; }
    }

    /// <summary>
    /// 大屏布局配置
    /// </summary>
    public class DashboardLayout
    {
        public int Rows { get; set; } = 12;
        public int Cols { get; set; } = 24;
        public bool AutoHeight { get; set; } = false;
        public LayoutItem[] Layouts { get; set; } = Array.Empty<LayoutItem>();
    }

    /// <summary>
    /// 数据指标实体（时序数据）
    /// </summary>
    [SugarTable("data_metrics", TableDescription = "数据指标表")]
    [SplitTable(SplitType.Month)] // 按月分区
    public class DataMetric
    {
        [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true, ColumnDescription = "主键ID")]
        public long Id { get; set; }

        [SplitField] // 分区字段
        [SugarColumn(ColumnName = "timestamp", ColumnDescription = "时间戳")]
        public DateTime Timestamp { get; set; }

        [SugarColumn(ColumnName = "metric_code", ColumnDescription = "指标编码", Length = 100)]
        public string MetricCode { get; set; }

        [SugarColumn(ColumnName = "metric_name", ColumnDescription = "指标名称", Length = 200)]
        public string MetricName { get; set; }

        [SugarColumn(ColumnName = "value", ColumnDescription = "指标值", DecimalDigits = 4)]
        public decimal Value { get; set; }

        [SugarColumn(ColumnName = "unit", ColumnDescription = "单位", Length = 20)]
        public string Unit { get; set; }

        [SugarColumn(ColumnName = "dimensions", ColumnDataType = "jsonb", ColumnDescription = "维度数据")]
        public Dictionary<string, string> Dimensions { get; set; } = new();

        [SugarColumn(ColumnName = "tags", ColumnDataType = "jsonb", ColumnDescription = "标签数据")]
        public Dictionary<string, string> Tags { get; set; } = new();

        [SugarColumn(ColumnName = "quality", ColumnDescription = "数据质量")]
        public DataQuality Quality { get; set; } = DataQuality.Good;

        [SugarColumn(ColumnName = "source_id", ColumnDescription = "数据源ID")]
        public string SourceId { get; set; }

        // 创建复合索引
        [SugarIndex("idx_metrics_code_time", nameof(MetricCode), OrderByType.Asc, nameof(Timestamp), OrderByType.Desc)]
        [SugarIndex("idx_metrics_timestamp", nameof(Timestamp), OrderByType.Desc)]
        public class MetricIndex { }
    }

    /// <summary>
    /// 大屏组件实体
    /// </summary>
    [SugarTable("dashboard_widgets", TableDescription = "大屏组件表")]
    public class Widget
    {
        [SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true)]
        public long Id { get; set; }

        [SugarColumn(ColumnName = "dashboard_id", ColumnDescription = "所属大屏ID")]
        public long DashboardId { get; set; }

        [SugarColumn(ColumnName = "widget_code", ColumnDescription = "组件编码", Length = 50)]
        public string WidgetCode { get; set; }

        [SugarColumn(ColumnName = "widget_type", ColumnDescription = "组件类型", Length = 50)]
        public WidgetType WidgetType { get; set; }

        [SugarColumn(ColumnName = "title", ColumnDescription = "标题", Length = 200)]
        public string Title { get; set; }

        [SugarColumn(ColumnName = "position_x", ColumnDescription = "X位置")]
        public int PositionX { get; set; }

        [SugarColumn(ColumnName = "position_y", ColumnDescription = "Y位置")]
        public int PositionY { get; set; }

        [SugarColumn(ColumnName = "width", ColumnDescription = "宽度")]
        public int Width { get; set; }

        [SugarColumn(ColumnName = "height", ColumnDescription = "高度")]
        public int Height { get; set; }

        [SugarColumn(ColumnName = "config", ColumnDataType = "jsonb", ColumnDescription = "组件配置")]
        public WidgetConfig Config { get; set; }

        [SugarColumn(ColumnName = "data_source_id", ColumnDescription = "数据源ID")]
        public string DataSourceId { get; set; }

        [SugarColumn(ColumnName = "refresh_interval", ColumnDescription = "刷新间隔(秒)")]
        public int RefreshInterval { get; set; }

        [SugarColumn(ColumnName = "is_active", ColumnDescription = "是否启用")]
        public bool IsActive { get; set; } = true;

        [SugarColumn(ColumnName = "z_index", ColumnDescription = "层级")]
        public int ZIndex { get; set; }

        [SugarColumn(ColumnName = "created_at", ColumnDescription = "创建时间")]
        public DateTime CreatedAt { get; set; } = DateTime.Now;

        [SugarColumn(ColumnName = "updated_at", ColumnDescription = "更新时间")]
        public DateTime UpdatedAt { get; set; } = DateTime.Now;

        [Navigate(NavigateType.OneToOne, nameof(DashboardId))]
        public DashboardConfig Dashboard { get; set; }
    }
}

// 枚举定义
public enum WidgetType
{
    LineChart = 1,      // 折线图
    BarChart = 2,       // 柱状图
    PieChart = 3,       // 饼图
    GaugeChart = 4,     // 仪表盘
    MapChart = 5,       // 地图
    NumberCard = 6,     // 数字卡片
    Table = 7,          // 表格
    Custom = 99         // 自定义
}

public enum DataQuality
{
    Good = 0,           // 良好
    Warning = 1,        // 警告
    Bad = 2,            // 坏值
    Unknown = 99        // 未知
}

2.2 数据库优化策略

-- 创建时序数据分区表（PostgreSQL）
CREATE TABLE data_metrics_202401 PARTITION OF data_metrics
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 创建BRIN索引（适用于时序数据）
CREATE INDEX idx_data_metrics_timestamp_brin ON data_metrics USING BRIN(timestamp);

-- 创建GIN索引（用于JSONB字段）
CREATE INDEX idx_data_metrics_dimensions_gin ON data_metrics USING GIN(dimensions);
CREATE INDEX idx_data_metrics_tags_gin ON data_metrics USING GIN(tags);

-- 创建物化视图（预计算聚合数据）
CREATE MATERIALIZED VIEW mv_dashboard_hourly_stats AS
SELECT 
    metric_code,
    DATE_TRUNC('hour', timestamp) as hour,
    COUNT(*) as count,
    AVG(value) as avg_value,
    MIN(value) as min_value,
    MAX(value) as max_value,
    SUM(value) as sum_value
FROM data_metrics
WHERE timestamp >= NOW() - INTERVAL '7 days'
GROUP BY metric_code, DATE_TRUNC('hour', timestamp);

-- 创建刷新物化视图的定时任务
CREATE OR REPLACE FUNCTION refresh_dashboard_stats()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_dashboard_hourly_stats;
END;
$$ LANGUAGE plpgsql;

三、SQLSugar数据访问层实现

3.1 多数据源配置

// Data/SqlSugarMultiDbContext.cs
public class SqlSugarMultiDbContext
{
    private readonly Dictionary<string, SqlSugarScope> _clients = new();
    private readonly IConfiguration _configuration;
    private readonly ILogger<SqlSugarMultiDbContext> _logger;

    public SqlSugarMultiDbContext(IConfiguration configuration, ILogger<SqlSugarMultiDbContext> logger)
    {
        _configuration = configuration;
        _logger = logger;
        InitializeClients();
    }

    private void InitializeClients()
    {
        // 实时库（写库）
        var realtimeConfig = new ConnectionConfig
        {
            ConfigId = "Realtime",
            ConnectionString = _configuration.GetConnectionString("RealtimeDb"),
            DbType = DbType.PostgreSQL,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            MoreSettings = new ConnMoreSettings
            {
                PgSqlIsAutoToLower = false,
                EnablePaginationOptimize = true
            }
        };

        // 统计库（读库）
        var statisticsConfig = new ConnectionConfig
        {
            ConfigId = "Statistics",
            ConnectionString = _configuration.GetConnectionString("StatisticsDb"),
            DbType = DbType.PostgreSQL,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute,
            MoreSettings = new ConnMoreSettings
            {
                PgSqlIsAutoToLower = false
            }
        };

        // 归档库（历史数据）
        var archiveConfig = new ConnectionConfig
        {
            ConfigId = "Archive",
            ConnectionString = _configuration.GetConnectionString("ArchiveDb"),
            DbType = DbType.PostgreSQL,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        };

        _clients["Realtime"] = new SqlSugarScope(realtimeConfig, db =>
        {
            ConfigureAop(db);
            ConfigureFilters(db);
        });

        _clients["Statistics"] = new SqlSugarScope(statisticsConfig, db =>
        {
            ConfigureAop(db);
        });

        _clients["Archive"] = new SqlSugarScope(archiveConfig);
    }

    private void ConfigureAop(SqlSugarScope db)
    {
        db.Aop.OnLogExecuting = (sql, pars) =>
        {
            // 慢查询监控
            var stopwatch = new Stopwatch();
            stopwatch.Start();
            
            db.Aop.OnLogExecuted = (sql2, pars2) =>
            {
                stopwatch.Stop();
                if (stopwatch.ElapsedMilliseconds > 1000) // 超过1秒为慢查询
                {
                    _logger.LogWarning($"慢查询检测: {stopwatch.ElapsedMilliseconds}ms, SQL: {sql}");
                }
            };
        };

        db.Aop.OnError = (exp) =>
        {
            _logger.LogError(exp, "SQL执行错误");
        };
    }

    private void ConfigureFilters(SqlSugarScope db)
    {
        // 全局过滤器
        db.QueryFilter.AddTableFilter<ISoftDelete>(it => it.IsDeleted == false);
    }

    public ISqlSugarClient GetClient(string configId = "Realtime")
    {
        if (_clients.TryGetValue(configId, out var client))
        {
            return client;
        }
        throw new ArgumentException($"未找到配置ID: {configId}");
    }

    // 根据时间自动选择数据源
    public ISqlSugarClient GetClientByTime(DateTime queryTime)
    {
        var cutoffTime = DateTime.Now.AddMonths(-6); // 6个月前的数据查归档库
        return queryTime < cutoffTime ? GetClient("Archive") : GetClient("Realtime");
    }

    // 获取统计库客户端
    public ISqlSugarClient Statistics => GetClient("Statistics");

    // 获取实时库客户端
    public ISqlSugarClient Realtime => GetClient("Realtime");

    // 获取归档库客户端
    public ISqlSugarClient Archive => GetClient("Archive");
}

3.2 数据访问仓储实现

// Repositories/DashboardRepository.cs
public class DashboardRepository : IDashboardRepository
{
    private readonly SqlSugarMultiDbContext _dbContext;
    private readonly IMemoryCache _cache;
    private readonly ILogger<DashboardRepository> _logger;

    public DashboardRepository(
        SqlSugarMultiDbContext dbContext,
        IMemoryCache cache,
        ILogger<DashboardRepository> logger)
    {
        _dbContext = dbContext;
        _cache = cache;
        _logger = logger;
    }

    /// <summary>
    /// 获取大屏配置（带缓存）
    /// </summary>
    public async Task<DashboardConfig> GetDashboardConfigAsync(string dashboardCode)
    {
        var cacheKey = $"dashboard_config_{dashboardCode}";
        
        // 尝试从缓存获取
        if (_cache.TryGetValue(cacheKey, out DashboardConfig cachedConfig))
        {
            return cachedConfig;
        }

        // 从数据库查询
        var config = await _dbContext.Realtime.Queryable<DashboardConfig>()
            .Where(d => d.Code == dashboardCode && d.IsActive)
            .Includes(d => d.WidgetList)
            .FirstAsync();

        if (config != null)
        {
            // 设置缓存（5分钟过期）
            _cache.Set(cacheKey, config, TimeSpan.FromMinutes(5));
        }

        return config;
    }

    /// <summary>
    /// 获取指标数据（支持多种聚合方式）
    /// </summary>
    public async Task<List<MetricData>> GetMetricDataAsync(MetricQueryRequest request)
    {
        var client = _dbContext.GetClientByTime(request.StartTime);

        // 构建基础查询
        var query = client.Queryable<DataMetric>()
            .Where(m => m.MetricCode == request.MetricCode)
            .Where(m => m.Timestamp >= request.StartTime && m.Timestamp <= request.EndTime);

        // 应用维度过滤
        if (request.Dimensions != null && request.Dimensions.Any())
        {
            foreach (var dim in request.Dimensions)
            {
                query = query.Where(m => SqlFunc.JsonLike(m.Dimensions, $"%\"{dim.Key}\":\"{dim.Value}\"%"));
            }
        }

        // 根据聚合类型处理
        switch (request.AggregationType)
        {
            case AggregationType.Raw:
                return await query.OrderBy(m => m.Timestamp)
                    .Select(m => new MetricData
                    {
                        Timestamp = m.Timestamp,
                        Value = m.Value,
                        Quality = m.Quality
                    })
                    .ToListAsync();

            case AggregationType.Average:
                return await query.GroupBy(m => SqlFunc.DateFormat(m.Timestamp, request.GroupFormat))
                    .Select(m => new MetricData
                    {
                        Timestamp = SqlFunc.ToDate(SqlFunc.DateFormat(m.Timestamp, request.GroupFormat)),
                        Value = SqlFunc.AggregateAvg(m.Value),
                        Count = SqlFunc.AggregateCount(m.Id)
                    })
                    .OrderBy(m => m.Timestamp)
                    .ToListAsync();

            case AggregationType.Sum:
                return await query.GroupBy(m => SqlFunc.DateFormat(m.Timestamp, request.GroupFormat))
                    .Select(m => new MetricData
                    {
                        Timestamp = SqlFunc.ToDate(SqlFunc.DateFormat(m.Timestamp, request.GroupFormat)),
                        Value = SqlFunc.AggregateSum(m.Value),
                        Count = SqlFunc.AggregateCount(m.Id)
                    })
                    .OrderBy(m => m.Timestamp)
                    .ToListAsync();

            case AggregationType.Max:
                return await query.GroupBy(m => SqlFunc.DateFormat(m.Timestamp, request.GroupFormat))
                    .Select(m => new MetricData
                    {
                        Timestamp = SqlFunc.ToDate(SqlFunc.DateFormat(m.Timestamp, request.GroupFormat)),
                        Value = SqlFunc.AggregateMax(m.Value),
                        Count = SqlFunc.AggregateCount(m.Id)
                    })
                    .OrderBy(m => m.Timestamp)
                    .ToListAsync();

            case AggregationType.Min:
                return await query.GroupBy(m => SqlFunc.DateFormat(m.Timestamp, request.GroupFormat))
                    .Select(m => new MetricData
                    {
                        Timestamp = SqlFunc.ToDate(SqlFunc.DateFormat(m.Timestamp, request.GroupFormat)),
                        Value = SqlFunc.AggregateMin(m.Value),
                        Count = SqlFunc.AggregateCount(m.Id)
                    })
                    .OrderBy(m => m.Timestamp)
                    .ToListAsync();

            default:
                throw new NotSupportedException($"不支持的聚合类型: {request.AggregationType}");
        }
    }

    /// <summary>
    /// 获取多个指标的对比数据
    /// </summary>
    public async Task<Dictionary<string, List<MetricData>>> GetMultiMetricsDataAsync(MultiMetricQueryRequest request)
    {
        var result = new Dictionary<string, List<MetricData>>();
        var tasks = new List<Task>();

        foreach (var metricCode in request.MetricCodes)
        {
            tasks.Add(Task.Run(async () =>
            {
                var query = new MetricQueryRequest
                {
                    MetricCode = metricCode,
                    StartTime = request.StartTime,
                    EndTime = request.EndTime,
                    AggregationType = request.AggregationType,
                    GroupFormat = request.GroupFormat,
                    Dimensions = request.Dimensions
                };

                var data = await GetMetricDataAsync(query);
                lock (result)
                {
                    result[metricCode] = data;
                }
            }));
        }

        await Task.WhenAll(tasks);
        return result;
    }

    /// <summary>
    /// 获取实时最新数据（带缓存）
    /// </summary>
    public async Task<RealTimeData> GetLatestMetricDataAsync(string metricCode, int cacheSeconds = 5)
    {
        var cacheKey = $"latest_metric_{metricCode}";

        if (_cache.TryGetValue(cacheKey, out RealTimeData cachedData))
        {
            return cachedData;
        }

        var data = await _dbContext.Realtime.Queryable<DataMetric>()
            .Where(m => m.MetricCode == metricCode)
            .OrderByDesc(m => m.Timestamp)
            .Select(m => new RealTimeData
            {
                MetricCode = m.MetricCode,
                MetricName = m.MetricName,
                Value = m.Value,
                Unit = m.Unit,
                Timestamp = m.Timestamp,
                Quality = m.Quality
            })
            .FirstAsync();

        if (data != null)
        {
            _cache.Set(cacheKey, data, TimeSpan.FromSeconds(cacheSeconds));
        }

        return data;
    }

    /// <summary>
    /// 批量插入指标数据（高性能）
    /// </summary>
    public async Task<bool> BulkInsertMetricsAsync(List<DataMetric> metrics)
    {
        if (metrics == null || !metrics.Any())
            return false;

        try
        {
            // 使用SQLSugar的批量插入功能
            await _dbContext.Realtime.Fastest<DataMetric>()
                .PageSize(5000) // 每批5000条
                .BulkCopyAsync(metrics);

            // 更新缓存
            foreach (var metric in metrics.GroupBy(m => m.MetricCode))
            {
                var cacheKey = $"latest_metric_{metric.Key}";
                var latest = metric.OrderByDescending(m => m.Timestamp).First();
                
                _cache.Set(cacheKey, new RealTimeData
                {
                    MetricCode = latest.MetricCode,
                    MetricName = latest.MetricName,
                    Value = latest.Value,
                    Unit = latest.Unit,
                    Timestamp = latest.Timestamp,
                    Quality = latest.Quality
                }, TimeSpan.FromSeconds(5));
            }

            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "批量插入指标数据失败");
            return false;
        }
    }

    /// <summary>
    /// 获取统计数据（使用物化视图）
    /// </summary>
    public async Task<List<DashboardStat>> GetDashboardStatsAsync(string dashboardCode)
    {
        var cacheKey = $"dashboard_stats_{dashboardCode}_{DateTime.Today:yyyyMMdd}";

        if (_cache.TryGetValue(cacheKey, out List<DashboardStat> cachedStats))
        {
            return cachedStats;
        }

        var stats = await _dbContext.Statistics.Queryable<DashboardHourlyStat>()
            .Where(s => s.DashboardCode == dashboardCode && s.StatDate >= DateTime.Today.AddDays(-7))
            .GroupBy(s => s.StatDate)
            .Select(s => new DashboardStat
            {
                Date = s.StatDate,
                TotalVisits = SqlFunc.AggregateSum(s.VisitCount),
                TotalUsers = SqlFunc.AggregateSum(s.UserCount),
                AvgDuration = SqlFunc.AggregateAvg(s.AvgDuration)
            })
            .OrderBy(s => s.Date)
            .ToListAsync();

        _cache.Set(cacheKey, stats, TimeSpan.FromMinutes(10));

        return stats;
    }

    /// <summary>
    /// 获取热力图数据
    /// </summary>
    public async Task<List<HeatMapData>> GetHeatMapDataAsync(HeatMapQueryRequest request)
    {
        var query = _dbContext.Realtime.Queryable<DataMetric>()
            .Where(m => m.MetricCode == request.MetricCode)
            .Where(m => m.Timestamp >= request.StartTime && m.Timestamp <= request.EndTime);

        // 按小时和维度值分组
        return await query.GroupBy(m => new 
            {
                Hour = m.Timestamp.Hour,
                DimensionValue = SqlFunc.JsonField(m.Dimensions, request.DimensionKey)
            })
            .Select(m => new HeatMapData
            {
                Hour = m.Hour,
                DimensionValue = m.DimensionValue,
                Value = SqlFunc.AggregateAvg(m.Value),
                Count = SqlFunc.AggregateCount(m.Id)
            })
            .OrderBy(m => m.Hour)
            .ThenBy(m => m.DimensionValue)
            .ToListAsync();
    }
}

// 查询请求模型
public class MetricQueryRequest
{
    public string MetricCode { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
    public AggregationType AggregationType { get; set; } = AggregationType.Raw;
    public string GroupFormat { get; set; } = "yyyy-MM-dd HH:00:00";
    public Dictionary<string, string> Dimensions { get; set; }
}

public enum AggregationType
{
    Raw,        // 原始数据
    Average,    // 平均值
    Sum,        // 求和
    Max,        // 最大值
    Min         // 最小值
}

四、实时数据推送服务

4.1 SignalR Hub实现

// Hubs/DashboardHub.cs
[Authorize]
public class DashboardHub : Hub
{
    private readonly IDashboardService _dashboardService;
    private readonly ILogger<DashboardHub> _logger;
    private readonly IHubContext<DashboardHub> _hubContext;
    private static readonly ConcurrentDictionary<string, DashboardConnection> _connections = new();

    public DashboardHub(
        IDashboardService dashboardService,
        ILogger<DashboardHub> logger,
        IHubContext<DashboardHub> hubContext)
    {
        _dashboardService = dashboardService;
        _logger = logger;
        _hubContext = hubContext;
    }

    public override async Task OnConnectedAsync()
    {
        var connectionId = Context.ConnectionId;
        var userId = Context.User?.FindFirst(ClaimTypes.NameIdentifier)?.Value;
        var dashboardCode = Context.GetHttpContext()?.Request.Query["dashboard"].ToString();

        _logger.LogInformation($"客户端连接: {connectionId}, 用户: {userId}, 大屏: {dashboardCode}");

        var connection = new DashboardConnection
        {
            ConnectionId = connectionId,
            UserId = userId,
            DashboardCode = dashboardCode,
            ConnectedAt = DateTime.Now
        };

        _connections[connectionId] = connection;

        // 加入分组
        if (!string.IsNullOrEmpty(dashboardCode))
        {
            await Groups.AddToGroupAsync(connectionId, $"dashboard_{dashboardCode}");
        }

        // 发送欢迎消息
        await Clients.Client(connectionId).SendAsync("Connected", new
        {
            ConnectionId = connectionId,
            ServerTime = DateTime.Now,
            Message = "连接成功"
        });

        await base.OnConnectedAsync();
    }

    public override async Task OnDisconnectedAsync(Exception exception)
    {
        var connectionId = Context.ConnectionId;
        
        if (_connections.TryRemove(connectionId, out var connection))
        {
            _logger.LogInformation($"客户端断开: {connectionId}, 用户: {connection.UserId}");
            
            // 从分组移除
            if (!string.IsNullOrEmpty(connection.DashboardCode))
            {
                await Groups.RemoveFromGroupAsync(connectionId, $"dashboard_{connection.DashboardCode}");
            }
        }

        await base.OnDisconnectedAsync(exception);
    }

    /// <summary>
    /// 订阅指标数据更新
    /// </summary>
    public async Task SubscribeMetric(string metricCode, int interval = 5)
    {
        var connectionId = Context.ConnectionId;
        
        if (_connections.TryGetValue(connectionId, out var connection))
        {
            var subscription = new MetricSubscription
            {
                MetricCode = metricCode,
                Interval = interval,
                LastUpdate = DateTime.Now
            };

            connection.Subscriptions[metricCode] = subscription;
            
            _logger.LogInformation($"用户 {connection.UserId} 订阅指标: {metricCode}, 间隔: {interval}秒");

            // 立即发送一次数据
            var data = await _dashboardService.GetMetricDataAsync(new MetricQueryRequest
            {
                MetricCode = metricCode,
                StartTime = DateTime.Now.AddMinutes(-5),
                EndTime = DateTime.Now,
                AggregationType = AggregationType.Raw
            });

            await Clients.Client(connectionId).SendAsync("MetricUpdate", new
            {
                MetricCode = metricCode,
                Data = data,
                Timestamp = DateTime.Now
            });
        }
    }

    /// <summary>
    /// 取消订阅指标
    /// </summary>
    public Task UnsubscribeMetric(string metricCode)
    {
        var connectionId = Context.ConnectionId;
        
        if (_connections.TryGetValue(connectionId, out var connection))
        {
            connection.Subscriptions.TryRemove(metricCode, out _);
            _logger.LogInformation($"用户 {connection.UserId} 取消订阅指标: {metricCode}");
        }

        return Task.CompletedTask;
    }

    /// <summary>
    /// 请求历史数据
    /// </summary>
    public async Task<ApiResult> RequestHistoryData(HistoryDataRequest request)
    {
        try
        {
            var data = await _dashboardService.GetMetricDataAsync(new MetricQueryRequest
            {
                MetricCode = request.MetricCode,
                StartTime = request.StartTime,
                EndTime = request.EndTime,
                AggregationType = request.AggregationType,
                GroupFormat = request.GroupFormat,
                Dimensions = request.Dimensions
            });

            await Clients.Caller.SendAsync("HistoryDataResponse", new
            {
                RequestId = request.RequestId,
                Data = data,
                Timestamp = DateTime.Now
            });

            return ApiResult.Success();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"获取历史数据失败: {request.MetricCode}");
            return ApiResult.Error($"获取历史数据失败: {ex.Message}");
        }
    }

    /// <summary>
    /// 广播数据更新（由后台服务调用）
    /// </summary>
    public async Task BroadcastMetricUpdate(string dashboardCode, MetricUpdateData updateData)
    {
        var groupName = $"dashboard_{dashboardCode}";
        
        await _hubContext.Clients.Group(groupName).SendAsync("MetricUpdate", new
        {
            updateData.MetricCode,
            Data = updateData.Data,
            updateData.Timestamp,
            updateData.ChangeType
        });
    }

    /// <summary>
    /// 获取在线用户统计
    /// </summary>
    public async Task<OnlineUserStats> GetOnlineUserStats(string dashboardCode = null)
    {
        var connections = _connections.Values;
        
        if (!string.IsNullOrEmpty(dashboardCode))
        {
            connections = connections.Where(c => c.DashboardCode == dashboardCode).ToList();
        }

        return new OnlineUserStats
        {
            TotalCount = connections.Count,
            Users = connections.GroupBy(c => c.UserId)
                .Select(g => new UserConnectionInfo
                {
                    UserId = g.Key,
                    ConnectionCount = g.Count(),
                    FirstConnected = g.Min(c => c.ConnectedAt),
                    LastActivity = g.Max(c => c.LastActivity)
                })
                .ToList(),
            DashboardConnections = connections.GroupBy(c => c.DashboardCode)
                .ToDictionary(g => g.Key, g => g.Count())
        };
    }
}

// 连接管理类
public class DashboardConnection
{
    public string ConnectionId { get; set; }
    public string UserId { get; set; }
    public string DashboardCode { get; set; }
    public DateTime ConnectedAt { get; set; }
    public DateTime LastActivity { get; set; } = DateTime.Now;
    public ConcurrentDictionary<string, MetricSubscription> Subscriptions { get; } = new();
}

public class MetricSubscription
{
    public string MetricCode { get; set; }
    public int Interval { get; set; }
    public DateTime LastUpdate { get; set; }
}

4.2 实时数据推送服务

// Services/RealTimeDataService.cs
public class RealTimeDataService : BackgroundService
{
    private readonly IServiceProvider _serviceProvider;
    private readonly ILogger<RealTimeDataService> _logger;
    private readonly Timer _broadcastTimer;
    private readonly ConcurrentDictionary<string, MetricDataCache> _metricCache = new();

    public RealTimeDataService(
        IServiceProvider serviceProvider,
        ILogger<RealTimeDataService> logger)
    {
        _serviceProvider = serviceProvider;
        _logger = logger;
        _broadcastTimer = new Timer(BroadcastData, null, TimeSpan.Zero, TimeSpan.FromSeconds(1));
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        _logger.LogInformation("实时数据推送服务启动");

        while (!stoppingToken.IsCancellationRequested)
        {
            try
            {
                await ProcessMetricUpdatesAsync();
                await Task.Delay(100, stoppingToken); // 100ms间隔
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "实时数据处理异常");
                await Task.Delay(1000, stoppingToken);
            }
        }

        _logger.LogInformation("实时数据推送服务停止");
    }

    private async Task ProcessMetricUpdatesAsync()
    {
        using var scope = _serviceProvider.CreateScope();
        var metricRepository = scope.ServiceProvider.GetRequiredService<IMetricRepository>();
        
        // 获取需要更新的指标列表
        var metricsToUpdate = _metricCache.Values
            .Where(c => DateTime.Now - c.LastUpdateTime > TimeSpan.FromSeconds(c.UpdateInterval))
            .ToList();

        foreach (var metricCache in metricsToUpdate)
        {
            try
            {
                var latestData = await metricRepository.GetLatestMetricDataAsync(metricCache.MetricCode);
                
                if (latestData != null)
                {
                    metricCache.Update(latestData);
                    
                    // 触发数据更新事件
                    OnMetricDataUpdated?.Invoke(this, new MetricUpdateEventArgs
                    {
                        MetricCode = metricCache.MetricCode,
                        Data = latestData
                    });
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"更新指标数据失败: {metricCache.MetricCode}");
            }
        }
    }

    private async void BroadcastData(object state)
    {
        try
        {
            using var scope = _serviceProvider.CreateScope();
            var hubContext = scope.ServiceProvider.GetRequiredService<IHubContext<DashboardHub>>();
            
            // 获取所有活跃的连接
            var connections = DashboardHub.GetActiveConnections();
            
            foreach (var connection in connections)
            {
                // 为每个连接的订阅发送数据
                foreach (var subscription in connection.Subscriptions.Values)
                {
                    if (DateTime.Now - subscription.LastUpdate > TimeSpan.FromSeconds(subscription.Interval))
                    {
                        if (_metricCache.TryGetValue(subscription.MetricCode, out var metricCache))
                        {
                            await hubContext.Clients.Client(connection.ConnectionId)
                                .SendAsync("MetricUpdate", new
                                {
                                    MetricCode = subscription.MetricCode,
                                    Data = metricCache.LatestData,
                                    Timestamp = DateTime.Now
                                });
                            
                            subscription.LastUpdate = DateTime.Now;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "广播数据失败");
        }
    }

    /// <summary>
    /// 注册需要监控的指标
    /// </summary>
    public void RegisterMetric(string metricCode, int updateInterval = 5)
    {
        _metricCache[metricCode] = new MetricDataCache
        {
            MetricCode = metricCode,
            UpdateInterval = updateInterval,
            LastUpdateTime = DateTime.Now
        };
    }

    /// <summary>
    /// 注销指标监控
    /// </summary>
    public void UnregisterMetric(string metricCode)
    {
        _metricCache.TryRemove(metricCode, out _);
    }

    public event EventHandler<MetricUpdateEventArgs> OnMetricDataUpdated;
}

// 数据缓存类
public class MetricDataCache
{
    public string MetricCode { get; set; }
    public RealTimeData LatestData { get; private set; }
    public int UpdateInterval { get; set; } = 5; // 秒
    public DateTime LastUpdateTime { get; set; }
    public List<RealTimeData> History { get; } = new List<RealTimeData>();

    public void Update(RealTimeData newData)
    {
        LatestData = newData;
        LastUpdateTime = DateTime.Now;
        
        // 保留最近1000条历史数据
        History.Add(newData);
        if (History.Count > 1000)
        {
            History.RemoveAt(0);
        }
    }
}

五、API接口设计

5.1 控制器实现

// Controllers/DashboardController.cs
[ApiController]
[Route("api/[controller]")]
[Authorize]
public class DashboardController : ControllerBase
{
    private readonly IDashboardService _dashboardService;
    private readonly IDataVisualizationService _visualizationService;
    private readonly ILogger<DashboardController> _logger;

    public DashboardController(
        IDashboardService dashboardService,
        IDataVisualizationService visualizationService,
        ILogger<DashboardController> logger)
    {
        _dashboardService = dashboardService;
        _visualizationService = visualizationService;
        _logger = logger;
    }

    /// <summary>
    /// 获取大屏配置
    /// </summary>
    [HttpGet("config/{code}")]
    public async Task<ApiResult<DashboardConfigDto>> GetDashboardConfig(string code)
    {
        try
        {
            var config = await _dashboardService.GetDashboardConfigAsync(code);
            if (config == null)
            {
                return ApiResult<DashboardConfigDto>.Error("大屏配置不存在");
            }

            var dto = MapToDto(config);
            return ApiResult<DashboardConfigDto>.Success(dto);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"获取大屏配置失败: {code}");
            return ApiResult<DashboardConfigDto>.Error("获取大屏配置失败");
        }
    }

    /// <summary>
    /// 获取指标数据
    /// </summary>
    [HttpPost("metrics/data")]
    public async Task<ApiResult<List<MetricDataDto>>> GetMetricData([FromBody] MetricQueryRequestDto request)
    {
        try
        {
            var data = await _dashboardService.GetMetricDataAsync(new MetricQueryRequest
            {
                MetricCode = request.MetricCode,
                StartTime = request.StartTime,
                EndTime = request.EndTime,
                AggregationType = request.AggregationType,
                GroupFormat = request.GroupFormat,
                Dimensions = request.Dimensions
            });

            var result = data.Select(d => new MetricDataDto
            {
                Timestamp = d.Timestamp,
                Value = d.Value,
                Quality = d.Quality.ToString(),
                Count = d.Count
            }).ToList();

            return ApiResult<List<MetricDataDto>>.Success(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"获取指标数据失败: {request.MetricCode}");
            return ApiResult<List<MetricDataDto>>.Error("获取指标数据失败");
        }
    }

    /// <summary>
    /// 获取多个指标对比数据
    /// </summary>
    [HttpPost("metrics/comparison")]
    public async Task<ApiResult<Dictionary<string, List<MetricDataDto>>>> GetMultiMetricsData(
        [FromBody] MultiMetricQueryRequestDto request)
    {
        try
        {
            var data = await _dashboardService.GetMultiMetricsDataAsync(new MultiMetricQueryRequest
            {
                MetricCodes = request.MetricCodes,
                StartTime = request.StartTime,
                EndTime = request.EndTime,
                AggregationType = request.AggregationType,
                GroupFormat = request.GroupFormat,
                Dimensions = request.Dimensions
            });

            var result = data.ToDictionary(
                kvp => kvp.Key,
                kvp => kvp.Value.Select(d => new MetricDataDto
                {
                    Timestamp = d.Timestamp,
                    Value = d.Value,
                    Quality = d.Quality.ToString(),
                    Count = d.Count
                }).ToList()
            );

            return ApiResult<Dictionary<string, List<MetricDataDto>>>.Success(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取多指标对比数据失败");
            return ApiResult<Dictionary<string, List<MetricDataDto>>>.Error("获取数据失败");
        }
    }

    /// <summary>
    /// 获取仪表盘统计数据
    /// </summary>
    [HttpGet("stats/{dashboardCode}")]
    public async Task<ApiResult<DashboardStatsDto>> GetDashboardStats(string dashboardCode)
    {
        try
        {
            var stats = await _dashboardService.GetDashboardStatsAsync(dashboardCode);
            
            var dto = new DashboardStatsDto
            {
                TodayVisits = stats.TodayVisits,
                YesterdayVisits = stats.YesterdayVisits,
                WeekVisits = stats.WeekVisits,
                MonthVisits = stats.MonthVisits,
                AvgDuration = stats.AvgDuration,
                PeakTime = stats.PeakTime,
                ActiveUsers = stats.ActiveUsers
            };

            return ApiResult<DashboardStatsDto>.Success(dto);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"获取仪表盘统计失败: {dashboardCode}");
            return ApiResult<DashboardStatsDto>.Error("获取统计失败");
        }
    }

    /// <summary>
    /// 获取热力图数据
    /// </summary>
    [HttpPost("heatmap")]
    public async Task<ApiResult<List<HeatMapDataDto>>> GetHeatMapData([FromBody] HeatMapQueryRequestDto request)
    {
        try
        {
            var data = await _dashboardService.GetHeatMapDataAsync(new HeatMapQueryRequest
            {
                MetricCode = request.MetricCode,
                StartTime = request.StartTime,
                EndTime = request.EndTime,
                DimensionKey = request.DimensionKey
            });

            var result = data.Select(d => new HeatMapDataDto
            {
                Hour = d.Hour,
                DimensionValue = d.DimensionValue,
                Value = d.Value,
                Count = d.Count
            }).ToList();

            return ApiResult<List<HeatMapDataDto>>.Success(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"获取热力图数据失败: {request.MetricCode}");
            return ApiResult<List<HeatMapDataDto>>.Error("获取数据失败");
        }
    }

    /// <summary>
    /// 导出数据为Excel
    /// </summary>
    [HttpPost("export")]
    public async Task<IActionResult> ExportData([FromBody] ExportRequestDto request)
    {
        try
        {
            var data = await _dashboardService.GetMetricDataAsync(new MetricQueryRequest
            {
                MetricCode = request.MetricCode,
                StartTime = request.StartTime,
                EndTime = request.EndTime,
                AggregationType = request.AggregationType
            });

            // 使用EPPlus生成Excel
            using var package = new ExcelPackage();
            var worksheet = package.Workbook.Worksheets.Add("数据导出");

            // 设置表头
            worksheet.Cells[1, 1].Value = "时间";
            worksheet.Cells[1, 2].Value = "数值";
            worksheet.Cells[1, 3].Value = "质量";

            // 填充数据
            for (int i = 0; i < data.Count; i++)
            {
                worksheet.Cells[i + 2, 1].Value = data[i].Timestamp.ToString("yyyy-MM-dd HH:mm:ss");
                worksheet.Cells[i + 2, 2].Value = data[i].Value;
                worksheet.Cells[i + 2, 3].Value = data[i].Quality.ToString();
            }

            // 自动调整列宽
            worksheet.Cells.AutoFitColumns();

            var stream = new MemoryStream();
            package.SaveAs(stream);
            stream.Position = 0;

            var fileName = $"{request.MetricCode}_{DateTime.Now:yyyyMMddHHmmss}.xlsx";
            return File(stream, 
                "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", 
                fileName);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "导出数据失败");
            return BadRequest("导出数据失败");
        }
    }

    /// <summary>
    /// 获取数据源列表
    /// </summary>
    [HttpGet("data-sources")]
    public async Task<ApiResult<List<DataSourceDto>>> GetDataSources()
    {
        try
        {
            var sources = await _dashboardService.GetDataSourcesAsync();
            var result = sources.Select(s => new DataSourceDto
            {
                Id = s.Id,
                Name = s.Name,
                Type = s.Type,
                ConnectionString = s.ConnectionString,
                LastUpdate = s.LastUpdate,
                Status = s.Status
            }).ToList();

            return ApiResult<List<DataSourceDto>>.Success(result);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "获取数据源列表失败");
            return ApiResult<List<DataSourceDto>>.Error("获取数据源失败");
        }
    }
}

5.2 数据缓存服务

// Services/DataCacheService.cs
public class DataCacheService : IDataCacheService
{
    private readonly IDistributedCache _distributedCache;
    private readonly IMemoryCache _memoryCache;
    private readonly ILogger<DataCacheService> _logger;

    public DataCacheService(
        IDistributedCache distributedCache,
        IMemoryCache memoryCache,
        ILogger<DataCacheService> logger)
    {
        _distributedCache = distributedCache;
        _memoryCache = memoryCache;
        _logger = logger;
    }

    /// <summary>
    /// 获取或设置缓存数据（二级缓存策略）
    /// </summary>
    public async Task<T> GetOrSetAsync<T>(string key, Func<Task<T>> factory, 
        TimeSpan? memoryExpiration = null, TimeSpan? distributedExpiration = null)
    {
        // 第一级：内存缓存
        if (_memoryCache.TryGetValue(key, out T memoryCachedValue))
        {
            return memoryCachedValue;
        }

        // 第二级：分布式缓存
        var distributedCachedValue = await _distributedCache.GetAsync<T>(key);
        if (distributedCachedValue != null)
        {
            // 设置回内存缓存
            _memoryCache.Set(key, distributedCachedValue, 
                memoryExpiration ?? TimeSpan.FromMinutes(5));
            return distributedCachedValue;
        }

        // 缓存未命中，执行工厂方法获取数据
        try
        {
            var value = await factory();

            // 设置缓存
            if (value != null)
            {
                // 设置分布式缓存
                await _distributedCache.SetAsync(key, value, 
                    new DistributedCacheEntryOptions
                    {
                        AbsoluteExpirationRelativeToNow = distributedExpiration ?? TimeSpan.FromMinutes(30)
                    });

                // 设置内存缓存
                _memoryCache.Set(key, value, 
                    memoryExpiration ?? TimeSpan.FromMinutes(5));
            }

            return value;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"缓存获取失败: {key}");
            throw;
        }
    }

    /// <summary>
    /// 批量获取缓存数据
    /// </summary>
    public async Task<Dictionary<string, T>> GetBatchAsync<T>(IEnumerable<string> keys)
    {
        var result = new Dictionary<string, T>();
        var missingKeys = new List<string>();

        // 先尝试从内存缓存获取
        foreach (var key in keys)
        {
            if (_memoryCache.TryGetValue(key, out T value))
            {
                result[key] = value;
            }
            else
            {
                missingKeys.Add(key);
            }
        }

        // 从分布式缓存获取缺失的键
        if (missingKeys.Any())
        {
            var distributedValues = await _distributedCache.GetBatchAsync<T>(missingKeys);
            foreach (var kvp in distributedValues)
            {
                result[kvp.Key] = kvp.Value;
                
                // 设置回内存缓存
                _memoryCache.Set(kvp.Key, kvp.Value, TimeSpan.FromMinutes(5));
            }
        }

        return result;
    }

    /// <summary>
    /// 批量设置缓存数据
    /// </summary>
    public async Task SetBatchAsync<T>(Dictionary<string, T> items, TimeSpan? expiration = null)
    {
        var options = new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = expiration ?? TimeSpan.FromMinutes(30)
        };

        // 设置分布式缓存
        await _distributedCache.SetBatchAsync(items, options);

        // 设置内存缓存
        foreach (var kvp in items)
        {
            _memoryCache.Set(kvp.Key, kvp.Value, TimeSpan.FromMinutes(5));
        }
    }

    /// <summary>
    /// 删除缓存
    /// </summary>
    public async Task RemoveAsync(string key)
    {
        _memoryCache.Remove(key);
        await _distributedCache.RemoveAsync(key);
    }

    /// <summary>
    /// 批量删除缓存
    /// </summary>
    public async Task RemoveBatchAsync(IEnumerable<string> keys)
    {
        foreach (var key in keys)
        {
            _memoryCache.Remove(key);
        }
        
        await _distributedCache.RemoveBatchAsync(keys);
    }

    /// <summary>
    /// 获取缓存统计信息
    /// </summary>
    public async Task<CacheStatistics> GetStatisticsAsync()
    {
        var stats = new CacheStatistics
        {
            MemoryCacheCount = GetMemoryCacheCount(),
            MemoryCacheSize = GetMemoryCacheSize(),
            DistributedCacheInfo = await GetDistributedCacheInfoAsync()
        };

        return stats;
    }

    private long GetMemoryCacheCount()
    {
        // 注意：实际项目中可能需要使用更精确的方法
        return _memoryCache.GetCurrentStatistics()?.CurrentEntryCount ?? 0;
    }

    private long GetMemoryCacheSize()
    {
        // 估算内存使用量
        return GC.GetTotalMemory(false);
    }

    private async Task<DistributedCacheInfo> GetDistributedCacheInfoAsync()
    {
        // 根据使用的分布式缓存实现获取信息
        // 这里以Redis为例
        return await Task.FromResult(new DistributedCacheInfo
        {
            Type = "Redis",
            Status = "Connected",
            UsedMemory = 1024 * 1024 * 100, // 100MB
            KeysCount = 1000
        });
    }
}

// 缓存统计信息
public class CacheStatistics
{
    public long MemoryCacheCount { get; set; }
    public long MemoryCacheSize { get; set; }
    public DistributedCacheInfo DistributedCacheInfo { get; set; }
}

六、前端Vue3实现

6.1 大屏组件封装

<!-- src/components/Dashboard/DashboardContainer.vue -->
<template>
  <div class="dashboard-container" :class="theme">
    <div class="dashboard-header">
      <h1>{{ dashboardConfig.name }}</h1>
      <div class="header-controls">
        <el-button @click="refreshAll">刷新</el-button>
        <el-button @click="toggleFullscreen">全屏</el-button>
        <el-select v-model="refreshInterval" placeholder="刷新间隔">
          <el-option label="5秒" value="5"></el-option>
          <el-option label="10秒" value="10"></el-option>
          <el-option label="30秒" value="30"></el-option>
          <el-option label="1分钟" value="60"></el-option>
        </el-select>
      </div>
    </div>
    
    <div class="dashboard-grid" :style="gridStyle">
      <template v-for="widget in widgets" :key="widget.id">
        <dashboard-widget
          :widget="widget"
          :data="widgetData[widget.id]"
          @refresh="refreshWidget(widget)"
          @configure="configureWidget(widget)"
          @remove="removeWidget(widget)"
        />
      </template>
    </div>
    
    <dashboard-config-dialog
      v-model="showConfigDialog"
      :widget="selectedWidget"
      @save="saveWidgetConfig"
    />
  </div>
</template>

<script setup lang="ts">
import { ref, computed, onMounted, onUnmounted, watch } from 'vue'
import { ElMessage } from 'element-plus'
import DashboardWidget from './DashboardWidget.vue'
import DashboardConfigDialog from './DashboardConfigDialog.vue'
import { useDashboardStore } from '@/stores/dashboard'
import { useWebSocket } from '@/composables/useWebSocket'

const props = defineProps<{
  dashboardCode: string
}>()

const dashboardStore = useDashboardStore()
const { connect, subscribe, unsubscribe, send } = useWebSocket()

// 状态管理
const dashboardConfig = ref<any>(null)
const widgets = ref<any[]>([])
const widgetData = ref<Record<string, any>>({})
const refreshInterval = ref('30')
const showConfigDialog = ref(false)
const selectedWidget = ref<any>(null)

// 计算属性
const gridStyle = computed(() => {
  if (!dashboardConfig.value) return {}
  return {
    'grid-template-columns': `repeat(${dashboardConfig.value.layout.cols}, 1fr)`,
    'grid-auto-rows': dashboardConfig.value.layout.autoHeight ? 'auto' : '100px'
  }
})

const theme = computed(() => {
  return `theme-${dashboardConfig.value?.theme || 'default'}`
})

// 生命周期
onMounted(async () => {
  await loadDashboardConfig()
  await loadWidgets()
  setupWebSocket()
  startAutoRefresh()
})

onUnmounted(() => {
  stopAutoRefresh()
  disconnectWebSocket()
})

// 方法
const loadDashboardConfig = async () => {
  try {
    const response = await dashboardStore.fetchDashboardConfig(props.dashboardCode)
    dashboardConfig.value = response.data
  } catch (error) {
    ElMessage.error('加载大屏配置失败')
  }
}

const loadWidgets = async () => {
  try {
    const response = await dashboardStore.fetchWidgets(props.dashboardCode)
    widgets.value = response.data
    
    // 初始化数据
    for (const widget of widgets.value) {
      await loadWidgetData(widget)
    }
  } catch (error) {
    ElMessage.error('加载组件失败')
  }
}

const loadWidgetData = async (widget: any) => {
  try {
    const response = await dashboardStore.fetchWidgetData(widget.id, {
      startTime: new Date(Date.now() - 3600000), // 1小时前
      endTime: new Date(),
      aggregationType: widget.config.aggregationType || 'raw'
    })
    
    widgetData.value[widget.id] = response.data
  } catch (error) {
    console.error(`加载组件数据失败: ${widget.id}`, error)
  }
}

const setupWebSocket = () => {
  connect(`ws://${window.location.host}/dashboard-hub?dashboard=${props.dashboardCode}`)
  
  // 监听实时数据更新
  subscribe('MetricUpdate', (data) => {
    const { metricCode, data: metricData } = data
    
    // 更新对应组件的数据
    widgets.value.forEach(widget => {
      if (widget.dataSourceId === metricCode) {
        updateWidgetData(widget.id, metricData)
      }
    })
  })
}

const updateWidgetData = (widgetId: string, newData: any) => {
  if (!widgetData.value[widgetId]) {
    widgetData.value[widgetId] = []
  }
  
  // 添加新数据，保留最近100个点
  widgetData.value[widgetId].push(...newData)
  if (widgetData.value[widgetId].length > 100) {
    widgetData.value[widgetId] = widgetData.value[widgetId].slice(-100)
  }
}

const refreshAll = async () => {
  for (const widget of widgets.value) {
    await loadWidgetData(widget)
  }
  ElMessage.success('刷新完成')
}

const refreshWidget = async (widget: any) => {
  await loadWidgetData(widget)
}

const configureWidget = (widget: any) => {
  selectedWidget.value = widget
  showConfigDialog.value = true
}

const saveWidgetConfig = async (config: any) => {
  try {
    await dashboardStore.updateWidgetConfig(selectedWidget.value.id, config)
    ElMessage.success('配置保存成功')
    showConfigDialog.value = false
  } catch (error) {
    ElMessage.error('保存配置失败')
  }
}

const removeWidget = async (widget: any) => {
  try {
    await dashboardStore.deleteWidget(widget.id)
    widgets.value = widgets.value.filter(w => w.id !== widget.id)
    delete widgetData.value[widget.id]
    ElMessage.success('组件已删除')
  } catch (error) {
    ElMessage.error('删除组件失败')
  }
}

const toggleFullscreen = () => {
  const elem = document.documentElement
  if (!document.fullscreenElement) {
    elem.requestFullscreen().catch(err => {
      ElMessage.error(`全屏失败: ${err.message}`)
    })
  } else {
    document.exitFullscreen()
  }
}

// 自动刷新
let refreshTimer: number | null = null

const startAutoRefresh = () => {
  const interval = parseInt(refreshInterval.value) * 1000
  refreshTimer = window.setInterval(() => {
    refreshAll()
  }, interval)
}

const stopAutoRefresh = () => {
  if (refreshTimer) {
    clearInterval(refreshTimer)
    refreshTimer = null
  }
}

// 监听刷新间隔变化
watch(refreshInterval, () => {
  stopAutoRefresh()
  startAutoRefresh()
})
</script>

<style scoped>
.dashboard-container {
  width: 100%;
  height: 100vh;
  background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
  padding: 20px;
  overflow: auto;
}

.dashboard-header {
  display: flex;
  justify-content: space-between;
  align-items: center;
  margin-bottom: 20px;
  color: white;
}

.dashboard-header h1 {
  margin: 0;
  font-size: 24px;
  font-weight: bold;
}

.header-controls {
  display: flex;
  gap: 10px;
}

.dashboard-grid {
  display: grid;
  gap: 20px;
  grid-template-columns: repeat(12, 1fr);
}

/* 主题样式 */
.theme-default {
  background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
}

.theme-dark {
  background: linear-gradient(135deg, #2c3e50 0%, #4a6491 100%);
}

.theme-light {
  background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
}
</style>

6.2 ECharts图表组件

<!-- src/components/Charts/EChartsLineChart.vue -->
<template>
  <div ref="chartRef" :style="{ width: width, height: height }"></div>
</template>

<script setup lang="ts">
import { ref, onMounted, onUnmounted, watch, computed } from 'vue'
import * as echarts from 'echarts'
import type { ECharts, EChartsOption } from 'echarts'

interface Props {
  data: Array<{ timestamp: string; value: number }>
  title?: string
  width?: string
  height?: string
  color?: string
  showLegend?: boolean
  smooth?: boolean
  areaStyle?: boolean
}

const props = withDefaults(defineProps<Props>(), {
  width: '100%',
  height: '400px',
  color: '#5470c6',
  showLegend: true,
  smooth: true,
  areaStyle: false
})

const chartRef = ref<HTMLElement>()
let chartInstance: ECharts | null = null

const chartOptions = computed<EChartsOption>(() => {
  const xAxisData = props.data.map(item => item.timestamp)
  const seriesData = props.data.map(item => item.value)

  return {
    title: {
      text: props.title,
      left: 'center',
      textStyle: {
        color: '#333',
        fontSize: 16,
        fontWeight: 'bold'
      }
    },
    tooltip: {
      trigger: 'axis',
      formatter: (params: any) => {
        const param = params[0]
        return `
          <div style="font-size: 14px; margin-bottom: 5px">
            ${param.axisValue}
          </div>
          <div style="display: flex; align-items: center">
            <span style="display: inline-block; width: 10px; height: 10px; 
              background: ${param.color}; border-radius: 50%; margin-right: 5px">
            </span>
            ${param.seriesName}: ${param.value}
          </div>
        `
      }
    },
    legend: props.showLegend ? {
      data: ['数据'],
      top: '30',
      textStyle: {
        color: '#666'
      }
    } : undefined,
    grid: {
      left: '3%',
      right: '4%',
      bottom: '3%',
      top: props.showLegend ? '60' : '40',
      containLabel: true
    },
    xAxis: {
      type: 'category',
      data: xAxisData,
      axisLine: {
        lineStyle: {
          color: '#ccc'
        }
      },
      axisLabel: {
        color: '#666',
        formatter: (value: string) => {
          const date = new Date(value)
          return `${date.getHours().toString().padStart(2, '0')}:${date.getMinutes().toString().padStart(2, '0')}`
        }
      }
    },
    yAxis: {
      type: 'value',
      axisLine: {
        lineStyle: {
          color: '#ccc'
        }
      },
      axisLabel: {
        color: '#666'
      },
      splitLine: {
        lineStyle: {
          color: '#f0f0f0'
        }
      }
    },
    series: [
      {
        name: '数据',
        type: 'line',
        data: seriesData,
        smooth: props.smooth,
        symbol: 'circle',
        symbolSize: 6,
        itemStyle: {
          color: props.color
        },
        lineStyle: {
          width: 3,
          color: props.color
        },
        areaStyle: props.areaStyle ? {
          color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [
            {
              offset: 0,
              color: `${props.color}40`
            },
            {
              offset: 1,
              color: `${props.color}10`
            }
          ])
        } : undefined
      }
    ],
    dataZoom: [
      {
        type: 'inside',
        start: 0,
        end: 100
      },
      {
        show: true,
        type: 'slider',
        top: '90%',
        start: 0,
        end: 100
      }
    ]
  }
})

// 监听数据变化
watch(() => props.data, () => {
  if (chartInstance) {
    chartInstance.setOption(chartOptions.value)
  }
}, { deep: true })

// 初始化图表
const initChart = () => {
  if (!chartRef.value) return
  
  chartInstance = echarts.init(chartRef.value)
  chartInstance.setOption(chartOptions.value)
  
  // 窗口大小变化时重新渲染
  window.addEventListener('resize', handleResize)
}

const handleResize = () => {
  if (chartInstance) {
    chartInstance.resize()
  }
}

onMounted(() => {
  initChart()
})

onUnmounted(() => {
  if (chartInstance) {
    chartInstance.dispose()
    window.removeEventListener('resize', handleResize)
  }
})

// 导出方法供父组件调用
defineExpose({
  getInstance: () => chartInstance,
  resize: () => chartInstance?.resize()
})
</script>

SQLSugar作为.NET生态中的高性能ORM框架，通过其在大数据处理、系统监控、数据库适配、桌面应用和可视化大屏等多个场景的卓越表现，证明了其在实际企业级应用中的价值。通过本文的五篇技术文章和本总结，读者可以获得：