using GSModbus.Config;
|
using SqlSugar;
|
using System.Text.Json;
|
|
namespace GSModbus.Database
|
{
|
/// <summary>
|
/// 数据库管理器 - 管理SqlSugar数据库连接和操作
|
/// </summary>
|
public class DatabaseManager : IDisposable
|
{
|
#region 私有字段
|
|
private ISqlSugarClient? _db;
|
private readonly DatabaseConfig _config;
|
private readonly string _projectName;
|
private readonly string _plcIpAddress;
|
private bool _disposed = false;
|
|
#endregion
|
|
#region 事件
|
|
/// <summary>
|
/// 数据库操作日志事件
|
/// </summary>
|
public event EventHandler<string>? DatabaseLogOccurred;
|
|
/// <summary>
|
/// 数据库错误事件
|
/// </summary>
|
public event EventHandler<string>? DatabaseErrorOccurred;
|
|
#endregion
|
|
#region 构造函数
|
|
/// <summary>
|
/// 初始化数据库管理器
|
/// </summary>
|
/// <param name="config">数据库配置</param>
|
/// <param name="projectName">项目名称</param>
|
/// <param name="plcIpAddress">PLC IP地址</param>
|
public DatabaseManager(DatabaseConfig config, string projectName, string plcIpAddress)
|
{
|
_config = config ?? throw new ArgumentNullException(nameof(config));
|
_projectName = projectName ?? string.Empty;
|
_plcIpAddress = plcIpAddress ?? string.Empty;
|
|
if (_config.Enabled)
|
{
|
InitializeDatabase();
|
}
|
}
|
|
#endregion
|
|
#region 公共属性
|
|
/// <summary>
|
/// 数据库是否已启用
|
/// </summary>
|
public bool IsEnabled => _config.Enabled;
|
|
/// <summary>
|
/// 数据库是否已连接
|
/// </summary>
|
public bool IsConnected => _db != null;
|
|
#endregion
|
|
#region 初始化方法
|
|
/// <summary>
|
/// 初始化数据库连接
|
/// </summary>
|
private void InitializeDatabase()
|
{
|
try
|
{
|
// 根据数据库类型创建SqlSugarClient
|
var dbType = GetDbType(_config.Type);
|
|
_db = new SqlSugarClient(new SqlSugar.ConnectionConfig()
|
{
|
ConnectionString = _config.ConnectionString,
|
DbType = dbType,
|
IsAutoCloseConnection = true,
|
|
// 配置SQL日志
|
MoreSettings = new ConnMoreSettings()
|
{
|
IsAutoRemoveDataCache = true
|
}
|
});
|
|
// 配置日志事件
|
_db.Aop.OnLogExecuting = (sql, pars) =>
|
{
|
OnDatabaseLog($"执行SQL: {sql}");
|
if (pars?.Any() == true)
|
{
|
OnDatabaseLog($"参数: {string.Join(", ", pars.Select(p => $"{p.ParameterName}={p.Value}"))}");
|
}
|
};
|
|
// 测试连接
|
var version = _db.Ado.GetString("SELECT @@VERSION");
|
OnDatabaseLog($"数据库连接成功，版本: {version?.Substring(0, Math.Min(version.Length, 50))}...");
|
|
// 自动创建表
|
if (_config.AutoCreateTables)
|
{
|
CreateTablesIfNotExists();
|
}
|
|
OnDatabaseLog("数据库管理器初始化完成");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"初始化数据库失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 获取SqlSugar数据库类型
|
/// </summary>
|
private DbType GetDbType(string dbType)
|
{
|
return dbType.ToLower() switch
|
{
|
"sqlserver" => DbType.SqlServer,
|
"mysql" => DbType.MySql,
|
"oracle" => DbType.Oracle,
|
"sqlite" => DbType.Sqlite,
|
"postgresql" => DbType.PostgreSQL,
|
_ => throw new NotSupportedException($"不支持的数据库类型: {dbType}")
|
};
|
}
|
|
/// <summary>
|
/// 创建数据库表（如果不存在）
|
/// </summary>
|
private void CreateTablesIfNotExists()
|
{
|
if (_db == null) return;
|
|
try
|
{
|
OnDatabaseLog("开始检查和创建数据库表...");
|
|
// 创建ModbusData表
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ModbusDataEntity));
|
OnDatabaseLog($"ModbusData表检查完成，表名: {_config.Tables.ModbusDataTable}");
|
|
// 创建CommunicationLog表
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CommunicationLogEntity));
|
OnDatabaseLog($"CommunicationLog表检查完成，表名: {_config.Tables.CommunicationLogTable}");
|
|
// 创建ErrorLog表
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ErrorLogEntity));
|
OnDatabaseLog($"ErrorLog表检查完成，表名: {_config.Tables.ErrorLogTable}");
|
|
// 创建Statistics表
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(StatisticsEntity));
|
OnDatabaseLog($"Statistics表检查完成，表名: {_config.Tables.StatisticsTable}");
|
|
OnDatabaseLog("所有数据库表检查完成");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"创建数据库表失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
#endregion
|
|
#region Modbus数据操作
|
|
/// <summary>
|
/// 保存Modbus数据到数据库
|
/// </summary>
|
/// <param name="data">动态Modbus数据</param>
|
/// <returns>是否保存成功</returns>
|
public async Task<bool> SaveModbusDataAsync(DynamicModbusData data)
|
{
|
if (!IsEnabled || _db == null || data == null) return false;
|
|
try
|
{
|
var entity = new ModbusDataEntity
|
{
|
ReadTime = data.ReadTime,
|
ProjectName = _projectName,
|
PlcIpAddress = _plcIpAddress,
|
CreatedAt = DateTime.Now
|
};
|
|
// 序列化各类别数据
|
var controlSignals = GetFieldsByCategory(data, "ControlSignals");
|
var productData = GetFieldsByCategory(data, "ProductData");
|
var measurementData = GetFieldsByCategory(data, "MeasurementData");
|
|
entity.ControlSignalsJson = controlSignals.Any() ? JsonSerializer.Serialize(controlSignals) : null;
|
entity.ProductDataJson = productData.Any() ? JsonSerializer.Serialize(productData) : null;
|
entity.MeasurementDataJson = measurementData.Any() ? JsonSerializer.Serialize(measurementData) : null;
|
|
// 原始寄存器数据（调试用）
|
if (data.RawRegisters?.Any() == true)
|
{
|
entity.RawRegistersJson = JsonSerializer.Serialize(data.RawRegisters);
|
}
|
|
var result = await _db.Insertable(entity).ExecuteCommandAsync();
|
|
if (result > 0)
|
{
|
OnDatabaseLog($"保存Modbus数据成功，ID: {entity.Id}");
|
return true;
|
}
|
else
|
{
|
OnDatabaseError("保存Modbus数据失败，返回结果为0");
|
return false;
|
}
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"保存Modbus数据异常: {ex.Message}");
|
return false;
|
}
|
}
|
|
/// <summary>
|
/// 批量保存Modbus数据
|
/// </summary>
|
/// <param name="dataList">数据列表</param>
|
/// <returns>保存成功的数量</returns>
|
public async Task<int> SaveModbusDataBatchAsync(IEnumerable<DynamicModbusData> dataList)
|
{
|
if (!IsEnabled || _db == null || !dataList.Any()) return 0;
|
|
try
|
{
|
var entities = dataList.Select(data => new ModbusDataEntity
|
{
|
ReadTime = data.ReadTime,
|
ProjectName = _projectName,
|
PlcIpAddress = _plcIpAddress,
|
ControlSignalsJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "ControlSignals")),
|
ProductDataJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "ProductData")),
|
MeasurementDataJson = JsonSerializer.Serialize(GetFieldsByCategory(data, "MeasurementData")),
|
RawRegistersJson = data.RawRegisters?.Any() == true ? JsonSerializer.Serialize(data.RawRegisters) : null,
|
CreatedAt = DateTime.Now
|
}).ToList();
|
|
var result = await _db.Insertable(entities).ExecuteCommandAsync();
|
OnDatabaseLog($"批量保存Modbus数据完成，保存 {result} 条记录");
|
return result;
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"批量保存Modbus数据异常: {ex.Message}");
|
return 0;
|
}
|
}
|
|
#endregion
|
|
#region 日志操作
|
|
/// <summary>
|
/// 记录通信日志
|
/// </summary>
|
/// <param name="eventType">事件类型</param>
|
/// <param name="description">事件描述</param>
|
/// <param name="isSuccess">是否成功</param>
|
/// <param name="durationMs">耗时毫秒</param>
|
/// <param name="additionalData">附加数据</param>
|
public async Task LogCommunicationAsync(string eventType, string? description = null,
|
bool isSuccess = true, int? durationMs = null, object? additionalData = null)
|
{
|
if (!IsEnabled || _db == null) return;
|
|
try
|
{
|
var entity = new CommunicationLogEntity
|
{
|
LogTime = DateTime.Now,
|
ProjectName = _projectName,
|
PlcIpAddress = _plcIpAddress,
|
EventType = eventType,
|
EventDescription = description,
|
IsSuccess = isSuccess,
|
DurationMs = durationMs ?? 0,
|
AdditionalDataJson = additionalData != null ? JsonSerializer.Serialize(additionalData) : null
|
};
|
|
await _db.Insertable(entity).ExecuteCommandAsync();
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"记录通信日志异常: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 记录错误日志
|
/// </summary>
|
/// <param name="errorType">错误类型</param>
|
/// <param name="errorMessage">错误消息</param>
|
/// <param name="exception">异常对象</param>
|
/// <param name="severity">严重级别</param>
|
/// <param name="retryCount">重试次数</param>
|
public async Task LogErrorAsync(string errorType, string errorMessage,
|
Exception? exception = null, string severity = ErrorSeverity.Medium, int retryCount = 0)
|
{
|
if (!IsEnabled || _db == null) return;
|
|
try
|
{
|
var entity = new ErrorLogEntity
|
{
|
ErrorTime = DateTime.Now,
|
ProjectName = _projectName,
|
PlcIpAddress = _plcIpAddress,
|
ErrorType = errorType,
|
ErrorMessage = errorMessage,
|
StackTrace = exception?.StackTrace,
|
Severity = severity,
|
RetryCount = retryCount,
|
IsResolved = false
|
};
|
|
await _db.Insertable(entity).ExecuteCommandAsync();
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"记录错误日志异常: {ex.Message}");
|
}
|
}
|
|
#endregion
|
|
#region 统计操作
|
|
/// <summary>
|
/// 保存统计数据
|
/// </summary>
|
/// <param name="stats">通信统计数据</param>
|
/// <param name="periodStart">统计周期开始时间</param>
|
/// <param name="periodEnd">统计周期结束时间</param>
|
public async Task SaveStatisticsAsync(CommunicationStats stats, DateTime periodStart, DateTime periodEnd)
|
{
|
if (!IsEnabled || _db == null || stats == null) return;
|
|
try
|
{
|
var entity = new StatisticsEntity
|
{
|
StatisticsTime = DateTime.Now,
|
ProjectName = _projectName,
|
PlcIpAddress = _plcIpAddress,
|
TotalConnectionAttempts = (int)stats.TotalConnectionAttempts,
|
SuccessfulConnections = (int)stats.SuccessfulConnections,
|
TotalDataReads = (int)stats.TotalDataReads,
|
SuccessfulDataReads = (int)stats.SuccessfulReads,
|
TotalErrors = (int)stats.TotalErrors,
|
AverageResponseTimeMs = stats.AverageResponseTimeMs,
|
MaxResponseTimeMs = stats.MaxResponseTimeMs,
|
MinResponseTimeMs = stats.MinResponseTimeMs,
|
ConnectionSuccessRate = stats.ConnectionSuccessRate,
|
DataReadSuccessRate = stats.DataReadSuccessRate,
|
PeriodStart = periodStart,
|
PeriodEnd = periodEnd
|
};
|
|
await _db.Insertable(entity).ExecuteCommandAsync();
|
OnDatabaseLog("统计数据保存成功");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"保存统计数据异常: {ex.Message}");
|
}
|
}
|
|
#endregion
|
|
#region 数据查询
|
|
/// <summary>
|
/// 查询最近的Modbus数据
|
/// </summary>
|
/// <param name="count">数量</param>
|
/// <returns>数据列表</returns>
|
public async Task<List<ModbusDataEntity>> GetRecentModbusDataAsync(int count = 100)
|
{
|
if (!IsEnabled || _db == null) return new List<ModbusDataEntity>();
|
|
try
|
{
|
return await _db.Queryable<ModbusDataEntity>()
|
.Where(x => x.ProjectName == _projectName)
|
.OrderBy(x => x.ReadTime, OrderByType.Desc)
|
.Take(count)
|
.ToListAsync();
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"查询Modbus数据异常: {ex.Message}");
|
return new List<ModbusDataEntity>();
|
}
|
}
|
|
/// <summary>
|
/// 查询指定时间范围的数据统计
|
/// </summary>
|
/// <param name="startTime">开始时间</param>
|
/// <param name="endTime">结束时间</param>
|
/// <returns>统计信息</returns>
|
public async Task<Dictionary<string, object>> GetDataStatisticsAsync(DateTime startTime, DateTime endTime)
|
{
|
if (!IsEnabled || _db == null) return new Dictionary<string, object>();
|
|
try
|
{
|
var stats = new Dictionary<string, object>();
|
|
// Modbus数据统计
|
var dataCount = await _db.Queryable<ModbusDataEntity>()
|
.Where(x => x.ProjectName == _projectName && x.ReadTime >= startTime && x.ReadTime <= endTime)
|
.CountAsync();
|
|
// 错误统计
|
var errorCount = await _db.Queryable<ErrorLogEntity>()
|
.Where(x => x.ProjectName == _projectName && x.ErrorTime >= startTime && x.ErrorTime <= endTime)
|
.CountAsync();
|
|
// 通信事件统计
|
var logCount = await _db.Queryable<CommunicationLogEntity>()
|
.Where(x => x.ProjectName == _projectName && x.LogTime >= startTime && x.LogTime <= endTime)
|
.CountAsync();
|
|
stats["DataCount"] = dataCount;
|
stats["ErrorCount"] = errorCount;
|
stats["LogCount"] = logCount;
|
stats["Period"] = $"{startTime:yyyy-MM-dd HH:mm:ss} - {endTime:yyyy-MM-dd HH:mm:ss}";
|
|
return stats;
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"查询数据统计异常: {ex.Message}");
|
return new Dictionary<string, object>();
|
}
|
}
|
|
#endregion
|
|
#region 数据维护
|
|
/// <summary>
|
/// 清理过期数据
|
/// </summary>
|
/// <returns>清理的记录数</returns>
|
public async Task<int> CleanupOldDataAsync()
|
{
|
if (!IsEnabled || _db == null || _config.DataRetentionDays <= 0) return 0;
|
|
try
|
{
|
var cutoffDate = DateTime.Now.AddDays(-_config.DataRetentionDays);
|
var totalDeleted = 0;
|
|
// 清理Modbus数据
|
var deletedData = await _db.Deleteable<ModbusDataEntity>()
|
.Where(x => x.ProjectName == _projectName && x.ReadTime < cutoffDate)
|
.ExecuteCommandAsync();
|
|
// 清理通信日志
|
var deletedLogs = await _db.Deleteable<CommunicationLogEntity>()
|
.Where(x => x.ProjectName == _projectName && x.LogTime < cutoffDate)
|
.ExecuteCommandAsync();
|
|
// 清理已解决的错误日志
|
var deletedErrors = await _db.Deleteable<ErrorLogEntity>()
|
.Where(x => x.ProjectName == _projectName && x.ErrorTime < cutoffDate && x.IsResolved)
|
.ExecuteCommandAsync();
|
|
totalDeleted = deletedData + deletedLogs + deletedErrors;
|
|
if (totalDeleted > 0)
|
{
|
OnDatabaseLog($"数据清理完成，删除 {totalDeleted} 条记录（数据: {deletedData}, 日志: {deletedLogs}, 错误: {deletedErrors}）");
|
}
|
|
return totalDeleted;
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"清理过期数据异常: {ex.Message}");
|
return 0;
|
}
|
}
|
|
#endregion
|
|
#region 辅助方法
|
|
/// <summary>
|
/// 从动态数据中获取指定类别的字段
|
/// </summary>
|
private Dictionary<string, object?> GetFieldsByCategory(DynamicModbusData data, string category)
|
{
|
var result = new Dictionary<string, object?>();
|
|
if (data.SourceConfiguration?.InputAddresses == null) return result;
|
|
Dictionary<string, DataField>? fields = category switch
|
{
|
"ControlSignals" => data.SourceConfiguration.InputAddresses.ControlSignals,
|
"ProductData" => data.SourceConfiguration.InputAddresses.ProductData,
|
"MeasurementData" => data.SourceConfiguration.InputAddresses.MeasurementData,
|
_ => null
|
};
|
|
if (fields != null)
|
{
|
foreach (var field in fields)
|
{
|
if (data.HasField(field.Key))
|
{
|
result[field.Key] = data.GetFieldValue(field.Key);
|
}
|
}
|
}
|
|
return result;
|
}
|
|
/// <summary>
|
/// 触发数据库日志事件
|
/// </summary>
|
private void OnDatabaseLog(string message)
|
{
|
DatabaseLogOccurred?.Invoke(this, $"[数据库] {message}");
|
}
|
|
/// <summary>
|
/// 触发数据库错误事件
|
/// </summary>
|
private void OnDatabaseError(string errorMessage)
|
{
|
DatabaseErrorOccurred?.Invoke(this, $"[数据库错误] {errorMessage}");
|
}
|
|
#endregion
|
|
#region IDisposable实现
|
|
public void Dispose()
|
{
|
Dispose(true);
|
GC.SuppressFinalize(this);
|
}
|
|
protected virtual void Dispose(bool disposing)
|
{
|
if (!_disposed)
|
{
|
if (disposing)
|
{
|
try
|
{
|
_db?.Dispose();
|
OnDatabaseLog("数据库连接已关闭");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"关闭数据库连接异常: {ex.Message}");
|
}
|
}
|
|
_db = null;
|
_disposed = true;
|
}
|
}
|
|
#endregion
|
}
|
}
|
