ExternalAPI_NET8/Common/Dapper/DapperHelper.cs

using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;
using Web.Core.Common.Helper;

namespace Web.Core.Common.Dapper;

public class DapperHelper : IDapperHelper
{
    public enum SysName
    {
        DEV,
        PRO,
        DataFill,
        Canteen
    }

    /// <summary>
    /// 查出一条记录的实体
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <returns></returns>
    public T QueryFirstOrDefault<T>(SysName sysName, string sql, object param = null)
    {
        using var _db = GetDbConnection(sysName);
        return _db.QueryFirstOrDefault<T>(sql, param);
    }

    public async Task<T> QueryFirstOrDefaultAsync<T>(SysName sysName, string sql, object param = null)
    {
        await using var _db = GetDbConnection(sysName);
        return await _db.QueryFirstOrDefaultAsync<T>(sql, param);
    }

    /// <summary>
    ///     查出多条记录的实体泛型集合
    /// </summary>
    /// <typeparam name="T">泛型T</typeparam>
    /// <returns></returns>
    public IEnumerable<T> Query<T>(SysName sysName, string sql, object param = null, IDbTransaction transaction = null,
        bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
    {
        using var _db = GetDbConnection(sysName);
        return _db.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
    }

    public async Task<IEnumerable<T>> QueryAsync<T>(SysName sysName, string sql, object param = null,
        IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        await using var _db = GetDbConnection(sysName);
        return await _db.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
    }

    public int Execute(SysName sysName, string sql, object param = null, IDbTransaction transaction = null,
        int? commandTimeout = null, CommandType? commandType = null)
    {
        using var _db = GetDbConnection(sysName);
        return _db.Execute(sql, param, transaction, commandTimeout, commandType);
    }

    public async Task<int> ExecuteAsync(SysName sysName, string sql, object param = null,
        IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        await using var _db = GetDbConnection(sysName);
        return await _db.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
    }

    public T ExecuteScalar<T>(SysName sysName, string sql, object param = null, IDbTransaction transaction = null,
        int? commandTimeout = null, CommandType? commandType = null)
    {
        using var _db = GetDbConnection(sysName);
        return _db.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
    }

    public async Task<T> ExecuteScalarAsync<T>(SysName sysName, string sql, object param = null,
        IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        await using var _db = GetDbConnection(sysName);
        return await _db.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
    }

    private SqlConnection GetDbConnection(SysName sysName)
    {
        SqlConnection db = sysName switch
        {
            SysName.DEV => new SqlConnection(AppSettings.GetConnectionString("DEV")),
            SysName.PRO => new SqlConnection(AppSettings.GetConnectionString("PRO")),
            SysName.DataFill => new SqlConnection(AppSettings.GetConnectionString("DataFill")),
            SysName.Canteen => new SqlConnection(AppSettings.GetConnectionString("Canteen")),
            _ => new SqlConnection(AppSettings.GetConnectionString("Default"))
        };

        //if (db.State != ConnectionState.Open) db.Open();
        return db;
    }
}