.Net Core中使用Dapper构建泛型仓储

前言:Dapper是.NET的简单对象映射器,在速度方面拥有ORM的称号,与使用原始ADO.NET读取数据一样快。ORM是对象关系映射器,它负责数据库和编程语言之间的映射。

仓储主要是用来解耦业务逻辑层与数据访问层,降低耦合,使得程序在后期有很强的扩展性。

首先新建四个类库

IRepository:

.Net Core中使用Dapper构建泛型仓储

 Repository:

.Net Core中使用Dapper构建泛型仓储

引用IRepository

.Net Core中使用Dapper构建泛型仓储

 IServices:

.Net Core中使用Dapper构建泛型仓储

 Services:

.Net Core中使用Dapper构建泛型仓储

 引用IServices、IRepository

.Net Core中使用Dapper构建泛型仓储

 导入两个包:Dapper,Npgsql(我用的是pgsql数据库)

.Net Core中使用Dapper构建泛型仓储

.Net Core中使用Dapper构建泛型仓储

 万事俱备,上代码了。

IBaseRepository

 public interface IBaseRepository<TEntity> where TEntity : class, new()
    {
        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定实体泛型</returns>
        Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 返回 dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定泛型集合</returns>
        Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);


        /// <summary>
        /// 简单分页,返回分页后的泛型集合
        /// </summary>
        /// <typeparam name="T">分页后的泛型集合</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="totalCount">返回 总记录数</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回分页后的泛型集合</returns>
        Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 2条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <param name="sql">2条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null);

        /// <summary>
        /// 3条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null);


        /// <summary>
        /// 4条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null);

        /// <summary>
        /// 5条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <typeparam name="TFive">实体集合五</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="tfiveList">返回第五条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null);


        /// <summary>
        /// 查询单个实体类型
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>泛型实体类型</returns>
        Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行sql语句,返回受影响的行数
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回受影响的行数</returns>
        Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行sql语句,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="sql">查询Sql语句</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回返回第一行第一列</returns>
        Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行存储过程,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="command">存储过程名称</param>
        /// <param name="paras">参数键值对</param>
        /// <returns>返回第一行第一列</returns>
        Task<TEntity> Execute(string command, Dictionary<string, object> paras);
    }

BaseRepository

  public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new()
    {
        private readonly IConfiguration _configuration;
        public BaseRepository(IConfiguration configuration) => this._configuration = configuration;

        /// <summary>
        /// 创建数据库连接,并打开连接
        /// 连接字符串写在 json 配置文件里面
        /// </summary>
        /// <returns>IDbConnection</returns>
        public IDbConnection GetOpenConn()
        {
            IDbConnection con = null;
            string connectionString = _configuration["Connection:dbContent"];
            con = new NpgsqlConnection(connectionString);
            try
            {
                con.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("数据库连接错误:" + ex.Message);
            }

            return con;
        }


        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定实体泛型</returns>
        public async Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection con = this.GetOpenConn())
            {
                return con.QueryFirst<TEntity>(sql, param, transaction, commandTimeout, commandType);
            }
        }

        /// <summary>
        /// 返回 dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public async Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (IDbConnection con = GetOpenConn())
            {
                IDataReader reader = con.ExecuteReader(sql, param, transaction, commandTimeout, commandType);
                DataSet ds = new XDataSet();
                ds.Load(reader, LoadOption.OverwriteChanges, null, new DataTable[] { });
                return ds;
            }
        }

