SimulationServerYL/ThirdParty/MySql/MySQLHelper.cs

using Cysharp.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MySQLTool
{
    public static class MySQLHelper
    {
        public static string connectionString = $"SERVER=server;DATABASE=database;UID=uid;PASSWORD=password;";

        #region 同步操作
        /// <summary>
        /// 同步执行查询操作
        /// </summary>
        /// <param name="query">sql 语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>字典数据</returns>
        public static List<Dictionary<string, string>> ExecuteQuery(string query, Dictionary<string, object> parameters = null)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                MySqlCommand command = new MySqlCommand(query, connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                MySqlDataReader dataReader = command.ExecuteReader();

                while (dataReader.Read())
                {
                    var row = new Dictionary<string, string>();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        row.Add(dataReader.GetName(i), dataReader[i].ToString());
                    }
                    result.Add(row);
                }

                dataReader.Close();
            }

            return result;
        }

        /// <summary>
        /// 同步执行一条 sql 语句
        /// </summary>
        /// <param name="query">sql 语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>操作条数</returns>
        public static int ExecuteNonQuery(string query, Dictionary<string, object> parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                MySqlCommand command = new MySqlCommand(query, connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 同步查询
        /// </summary>
        /// <param name="talbeName">表名</param>
        /// <param name="parameters">参数</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>数据列表</returns>
        public static List<T> Query<T>(string talbeName, Dictionary<string, object> parameters = null) where T : new()
        {
            List<T> result = new List<T>();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                MySqlCommand command = new MySqlCommand($"SELECT * FROM {talbeName}", connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                using (MySqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        T item = new T();
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            var propertyName = dataReader.GetName(i);
                            var propertyInfo = typeof(T).GetProperty(propertyName);
                            if (propertyInfo != null && dataReader[propertyName] != DBNull.Value)
                            {
                                object value = Convert.ChangeType(dataReader[propertyName], propertyInfo.PropertyType);
                                propertyInfo.SetValue(item, value, null);
                            }
                        }
                        result.Add(item);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 同步插入数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="data">数据</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>操作条数</returns>
        public static int Insert<T>(string table, T data)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                var properties = typeof(T).GetProperties();
                var columns = string.Join(", ", properties.Select(p => p.Name));
                var values = string.Join(", ", properties.Select(p => $"@{p.Name}"));

                MySqlCommand command = connection.CreateCommand();
                command.CommandText = $"INSERT INTO {table} ({columns}) VALUES ({values})";

                foreach (var property in properties)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = $"@{property.Name}";
                    parameter.Value = property.GetValue(data);
                    command.Parameters.Add(parameter);
                }

                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 同步更新数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="data">数据</param>
        /// <param name="condition">条件</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>操作条数</returns>
        public static int Update<T>(string table, T data, string condition)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                var properties = typeof(T).GetProperties();
                var updateColumns = string.Join(", ", properties.Select(p => $"{p.Name} = @{p.Name}"));

                MySqlCommand command = connection.CreateCommand();
                command.CommandText = $"UPDATE {table} SET {updateColumns} WHERE {condition}";

                foreach (var property in properties)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = $"@{property.Name}";
                    parameter.Value = property.GetValue(data);
                    command.Parameters.Add(parameter);
                }

                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 同步删除数据
        /// </summary>
        /// <param name="table">表明</param>
        /// <param name="condition">条件</param>
        /// <returns>操作条数</returns>
        public static int Delete(string table, string condition)
        {
            string query = $"DELETE FROM {table} WHERE {condition}";
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                MySqlCommand command = new MySqlCommand(query, connection);
                return command.ExecuteNonQuery();
            }
        }
        
        #endregion

        #region 异步操作
        
        /// <summary>
        /// 异步查询
        /// </summary>
        /// <param name="query">sql 语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>数据字典</returns>
        public static async UniTask<List<Dictionary<string, string>>> ExecuteQueryAsync(string query, Dictionary<string, object> parameters = null)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                MySqlCommand command = new MySqlCommand(query, connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                MySqlDataReader dataReader = command.ExecuteReader();

                while (await dataReader.ReadAsync())
                {
                    var row = new Dictionary<string, string>();
                    for (int i = 0; i < dataReader.FieldCount; i++)
                    {
                        row.Add(dataReader.GetName(i), dataReader[i].ToString());
                    }
                    result.Add(row);
                }

                dataReader.Close();
            }

            return result;
        }

        /// <summary>
        /// 异步执行一条语句
        /// </summary>
        /// <param name="query">sql 语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>操作条数</returns>
        public static async UniTask<int> ExecuteNonQueryAsync(string query, Dictionary<string, object> parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                MySqlCommand command = new MySqlCommand(query, connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                return await command.ExecuteNonQueryAsync();
            }
        }

        /// <summary>
        /// 异步查询数据
        /// </summary>
        /// <param name="talbeName">表明</param>
        /// <param name="parameters">参数</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>查询到的列表</returns>
        public static async UniTask<List<T>> QueryAsync<T>(string talbeName, Dictionary<string, object> parameters = null) where T : new()
        {
            List<T> result = new List<T>();

            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                MySqlCommand command = new MySqlCommand($"SELECT * FROM {talbeName}", connection);

                if (parameters != null)
                {
                    foreach (var parameter in parameters)
                    {
                        command.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }

                using (MySqlDataReader dataReader = command.ExecuteReader())
                {
                    while (await dataReader.ReadAsync())
                    {
                        T item = new T();
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            var propertyName = dataReader.GetName(i);
                            var propertyInfo = typeof(T).GetProperty(propertyName);
                            if (propertyInfo != null && dataReader[propertyName] != DBNull.Value)
                            {
                                object value = Convert.ChangeType(dataReader[propertyName], propertyInfo.PropertyType);
                                propertyInfo.SetValue(item, value, null);
                            }
                        }
                        result.Add(item);
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 异步插入一条数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="data">数据</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>操作条数</returns>
        public static async UniTask<int> InsertAsync<T>(string table, T data)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                var properties = typeof(T).GetProperties();
                var columns = string.Join(", ", properties.Select(p => p.Name));
                var values = string.Join(", ", properties.Select(p => $"@{p.Name}"));

                MySqlCommand command = connection.CreateCommand();
                command.CommandText = $"INSERT INTO {table} ({columns}) VALUES ({values})";

                foreach (var property in properties)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = $"@{property.Name}";
                    parameter.Value = property.GetValue(data);
                    command.Parameters.Add(parameter);
                }

                return await command.ExecuteNonQueryAsync();
            }
        }

        /// <summary>
        /// 异步更新数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="data">数据</param>
        /// <param name="condition">条件</param>
        /// <typeparam name="T">数据类型</typeparam>
        /// <returns>操作条数</returns>
        public static async UniTask<int> UpdateAsync<T>(string table, T data, string condition)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                var properties = typeof(T).GetProperties();
                var updateColumns = string.Join(", ", properties.Select(p => $"{p.Name} = @{p.Name}"));

                MySqlCommand command = connection.CreateCommand();
                command.CommandText = $"UPDATE {table} SET {updateColumns} WHERE {condition}";

                foreach (var property in properties)
                {
                    var parameter = command.CreateParameter();
                    parameter.ParameterName = $"@{property.Name}";
                    parameter.Value = property.GetValue(data);
                    command.Parameters.Add(parameter);
                }

                return await command.ExecuteNonQueryAsync();
            }
        }

        /// <summary>
        /// 异步删除数据
        /// </summary>
        /// <param name="table">表名</param>
        /// <param name="condition">条件</param>
        /// <returns>操作条数</returns>
        public static async UniTask<int> DeleteAsync(string table, string condition)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();
                string query = $"DELETE FROM {table} WHERE {condition}";
                MySqlCommand command = new MySqlCommand(query, connection);
                return await command.ExecuteNonQueryAsync();
            }
            
        }
        
        #endregion
    }
}