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

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);
        }
    }
}