/// <summary> /// 查询数据集合 /// </summary> /// <param name="sql">查询Sql语句或存储过程名称</param> /// <param name="param">参数值(可选)</param> /// <param name="transaction">事务名称(可选)</param> /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param> /// <param name="commandTimeout">超时时间(可选)</param> /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param> /// <returns>返回指定泛型集合</returns> public async Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { using (IDbConnection con = GetOpenConn()) { return con.Query<TEntity>(sql, param, transaction, buffered, commandTimeout, commandType); } } /// <summary> /// 简单分页,返回分页后的泛型集合 /// </summary> /// <typeparam name="T">分页后的泛型集合</typeparam> /// <param name="sql">查询Sql语句或存储过程名称</param> /// <param name="totalCount">返回 总记录数</param> /// <param name="param">参数值(可选)</param> /// <param name="transaction">事务名称(可选)</param> /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param> /// <param name="commandTimeout">超时时间(可选)</param> /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param> /// <returns>返回分页后的泛型集合</returns> public async Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { using (IDbConnection con = GetOpenConn()) { var multi = con.QueryMultiple(sql, param, transaction, commandTimeout, commandType); int totalCount = int.Parse(multi.Read<long>().Single().ToString()); return Tuple.Create<IEnumerable<TEntity>, int>(multi.Read<TEntity>(), totalCount); } } /// <summary> /// 2条Sql语句查询 /// </summary> /// <typeparam name="TFirst">实体集合一</typeparam> /// <typeparam name="TSecond">实体集合二</typeparam> /// <param name="sql">2条查询语句</param> /// <param name="tfList">返回第一条语句的实体集合</param> /// <param name="tsList">返回第二条语句的实体集合</param> /// <param name="param">参数值(可选)</param> public async Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null) { using (IDbConnection con = GetOpenConn()) { var multi = con.QueryMultiple(sql, param); var tfList = new List<TFirst>(); var tsList = new List<TSecond>(); if (!multi.IsConsumed) { tfList = multi.Read<TFirst>().ToList(); tsList = multi.Read<TSecond>().ToList(); } return Tuple.Create<List<TFirst>, List<TSecond>>(tfList, tsList); } } /// <summary> /// 3条Sql语句查询 /// </summary> /// <typeparam name="TFirst">实体集合一</typeparam> /// <typeparam name="TSecond">实体集合二</typeparam> /// <typeparam name="TThird">实体集合三</typeparam> /// <param name="sql">5条查询语句</param> /// <param name="tfList">返回第一条语句的实体集合</param> /// <param name="tsList">返回第二条语句的实体集合</param> /// <param name="ttList">返回第三条语句的实体集合</param> /// <param name="param">参数值(可选)</param> public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null) { using (IDbConnection con = GetOpenConn()) { var multi = con.QueryMultiple(sql, param); var tfList = new List<TFirst>(); var tsList = new List<TSecond>(); var ttList = new List<TThird>(); if (!multi.IsConsumed) { tfList = multi.Read<TFirst>().ToList(); tsList = multi.Read<TSecond>().ToList(); ttList = multi.Read<TThird>().ToList(); } return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>>(tfList, tsList, ttList); } } /// <summary> /// 4条Sql语句查询 /// </summary> /// <typeparam name="TFirst">实体集合一</typeparam> /// <typeparam name="TSecond">实体集合二</typeparam> /// <typeparam name="TThird">实体集合三</typeparam> /// <typeparam name="TFour">实体集合四</typeparam> /// <param name="sql">5条查询语句</param> /// <param name="tfList">返回第一条语句的实体集合</param> /// <param name="tsList">返回第二条语句的实体集合</param> /// <param name="ttList">返回第三条语句的实体集合</param> /// <param name="tfourList">返回第四条语句的实体集合</param> /// <param name="param">参数值(可选)</param> public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null) { using (IDbConnection con = GetOpenConn()) { var multi = con.QueryMultiple(sql, param); var tfList = new List<TFirst>(); var tsList = new List<TSecond>(); var ttList = new List<TThird>(); var tfourList = new List<TFour>(); if (!multi.IsConsumed) { tfList = multi.Read<TFirst>().ToList(); tsList = multi.Read<TSecond>().ToList(); ttList = multi.Read<TThird>().ToList(); tfourList = multi.Read<TFour>().ToList(); } return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>(tfList, tsList, ttList, tfourList); } } /// <summary> /// 5条Sql语句查询 /// </summary> /// <typeparam name="TFirst">实体集合一</typeparam> /// <typeparam name="TSecond">实体集合二</typeparam> /// <typeparam name="TThird">实体集合三</typeparam> /// <typeparam name="TFour">实体集合四</typeparam> /// <typeparam name="TFive">实体集合五</typeparam> /// <param name="sql">5条查询语句</param> /// <param name="tfList">返回第一条语句的实体集合</param> /// <param name="tsList">返回第二条语句的实体集合</param> /// <param name="ttList">返回第三条语句的实体集合</param> /// <param name="tfourList">返回第四条语句的实体集合</param> /// <param name="tfiveList">返回第五条语句的实体集合</param> /// <param name="param">参数值(可选)</param> public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null) { using (IDbConnection con = GetOpenConn()) { var multi = con.QueryMultiple(sql, param); var tfList = new List<TFirst>(); var tsList = new List<TSecond>(); var ttList = new List<TThird>(); var tfourList = new List<TFour>(); var tfiveList = new List<TFive>(); if (!multi.IsConsumed) { tfList = multi.Read<TFirst>().ToList(); tsList = multi.Read<TSecond>().ToList(); ttList = multi.Read<TThird>().ToList(); tfourList = multi.Read<TFour>().ToList(); tfiveList = multi.Read<TFive>().ToList(); } return Tuple.Create<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>(tfList, tsList, ttList, tfourList, tfiveList); } } /// <summary> /// 查询单个实体类型 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="sql">查询Sql语句或存储过程名称</param> /// <param name="param">参数值(可选)</param> /// <param name="transaction">事务名称(可选)</param> /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param> /// <param name="commandTimeout">超时时间(可选)</param> /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param> /// <returns>泛型实体类型</returns> public async Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { var dataResult = await QueryList(sql, param, transaction, buffered, commandTimeout, commandType); return dataResult != null && dataResult.Count() > 0 ? dataResult.ToList()[0] : new TEntity(); } /// <summary> /// 执行sql语句,返回受影响的行数 /// </summary> /// <param name="sql">查询Sql语句或存储过程名称</param> /// <param name="param">参数值(可选)</param> /// <param name="transaction">事务名称(可选)</param> /// <param name="commandTimeout">超时时间(可选)</param> /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param> /// <returns>返回受影响的行数</returns> public async Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) { using (IDbConnection con = GetOpenConn()) { return con.Execute(sql, param, transaction, commandTimeout, commandType); } } /// <summary> /// 执行sql语句,返回第一行第一列 /// </summary> /// <typeparam name="T">指定类型</typeparam> /// <param name="sql">查询Sql语句</param> /// <param name="param">参数值(可选)</param> /// <param name="transaction">事务名称(可选)</param> /// <param name="commandTimeout">超时时间(可选)</param> /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param> /// <returns>返回返回第一行第一列</returns> public async Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) { using (IDbConnection con = GetOpenConn()) { return con.ExecuteScalar<TEntity>(sql, param, transaction, commandTimeout, commandType); } } /// <summary> /// 执行存储过程,返回第一行第一列 /// </summary> /// <typeparam name="T">指定类型</typeparam> /// <param name="command">存储过程名称</param> /// <param name="paras">参数键值对</param> /// <returns>返回第一行第一列</returns> public async Task<TEntity> Execute(string command, Dictionary<string, object> paras) { using (IDbConnection con = GetOpenConn()) { IDbCommand com = con.CreateCommand(); com.CommandText = command; com.CommandType = CommandType.StoredProcedure; if (paras != null) { foreach (var item in paras.Keys) { IDbDataParameter para = com.CreateParameter(); para.Value = paras[item]; para.ParameterName = item; com.Parameters.Add(para); } } return (TEntity)com.ExecuteScalar(); } } /// <summary> /// 数据适配器,扩展Fill方法 /// .NET的DataSet.Load方法,底层调用DataAdapter.Fill(DataTable[], IDataReader, int, int) /// Dapper想要返回DataSet,需要重写Load方法,不必传入DataTable[],因为数组长度不确定 /// </summary> public class XLoadAdapter : DataAdapter { /// <summary> /// 数据适配器 /// </summary> public XLoadAdapter() { } /// <summary> /// 读取dataReader /// </summary> /// <param name="ds"></param> /// <param name="dataReader"></param> /// <param name="startRecord"></param> /// <param name="maxRecords"></param> /// <returns></returns> public int FillFromReader(DataSet ds, IDataReader dataReader, int startRecord, int maxRecords) { return this.Fill(ds, "Table", dataReader, startRecord, maxRecords); } } /// <summary> /// 扩展Load方法 /// </summary> public class XDataSet : DataSet { /// <summary> /// Dapper想要返回DataSet,需要重写Load方法 /// </summary> /// <param name="reader">IDataReader</param> /// <param name="loadOption">LoadOption</param> /// <param name="handler">FillErrorEventHandler</param> /// <param name="tables">DataTable</param> public override void Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler handler, params DataTable[] tables) { XLoadAdapter adapter = new XLoadAdapter { FillLoadOption = loadOption, MissingSchemaAction = MissingSchemaAction.AddWithKey }; if (handler != null) { adapter.FillError += handler; } adapter.FillFromReader(this, reader, 0, 0); if (!reader.IsClosed && !reader.NextResult()) { reader.Close(); } } } }

