From 038587a4ff652278fe22c2d03c1ebb602b95c5f0 Mon Sep 17 00:00:00 2001
From: 啊鑫 <t2856754968@163.com>
Date: 星期二, 22 七月 2025 12:54:49 +0800
Subject: [PATCH] 增强数据库功能和用户界面
---
Database/DatabaseManager.cs | 454 +++++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 411 insertions(+), 43 deletions(-)
diff --git a/Database/DatabaseManager.cs b/Database/DatabaseManager.cs
index f494be9..34bedba 100644
--- a/Database/DatabaseManager.cs
+++ b/Database/DatabaseManager.cs
@@ -1,6 +1,7 @@
using GSModbus.Config;
using SqlSugar;
using System.Text.Json;
+using Oracle.ManagedDataAccess.Client;
namespace GSModbus.Database
{
@@ -78,40 +79,16 @@
{
try
{
- // 鏍规嵁鏁版嵁搴撶被鍨嬪垱寤篠qlSugarClient
- var dbType = GetDbType(_config.Type);
+ OnDatabaseLog("寮�濮嬪垵濮嬪寲鏁版嵁搴撹繛鎺�...");
- _db = new SqlSugarClient(new SqlSugar.ConnectionConfig()
+ // 瀵筄racle鐗规畩澶勭悊锛岄伩鍏峉qlSugar璇硶闂
+ if (_config.Type.ToLower() == "oracle")
{
- ConnectionString = _config.ConnectionString,
- DbType = dbType,
- IsAutoCloseConnection = true,
-
- // 閰嶇疆SQL鏃ュ織
- MoreSettings = new ConnMoreSettings()
- {
- IsAutoRemoveDataCache = true
- }
- });
-
- // 閰嶇疆鏃ュ織浜嬩欢
- _db.Aop.OnLogExecuting = (sql, pars) =>
+ InitializeOracleDirectly();
+ }
+ else
{
- 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();
+ InitializeWithSqlSugar();
}
OnDatabaseLog("鏁版嵁搴撶鐞嗗櫒鍒濆鍖栧畬鎴�");
@@ -120,6 +97,93 @@
{
OnDatabaseError($"鍒濆鍖栨暟鎹簱澶辫触: {ex.Message}");
throw;
+ }
+ }
+
+ /// <summary>
+ /// 鐩存帴鍒濆鍖朞racle鏁版嵁搴�
+ /// </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()
+ {
+ // 鏍规嵁鏁版嵁搴撶被鍨嬪垱寤篠qlSugarClient
+ 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();
}
}
@@ -150,27 +214,173 @@
{
OnDatabaseLog("寮�濮嬫鏌ュ拰鍒涘缓鏁版嵁搴撹〃...");
- // 鍒涘缓ModbusData琛�
- _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(ModbusDataEntity));
- OnDatabaseLog($"ModbusData琛ㄦ鏌ュ畬鎴愶紝琛ㄥ悕: {_config.Tables.ModbusDataTable}");
+ // 瀵筄racle浣跨敤鐩存帴SQL寤鸿〃锛岄伩鍏峉qlSugar璇硶闂
+ if (GetDbType(_config.Type) == DbType.Oracle)
+ {
+ CreateOracleTablesDirectly();
+ }
+ else
+ {
+ // 鍏朵粬鏁版嵁搴撲娇鐢⊿qlSugar CodeFirst
+ _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}");
+ _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}");
+ _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}");
+ _db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(StatisticsEntity));
+ OnDatabaseLog($"Statistics琛ㄦ鏌ュ畬鎴愶紝琛ㄥ悕: {_config.Tables.StatisticsTable}");
+ }
OnDatabaseLog("鎵�鏈夋暟鎹簱琛ㄦ鏌ュ畬鎴�");
}
catch (Exception ex)
{
OnDatabaseError($"鍒涘缓鏁版嵁搴撹〃澶辫触: {ex.Message}");
+ throw;
+ }
+ }
+
+ /// <summary>
+ /// 鐩存帴浣跨敤SQL涓篛racle鍒涘缓琛�
+ /// </summary>
+ private void CreateOracleTablesDirectly()
+ {
+ try
+ {
+ OnDatabaseLog("寮�濮嬩娇鐢ㄥ師鐢烵racle 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("鎵�鏈塐racle琛ㄥ垱寤哄畬鎴愶紝杩炴帴宸插叧闂�");
+ }
+ }
+ catch (Exception ex)
+ {
+ OnDatabaseError($"Oracle寤鸿〃澶辫触: {ex.Message}");
throw;
}
}
@@ -507,6 +717,164 @@
#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
+ {
+ // 鍏朵粬鏁版嵁搴撲娇鐢⊿qlSugar
+ 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>
--
Gitblit v1.9.3