网站搭建实录————数据库接入

MySQL接入

1. 使用NuGet获取MySQL开发库mysql.data
2. 创建MySQL服务

  public class DbService : IDbService
    {
        private readonly string _connectionString;
        private readonly ILogger<DbService> _logger;

        public DbService(IConfiguration configuration, ILogger<DbService> logger)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
            _logger = logger;
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        private async Task<MySqlConnection> GetConnectionAsync()
        {
            try
            {
                var connection = new MySqlConnection(_connectionString);
                await connection.OpenAsync();
                return connection;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "数据库连接失败");
                throw new Exception("数据库连接失败", ex);
            }
        }

        /// <summary>
        /// 执行查询并返回结果集
        /// </summary>
        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null)
        {
            using var connection = await GetConnectionAsync();
            try
            {
                var command = new MySqlCommand(sql, connection);
                if (param != null)
                {
                    foreach (var prop in param.GetType().GetProperties())
                    {
                        command.Parameters.AddWithValue($"@{prop.Name}", prop.GetValue(param) ?? DBNull.Value);
                    }
                }

                using var reader = await command.ExecuteReaderAsync();
                var list = new List<T>();
                var properties = typeof(T).GetProperties()
                    .Select(p => new
                    {
                        Property = p,
                        ColumnAttr = p.GetCustomAttributes(typeof(ColumnAttribute), false)
                            .FirstOrDefault() as ColumnAttribute
                    })
                    .ToList();

                while (await reader.ReadAsync())
                {
                    var item = Activator.CreateInstance<T>();
                    foreach (var prop in properties)
                    {
                        var columnName = prop.ColumnAttr?.Name ?? prop.Property.Name.ToLower();
                        if (!reader.IsDBNull(reader.GetOrdinal(columnName)))
                        {
                            prop.Property.SetValue(item, reader[columnName]);
                        }
                    }
                    list.Add(item);
                }

                return list;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "执行查询时发生错误: {Sql}", sql);
                throw new Exception("执行查询时发生错误", ex);
            }
        }

        /// <summary>
        /// 执行非查询SQL语句
        /// </summary>
        public async Task<int> ExecuteAsync(string sql, object param = null)
        {
            using var connection = await GetConnectionAsync();
            try
            {
                var command = new MySqlCommand(sql, connection);
                if (param != null)
                {
                    foreach (var prop in param.GetType().GetProperties())
                    {
                        command.Parameters.AddWithValue($"@{prop.Name}", prop.GetValue(param) ?? DBNull.Value);
                    }
                }

                return await command.ExecuteNonQueryAsync();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "执行非查询命令时发生错误: {Sql}", sql);
                throw new Exception("执行非查询命令时发生错误", ex);
            }
        }
    }