IBaseServices

   public interface IBaseServices<TEntity> where TEntity : class
    {
        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定实体泛型</returns>
        Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 返回 dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定泛型集合</returns>
        Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);


        /// <summary>
        /// 简单分页,返回分页后的泛型集合
        /// </summary>
        /// <typeparam name="T">分页后的泛型集合</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="totalCount">返回 总记录数</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回分页后的泛型集合</returns>
        Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 2条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <param name="sql">2条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null);

        /// <summary>
        /// 3条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null);


        /// <summary>
        /// 4条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null);

        /// <summary>
        /// 5条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <typeparam name="TFive">实体集合五</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="tfiveList">返回第五条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null);


        /// <summary>
        /// 查询单个实体类型
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>泛型实体类型</returns>
        Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行sql语句,返回受影响的行数
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回受影响的行数</returns>
        Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行sql语句,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="sql">查询Sql语句</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回返回第一行第一列</returns>
        Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行存储过程,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="command">存储过程名称</param>
        /// <param name="paras">参数键值对</param>
        /// <returns>返回第一行第一列</returns>
        Task<TEntity> Execute(string command, Dictionary<string, object> paras);
    }

BaseServices

 public class BaseServices<TEntity> : IBaseServices<TEntity> where TEntity : class, new()
    {
        public IBaseRepository<TEntity> BaseDal;

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定实体泛型</returns>
        public async Task<TEntity> QueryFirst(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return await BaseDal.QueryFirst(sql, param, transaction, commandTimeout, commandType);
        }

        public Task<DataSet> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return BaseDal.Query(sql, param, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 查询数据集合
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回指定泛型集合</returns>
        public async Task<IEnumerable<TEntity>> QueryList(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return await BaseDal.QueryList(sql, param, transaction, buffered, commandTimeout, commandType);
        }


        /// <summary>
        /// 简单分页,返回分页后的泛型集合
        /// </summary>
        /// <typeparam name="T">分页后的泛型集合</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="totalCount">返回 总记录数</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回分页后的泛型集合</returns>
        public async Task<Tuple<IEnumerable<TEntity>, int>> QueryPagination(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {

            return await BaseDal.QueryPagination(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 2条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <param name="sql">2条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        public async Task<Tuple<List<TFirst>, List<TSecond>>> QueryMultiple<TFirst, TSecond>(string sql, object param = null)
        {

            return await BaseDal.QueryMultiple<TFirst, TSecond>(sql, param);
        }

        /// <summary>
        /// 3条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null)
        {
            return await BaseDal.QueryMultiple<TFirst, TSecond, TThird>(sql, param);
        }

        /// <summary>
        /// 4条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>>> QueryMultiple<TFirst, TSecond, TThird, TFour>(string sql, object param = null)
        {

            return await BaseDal.QueryMultiple<TFirst, TSecond, TThird, TFour>(sql, param);
        }

        /// <summary>
        /// 5条Sql语句查询
        /// </summary>
        /// <typeparam name="TFirst">实体集合一</typeparam>
        /// <typeparam name="TSecond">实体集合二</typeparam>
        /// <typeparam name="TThird">实体集合三</typeparam>
        /// <typeparam name="TFour">实体集合四</typeparam>
        /// <typeparam name="TFive">实体集合五</typeparam>
        /// <param name="sql">5条查询语句</param>
        /// <param name="tfList">返回第一条语句的实体集合</param>
        /// <param name="tsList">返回第二条语句的实体集合</param>
        /// <param name="ttList">返回第三条语句的实体集合</param>
        /// <param name="tfourList">返回第四条语句的实体集合</param>
        /// <param name="tfiveList">返回第五条语句的实体集合</param>
        /// <param name="param">参数值(可选)</param>
        public async Task<Tuple<List<TFirst>, List<TSecond>, List<TThird>, List<TFour>, List<TFive>>> QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(string sql, object param = null)
        {
            return await BaseDal.QueryMultiple<TFirst, TSecond, TThird, TFour, TFive>(sql, param);
        }

        /// <summary>
        /// 查询单个实体类型
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="buffered">是否将查询结果缓存到内存中(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>泛型实体类型</returns>
        public async Task<TEntity> QueryOne(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            return await BaseDal.QueryOne(sql, param, transaction, buffered, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行sql语句,返回受影响的行数
        /// </summary>
        /// <param name="sql">查询Sql语句或存储过程名称</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回受影响的行数</returns>
        public async Task<int> Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return await BaseDal.Execute(sql, param, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行sql语句,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="sql">查询Sql语句</param>
        /// <param name="param">参数值(可选)</param>
        /// <param name="transaction">事务名称(可选)</param>
        /// <param name="commandTimeout">超时时间(可选)</param>
        /// <param name="commandType">指定如果解释sql字符串:语句/存储过程(可选)</param>
        /// <returns>返回返回第一行第一列</returns>
        public async Task<TEntity> ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return await ExecuteScalar(sql, param, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行存储过程,返回第一行第一列
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="command">存储过程名称</param>
        /// <param name="paras">参数键值对</param>
        /// <returns>返回第一行第一列</returns>
        public async Task<TEntity> Execute(string command, Dictionary<string, object> paras)
        {
            return await BaseDal.Execute(command, paras);
        }
    }

下面写一个简单的查询,用户登录判断用户名与密码

.Net Core中使用Dapper构建泛型仓储

   public interface IUserInfoRepository: IBaseRepository<UserInfo>
    {
    }

.Net Core中使用Dapper构建泛型仓储

  public class UserInfoRepository : BaseRepository<UserInfo>, IUserInfoRepository
    {
        public UserInfoRepository(IConfiguration configuration) : base(configuration)
        {
        }
    }

.Net Core中使用Dapper构建泛型仓储

    public interface IUserInfoServices:IBaseServices<UserInfo>
    {
        Task<IEnumerable<UserInfo>> QueryUserInfo(string userName, string password);
    }

.Net Core中使用Dapper构建泛型仓储

 public class UserInfoServices : BaseServices<UserInfo>, IUserInfoServices
    {
        IUserInfoRepository _dal;
        public UserInfoServices(IUserInfoRepository dal)
        {
            this._dal = dal;
            base.BaseDal = dal;
        }

        public async Task<IEnumerable<UserInfo>> QueryUserInfo(string userName, string password)
        {var Result = await _dal.QueryList($"SELECT * FROM user_info where user_name=‘{userName}‘ and password=‘{password}‘ limit 1 OFFSET 0");
            return Result;
        }
    }

在控制器中以依赖注入的形式使用(前提是你要借助IOC容器注入好你要使用的对象),如果对此有困惑,可看下面这篇博客:

https://www.cnblogs.com/zhangnever/p/12324133.html

.Net Core中使用Dapper构建泛型仓储

 至此就没有了,各位同学可以再行封装。

如有不足的地方,还望见谅!

.Net Core中使用Dapper构建泛型仓储

.Net Core中使用Dapper构建泛型仓储

上一篇:微信公众号开发之样式库


下一篇:PyQt5高清屏幕自适应设置 QApplication.setAttribute(Qt.AA_EnableHighDpiScaling)