基于Dapper的泛型Repository

为减少代码量，这里实现一个基于Dapper的泛型Repository。

这里需要引用Dapper.dll和Dapper.Contrib.dll。

接口定义： 

/// <summary>
/// Repository接口
/// </summary>
/// <typeparam name="T"></typeparam>
public interface IRepository<T> where T : class, IEntity
{
    /// <summary>
    /// 查询
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    IEnumerable<T> Query(string sql, object param = null, CommandType? commandType = null);

    /// <summary>
    /// 删除行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    bool Delete(T entity);
    /// <summary>
    /// 删除表所有数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    bool DeleteAll();
    /// <summary>
    /// 获取行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="id"></param>
    /// <returns></returns>
    T Get(object id);
    /// <summary>
    /// 获取表的所有数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    IEnumerable<T> GetAll();
    /// <summary>
    /// 添加行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    long Insert(T entity);
    /// <summary>
    /// 更新行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    bool Update(T entity);

    /// <summary>
    /// 分页方法
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pageIndex">当前页码</param>
    /// <param name="pageSize">每页显示条数</param>
    /// <param name="param">参数</param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    PagedResult<T> GetPageList(string sql, int pageIndex, int pageSize, object param = null);
}

 

 

接口实现：

/// <summary>
/// 泛型Repository
/// </summary>
/// <typeparam name="T"></typeparam>
public class Repository<T> : IRepository<T> where T : class, IEntity
{
    private IUnitOfWork _unitOfWork;

    public Repository(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;            
    }
    /// <summary>
    /// 查询
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <param name="commandType"></param>
    /// <returns></returns>
    public IEnumerable<T> Query(string sql, object param = null, CommandType? commandType = null)
    {
        var r = _unitOfWork.DbConnection.Query<T>(sql, param: param, transaction: _unitOfWork.DbTransaction, commandType: commandType);
        return r;
    }
    /// <summary>
    /// 删除行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entityToDelete"></param>
    /// <returns></returns>
    public bool Delete(T entity)
    {
        var r = _unitOfWork.DbConnection.Delete<T>(entity, _unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 删除表所有数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public bool DeleteAll()
    {
        var r = _unitOfWork.DbConnection.DeleteAll<T>(_unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 获取行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="id"></param>
    /// <returns></returns>
    public T Get(object id)
    {
        var r = _unitOfWork.DbConnection.Get<T>(id, _unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 获取表的所有数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public IEnumerable<T> GetAll()
    {
        var r = _unitOfWork.DbConnection.GetAll<T>(_unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 添加行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    public long Insert(T entity)
    {
        var r = _unitOfWork.DbConnection.Insert<T>(entity, _unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 更新行数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    public bool Update(T entity)
    {
        var r = _unitOfWork.DbConnection.Update<T>(entity, _unitOfWork.DbTransaction);
        return r;
    }
    /// <summary>
    /// 分页方法
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="pageIndex">当前页码</param>
    /// <param name="pageSize">每页显示条数</param>
    /// <param name="param">参数</param>
    /// <param name="transaction"></param>
    /// <param name="commandTimeout"></param>
    /// <returns></returns>
    public PagedResult<T> GetPageList(string sql, int pageIndex, int pageSize, object param = null)
    {
       var pagingResult =  _unitOfWork.DbConnection.GetPageList<T>(sql, pageIndex, pageSize, param: param, transaction: _unitOfWork.DbTransaction);
        return pagingResult;
    }
}

 

GetPageList为Dapper的一个分页扩展：

/// <summary>
/// 分页方法
/// </summary>
/// <param name="sql"></param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示条数</param>
/// <param name="param">参数</param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns></returns>
public static PagedResult<T> GetPageList<T>(this IDbConnection connection, string sql, int pageIndex, int pageSize, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
{
    if (pageIndex < 1) pageIndex = 1;
    if (pageSize < 1) pageSize = 20;
    var startRow = (pageIndex - 1) * pageSize;

    //MySql分页
    sql = $"SELECT * FROM ({sql}) tt1  LIMIT {startRow},{pageSize};  SELECT COUNT(1) FROM ({sql}) tt2;";

    PagedResult<T> pagingResult = new PagedResult<T>();
    pagingResult.Paged.PageIndex = pageIndex;
    pagingResult.Paged.PageSize = pageSize;
    using (var result = connection.QueryMultiple(sql, param: param, transaction, commandTimeout))
    {
        var list = result.Read<T>();
        var totalCount = result.Read<long>().FirstOrDefault();
        pagingResult.Data = list;
        pagingResult.Paged.TotalRowCount = totalCount;
    }
    return pagingResult;
}

 

PagedResult类：

/// <summary>
/// 分页模型
/// </summary>
/// <typeparam name="T"></typeparam>
public class PagedResult<T>
{
    public PagedResult()
    {
        this.Paged = new Paged();
    }

    /// <summary>
    /// 结果
    /// </summary>
    public IEnumerable<T> Data { get; set; }

    /// <summary>
    /// 分页数据，包含数据总行数、当前页码、页数据行数和总的分页数
    /// </summary>
    public Paged Paged { get; set; }
}

 

Paged类：

/// <summary>
/// 分页数据
/// </summary>
public class Paged
{
    /// <summary>
    /// 当前页码
    /// </summary>
    public int PageIndex { get; set; }
    /// <summary>
    /// 页数据行数
    /// </summary>
    public int PageSize { get; set; }
    /// <summary>
    /// 总行数
    /// </summary>
    public long TotalRow { get; set; }
    /// <summary>
    /// 总的分页数
    /// </summary>
    public int TotalPage
    {
        get
        {
            if (this.TotalRow > 0 && this.PageSize > 0)
                return (int)Math.Ceiling((decimal)this.TotalRow / this.PageSize);
            else
                return 0;
        }
    }
}

 

在IServiceCollection容器中注册：

services.AddScoped(typeof(IRepository<>), typeof(Repository<>));