From 5ddadba291ea2d9dba78259973594a4664b94f57 Mon Sep 17 00:00:00 2001
From: C3032 <C3032@BC3032>
Date: 星期四, 08 一月 2026 16:45:35 +0800
Subject: [PATCH] 简化相机逻辑，支持手动数据处理
---
LB_SmartVision/SQL/LB_SqlCommand.cs | 552 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 552 insertions(+), 0 deletions(-)
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;
+ }
+ }
+}
--
Gitblit v1.9.3