using 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 实例和数据库(实际会读取dll.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>值存在True</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>值存在True</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 中更新行号的写法（假设indexName是自增列）
|
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();
|
|
// 复制表结构和数据（MySQL 简化写法）
|
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;
|
}
|
}
|
}
|
