From c154cfddec4c770e58c23c28bdafa7a5c7b5a389 Mon Sep 17 00:00:00 2001
From: C3204 <zhengyabo@lanpucloud.cn>
Date: 星期三, 07 一月 2026 15:07:33 +0800
Subject: [PATCH] 增加MySQL数据库产品信息的增删改查。
---
LB_SmartVision/SQL/LB_SqlCommand.cs | 552 ++++++++++++++++++
/dev/null | 573 -------------------
LB_SmartVision/Forms/CreateProductForm.cs | 1
LB_SmartVision/SQL/RecordProductDataRepository.cs | 419 +++++++++++++
LB_SmartVisionCommon/UserData.cs | 4
LB_VisionProcesses/Communicators/SiemensS7/SiemensLBS7.cs | 16
LB_SmartVision/SQL/DatabaseRecordProductDataHelper.cs | 72 ++
LB_SmartVision/SQL/LB_Collections.cs | 2
LB_SmartVision/CSV/CsvRecordProductData.cs | 52 +
LB_SmartVision/LB_SmartVision.csproj | 1
LB_SmartVision/SQL/RecordProductData.cs | 75 ++
11 files changed, 1,190 insertions(+), 577 deletions(-)
diff --git a/LB_SmartVision/CSV/CsvRecordProductData.cs b/LB_SmartVision/CSV/CsvRecordProductData.cs
new file mode 100644
index 0000000..6648f12
--- /dev/null
+++ b/LB_SmartVision/CSV/CsvRecordProductData.cs
@@ -0,0 +1,52 @@
+锘縰sing LB_SmartVisionCommon;
+using System;
+using System.Collections.Generic;
+using System.ComponentModel;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace LB_SmartVision.CSV
+{
+ /// <summary>
+ /// 鐢ㄤ簬CSV鏄犲皠鐨勭被
+ /// </summary>
+ public class CsvRecordProductData
+ {
+ /// <summary>
+ /// SKU 鐗╂枡鍙锋垨浜у搧鍚嶇О
+ /// </summary>
+ public string ProductName { get; set; }
+
+ /// <summary>
+ /// 浜у搧SN鍙�
+ /// </summary>
+ public string ProductSN { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬩綔涓氬憳
+ /// </summary>
+ public string InspectionOperator { get; set; }
+
+ /// <summary>
+ /// NG绫诲瀷
+ /// </summary>
+ public string NGType { get; set; }
+
+ /// <summary>
+ /// NG澶у皬
+ /// </summary>
+ public string NGSize { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬫椂闂�
+ /// </summary>
+ public string DetectionTime { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬬浉鏈�
+ /// </summary>
+ public string CameraInspection { get; set; }
+ }
+
+}
diff --git a/LB_SmartVision/Forms/CreateProductForm.cs b/LB_SmartVision/Forms/CreateProductForm.cs
index 8ea53ef..80d58c0 100644
--- a/LB_SmartVision/Forms/CreateProductForm.cs
+++ b/LB_SmartVision/Forms/CreateProductForm.cs
@@ -1,4 +1,5 @@
锘�
+using LB_SmartVision.SQL;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using ReaLTaiizor.Forms;
diff --git a/LB_SmartVision/LB_SmartVision.csproj b/LB_SmartVision/LB_SmartVision.csproj
index 6a5662b..e242bd3 100644
--- a/LB_SmartVision/LB_SmartVision.csproj
+++ b/LB_SmartVision/LB_SmartVision.csproj
@@ -14,6 +14,7 @@
</ItemGroup>
<ItemGroup>
+ <ProjectReference Include="..\LB_SmartVisionCommon\LB_SmartVisionCommon.csproj" />
<ProjectReference Include="..\LB_SmartVisionLoginUI\LB_SmartVisionLoginUI.csproj" />
<ProjectReference Include="..\LB_VisionControl\LB_VisionControl.csproj" />
<ProjectReference Include="..\LB_VisionFlowNode\LB_VisionFlowNode.csproj" />
diff --git a/LB_SmartVision/LB_SqlCommand.cs b/LB_SmartVision/LB_SqlCommand.cs
deleted file mode 100644
index dd2cb12..0000000
--- a/LB_SmartVision/LB_SqlCommand.cs
+++ /dev/null
@@ -1,573 +0,0 @@
-锘縰sing System;
-using System.Collections.Generic;
-using System.Data;
-using System.Data.SqlClient;
-using System.Diagnostics;
-using System.Linq;
-using System.Text;
-using System.Threading.Tasks;
-
-namespace LB_SmartVision
-{
- public class LB_SqlCommand
- {
- /// <summary>
- /// 杩炴帴瀛楃涓诧細鎸囧畾 SQL Server 瀹炰緥鍜屾暟鎹簱(瀹為檯浼氳鍙杁ll.config涓殑鍊兼潵鏇存柊杩炴帴瀛楃涓�)
- /// </summary>
- public static string ConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=True;";
-
- /// <summary>
- /// 鍒ゆ柇鎸囧畾鍒楁槸鍚﹀瓨鍦ㄦ煇鍊�
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="columnName">鎸囧畾鍒楀悕</param>
- /// <param name="value">鎸囧畾鍊�</param>
- /// <param name="connectionString"></param>
- /// <returns>鍊煎瓨鍦═rue</returns>
- public static bool IsExist(string tableName, string columnName, string value, string connectionString = "")
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = string.Format("SELECT CASE WHEN EXISTS (SELECT 1 FROM [{0}] " +
- "WHERE [{1}] = @Value) THEN 1 ELSE 0 END", tableName, columnName);
-
- // 鍒涘缓 SQL 杩炴帴
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- // 鍒涘缓 SQL 鍛戒护
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 娣诲姞鍙傛暟锛岄槻姝� SQL 娉ㄥ叆
- command.Parameters.AddWithValue("@Value", value);
-
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
- int result = (int)command.ExecuteScalar();
-
- // 濡傛灉 result == 1锛岃〃绀哄瓨鍦ㄨ鍊硷紱鍚﹀垯涓嶅瓨鍦�
- if (result == 1)
- return true;
- else
- return false;
- }
- }
- }
-
- /// <summary>
- /// 鍒ゆ柇鎸囧畾琛ㄦ槸鍚﹀瓨鍦�
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="connectionString"></param>
- /// <returns>鍊煎瓨鍦═rue</returns>
- public static bool IsExistTable(string tableName, string connectionString = "")
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = $@"
- SELECT COUNT(*)
- FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_NAME = @TableName";
-
- // 鍒涘缓 SQL 杩炴帴
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- // 鍒涘缓 SQL 鍛戒护
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 娣诲姞琛ㄥ悕鍙傛暟
- command.Parameters.AddWithValue("@TableName", tableName);
-
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
- int result = (int)command.ExecuteScalar();
-
- // 濡傛灉 result > 0锛岃〃绀哄瓨鍦ㄨ琛紱鍚﹀垯涓嶅瓨鍦�
- if (result > 0)
- return true;
- else
- return false;
- }
- }
- }
-
- /// <summary>
- /// 閲嶅懡鍚嶈〃
- /// </summary>
- /// <param name="oldTableName"></param>
- /// <param name="newTableName"></param>
- /// <param name="connectionString"></param>
- /// <returns></returns>
- public static bool RenameTable(string oldTableName, string newTableName, string connectionString = "")
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- if (!IsExistTable(oldTableName))
- return false;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = $"EXEC sp_rename '{oldTableName}', '{newTableName}'"; ;
-
- // 鍒涘缓 SQL 杩炴帴
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- // 鍒涘缓 SQL 鍛戒护
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
- int result = (int)command.ExecuteNonQuery();
-
- // 濡傛灉 result == -1锛岃〃绀洪噸鍛藉悕鎴愬姛
- if (result == -1)
- return true;
- else
- return false;
- }
- }
- }
-
- /// <summary>
- /// 鍒ゆ柇鎸囧畾琛ㄦ槸鍚﹀瓨鍦�
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="connectionString"></param>
- /// <returns>鍊煎瓨鍦═rue</returns>
- public static bool DeleteTable(string tableName, string connectionString = "")
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = $"DROP TABLE IF EXISTS [{tableName}]";
-
- // 鍒涘缓 SQL 杩炴帴
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- // 鍒涘缓 SQL 鍛戒护
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
- int result = (int)command.ExecuteNonQuery();
-
- // 濡傛灉 result = -1锛岃〃绀哄瓨鍦ㄨ琛ㄧЩ闄ゆ垚鍔�
- if (result == -1)
- return true;
- else
- return false;
- }
- }
- }
-
- /// <summary>
- /// 鑾峰彇鎵�闇�鐨勫�奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊煎畾浣嶅埌鎵�鍦ㄨ鐨勬寚瀹氬垪
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="indexName">鎸囧畾鍒楀悕</param>
- /// <param name="indexValue">鎸囧畾鍒楀��</param>
- /// <param name="columnName">鎵�闇�鍊肩殑鍒楀悕</param>
- /// <param name="connectionString"></param>
- /// <returns>鎵�闇�鍒楀��</returns>
- public static string FindValueFromIndexName(string tableName, string indexName, string indexValue, string columnName, string connectionString = "")
- {
- try
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = $"SELECT {columnName} FROM [{tableName}] WHERE {indexName} = @IndexValue";
- string resultValue = string.Empty;
-
- // 杩炴帴鏁版嵁搴撳苟鎵ц鏌ヨ
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 娣诲姞鍙傛暟浠ラ槻姝� SQL 娉ㄥ叆
- command.Parameters.AddWithValue("@IndexValue", indexValue);
-
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
- var result = command.ExecuteScalar();
-
- if (result != null)
- {
- resultValue = result.ToString();
- }
- }
- }
- return resultValue;
- }
- catch { return string.Empty; }
- }
-
- /// <summary>
- /// 鍒犻櫎鎸囧畾琛岄�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="indexName">鎸囧畾鍒楀悕</param>
- /// <param name="indexValue">鎸囧畾鍒楀��</param>
- /// <param name="connectionString"></param>
- /// <returns>鍒犻櫎鎴愬姛True</returns>
- public static bool DeleteRowFromIndexName(string tableName, string indexName, string indexValue, string connectionString = "")
- {
- try
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- conn.Open();
-
- // 鍒犻櫎鎸囧畾琛�
- string deleteQuery = string.Format("DELETE FROM [{0}] WHERE {1} = @indexValue", tableName, indexName);
- using (SqlCommand cmd = new SqlCommand(deleteQuery, conn))
- {
- cmd.Parameters.AddWithValue("@indexValue", indexValue);
- cmd.ExecuteNonQuery();
- }
-
- // 鏇存柊鍓╀綑琛岀殑 step
- string updateQuery = string.Format(@"
- WITH OrderedSteps AS (
- SELECT {1}, ROW_NUMBER() OVER (ORDER BY {1}) AS NewValue
- FROM [{0}]
- )
- UPDATE [{0}]
- SET {1} = NewValue
- FROM [{0}] s
- JOIN OrderedSteps o ON s.{1} = o.{1};
- ", tableName, indexName);
-
- using (SqlCommand cmd = new SqlCommand(updateQuery, conn))
- {
- cmd.ExecuteNonQuery();
- }
- return true;
- }
- }
- catch { return false; }
- }
-
- /// <summary>
- /// 浜ゆ崲鎸囧畾琛岀殑鎵�鏈夊�奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="swapName">鍒楀悕</param>
- /// <param name="swapValue1">浜ゆ崲鐨勫垪鍊�</param>
- /// <param name="swapValue2">琚氦鎹㈢殑鍒楀��</param>
- /// <param name="connectionString"></param>
- /// <returns>浜ゆ崲鎴愬姛True</returns>
- public static bool SwapRowFromIndexName(string tableName, string swapName, string swapValue1, string swapValue2, string connectionString = "")
- {
-
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
- using (SqlConnection conn = new SqlConnection(connectionString))
- {
- conn.Open();
-
- // 寮�濮嬩簨鍔�
- SqlTransaction transaction = conn.BeginTransaction();
- try
- {
- // 浜ゆ崲 step 涓� 3 鍜� 4 鐨勮
- string swapQuery = string.Format(@"
- BEGIN TRANSACTION;
- UPDATE [{0}]
- SET {1} = @SwapTempValue
- WHERE {1} = @SwapValue1;
-
- UPDATE [{0}]
- SET {1} = @SwapValue1
- WHERE {1} = @SwapValue2;
-
- UPDATE [{0}]
- SET {1} = @SwapValue2
- WHERE {1} = @SwapTempValue;
- COMMIT;", tableName, swapName);
-
- using (SqlCommand cmd = new SqlCommand(swapQuery, conn, transaction))
- {
- // 娣诲姞鍙傛暟
- cmd.Parameters.AddWithValue("@SwapValue1", swapValue1);
- cmd.Parameters.AddWithValue("@SwapValue2", swapValue2);
- cmd.Parameters.AddWithValue("@SwapTempValue", Guid.NewGuid().ToString());
-
- // 鎵ц鏌ヨ
- cmd.ExecuteNonQuery();
- }
-
- // 鎻愪氦浜嬪姟
- transaction.Commit();
- return true;
- }
- catch (Exception ex)
- {
- Debug.WriteLine("浜ゆ崲澶辫触,鍘熷洜鏄�:" + ex.Message.ToString());
- // 鍑虹幇寮傚父鏃跺洖婊氫簨鍔�
- transaction.Rollback();
- return false;
- }
- }
- }
-
- /// <summary>
- /// 鏇存柊鎸囧畾鍒楃殑鍊奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
- /// </summary>
- /// <param name="tableName"></param>
- /// <param name="oriValue"></param>
- /// <param name="newValue"></param>
- /// <param name="columnName"></param>
- /// <param name="connectionString"></param>
- /// <returns></returns>
- public static bool UpdataValueFromIndexName(string tableName, string oriValue, string newValue, string columnName, string connectionString = "")
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- // SQL 鏌ヨ璇彞锛屾鏌� ProcessName 鍒楁槸鍚﹀瓨鍦ㄦ寚瀹氱殑鍊�
- string sqlQuery = string.Format("UPDATE [{0}] SET {1} = @NewValue WHERE {1} = @OriValue", tableName, columnName);
- string resultValue = string.Empty;
-
- // 杩炴帴鏁版嵁搴撳苟鎵ц鏌ヨ
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- using (SqlCommand command = new SqlCommand(sqlQuery, connection))
- {
- // 鍙傛暟鍖栨煡璇紝闃叉 SQL 娉ㄥ叆
- command.Parameters.AddWithValue("@OriValue", oriValue); // 瑕佹浛鎹㈢殑鍘熷鍊�
- command.Parameters.AddWithValue("@NewValue", newValue); // 鏇挎崲鍚庣殑鏂板��
-
- // 鎵撳紑杩炴帴
- connection.Open();
-
- // 鎵ц鏇存柊鎿嶄綔
- int rowsAffected = command.ExecuteNonQuery();
-
- if (rowsAffected > 0)
- {
- return true;
- }
- }
- }
- return false;
- }
-
-
- /// <summary>
- /// 妫�鏌ユ暟鎹簱鏄惁瀛樺湪
- /// </summary>
- /// <param name="connectionString"></param>
- /// <param name="databaseName"></param>
- /// <returns></returns>
- public static bool DatabaseExists(string connectionString, string databaseName)
- {
- string query = "SELECT database_id FROM sys.databases WHERE name = @databaseName";
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
- using (SqlCommand command = new SqlCommand(query, connection))
- {
- command.Parameters.AddWithValue("@databaseName", databaseName);
- var result = command.ExecuteScalar();
- return result != null;
- }
- }
- }
-
- /// <summary>
- /// 鍒涘缓鏁版嵁搴�(闇�瑕乵aster鏉冮檺)
- /// </summary>
- /// <param name="connectionString"></param>
- /// <param name="databaseName"></param>
- /// <param name="databasePath"></param>
- /// <param name="logFilePath"></param>
- public static bool CreateDatabase(string connectionString, string databaseName)
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- if (!connectionString.Contains("master"))
- return false;
-
- try
- {
- string createQuery = $@"CREATE DATABASE {databaseName}";
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- connection.Open();
- using (SqlCommand command = new SqlCommand(createQuery, connection))
- {
- command.ExecuteNonQuery();
- }
- }
- return true;
- }
- catch { return false; }
- }
-
- /// <summary>
- /// 鍒犻櫎鏁版嵁搴�(闇�瑕乵aster鏉冮檺)
- /// </summary>
- /// <param name="tableName">琛ㄥ悕</param>
- /// <param name="connectionString"></param>
- /// <returns>鍊煎瓨鍦═rue</returns>
- public static bool DeleteDatabase(string databaseName, string connectionString)
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- if (!connectionString.Contains("master"))
- return false;
-
- // 璁剧疆 SQL DROP DATABASE 璇彞
- string dropDatabaseQuery = $"DROP DATABASE IF EXISTS {databaseName}";
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- try
- {
- // 鎵撳紑鏁版嵁搴撹繛鎺�
- connection.Open();
-
- // 1. 璁剧疆鏁版嵁搴撲负鍗曠敤鎴锋ā寮忥紝寮哄埗鏂紑鎵�鏈夎繛鎺�
- string setSingleUserQuery = $@"ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;";
- using (var cmd = new SqlCommand(setSingleUserQuery, connection))
- {
- cmd.ExecuteNonQuery();
- Debug.WriteLine($"Database {databaseName} is now in single-user mode.");
- }
-
- // 鍒涘缓 MySqlCommand 瀵硅薄
- using (SqlCommand cmd = new SqlCommand(dropDatabaseQuery, connection))
- {
- // 鎵ц鍒犻櫎鏁版嵁搴撴搷浣�
- cmd.ExecuteNonQuery();
- Debug.WriteLine("Database dropped successfully.");
- return true;
- }
- }
- catch (Exception ex)
- {
- Debug.WriteLine("Error: " + ex.Message);
- return false;
- }
- }
- }
-
- /// <summary>
- /// 澶嶅埗琛ㄧ粨鏋勫拰鏁版嵁(闇�瑕乵aster鏉冮檺)
- /// </summary>
- /// <param name="connectionString"></param>
- /// <param name="sourceDatabase"></param>
- /// <param name="targetDatabase"></param>
- public static bool CopyDatabaseData(string connectionString, string sourceDatabase, string targetDatabase)
- {
- if (string.IsNullOrEmpty(connectionString))
- connectionString = ConnectionString;
-
- if (!connectionString.Contains("master"))
- return false;
-
- try
- {
- using (var connection = new SqlConnection(connectionString))
- {
- connection.Open();
- string useSourceDb = $"USE {sourceDatabase};";
- string useTargetDb = $"USE {targetDatabase};";
-
- // 鍒囨崲鍒版簮鏁版嵁搴�
- using (var cmd = new SqlCommand(useSourceDb, connection))
- {
- cmd.ExecuteNonQuery();
- }
-
- // 鑾峰彇婧愭暟鎹簱涓墍鏈夎〃鐨勫垪琛�
- string getTablesQuery = "SELECT name FROM sys.tables;";
- using (var cmd = new SqlCommand(getTablesQuery, connection))
- {
- // 浣跨敤 CommandBehavior.CloseConnection 鑷姩鍏抽棴杩炴帴
- using (var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
- {
- while (reader.Read())
- {
- string tableName = reader.GetString(0);
- Debug.WriteLine($"澶嶅埗琛�: {tableName}");
-
- // 澶嶅埗琛ㄧ粨鏋�
- string createTableQuery = $"SELECT * INTO {targetDatabase}.dbo.{tableName} FROM {sourceDatabase}.dbo.{tableName} WHERE 1 = 0;";
- using (var cmdCreateTable = new SqlCommand(createTableQuery, connection))
- {
- cmdCreateTable.ExecuteNonQuery();
- }
-
- // 澶嶅埗琛ㄦ暟鎹�
- string insertDataQuery = $"INSERT INTO {targetDatabase}.dbo.{tableName} SELECT * FROM {sourceDatabase}.dbo.{tableName};";
- using (var cmdInsertData = new SqlCommand(insertDataQuery, connection))
- {
- cmdInsertData.ExecuteNonQuery();
- }
- }
- }
- }
- }
- return true;
- }
- catch { return false; }
- }
-
- public static List<string> GetUserDatabases()
- {
- List<string> databases = new List<string>();
- string connectionString = "Server=localhost;Database=master;Integrated Security=True;";
-
- using (SqlConnection connection = new SqlConnection(connectionString))
- {
- try
- {
- connection.Open();
- string query = @"
- SELECT name
- FROM sys.databases
- WHERE database_id > 4 -- 杩囨护绯荤粺鏁版嵁搴�
- AND name NOT IN ('model','msdb','tempdb','DWConfiguration','DWDiagnostics','DWQueue')
- AND source_database_id IS NULL -- 鎺掗櫎鏁版嵁搴撳揩鐓�";
-
- using (SqlCommand command = new SqlCommand(query, connection))
- using (SqlDataReader reader = command.ExecuteReader())
- {
- while (reader.Read())
- {
- databases.Add(reader["name"]?.ToString());
- }
- }
- }
- catch (SqlException ex)
- {
- Debug.WriteLine($"鏁版嵁搴撴煡璇㈠け璐�: {ex.Message}");
- }
- }
- return databases;
- }
- }
-}
diff --git a/LB_SmartVision/SQL/DatabaseRecordProductDataHelper.cs b/LB_SmartVision/SQL/DatabaseRecordProductDataHelper.cs
new file mode 100644
index 0000000..ae357bf
--- /dev/null
+++ b/LB_SmartVision/SQL/DatabaseRecordProductDataHelper.cs
@@ -0,0 +1,72 @@
+锘縰sing LB_SmartVisionCommon;
+using MySql.Data.MySqlClient;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace LB_SmartVision.SQL
+{
+ public class DatabaseRecordProductDataHelper
+ {
+ private static string connectionString = "Server=localhost;Database=product_db;Uid=root;Pwd=root;";
+ private static object databaseRecordProductDataHelperObject = new object();
+ public static MySqlConnection GetConnection()
+ {
+ return new MySqlConnection(connectionString);
+ }
+ // 鍒涘缓鏁版嵁搴撳拰琛�
+ public static void InitializeDatabase()
+ {
+ lock (databaseRecordProductDataHelperObject)
+ {
+ try
+ {
+ using (var connection = GetConnection())
+ {
+ connection.Open();
+ // 鍒涘缓鏁版嵁搴�
+ string createDbSql = @"CREATE DATABASE IF NOT EXISTS product_db;";
+ using (var cmd = new MySqlCommand(createDbSql, connection))
+ {
+ cmd.ExecuteNonQuery();
+ }
+ // 浣跨敤鏁版嵁搴�
+ string useDbSql = @"USE product_db;";
+ using (var cmd = new MySqlCommand(useDbSql, connection))
+ {
+ cmd.ExecuteNonQuery();
+ }
+ // 鍒涘缓琛紙澧炲姞绱㈠紩浠ユ彁楂樻煡璇㈡�ц兘锛�
+ string createTableSql = @"
+ CREATE TABLE IF NOT EXISTS RecordProductData (
+ Id INT AUTO_INCREMENT PRIMARY KEY,
+ ProductName VARCHAR(255) NOT NULL,
+ ProductSN VARCHAR(255) NOT NULL,
+ InspectionOperator VARCHAR(255),
+ NGType VARCHAR(255),
+ NGSize VARCHAR(255),
+ DetectionTime VARCHAR(255)),
+ CameraInspection VARCHAR(255),
+ CreatedDate DATETIME DEFAULT CURRENT_TIMESTAMP,
+ INDEX idx_created_date (CreatedDate),
+ INDEX idx_product_number (ProductSN),
+ INDEX idx_operator (InspectionOperator)
+ );";
+ using (var cmd = new MySqlCommand(createTableSql, connection))
+ {
+ cmd.ExecuteNonQuery();
+ AsyncLogHelper.Info("鏁版嵁搴撳拰琛ㄥ垱寤烘垚鍔燂紒");
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"鏁版嵁搴撳垵濮嬪寲澶辫触: {ex.Message}");
+ throw;
+ }
+ }
+ }
+ }
+}
diff --git a/LB_SmartVision/LB_Collections.cs b/LB_SmartVision/SQL/LB_Collections.cs
similarity index 99%
rename from LB_SmartVision/LB_Collections.cs
rename to LB_SmartVision/SQL/LB_Collections.cs
index 30ee0d7..bb7a1f0 100644
--- a/LB_SmartVision/LB_Collections.cs
+++ b/LB_SmartVision/SQL/LB_Collections.cs
@@ -5,7 +5,7 @@
using System.Text;
using System.Threading.Tasks;
-namespace LB_SmartVision
+namespace LB_SmartVision.SQL
{
[Serializable]
public class LB_Collections<T> : ICollection<CollectionItem<T>>
diff --git a/LB_SmartVision/SQL/LB_SqlCommand.cs b/LB_SmartVision/SQL/LB_SqlCommand.cs
new file mode 100644
index 0000000..b52b06d
--- /dev/null
+++ b/LB_SmartVision/SQL/LB_SqlCommand.cs
@@ -0,0 +1,552 @@
+锘縰sing MySql.Data.MySqlClient;
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.Data.SqlClient;
+using System.Diagnostics;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace LB_SmartVision.SQL
+{
+ public class LB_SqlCommand
+ {
+ /// <summary>
+ /// 杩炴帴瀛楃涓诧細鎸囧畾 MySQL 瀹炰緥鍜屾暟鎹簱(瀹為檯浼氳鍙杁ll.config涓殑鍊兼潵鏇存柊杩炴帴瀛楃涓�)
+ /// </summary>
+ public static string ConnectionString = "Server=localhost;Database=product_mes;Uid=root;Pwd=root;";
+
+ /// <summary>
+ /// 鍒ゆ柇鎸囧畾鍒楁槸鍚﹀瓨鍦ㄦ煇鍊�
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="columnName">鎸囧畾鍒楀悕</param>
+ /// <param name="value">鎸囧畾鍊�</param>
+ /// <param name="connectionString"></param>
+ /// <returns>鍊煎瓨鍦═rue</returns>
+ public static bool IsExist(string tableName, string columnName, string value, string connectionString = "")
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鏌ヨ璇彞锛屾鏌ユ寚瀹氬垪鏄惁瀛樺湪鎸囧畾鐨勫��
+ string sqlQuery = string.Format("SELECT CASE WHEN EXISTS (SELECT 1 FROM `{0}` " +
+ "WHERE `{1}` = @Value) THEN 1 ELSE 0 END", tableName, columnName);
+
+ // 鍒涘缓 MySQL 杩炴帴
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ // 鍒涘缓 MySQL 鍛戒护
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 娣诲姞鍙傛暟锛岄槻姝� SQL 娉ㄥ叆
+ command.Parameters.AddWithValue("@Value", value);
+
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
+ int result = Convert.ToInt32(command.ExecuteScalar());
+
+ // 濡傛灉 result == 1锛岃〃绀哄瓨鍦ㄨ鍊硷紱鍚﹀垯涓嶅瓨鍦�
+ return result == 1;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鍒ゆ柇鎸囧畾琛ㄦ槸鍚﹀瓨鍦�
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="connectionString"></param>
+ /// <returns>鍊煎瓨鍦═rue</returns>
+ public static bool IsExistTable(string tableName, string connectionString = "")
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鏌ヨ璇彞锛屾鏌ヨ〃鏄惁瀛樺湪
+ string sqlQuery = $@"
+ SELECT COUNT(*)
+ FROM INFORMATION_SCHEMA.TABLES
+ WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = @TableName";
+
+ // 鍒涘缓 MySQL 杩炴帴
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ // 鍒涘缓 MySQL 鍛戒护
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 娣诲姞琛ㄥ悕鍙傛暟
+ command.Parameters.AddWithValue("@TableName", tableName);
+
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
+ int result = Convert.ToInt32(command.ExecuteScalar());
+
+ // 濡傛灉 result > 0锛岃〃绀哄瓨鍦ㄨ琛紱鍚﹀垯涓嶅瓨鍦�
+ return result > 0;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 閲嶅懡鍚嶈〃
+ /// </summary>
+ /// <param name="oldTableName"></param>
+ /// <param name="newTableName"></param>
+ /// <param name="connectionString"></param>
+ /// <returns></returns>
+ public static bool RenameTable(string oldTableName, string newTableName, string connectionString = "")
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ if (!IsExistTable(oldTableName))
+ return false;
+
+ // MySQL 閲嶅懡鍚嶈〃璇硶
+ string sqlQuery = $"ALTER TABLE `{oldTableName}` RENAME TO `{newTableName}`";
+
+ // 鍒涘缓 MySQL 杩炴帴
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ // 鍒涘缓 MySQL 鍛戒护
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ try
+ {
+ // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
+ command.ExecuteNonQuery();
+ return true;
+ }
+ catch
+ {
+ return false;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鍒犻櫎鎸囧畾琛�
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="connectionString"></param>
+ /// <returns>鍒犻櫎鎴愬姛True</returns>
+ public static bool DeleteTable(string tableName, string connectionString = "")
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鍒犻櫎琛ㄨ鍙�
+ string sqlQuery = $"DROP TABLE IF EXISTS `{tableName}`";
+
+ // 鍒涘缓 MySQL 杩炴帴
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ // 鍒涘缓 MySQL 鍛戒护
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ try
+ {
+ // 鎵ц鍒犻櫎鎿嶄綔
+ command.ExecuteNonQuery();
+ return true;
+ }
+ catch
+ {
+ return false;
+ }
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇鎵�闇�鐨勫�奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊煎畾浣嶅埌鎵�鍦ㄨ鐨勬寚瀹氬垪
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="indexName">鎸囧畾鍒楀悕</param>
+ /// <param name="indexValue">鎸囧畾鍒楀��</param>
+ /// <param name="columnName">鎵�闇�鍊肩殑鍒楀悕</param>
+ /// <param name="connectionString"></param>
+ /// <returns>鎵�闇�鍒楀��</returns>
+ public static string FindValueFromIndexName(string tableName, string indexName, string indexValue, string columnName, string connectionString = "")
+ {
+ try
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鏌ヨ璇彞
+ string sqlQuery = $"SELECT `{columnName}` FROM `{tableName}` WHERE `{indexName}` = @IndexValue";
+ string resultValue = string.Empty;
+
+ // 杩炴帴鏁版嵁搴撳苟鎵ц鏌ヨ
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 娣诲姞鍙傛暟浠ラ槻姝� SQL 娉ㄥ叆
+ command.Parameters.AddWithValue("@IndexValue", indexValue);
+
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ // 鎵ц鏌ヨ骞惰幏鍙栫粨鏋�
+ var result = command.ExecuteScalar();
+
+ if (result != null)
+ {
+ resultValue = result.ToString();
+ }
+ }
+ }
+ return resultValue;
+ }
+ catch
+ {
+ return string.Empty;
+ }
+ }
+
+ /// <summary>
+ /// 鍒犻櫎鎸囧畾琛岄�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="indexName">鎸囧畾鍒楀悕</param>
+ /// <param name="indexValue">鎸囧畾鍒楀��</param>
+ /// <param name="connectionString"></param>
+ /// <returns>鍒犻櫎鎴愬姛True</returns>
+ public static bool DeleteRowFromIndexName(string tableName, string indexName, string indexValue, string connectionString = "")
+ {
+ try
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ using (MySqlConnection conn = new MySqlConnection(connectionString))
+ {
+ conn.Open();
+
+ // 鍒犻櫎鎸囧畾琛�
+ string deleteQuery = string.Format("DELETE FROM `{0}` WHERE `{1}` = @indexValue", tableName, indexName);
+ using (MySqlCommand cmd = new MySqlCommand(deleteQuery, conn))
+ {
+ cmd.Parameters.AddWithValue("@indexValue", indexValue);
+ cmd.ExecuteNonQuery();
+ }
+
+ // MySQL 涓洿鏂拌鍙风殑鍐欐硶锛堝亣璁緄ndexName鏄嚜澧炲垪锛�
+ string updateQuery = string.Format(@"
+ SET @row_number = 0;
+ UPDATE `{0}`
+ SET `{1}` = @row_number := @row_number + 1
+ ORDER BY `{1}`;", tableName, indexName);
+
+ using (MySqlCommand cmd = new MySqlCommand(updateQuery, conn))
+ {
+ cmd.ExecuteNonQuery();
+ }
+ return true;
+ }
+ }
+ catch
+ {
+ return false;
+ }
+ }
+
+ /// <summary>
+ /// 浜ゆ崲鎸囧畾琛岀殑鎵�鏈夊�奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
+ /// </summary>
+ /// <param name="tableName">琛ㄥ悕</param>
+ /// <param name="swapName">鍒楀悕</param>
+ /// <param name="swapValue1">浜ゆ崲鐨勫垪鍊�</param>
+ /// <param name="swapValue2">琚氦鎹㈢殑鍒楀��</param>
+ /// <param name="connectionString"></param>
+ /// <returns>浜ゆ崲鎴愬姛True</returns>
+ public static bool SwapRowFromIndexName(string tableName, string swapName, string swapValue1, string swapValue2, string connectionString = "")
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ using (MySqlConnection conn = new MySqlConnection(connectionString))
+ {
+ conn.Open();
+
+ // 寮�濮嬩簨鍔�
+ MySqlTransaction transaction = conn.BeginTransaction();
+ try
+ {
+ // MySQL 浜ゆ崲琛屽�肩殑鍐欐硶
+ string swapQuery = string.Format(@"
+ UPDATE `{0}`
+ SET `{1}` = CASE
+ WHEN `{1}` = @SwapValue1 THEN @SwapTempValue
+ WHEN `{1}` = @SwapValue2 THEN @SwapValue1
+ WHEN `{1}` = @SwapTempValue THEN @SwapValue2
+ END
+ WHERE `{1}` IN (@SwapValue1, @SwapValue2, @SwapTempValue);", tableName, swapName);
+
+ using (MySqlCommand cmd = new MySqlCommand(swapQuery, conn, transaction))
+ {
+ // 娣诲姞鍙傛暟
+ cmd.Parameters.AddWithValue("@SwapValue1", swapValue1);
+ cmd.Parameters.AddWithValue("@SwapValue2", swapValue2);
+ cmd.Parameters.AddWithValue("@SwapTempValue", Guid.NewGuid().ToString());
+
+ // 鎵ц鏌ヨ
+ cmd.ExecuteNonQuery();
+ }
+
+ // 鎻愪氦浜嬪姟
+ transaction.Commit();
+ return true;
+ }
+ catch (Exception ex)
+ {
+ Debug.WriteLine("浜ゆ崲澶辫触,鍘熷洜鏄�:" + ex.Message.ToString());
+ // 鍑虹幇寮傚父鏃跺洖婊氫簨鍔�
+ transaction.Rollback();
+ return false;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鏇存柊鎸囧畾鍒楃殑鍊奸�氳繃绱㈠紩鎸囧畾鍒楃殑鎸囧畾鍊�
+ /// </summary>
+ /// <param name="tableName"></param>
+ /// <param name="oriValue"></param>
+ /// <param name="newValue"></param>
+ /// <param name="columnName"></param>
+ /// <param name="connectionString"></param>
+ /// <returns></returns>
+ public static bool UpdateValueFromIndexName(string tableName, string oriValue, string newValue, string columnName, string connectionString = "")
+ {
+ // 淇鍘熸柟娉曞悕鎷煎啓閿欒 Updata -> Update
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鏇存柊璇彞
+ string sqlQuery = string.Format("UPDATE `{0}` SET `{1}` = @NewValue WHERE `{1}` = @OriValue", tableName, columnName);
+
+ // 杩炴帴鏁版嵁搴撳苟鎵ц鏌ヨ
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ using (MySqlCommand command = new MySqlCommand(sqlQuery, connection))
+ {
+ // 鍙傛暟鍖栨煡璇紝闃叉 SQL 娉ㄥ叆
+ command.Parameters.AddWithValue("@OriValue", oriValue); // 瑕佹浛鎹㈢殑鍘熷鍊�
+ command.Parameters.AddWithValue("@NewValue", newValue); // 鏇挎崲鍚庣殑鏂板��
+
+ // 鎵撳紑杩炴帴
+ connection.Open();
+
+ // 鎵ц鏇存柊鎿嶄綔
+ int rowsAffected = command.ExecuteNonQuery();
+
+ return rowsAffected > 0;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 妫�鏌ユ暟鎹簱鏄惁瀛樺湪
+ /// </summary>
+ /// <param name="connectionString"></param>
+ /// <param name="databaseName"></param>
+ /// <returns></returns>
+ public static bool DatabaseExists(string connectionString, string databaseName)
+ {
+ string query = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = @databaseName";
+
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ connection.Open();
+ using (MySqlCommand command = new MySqlCommand(query, connection))
+ {
+ command.Parameters.AddWithValue("@databaseName", databaseName);
+ var result = command.ExecuteScalar();
+ return result != null;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 鍒涘缓鏁版嵁搴�
+ /// </summary>
+ /// <param name="connectionString"></param>
+ /// <param name="databaseName"></param>
+ /// <returns></returns>
+ public static bool CreateDatabase(string connectionString, string databaseName)
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ try
+ {
+ string createQuery = $"CREATE DATABASE IF NOT EXISTS `{databaseName}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
+
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ connection.Open();
+ using (MySqlCommand command = new MySqlCommand(createQuery, connection))
+ {
+ command.ExecuteNonQuery();
+ }
+ }
+ return true;
+ }
+ catch
+ {
+ return false;
+ }
+ }
+
+ /// <summary>
+ /// 鍒犻櫎鏁版嵁搴�
+ /// </summary>
+ /// <param name="databaseName"></param>
+ /// <param name="connectionString"></param>
+ /// <returns></returns>
+ public static bool DeleteDatabase(string databaseName, string connectionString)
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ // MySQL 鍒犻櫎鏁版嵁搴撹鍙�
+ string dropDatabaseQuery = $"DROP DATABASE IF EXISTS `{databaseName}`";
+
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ try
+ {
+ // 鎵撳紑鏁版嵁搴撹繛鎺�
+ connection.Open();
+
+ // 鍒涘缓 MySqlCommand 瀵硅薄
+ using (MySqlCommand cmd = new MySqlCommand(dropDatabaseQuery, connection))
+ {
+ // 鎵ц鍒犻櫎鏁版嵁搴撴搷浣�
+ cmd.ExecuteNonQuery();
+ Debug.WriteLine("Database dropped successfully.");
+ return true;
+ }
+ }
+ catch (Exception ex)
+ {
+ Debug.WriteLine("Error: " + ex.Message);
+ return false;
+ }
+ }
+ }
+
+ /// <summary>
+ /// 澶嶅埗琛ㄧ粨鏋勫拰鏁版嵁
+ /// </summary>
+ /// <param name="connectionString"></param>
+ /// <param name="sourceDatabase"></param>
+ /// <param name="targetDatabase"></param>
+ /// <returns></returns>
+ public static bool CopyDatabaseData(string connectionString, string sourceDatabase, string targetDatabase)
+ {
+ if (string.IsNullOrEmpty(connectionString))
+ connectionString = ConnectionString;
+
+ try
+ {
+ // 鍏堝垱寤虹洰鏍囨暟鎹簱锛堝鏋滀笉瀛樺湪锛�
+ CreateDatabase(connectionString, targetDatabase);
+
+ using (var connection = new MySqlConnection(connectionString))
+ {
+ connection.Open();
+
+ // 鑾峰彇婧愭暟鎹簱涓墍鏈夎〃鐨勫垪琛�
+ string getTablesQuery = $"SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = @sourceDb";
+ using (var cmd = new MySqlCommand(getTablesQuery, connection))
+ {
+ cmd.Parameters.AddWithValue("@sourceDb", sourceDatabase);
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ string tableName = reader.GetString(0);
+ Debug.WriteLine($"澶嶅埗琛�: {tableName}");
+
+ // 鍏抽棴reader鎵嶈兘鎵ц鍚庣画鎿嶄綔
+ reader.Close();
+
+ // 澶嶅埗琛ㄧ粨鏋勫拰鏁版嵁锛圡ySQL 绠�鍖栧啓娉曪級
+ string copyTableQuery = $"CREATE TABLE `{targetDatabase}`.`{tableName}` LIKE `{sourceDatabase}`.`{tableName}`; " +
+ $"INSERT INTO `{targetDatabase}`.`{tableName}` SELECT * FROM `{sourceDatabase}`.`{tableName}`;";
+
+ using (var cmdCopy = new MySqlCommand(copyTableQuery, connection))
+ {
+ cmdCopy.ExecuteNonQuery();
+ }
+
+ // 閲嶆柊鎵撳紑reader缁х画璇诲彇涓嬩竴涓〃
+ reader.NextResult();
+ }
+ }
+ }
+ }
+ return true;
+ }
+ catch (Exception ex)
+ {
+ Debug.WriteLine($"澶嶅埗澶辫触: {ex.Message}");
+ return false;
+ }
+ }
+
+ /// <summary>
+ /// 鑾峰彇鐢ㄦ埛鏁版嵁搴撳垪琛�
+ /// </summary>
+ /// <returns></returns>
+ public static List<string> GetUserDatabases()
+ {
+ List<string> databases = new List<string>();
+ string connectionString = "Server=localhost;Database=user_pt;Uid=root;Pwd=root;";
+
+ using (MySqlConnection connection = new MySqlConnection(connectionString))
+ {
+ try
+ {
+ connection.Open();
+ string query = @"
+ SELECT SCHEMA_NAME
+ FROM INFORMATION_SCHEMA.SCHEMATA
+ WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')";
+
+ using (MySqlCommand command = new MySqlCommand(query, connection))
+ using (MySqlDataReader reader = command.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ databases.Add(reader["SCHEMA_NAME"]?.ToString());
+ }
+ }
+ }
+ catch (MySqlException ex)
+ {
+ Debug.WriteLine($"鏁版嵁搴撴煡璇㈠け璐�: {ex.Message}");
+ }
+ }
+ return databases;
+ }
+ }
+}
diff --git a/LB_SmartVision/SQL/RecordProductData.cs b/LB_SmartVision/SQL/RecordProductData.cs
new file mode 100644
index 0000000..0020776
--- /dev/null
+++ b/LB_SmartVision/SQL/RecordProductData.cs
@@ -0,0 +1,75 @@
+锘縰sing LB_SmartVisionCommon;
+using Newtonsoft.Json;
+using System;
+using System.Collections.Generic;
+using System.ComponentModel;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace LB_SmartVision.SQL
+{
+ /// <summary>
+ /// 璁板綍鐢熶骇鏁版嵁
+ /// </summary>
+ [JsonObject(MemberSerialization.OptOut)]
+ [TypeConverter(typeof(PropertySorter))]
+ public class RecordProductData
+ {
+ /// <summary>
+ /// SKU 鐗╂枡鍙锋垨浜у搧鍚嶇О
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(0)]
+ [DisplayName("SKU")]
+ [Browsable(true)]
+ public string ProductName { get; set; }
+
+ /// <summary>
+ /// 浜у搧SN鍙�
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(1)]
+ [DisplayName("SN")]
+ [Browsable(true)]
+ public string ProductSN { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬩綔涓氬憳
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(2)]
+ [DisplayName("妫�娴嬩綔涓氬憳")]
+ [Browsable(true)]
+ public string InspectionOperator { get; set; }
+
+ /// <summary>
+ /// NG绫诲瀷
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(3)]
+ [DisplayName("NG绫诲瀷")]
+ [Browsable(true)]
+ public string NGType { get; set; }
+
+ /// <summary>
+ /// NG澶у皬
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(4)]
+ [DisplayName("NG澶у皬")]
+ [Browsable(true)]
+ public string NGSize { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬫椂闂�
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(5)]
+ [DisplayName("妫�娴嬫椂闂�")]
+ [Browsable(true)]
+ public string DetectionTime { get; set; }
+
+ /// <summary>
+ /// 妫�娴嬬浉鏈�
+ /// </summary>
+ [Category("RecordProductData"), PropertyOrder(6)]
+ [DisplayName("妫�娴嬬浉鏈�")]
+ [Browsable(true)]
+ public string CameraInspection { get; set; }
+ }
+}
diff --git a/LB_SmartVision/SQL/RecordProductDataRepository.cs b/LB_SmartVision/SQL/RecordProductDataRepository.cs
new file mode 100644
index 0000000..4c5915e
--- /dev/null
+++ b/LB_SmartVision/SQL/RecordProductDataRepository.cs
@@ -0,0 +1,419 @@
+锘縰sing LB_SmartVisionCommon;
+using MySql.Data.MySqlClient;
+using System;
+using System.Collections.Generic;
+using System.Diagnostics;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace LB_SmartVision.SQL
+{
+ /// <summary>
+ /// 鏁版嵁璁块棶灞�
+ /// </summary>
+ public class RecordProductDataRepository
+ {
+ private static object recordProductDataRepositoryObject = new object();
+ /// <summary>
+ /// 娣诲姞鏁版嵁
+ /// </summary>
+ /// <param name="record">鐢熶骇鏁版嵁璁板綍</param>
+ /// <returns>鏄惁娣诲姞鎴愬姛</returns>
+ public static bool AddRecord(RecordProductData record)
+ {
+ Stopwatch stopwatch = Stopwatch.StartNew();
+ lock (recordProductDataRepositoryObject)
+ {
+ stopwatch.Start();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = @"
+ INSERT INTO RecordProductData
+ (ProductName, ProductSN, InspectionOperator, NGType, NGSize,DetectionTime,CameraInspection)
+ VALUES
+ (@ProductName, @ProductSN, @InspectionOperator, @NGType, @NGSize, @DetectionTime, @CameraInspection)";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@ProductName", record.ProductName);
+ cmd.Parameters.AddWithValue("@ProductSN", record.ProductSN);
+ cmd.Parameters.AddWithValue("@InspectionOperator", record.InspectionOperator);
+ cmd.Parameters.AddWithValue("@NGType", record.NGType);
+ cmd.Parameters.AddWithValue("@NGSize", record.NGSize);
+ cmd.Parameters.AddWithValue("@DetectionTime", record.DetectionTime);
+ cmd.Parameters.AddWithValue("@CameraInspection", record.CameraInspection);
+ int rowsAffected = cmd.ExecuteNonQuery();
+ stopwatch.Stop();
+ Task.Factory.StartNew(() =>
+ {
+ LogHelper.Info($"娣诲姞淇℃伅鑰楁椂: {stopwatch.ElapsedMilliseconds}ms");
+ });
+ return rowsAffected > 0;
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"娣诲姞璁板綍澶辫触: {ex.Message}");
+ return false;
+ }
+ }
+ }
+ /// <summary>
+ /// 鏌ヨ鎵�鏈夋暟鎹�
+ /// </summary>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetAllRecords()
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = "SELECT * FROM RecordProductData ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"鏌ヨ璁板綍澶辫触: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 鏍规嵁浜у搧缂栧彿鏌ヨ
+ /// </summary>
+ /// <param name="productSN">浜у搧缂栧彿</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByProductNumber(string productSN)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = "SELECT * FROM RecordProductData WHERE productSN = @productSN ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@productSN", productSN);
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"鏍规嵁浜у搧缂栧彿鏌ヨ澶辫触: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 鏍规嵁鎿嶄綔鑰呮煡璇�
+ /// </summary>
+ /// <param name="inspectionOperator">鎿嶄綔鑰�</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByOperator(string inspectionOperator)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = "SELECT * FROM RecordProductData WHERE InspectionOperator = @InspectionOperator ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"鏍规嵁鎿嶄綔鑰呮煡璇㈠け璐�: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 鏍规嵁鏃堕棿娈垫煡璇� - 浣跨敤CreatedDate
+ /// </summary>
+ /// <param name="startTime">璧峰鏃堕棿</param>
+ /// <param name="endTime">缁撴潫鏃堕棿</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByTimeRange(DateTime startTime, DateTime endTime)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = @"
+ SELECT * FROM RecordProductData
+ WHERE CreatedDate BETWEEN @StartTime AND @EndTime
+ ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@StartTime", startTime);
+ cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1)); // 鍖呭惈缁撴潫鏃ユ湡鐨勫叏澶�
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"鏍规嵁鏃堕棿娈垫煡璇㈠け璐�: {ex.Message}");
+ }
+ return records;
+ }
+ }
+
+ /// <summary>
+ /// 缁勫悎鏌ヨ锛氫骇鍝佺紪鍙� + 鏃堕棿娈�
+ /// </summary>
+ /// <param name="productSN">浜у搧缂栧彿</param>
+ /// <param name="startTime">璧峰鏃堕棿</param>
+ /// <param name="endTime">缁撴潫鏃堕棿</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByProductAndTimeRange(string productSN, DateTime startTime, DateTime endTime)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = @"
+ SELECT * FROM RecordProductData
+ WHERE productSN = @productSN
+ AND CreatedDate BETWEEN @StartTime AND @EndTime
+ ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@productSN", productSN);
+ cmd.Parameters.AddWithValue("@StartTime", startTime);
+ cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"缁勫悎鏌ヨ澶辫触: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 缁勫悎鏌ヨ锛氭搷浣滆�� + 鏃堕棿娈�
+ /// </summary>
+ /// <param name="inspectionOperator">鎿嶄綔鑰�</param>
+ /// <param name="startTime">璧峰鏃堕棿</param>
+ /// <param name="endTime">缁撴潫鏃堕棿</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByOperatorAndTimeRange(string inspectionOperator, DateTime startTime, DateTime endTime)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = @"
+ SELECT * FROM RecordProductData
+ WHERE InspectionOperator = @InspectionOperator
+ AND CreatedDate BETWEEN @StartTime AND @EndTime
+ ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@InspectionOperator", inspectionOperator);
+ cmd.Parameters.AddWithValue("@StartTime", startTime);
+ cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"缁勫悎鏌ヨ澶辫触: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 楂樼骇缁勫悎鏌ヨ锛氭敮鎸佸涓潯浠�
+ /// </summary>
+ /// <param name="productSN">浜у搧缂栧彿</param>
+ /// <param name="operatorName">鎿嶄綔鑰�</param>
+ /// <param name="startTime">璧峰鏃堕棿</param>
+ /// <param name="endTime">缁撴潫鏃堕棿</param>
+ /// <returns>List<RecordProductData></returns>
+ public static List<RecordProductData> GetRecordsByMultipleConditions(
+ string productSN = null,
+ string operatorName = null,
+ DateTime? startTime = null,
+ DateTime? endTime = null)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ var records = new List<RecordProductData>();
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ var sql = "SELECT * FROM RecordProductData WHERE 1=1";
+ var parameters = new List<MySqlParameter>();
+ if (!string.IsNullOrEmpty(productSN))
+ {
+ sql += " AND ProductSN = @ProductSN";
+ parameters.Add(new MySqlParameter("@ProductSN", productSN));
+ }
+ if (!string.IsNullOrEmpty(operatorName))
+ {
+ sql += " AND MeasurementOperator = @InspectionOperator";
+ parameters.Add(new MySqlParameter("@InspectionOperator", operatorName));
+ }
+ if (startTime.HasValue)
+ {
+ sql += " AND CreatedDate >= @StartTime";
+ parameters.Add(new MySqlParameter("@StartTime", startTime.Value));
+ }
+ if (endTime.HasValue)
+ {
+ sql += " AND CreatedDate <= @EndTime";
+ parameters.Add(new MySqlParameter("@EndTime", endTime.Value.AddDays(1).AddSeconds(-1)));
+ }
+ sql += " ORDER BY CreatedDate DESC";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ foreach (var param in parameters)
+ {
+ cmd.Parameters.Add(param);
+ }
+ using (var reader = cmd.ExecuteReader())
+ {
+ while (reader.Read())
+ {
+ records.Add(MapReaderToRecord(reader));
+ }
+ }
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"楂樼骇缁勫悎鏌ヨ澶辫触: {ex.Message}");
+ }
+ return records;
+ }
+ }
+ /// <summary>
+ /// 缁熻鏌ヨ锛氳幏鍙栨煇涓椂闂存鍐呯殑璁板綍鏁伴噺
+ /// </summary>
+ /// <param name="startTime">璧峰鏃堕棿</param>
+ /// <param name="endTime">缁撴潫鏃堕棿</param>
+ /// <returns>璁板綍鏁伴噺</returns>
+ public static int GetRecordCountByTimeRange(DateTime startTime, DateTime endTime)
+ {
+ lock (recordProductDataRepositoryObject)
+ {
+ try
+ {
+ using (var connection = DatabaseRecordProductDataHelper.GetConnection())
+ {
+ connection.Open();
+ string sql = @"
+ SELECT COUNT(*) FROM RecordProductData
+ WHERE CreatedDate BETWEEN @StartTime AND @EndTime";
+ using (var cmd = new MySqlCommand(sql, connection))
+ {
+ cmd.Parameters.AddWithValue("@StartTime", startTime);
+ cmd.Parameters.AddWithValue("@EndTime", endTime.AddDays(1).AddSeconds(-1));
+ return Convert.ToInt32(cmd.ExecuteScalar());
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ AsyncLogHelper.Error($"缁熻鏌ヨ澶辫触: {ex.Message}");
+ return 0;
+ }
+ }
+ }
+ /// <summary>
+ /// 杈呭姪鏂规硶锛氬皢鏁版嵁璇诲彇鍣ㄦ槧灏勫埌RecordProductData瀵硅薄
+ /// </summary>
+ /// <param name="reader">MySqlDataReader reader</param>
+ /// <returns>RecordProductData</returns>
+ private static RecordProductData MapReaderToRecord(MySqlDataReader reader)
+ {
+ return new RecordProductData
+ {
+ ProductName = reader["ProductName"]?.ToString() ?? string.Empty,
+ ProductSN = reader["ProductSN"]?.ToString() ?? string.Empty,
+ InspectionOperator = reader["InspectionOperator"]?.ToString() ?? string.Empty,
+ NGType = reader["NGType"]?.ToString() ?? string.Empty,
+ NGSize = reader["NGSize"]?.ToString() ?? string.Empty,
+ DetectionTime = reader["DetectionTime"]?.ToString() ?? string.Empty,
+ CameraInspection = reader["CameraInspection"]?.ToString() ?? string.Empty,
+ };
+ }
+ }
+}
diff --git a/LB_SmartVisionCommon/UserData.cs b/LB_SmartVisionCommon/UserData.cs
index 4a09bcf..24b2f03 100644
--- a/LB_SmartVisionCommon/UserData.cs
+++ b/LB_SmartVisionCommon/UserData.cs
@@ -33,12 +33,14 @@
/// 鍛樺伐璐﹀彿
/// </summary>
[Category("RecordUserData"), PropertyOrder(3)]
- [DisplayName("璐﹀彿")]
+ [DisplayName("鍛樺伐璐﹀彿")]
[Browsable(true)]
public string EmployeeAccount { get; set; }
/// <summary>
/// 鍛樺伐瀵嗙爜
/// </summary>
+ [Category("RecordUserData"), PropertyOrder(4)]
+ [DisplayName("鍛樺伐瀵嗙爜")]
[Browsable(false)]
public string EmployeePassword { get; set; }
/// <summary>
diff --git a/LB_VisionProcesses/Communicators/SiemensS7/SiemensLBS7.cs b/LB_VisionProcesses/Communicators/SiemensS7/SiemensLBS7.cs
index b82d523..006d3c6 100644
--- a/LB_VisionProcesses/Communicators/SiemensS7/SiemensLBS7.cs
+++ b/LB_VisionProcesses/Communicators/SiemensS7/SiemensLBS7.cs
@@ -32,6 +32,7 @@
short slot;
short.TryParse(CommunicatorConnections["绔彛"].ToString(), out slot);
S7.Net.CpuType cpuType = (CpuType)CommunicatorConnections["鍨嬪彿"];
+ variable = CommunicatorConnections["鍙橀噺鍦板潃"].ToString();
plc = new Plc(cpuType, IP, 0, slot);
plc.Open();
return true;
@@ -60,8 +61,19 @@
{
try
{
- plc.Write(variable, message);
- return true;
+ if (plc!=null)
+ {
+ if (string.IsNullOrEmpty(variable))
+ {
+ variable = CommunicatorConnections["鍙橀噺鍦板潃"].ToString();
+ }
+ plc.Write(variable, message);
+ return true;
+ }
+ else
+ {
+ return false;
+ }
}
catch
{
--
Gitblit v1.9.3