using System;
|
using System.Collections.Generic;
|
using System.Data.SqlClient;
|
using System.Data;
|
using System.Diagnostics;
|
using System.Linq;
|
using System.Text;
|
|
namespace DeviceITisation
|
{
|
public class MySqlCommand
|
{
|
/// <summary>
|
/// 连接字符串：指定 SQL Server 实例和数据库(实际会读取dll.config中的值来更新连接字符串)
|
/// </summary>
|
public static string ConnectionString = "Data Source=10.126.69.225 ;Initial Catalog=EngDB;User ID=app_eng;Password=Pa55wd4ENG@202205#";
|
|
/// <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;
|
|
// 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>值存在True</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>值存在True</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="columnNames">列名数组</param>
|
/// <param name="columnValues">列值数组</param>
|
/// <returns>受影响的行数</returns>
|
public static bool AddRow(string tableName, string[] columnNames, string[] keyColumns = null, params object[] columnValues)
|
{
|
if (columnNames.Length != columnValues.Length)
|
{
|
Debug.WriteLine("列名和列值的数量必须相同");
|
return false;
|
}
|
|
// 使用事务确保操作的原子性
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
{
|
connection.Open();
|
SqlTransaction transaction = connection.BeginTransaction();
|
|
try
|
{
|
// 1. 检查记录是否存在
|
bool recordExists = CheckRecordExists(connection, transaction, tableName, keyColumns, columnNames, columnValues);
|
|
//// 2. 根据检查结果执行更新或插入
|
if (recordExists)
|
UpdateRecord(connection, transaction, tableName, columnNames, columnValues, keyColumns);
|
else
|
InsertRecord(connection, transaction, tableName, columnNames, columnValues);
|
|
transaction.Commit();
|
return true;
|
}
|
catch (Exception ex)
|
{
|
transaction.Rollback();
|
Debug.WriteLine($"执行插入/更新操作时出错: {ex.Message}");
|
return false;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 检查记录是否存在
|
/// </summary>
|
private static bool CheckRecordExists(SqlConnection connection, SqlTransaction transaction,
|
string tableName, string[] keyColumns,
|
string[] allColumnNames, object[] allColumnValues)
|
{
|
string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
|
string sql = $"SELECT COUNT(1) FROM {tableName} WITH (UPDLOCK, SERIALIZABLE) WHERE {whereClause}";
|
|
using (SqlCommand command = new SqlCommand(sql, connection, transaction))
|
{
|
// 只添加键列参数
|
foreach (var keyCol in keyColumns)
|
{
|
int index = Array.IndexOf(allColumnNames, keyCol);
|
if (index >= 0)
|
command.Parameters.AddWithValue("@" + keyCol, allColumnValues[index] ?? DBNull.Value);
|
}
|
|
int count = (int)command.ExecuteScalar();
|
return count > 0;
|
}
|
}
|
|
public static bool CheckRecordExists(string tableName, string[] keyColumns,
|
string[] allColumnNames, params object[] allColumnValues)
|
{
|
string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
|
string sql = $"SELECT COUNT(1) FROM {tableName} WITH (UPDLOCK, SERIALIZABLE) WHERE {whereClause}";
|
|
// 使用事务确保操作的原子性
|
using (SqlConnection connection = new SqlConnection(ConnectionString))
|
{
|
connection.Open();
|
SqlTransaction transaction = connection.BeginTransaction();
|
|
using (SqlCommand command = new SqlCommand(sql, connection, transaction))
|
{
|
// 只添加键列参数
|
foreach (var keyCol in keyColumns)
|
{
|
int index = Array.IndexOf(allColumnNames, keyCol);
|
if (index >= 0)
|
command.Parameters.AddWithValue("@" + keyCol, allColumnValues[index] ?? DBNull.Value);
|
}
|
|
int count = (int)command.ExecuteScalar();
|
return count > 0;
|
}
|
}
|
}
|
|
/// <summary>
|
/// 更新记录
|
/// </summary>
|
private static void UpdateRecord(SqlConnection connection, SqlTransaction transaction,
|
string tableName, string[] columnNames, object[] columnValues,
|
string[] keyColumns)
|
{
|
// 只更新非键列
|
var updateColumns = columnNames.Except(keyColumns).ToList();
|
if (updateColumns.Count == 0)
|
{
|
Debug.WriteLine("没有需要更新的列");
|
return;
|
}
|
|
string setClause = string.Join(", ", updateColumns.Select(col => $"{col} = @{col}").ToArray());
|
string whereClause = string.Join(" AND ", keyColumns.Select(col => $"{col} = @{col}").ToArray());
|
string sql = $"UPDATE {tableName} SET {setClause} WHERE {whereClause}";
|
|
using (SqlCommand command = new SqlCommand(sql, connection, transaction))
|
{
|
// 添加所有参数
|
for (int i = 0; i < columnNames.Length; i++)
|
{
|
command.Parameters.AddWithValue("@" + columnNames[i], columnValues[i] ?? DBNull.Value);
|
}
|
|
int rowsAffected = command.ExecuteNonQuery();
|
Debug.WriteLine($"更新了 {rowsAffected} 行记录");
|
}
|
}
|
|
/// <summary>
|
/// 插入记录
|
/// </summary>
|
private static void InsertRecord(SqlConnection connection, SqlTransaction transaction,
|
string tableName, string[] columnNames, object[] columnValues)
|
{
|
string columns = string.Join(", ", columnNames);
|
string parameters = string.Join(", ", columnNames.Select(col => "@" + col).ToArray());
|
string sql = $"INSERT INTO {tableName} ({columns}) VALUES ({parameters})";
|
|
using (SqlCommand command = new SqlCommand(sql, connection, transaction))
|
{
|
// 添加所有参数
|
for (int i = 0; i < columnNames.Length; i++)
|
{
|
command.Parameters.AddWithValue("@" + columnNames[i], columnValues[i]);
|
}
|
|
int rowsAffected = command.ExecuteNonQuery();
|
Debug.WriteLine($"插入了 {rowsAffected} 行记录");
|
}
|
}
|
|
/// <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>
|
/// 创建数据库(需要master权限)
|
/// </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>
|
/// 删除数据库(需要master权限)
|
/// </summary>
|
/// <param name="tableName">表名</param>
|
/// <param name="connectionString"></param>
|
/// <returns>值存在True</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>
|
/// 复制表结构和数据(需要master权限)
|
/// </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;
|
}
|
}
|
}
|
