using GSModbus.Config;
|
using SqlSugar;
|
using System.Text.Json;
|
using Oracle.ManagedDataAccess.Client;
|
|
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
|
{
|
OnDatabaseLog("开始初始化数据库连接...");
|
|
// 对Oracle特殊处理，避免SqlSugar语法问题
|
if (_config.Type.ToLower() == "oracle")
|
{
|
InitializeOracleDirectly();
|
}
|
else
|
{
|
InitializeWithSqlSugar();
|
}
|
|
OnDatabaseLog("数据库管理器初始化完成");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"初始化数据库失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 直接初始化Oracle数据库
|
/// </summary>
|
private void InitializeOracleDirectly()
|
{
|
try
|
{
|
OnDatabaseLog("使用Oracle原生方式初始化...");
|
|
// 先测试连接
|
using (var testConnection = new OracleConnection(_config.ConnectionString))
|
{
|
testConnection.Open();
|
OnDatabaseLog("Oracle数据库连接测试成功");
|
testConnection.Close();
|
}
|
|
// 创建SqlSugar客户端（只用于数据操作，不用于连接测试）
|
var dbType = GetDbType(_config.Type);
|
_db = new SqlSugarClient(new SqlSugar.ConnectionConfig()
|
{
|
ConnectionString = _config.ConnectionString,
|
DbType = dbType,
|
IsAutoCloseConnection = true,
|
MoreSettings = new ConnMoreSettings()
|
{
|
IsAutoRemoveDataCache = true
|
}
|
});
|
|
OnDatabaseLog("SqlSugar客户端创建成功");
|
|
// 自动创建表（使用直接SQL方式）
|
if (_config.AutoCreateTables)
|
{
|
CreateTablesIfNotExists();
|
}
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"Oracle数据库初始化失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 使用SqlSugar初始化其他数据库
|
/// </summary>
|
private void InitializeWithSqlSugar()
|
{
|
// 根据数据库类型创建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();
|
}
|
}
|
|
/// <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("开始检查和创建数据库表...");
|
|
// 对Oracle使用直接SQL建表，避免SqlSugar语法问题
|
if (GetDbType(_config.Type) == DbType.Oracle)
|
{
|
CreateOracleTablesDirectly();
|
}
|
else
|
{
|
// 其他数据库使用SqlSugar CodeFirst
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ModbusDataEntity));
|
OnDatabaseLog($"ModbusData表检查完成，表名: {_config.Tables.ModbusDataTable}");
|
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CommunicationLogEntity));
|
OnDatabaseLog($"CommunicationLog表检查完成，表名: {_config.Tables.CommunicationLogTable}");
|
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ErrorLogEntity));
|
OnDatabaseLog($"ErrorLog表检查完成，表名: {_config.Tables.ErrorLogTable}");
|
|
_db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(StatisticsEntity));
|
OnDatabaseLog($"Statistics表检查完成，表名: {_config.Tables.StatisticsTable}");
|
}
|
|
OnDatabaseLog("所有数据库表检查完成");
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"创建数据库表失败: {ex.Message}");
|
throw;
|
}
|
}
|
|
/// <summary>
|
/// 直接使用SQL为Oracle创建表
|
/// </summary>
|
private void CreateOracleTablesDirectly()
|
{
|
try
|
{
|
OnDatabaseLog("开始使用原生Oracle SQL创建表...");
|
|
using (var connection = new OracleConnection(_config.ConnectionString))
|
{
|
connection.Open();
|
OnDatabaseLog("Oracle连接已打开，开始执行建表脚本...");
|
|
// 1. 创建ModbusData表（主要数据表）
|
var createModbusDataTable = $@"
|
BEGIN
|
EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.ModbusDataTable} (
|
ID NUMBER PRIMARY KEY,
|
READ_TIME TIMESTAMP NOT NULL,
|
PROJECT_NAME VARCHAR2(100) NOT NULL,
|
PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
|
CONTROL_SIGNALS_JSON CLOB,
|
PRODUCT_DATA_JSON CLOB,
|
MEASUREMENT_DATA_JSON CLOB,
|
RAW_REGISTERS_JSON CLOB,
|
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
|
)';
|
EXCEPTION
|
WHEN OTHERS THEN
|
IF SQLCODE != -955 THEN -- -955 = table already exists
|
RAISE;
|
END IF;
|
END;";
|
|
using (var cmd = new OracleCommand(createModbusDataTable, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
OnDatabaseLog($"ModbusData表检查完成，表名: {_config.Tables.ModbusDataTable}");
|
|
// 2. 创建CommunicationLog表
|
var createCommLogTable = $@"
|
BEGIN
|
EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.CommunicationLogTable} (
|
ID NUMBER PRIMARY KEY,
|
LOG_TIME TIMESTAMP NOT NULL,
|
PROJECT_NAME VARCHAR2(100) NOT NULL,
|
PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
|
EVENT_TYPE VARCHAR2(50) NOT NULL,
|
EVENT_DESCRIPTION VARCHAR2(500),
|
IS_SUCCESS NUMBER(1) DEFAULT 1,
|
DURATION_MS NUMBER,
|
ADDITIONAL_DATA_JSON CLOB
|
)';
|
EXCEPTION
|
WHEN OTHERS THEN
|
IF SQLCODE != -955 THEN
|
RAISE;
|
END IF;
|
END;";
|
|
using (var cmd = new OracleCommand(createCommLogTable, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
OnDatabaseLog($"CommunicationLog表检查完成，表名: {_config.Tables.CommunicationLogTable}");
|
|
// 3. 创建ErrorLog表
|
var createErrorLogTable = $@"
|
BEGIN
|
EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.ErrorLogTable} (
|
ID NUMBER PRIMARY KEY,
|
ERROR_TIME TIMESTAMP NOT NULL,
|
PROJECT_NAME VARCHAR2(100) NOT NULL,
|
PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
|
ERROR_TYPE VARCHAR2(100) NOT NULL,
|
ERROR_MESSAGE VARCHAR2(1000) NOT NULL,
|
STACK_TRACE CLOB,
|
SEVERITY VARCHAR2(20) DEFAULT ''Medium'',
|
RETRY_COUNT NUMBER DEFAULT 0,
|
IS_RESOLVED NUMBER(1) DEFAULT 0
|
)';
|
EXCEPTION
|
WHEN OTHERS THEN
|
IF SQLCODE != -955 THEN
|
RAISE;
|
END IF;
|
END;";
|
|
using (var cmd = new OracleCommand(createErrorLogTable, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
OnDatabaseLog($"ErrorLog表检查完成，表名: {_config.Tables.ErrorLogTable}");
|
|
// 4. 创建Statistics表
|
var createStatsTable = $@"
|
BEGIN
|
EXECUTE IMMEDIATE 'CREATE TABLE {_config.Tables.StatisticsTable} (
|
ID NUMBER PRIMARY KEY,
|
STATISTICS_TIME TIMESTAMP NOT NULL,
|
PROJECT_NAME VARCHAR2(100) NOT NULL,
|
PLC_IP_ADDRESS VARCHAR2(50) NOT NULL,
|
TOTAL_CONNECTION_ATTEMPTS NUMBER NOT NULL,
|
SUCCESSFUL_CONNECTIONS NUMBER NOT NULL,
|
TOTAL_DATA_READS NUMBER NOT NULL,
|
SUCCESSFUL_DATA_READS NUMBER NOT NULL,
|
TOTAL_ERRORS NUMBER NOT NULL,
|
AVERAGE_RESPONSE_TIME_MS NUMBER NOT NULL,
|
MAX_RESPONSE_TIME_MS NUMBER NOT NULL,
|
MIN_RESPONSE_TIME_MS NUMBER NOT NULL,
|
CONNECTION_SUCCESS_RATE NUMBER NOT NULL,
|
DATA_READ_SUCCESS_RATE NUMBER NOT NULL,
|
PERIOD_START TIMESTAMP NOT NULL,
|
PERIOD_END TIMESTAMP NOT NULL
|
)';
|
EXCEPTION
|
WHEN OTHERS THEN
|
IF SQLCODE != -955 THEN
|
RAISE;
|
END IF;
|
END;";
|
|
using (var cmd = new OracleCommand(createStatsTable, connection))
|
{
|
cmd.ExecuteNonQuery();
|
}
|
OnDatabaseLog($"Statistics表检查完成，表名: {_config.Tables.StatisticsTable}");
|
|
connection.Close();
|
OnDatabaseLog("所有Oracle表创建完成，连接已关闭");
|
}
|
}
|
catch (Exception ex)
|
{
|
OnDatabaseError($"Oracle建表失败: {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>
|
/// <returns>连接测试结果</returns>
|
public async Task<(bool Success, string Message)> TestConnectionAsync()
|
{
|
try
|
{
|
if (!_config.Enabled)
|
{
|
return (false, "数据库功能已禁用");
|
}
|
|
if (string.IsNullOrEmpty(_config.ConnectionString))
|
{
|
return (false, "数据库连接字符串为空");
|
}
|
|
// 对于Oracle数据库，使用原生连接方式测试
|
if (_config.Type.ToLower() == "oracle")
|
{
|
return await TestOracleConnectionAsync();
|
}
|
|
// 创建临时连接进行测试
|
var dbType = GetDbType(_config.Type);
|
using var testDb = new SqlSugarClient(new SqlSugar.ConnectionConfig()
|
{
|
ConnectionString = _config.ConnectionString,
|
DbType = dbType,
|
IsAutoCloseConnection = true
|
});
|
|
// 执行简单查询测试连接
|
string testQuery = dbType switch
|
{
|
DbType.SqlServer => "SELECT 1",
|
DbType.MySql => "SELECT 1",
|
DbType.Sqlite => "SELECT 1",
|
DbType.PostgreSQL => "SELECT 1",
|
_ => "SELECT 1"
|
};
|
|
var result = await testDb.Ado.GetScalarAsync(testQuery);
|
|
if (result != null)
|
{
|
return (true, $"连接成功！数据库类型: {_config.Type}");
|
}
|
else
|
{
|
return (false, "连接测试查询返回空结果");
|
}
|
}
|
catch (Exception ex)
|
{
|
return (false, $"连接失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 测试Oracle数据库连接的专用方法
|
/// </summary>
|
private async Task<(bool Success, string Message)> TestOracleConnectionAsync()
|
{
|
try
|
{
|
using var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(_config.ConnectionString);
|
await connection.OpenAsync();
|
|
using var command = connection.CreateCommand();
|
command.CommandText = "SELECT SYSDATE FROM DUAL";
|
var result = await command.ExecuteScalarAsync();
|
|
if (result != null)
|
{
|
await connection.CloseAsync();
|
return (true, $"Oracle连接成功！当前时间: {result}");
|
}
|
else
|
{
|
return (false, "Oracle连接测试查询返回空结果");
|
}
|
}
|
catch (Exception ex)
|
{
|
return (false, $"Oracle连接失败: {ex.Message}");
|
}
|
}
|
|
/// <summary>
|
/// 静态方法：测试数据库连接（用于配置验证）
|
/// </summary>
|
/// <param name="config">数据库配置</param>
|
/// <returns>连接测试结果</returns>
|
public static async Task<(bool Success, string Message)> TestConnectionAsync(DatabaseConfig config)
|
{
|
if (config == null)
|
{
|
return (false, "数据库配置为空");
|
}
|
|
if (!config.Enabled)
|
{
|
return (false, "数据库功能已禁用");
|
}
|
|
if (string.IsNullOrEmpty(config.ConnectionString))
|
{
|
return (false, "数据库连接字符串为空");
|
}
|
|
try
|
{
|
// 对于Oracle，直接使用原生连接
|
if (config.Type.ToLower() == "oracle")
|
{
|
using var connection = new OracleConnection(config.ConnectionString);
|
await connection.OpenAsync();
|
|
using var command = connection.CreateCommand();
|
command.CommandText = "SELECT SYSDATE FROM DUAL";
|
var result = await command.ExecuteScalarAsync();
|
|
if (result != null)
|
{
|
await connection.CloseAsync();
|
return (true, $"Oracle连接成功！当前时间: {result}");
|
}
|
else
|
{
|
return (false, "Oracle连接测试查询返回空结果");
|
}
|
}
|
else
|
{
|
// 其他数据库使用SqlSugar
|
var tempManager = new DatabaseManager(config, "TestConnection", "127.0.0.1");
|
try
|
{
|
return await tempManager.TestConnectionAsync();
|
}
|
finally
|
{
|
tempManager.Dispose();
|
}
|
}
|
}
|
catch (Exception ex)
|
{
|
return (false, $"连接失败: {ex.Message}");
|
}
|
}
|
|
#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
|
}
|
}
|
