493 lines
20 KiB
C#
493 lines
20 KiB
C#
using Cowain.Base.DBContext;
|
||
using Microsoft.EntityFrameworkCore;
|
||
using MySqlConnector;
|
||
using System.Data;
|
||
using System.Linq.Expressions;
|
||
using Dapper;
|
||
using Cowain.Base.IServices;
|
||
|
||
namespace Cowain.Base.Services;
|
||
|
||
public class BaseService : IBaseService
|
||
{
|
||
// 替换为 IDbContextFactory<SqlDbContext>
|
||
protected readonly IDbContextFactory<SqlDbContext> _dbContextFactory;
|
||
|
||
public BaseService(IDbContextFactory<SqlDbContext> dbContextFactory)
|
||
{
|
||
_dbContextFactory = dbContextFactory;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据ID删除实体
|
||
/// </summary>
|
||
public int Delete<T>(int Id) where T : class
|
||
{
|
||
// 每次操作创建独立上下文，using 自动释放
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
T? t = dbContext.Set<T>().Find(Id);
|
||
if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
|
||
dbContext.Set<T>().Remove(t);
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据ID异步删除实体（支持取消）
|
||
/// </summary>
|
||
public async Task<int> DeleteAsync<T>(int Id, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
var t = await dbContext.Set<T>().FindAsync(Id, cancellationToken).ConfigureAwait(false);
|
||
if (t == null) throw new KeyNotFoundException($"未找到类型 {typeof(T).Name}，Id={Id}");
|
||
dbContext.Set<T>().Remove(t);
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除实体
|
||
/// </summary>
|
||
public int Delete<T>(T t) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
var entry = dbContext.Entry(t);
|
||
if (entry.State == EntityState.Detached)
|
||
{
|
||
dbContext.Set<T>().Attach(t);
|
||
}
|
||
dbContext.Set<T>().Remove(t);
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 删除实体（异步，支持取消）
|
||
/// </summary>
|
||
public async Task<int> DeleteAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
var entry = dbContext.Entry(t);
|
||
if (entry.State == EntityState.Detached)
|
||
{
|
||
dbContext.Set<T>().Attach(t);
|
||
}
|
||
dbContext.Set<T>().Remove(t);
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 批量读取实体
|
||
/// </summary>
|
||
public List<T> Find<T>() where T : class
|
||
{
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
return dbContext.Set<T>().ToList();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 异步、安全的批量读取（支持取消）
|
||
/// </summary>
|
||
public async Task<List<T>> FindAsync<T>(CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
return await dbContext.Set<T>().ToListAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据ID查询实体
|
||
/// </summary>
|
||
public T? Find<T>(int id) where T : class
|
||
{
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
return dbContext.Set<T>().Find(id);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 根据ID异步查询实体（支持取消）
|
||
/// </summary>
|
||
public async Task<T?> FindAsync<T>(int id, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
return await dbContext.Set<T>().FindAsync(new object[] { id }, cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 带表达式的异步查询（支持取消）
|
||
/// </summary>
|
||
public async Task<T?> FirstOrDefaultAsync<T>(Expression<Func<T, bool>> funcWhere, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
return await dbContext.Set<T>().Where(funcWhere).FirstOrDefaultAsync(cancellationToken);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 插入实体
|
||
/// </summary>
|
||
public int Insert<T>(T t) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
dbContext.Set<T>().Add(t);
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 异步插入实体
|
||
/// </summary>
|
||
public async Task<int> InsertAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
dbContext.Set<T>().Add(t);
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 批量插入实体
|
||
/// </summary>
|
||
public int Insert<T>(IEnumerable<T> tList) where T : class
|
||
{
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
dbContext.Set<T>().AddRange(tList);
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 批量异步插入实体
|
||
/// </summary>
|
||
public async Task<int> InsertAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
dbContext.Set<T>().AddRange(tList);
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 带表达式的查询
|
||
/// </summary>
|
||
public List<T> Query<T>(Expression<Func<T, bool>>? funcWhere = null) where T : class
|
||
{
|
||
// 注意：Query 返回 IQueryable 时，上下文生命周期需由调用方管理，或改为立即执行（ToList/First 等）
|
||
var dbContext = _dbContextFactory.CreateDbContext();
|
||
IQueryable<T> query = dbContext.Set<T>();
|
||
if (funcWhere is not null)
|
||
{
|
||
query = query.Where(funcWhere);
|
||
}
|
||
return query.ToList();
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 带表达式的异步查询（支持取消、空条件查全部）
|
||
/// </summary>
|
||
public async Task<List<T>> QueryAsync<T>(
|
||
Expression<Func<T, bool>>? funcWhere = null, // 设置默认值，调用更便捷
|
||
CancellationToken cancellationToken = default)
|
||
where T : class
|
||
{
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
IQueryable<T> query = dbContext.Set<T>();
|
||
|
||
if (funcWhere is not null)
|
||
{
|
||
query = query.Where(funcWhere);
|
||
}
|
||
|
||
return await query.ToListAsync(cancellationToken);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 基础版：返回实体列表+总条数（无投影）
|
||
/// </summary>
|
||
public async Task<(List<T> Data, int TotalCount)> QueryAsync<T, TKey>(
|
||
Expression<Func<T, bool>>? funcWhere = null,
|
||
Expression<Func<T, TKey>>? orderBy = null,
|
||
bool isAscending = true,
|
||
int pageIndex = 1,
|
||
int pageSize = 20,
|
||
CancellationToken cancellationToken = default)
|
||
where T : class
|
||
{
|
||
pageIndex = pageIndex < 1 ? 1 : pageIndex;
|
||
pageSize = pageSize < 0 ? 0 : pageSize;
|
||
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
IQueryable<T> query = dbContext.Set<T>();
|
||
|
||
if (funcWhere is not null) query = query.Where(funcWhere);
|
||
if (orderBy is not null) query = isAscending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);
|
||
|
||
if (pageSize > 0)
|
||
{
|
||
long totalCountLong = await query.LongCountAsync(cancellationToken);
|
||
int totalCount = (int)Math.Min(totalCountLong, int.MaxValue);
|
||
var data = await query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(cancellationToken);
|
||
return (data, totalCount);
|
||
}
|
||
else
|
||
{
|
||
var data = await query.ToListAsync(cancellationToken);
|
||
return (data, data.Count);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 增强版：支持投影（Select映射），直接返回ViewModel列表+总条数
|
||
/// </summary>
|
||
/// <typeparam name="T">原实体类型</typeparam>
|
||
/// <typeparam name="TKey">排序字段类型</typeparam>
|
||
/// <typeparam name="TViewModel">目标ViewModel类型</typeparam>
|
||
/// <param name="selectExpression">投影表达式（EF会解析为SQL的SELECT字段）</param>
|
||
public async Task<(List<TViewModel> Data, int TotalCount)> QueryAsync<T, TKey, TViewModel>(
|
||
Expression<Func<T, TViewModel>> selectExpression, // 新增：投影映射表达式
|
||
Expression<Func<T, bool>>? funcWhere = null,
|
||
Expression<Func<T, TKey>>? orderBy = null,
|
||
bool isAscending = true,
|
||
int pageIndex = 1,
|
||
int pageSize = 20,
|
||
CancellationToken cancellationToken = default)
|
||
where T : class
|
||
{
|
||
pageIndex = pageIndex < 1 ? 1 : pageIndex;
|
||
pageSize = pageSize < 0 ? 0 : pageSize;
|
||
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
IQueryable<T> query = dbContext.Set<T>();
|
||
|
||
// 1. 过滤
|
||
if (funcWhere is not null) query = query.Where(funcWhere);
|
||
// 2. 排序
|
||
if (orderBy is not null) query = isAscending ? query.OrderBy(orderBy) : query.OrderByDescending(orderBy);
|
||
|
||
// 3. 先查总条数（注意：总条数要在投影前查，避免投影影响计数）
|
||
long totalCountLong = await query.LongCountAsync(cancellationToken);
|
||
int totalCount = (int)Math.Min(totalCountLong, int.MaxValue);
|
||
|
||
// 4. 分页+投影（EF会转换为SQL：SELECT Id, Name, LayOutX, LayOutY FROM ... LIMIT ...）
|
||
IQueryable<TViewModel> viewModelQuery = query
|
||
.Skip((pageIndex - 1) * pageSize)
|
||
.Take(pageSize)
|
||
.Select(selectExpression); // 内置投影，替代手动Select
|
||
|
||
// 5. 转为List
|
||
var data = await viewModelQuery.ToListAsync(cancellationToken);
|
||
|
||
return (data, totalCount);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 更新实体
|
||
/// </summary>
|
||
public int Update<T>(T t) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
var entry = dbContext.Entry(t);
|
||
if (entry.State == EntityState.Detached)
|
||
{
|
||
dbContext.Set<T>().Attach(t);
|
||
}
|
||
dbContext.Entry(t).State = EntityState.Modified;
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 异步更新实体（支持取消）
|
||
/// </summary>
|
||
public async Task<int> UpdateAsync<T>(T t, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(t);
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
var entry = dbContext.Entry(t);
|
||
if (entry.State == EntityState.Detached)
|
||
{
|
||
dbContext.Set<T>().Attach(t);
|
||
}
|
||
dbContext.Entry(t).State = EntityState.Modified;
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 批量更新实体
|
||
/// </summary>
|
||
public int Update<T>(IEnumerable<T> tList) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(tList);
|
||
var list = tList as IList<T> ?? tList.ToList();
|
||
if (list.Count == 0) return 0;
|
||
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
dbContext.Set<T>().UpdateRange(list);
|
||
return dbContext.SaveChanges();
|
||
}
|
||
|
||
/// <summary>
|
||
/// 异步批量更新实体（支持取消）
|
||
/// </summary>
|
||
public async Task<int> UpdateAsync<T>(IEnumerable<T> tList, CancellationToken cancellationToken = default) where T : class
|
||
{
|
||
ArgumentNullException.ThrowIfNull(tList);
|
||
var list = tList as IList<T> ?? tList.ToList();
|
||
if (list.Count == 0) return 0;
|
||
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
dbContext.Set<T>().UpdateRange(list);
|
||
return await dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用sql语句获取DataTable
|
||
/// </summary>
|
||
public DataTable GetDataTable(string sql, IEnumerable<MySqlParameter>? parameters = null)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
|
||
// 从工厂创建上下文获取连接字符串
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
using var cmd = new MySqlCommand(sql, conn)
|
||
{
|
||
CommandType = CommandType.Text
|
||
};
|
||
|
||
if (parameters != null)
|
||
{
|
||
foreach (var p in parameters) cmd.Parameters.Add(p);
|
||
}
|
||
using var adapter = new MySqlDataAdapter(cmd);
|
||
var table = new DataTable();
|
||
adapter.Fill(table);
|
||
return table;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 异步使用sql语句获取DataTable（支持取消）
|
||
/// </summary>
|
||
public async Task<DataTable> GetDataTableAsync(string sql, IEnumerable<MySqlParameter>? parameters = null, CancellationToken cancellationToken = default)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
|
||
|
||
using var cmd = new MySqlCommand(sql, conn) { CommandType = CommandType.Text };
|
||
if (parameters != null) cmd.Parameters.AddRange(parameters.ToArray());
|
||
|
||
using var reader = await cmd.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
|
||
var table = new DataTable();
|
||
table.Load(reader);
|
||
return table;
|
||
}
|
||
|
||
// ================= Dapper 辅助方法 =================
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 查询并映射到 POCO（同步）
|
||
/// </summary>
|
||
public IEnumerable<T> QueryByDapper<T>(string sql, object? param = null)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
conn.Open();
|
||
return conn.Query<T>(sql, param);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 查询并映射到 POCO（异步，支持 CancellationToken）
|
||
/// </summary>
|
||
public async Task<IEnumerable<T>> QueryByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
|
||
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
|
||
var result = await conn.QueryAsync<T>(command).ConfigureAwait(false);
|
||
return result;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 查询单条记录（同步）
|
||
/// </summary>
|
||
public T? QueryFirstOrDefaultByDapper<T>(string sql, object? param = null)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
conn.Open();
|
||
return conn.QueryFirstOrDefault<T>(sql, param);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 查询单条记录（异步）
|
||
/// </summary>
|
||
public async Task<T?> QueryFirstOrDefaultByDapperAsync<T>(string sql, object? param = null, CancellationToken cancellationToken = default)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
|
||
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
|
||
return await conn.QueryFirstOrDefaultAsync<T>(command).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 执行非查询 SQL（同步），返回受影响行数
|
||
/// </summary>
|
||
public int ExecuteByDapper(string sql, object? param = null)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
conn.Open();
|
||
return conn.Execute(sql, param);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 执行非查询 SQL（异步），返回受影响行数
|
||
/// </summary>
|
||
public async Task<int> ExecuteByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
|
||
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken);
|
||
return await conn.ExecuteAsync(command).ConfigureAwait(false);
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 执行 SQL 并返回 DataTable（异步，支持 CancellationToken）
|
||
/// </summary>
|
||
public async Task<DataTable> QueryToDataTableByDapperAsync(string sql, object? param = null, CancellationToken cancellationToken = default)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
await using var dbContext = await _dbContextFactory.CreateDbContextAsync(cancellationToken).ConfigureAwait(false);
|
||
await using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
await conn.OpenAsync(cancellationToken).ConfigureAwait(false);
|
||
var command = new CommandDefinition(sql, param, cancellationToken: cancellationToken, flags: CommandFlags.Buffered);
|
||
await using var reader = await conn.ExecuteReaderAsync(command).ConfigureAwait(false);
|
||
var table = new DataTable();
|
||
table.Load(reader);
|
||
return table;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 使用 Dapper 执行 SQL 并返回 DataTable（同步）
|
||
/// </summary>
|
||
public DataTable QueryToDataTableByDapper(string sql, object? param = null)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(sql)) throw new ArgumentException("sql 不能为空", nameof(sql));
|
||
using var dbContext = _dbContextFactory.CreateDbContext();
|
||
using var conn = new MySqlConnection(dbContext.Database.GetConnectionString());
|
||
conn.Open();
|
||
using var reader = conn.ExecuteReader(sql, param);
|
||
var table = new DataTable();
|
||
table.Load(reader);
|
||
return table;
|
||
}
|
||
}
|
