比较好的Dapper封装的仓储实现类及扩展 相关来源：https://www.cnblogs.com/liuchang/articles/4220671.html

转载注明出处
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Net.Cache;
using System.Text;
using Dapper;
using DapperExtensions;
using TestData.Entity;
using TestData.Business;

namespace TestData.Business
{
    /// <summary>
    /// 业务逻辑的基类  包含了一些简单的操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public abstract class BaseManager<T> where T : class, IDataEntity
    {
        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> GetAll()
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.GetList<T>();
            }
        }

        /// <summary>
        /// 根绝sql查询
        /// </summary>
        /// <param name="sql">sql</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public IEnumerable<T> SelectCommond(string sql, object parameters = null)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<T>(sql, parameters);
            }
        }
        /// <summary>
        /// 根据表明查询
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public IEnumerable<T> GetAll(string name)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<T>(string.Format("select * from {0}", name));
            }
        }
        /// <summary>
        /// 根据编号查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetById(int? id)
        {
            if (id == null)
                return default(T);
            using (var conn = ConnectionFactory.Connection)
            {

                return conn.Get<T>(id.Value);
            }
        }
        /// <summary>
        /// 修改实体
        /// </summary>
        /// <param name="t">实体对象</param>
        /// <returns></returns>
        public bool Update(T t)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                t.EditorDate = DateTime.Now;
                return conn.Update(t);
            }
        }

        /// <summary>
        /// 得到数量
        /// </summary>
        /// <returns></returns>
        public int GetCount()
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Count<T>(null);
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="pageindex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public Tuple<int, IEnumerable<T>> GetPaged(object predicate, int pageindex, int pageSize)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                var sort = new List<ISort>
                    {
                        Predicates.Sort<T>(p=>p.EditorDate)
                    };

                var total = conn.Count<T>(predicate);
                return new Tuple<int, IEnumerable<T>>(total, conn.GetPage<T>(predicate, sort, pageindex, pageSize).ToList());
            }
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="t">实体对象</param>
        /// <returns></returns>
        public bool Insert(T t)
        {
            t.EditorDate = DateTime.Now;
            return this.Add(t, false) == t.Id;
        }
        /// <summary>
        /// 添加实体集合
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool Insert(List<T> list)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                list.ForEach(p => p.EditorDate = DateTime.Now);
                return conn.Insert(list);
            }
        }


        /// <summary>
        /// 添加实体
        /// </summary>
        /// <param name="t">实体对象</param>
        /// <param name="isAutoGenId"></param>
        /// <returns></returns>
        public int Add(T t, bool isAutoGenId = true)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                //var maxindex = conn.Query<int?>(string.Format("select max(indexs) from {0}", typeof(T).Name)).FirstOrDefault() ?? 0;
                //t.Indexs = maxindex + 1;
                return conn.Insert(t, isGenId: isAutoGenId);
            }
        }
        /// <summary>
        /// 根据编号删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int? id)
        {
            var obj = this.GetById(id);
            if (obj == null) return false;
            return this.Update(obj);
        }
        /// <summary>
        /// 根据编号修改
        /// </summary>
        /// <param name="id"></param>
        /// <param name="mark"></param>
        /// <returns></returns>
        public bool UpdataStatus(int? id)
        {
            var obj = this.GetById(id);
            if (obj == null) return false;
            return this.Update(obj);
        }
        /// <summary>
        /// 根据外键得到数据
        /// </summary>
        /// <param name="foreignKeyName">外键名称</param>
        /// <param name="foreignKeyValue">外键的值</param>
        /// <returns></returns>
        public IEnumerable<T> GetByForeignKey(string foreignKeyName, Guid foreignKeyValue)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, foreignKeyName), new { value = foreignKeyValue });
            }
        }
        /// <summary>
        /// 根据列查询
        /// </summary>
        /// <param name="fieldName">列名称</param>
        /// <param name="fieldValue">列的值</param>
        /// <returns></returns>
        public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<T>(string.Format("select * from {0} where {1}=@value", typeof(T).Name, fieldName), new { value = fieldValue });
            }
        }
        /// <summary>
        /// lxh 根据某列查询的方法--带排序
        /// </summary>
        /// <param name="fieldName">查询列名</param>
        /// <param name="fieldValue">条件内容</param>
        /// <param name="sortFieldName">排序列名</param>
        /// <returns></returns>
        public IEnumerable<T> GetByField(string fieldName, dynamic fieldValue, string sortFieldName)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<T>(string.Format("select * from {0} where {1}=@value order by {2}", typeof(T).Name, fieldName, sortFieldName), new { value = fieldValue });
            }
        }

        /// <summary>
        /// lxh 获取排序号的方法
        /// </summary>
        /// <returns></returns>
        public int GetNextSequence(T t)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query<int>(string.Format("select isnull(max(Sequence),0)+1 from {0}", typeof(T).Name)).FirstOrDefault();
            }
        }
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public List<dynamic> SelectProc(string procName, object obj = null)
        {
            using (var conn = ConnectionFactory.Connection)
            {
                return conn.Query(procName, obj, commandType: CommandType.StoredProcedure).ToList();
            }
        }
    }
}

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309

using CustomerInterface; using Dapper;   using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using System.Linq; using Demo.Expressions; using DapperExtensions; using DapperExtensions.Mapper; using DapperExtensions.Sql; using Demo.Common;   namespace Demo.Demo {     public class DapperRepository<T> : IDisposable,IRepository<T> where T : class     {                      private IDbConnection _innerConn = null;         private IDbTransaction _innerTran = null;           private IDbConnection _refConn = null;         private IDbTransaction _refTran = null;           /// <summary>         /// 返回仓储类当前连接         /// </summary>         public IDbConnection Connection         {             get             {                 if (_refConn != null)                 {                     return _refConn;                 }                 else                 {                     return _innerConn;                 }             }         }           /// <summary>         /// 返回仓储类当前事务         /// </summary>         public IDbTransaction Transaction         {             get             {                 if (_refTran != null)                 {                     return _refTran;                 }                 else                 {                     return _innerTran;                 }             }         }           public DapperRepository()         {             _innerConn = DbConnectionFactory.CreateDbConnection();             _innerConn.Open();             _innerConn.Execute("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");         }           public DapperRepository(IDbConnection conn,IDbTransaction trans=null)         {             if (conn == null)             {                 throw new Exception("conn can not be null!");             }               if (trans != null)             {                 if (trans.Connection != conn)                 {                     throw new Exception("trans'connection must be same as conn!");                 }             }             _refConn = conn;             _refTran = trans;         }           public void BeginTrans()         {             _innerTran = this.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);         }           public void Rollback()         {             if (Transaction != null)             {                 this.Transaction.Rollback();             }         }           public void Commit()         {             if (this.Transaction != null)             {                 this.Transaction.Commit();             }         }         /// <summary>         /// 添加数据         /// </summary>         /// <param name="entity">添加数据对象</param>         /// <returns>返回插入数据的主键</returns>         public dynamic Add(T entity)         {             return this.Connection.Insert<T>(entity,this.Transaction);         }         /// <summary>         /// 添加多组数据         /// </summary>         /// <param name="entitys">IEnumerable<T></param>         /// <returns></returns>         public List<dynamic> AddBatch(IEnumerable<T> entitys)         {             List<dynamic> retVal = new List<dynamic>();             foreach (T entity in entitys)             {                retVal.Add( Add(entity));             }             return retVal;         }         /// <summary>         /// 更新数据         /// </summary>         /// <param name="entity"></param>         /// <returns>bool</returns>         public bool Update(T entity)         {             return this.Connection.Update(entity,this.Transaction);         }         /// <summary>         /// 删除数据 根据对象删除         /// </summary>         /// <param name="entity"></param>         /// <returns>bool</returns>         public bool Delete(T entity)         {             return this.Connection.Delete(entity, this.Transaction);         }           /// <summary>         /// 删除数据  根据主键Id删除         /// </summary>         /// <param name="Id"></param>         /// <returns></returns>         public bool Delete(object predicate=null)         {             return this.Connection.Delete(predicate, this.Transaction);         }         /// <summary>         /// 获取数据         /// </summary>         /// <param name="Id"></param>         /// <returns></returns>         public T Get(object Id)         {             return this.Connection.Get<T>(Id,this.Transaction);         }         /// <summary>         /// 返回所有数据         /// </summary>         /// <returns></returns>         public IEnumerable<T> GetAll()         {             return this.Connection.GetList<T>(this.Transaction);         }           public IEnumerable<T> GetList(string sql, object parameters = null)         {             return this.Connection.Query<T>(sql, parameters,this.Transaction);         }           public int Execute(string sql, object parameters = null)         {             return this.Connection.Execute(sql, parameters, this.Transaction);         }             public IEnumerable<T> GetList(IPredicateGroup predGroup,  List<ISort> sort)         {             IEnumerable<T> list = this.Connection.GetList<T>(predGroup, sort, this.Transaction);             return list;           }           public Tuple<int, IEnumerable<T>> GetPage(IPredicateGroup predicate, int pageindex, int pageSize,List<ISort> sort)         {             var multi = this.Connection.GetPage<T>(predicate, sort, pageindex, pageSize,this.Transaction);             var count = multi.Count();             var results = multi.ToList();             return new Tuple<int, IEnumerable<T>>(count, results);         }           public PagedDataTable GetPagedTable(IPredicateGroup predicate, int pageindex, int pageSize, IList<ISort> sort)         {             var totalCount=this.Connection.Count<T>(predicate,this.Transaction);               List<T> multi = this.Connection.GetPage<T>(predicate, sort, pageindex, pageSize, this.Transaction).ToList();               PagedDataTable retVal = new PagedDataTable() {                 Data=IITDeductionDataType.Convert<T>(multi),                 TotalCount = totalCount,                 PageIndex=pageindex,                 PageSize=pageSize             };               return retVal;         }             public long Count(IPredicateGroup predicate)         {             return this.Connection.Count<T>(predicate, this.Transaction);         }           public  object ExecuteScalar(string query, object parameters = null)         {             return  this.Connection.ExecuteScalar(query, parameters,this.Transaction);         }           /// <summary>         /// 多条件组合查询         /// </summary>         /// <param name="predGroup"></param>         /// <returns>IEnumerable<T></returns>         public IEnumerable<T> QueryByPredGroup(IPredicateGroup predGroup, List<ISort> sort)         {             IEnumerable<T> list = this.Connection.GetList<T>(predGroup, sort);             return list;         }         /// <summary>         /// 查询返回List<object>         /// </summary>         /// <typeparam name="TAny">自定义传输返回的Obect</typeparam>         /// <param name="query">querySql</param>         /// <param name="parameters">querySql参数</param>         /// <returns></returns>         public IEnumerable<TAny> Query<TAny>(string query, object parameters = null) where TAny : class         {             return Connection.Query<TAny>(query, parameters, Transaction);         }         /// <summary>         /// 通过Linq方式查询         /// </summary>         /// <param name="expression"></param>         /// <returns></returns>         public T FirstOrDefault(Expression<Func<T, bool>> expression)         {             IPredicate ipredicate = expression.ToPredicateGroup();               var List = this.Connection.GetList<T>(ipredicate,null,this.Transaction).FirstOrDefault();             return List;         }         /// <summary>         /// 通过Linq获取LIST数据         /// </summary>         /// <param name="expression"></param>         /// <returns></returns>         public IEnumerable<T> GetList(Expression<Func<T, bool>> expression)         {             IPredicate ipredicate = expression.ToPredicateGroup();             IEnumerable<T> list = this.Connection.GetList<T>(expression, null, this.Transaction);             return list;         }             public string AddPageQuery(string sql)         {               string querySql ="select * from("+sql                                   + @")AS RowConstrainedResult                              WHERE RowNum >= (@PageIndex * @PageSize + 1)                                  AND RowNum <= (@PageIndex + 1) * @PageSize                              ORDER BY RowNum";               return querySql;         }               public void Dispose()         {             if (_innerTran != null)             {                 _innerTran.Dispose();                 _innerTran = null;             }               if (_innerConn != null)             {                 _innerConn.Close();                 _innerConn.Dispose();                 _innerConn = null;             }         }         } }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

public interface IRepository<T> where T : class    {        IDbConnection Connection{get;}        IDbTransaction Transaction{get;}          dynamic Add(T entity);          List<dynamic> AddBatch(IEnumerable<T> entitys);          bool Update(T entity);          bool Delete(T entity);          bool Delete(object Id);          T Get(object Id);          IEnumerable<T> GetAll();          IEnumerable<T> GetList(string sql, object parameters = null);          int Execute(string sql, object parameters = null);          long Count(IPredicateGroup predicate);        object ExecuteScalar(string query, object parameters = null);            T FirstOrDefault(Expression<Func<T, bool>> expression);        IEnumerable<T> GetList(Expression<Func<T, bool>> expression);        IEnumerable<T> GetList(IPredicateGroup predGroup, List<ISort> sort);          IEnumerable<TAny> Query<TAny>(string query, object parameters = null) where TAny : class;                       }

　　

?

1	predGroup使用方法

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

public class DbConnectionFactory {       private static readonly string connectionString;     private static readonly string databaseType;       static DbConnectionFactory()     {         connectionString = ConfigurationManager.AppSettings["Connection"];         databaseType = ConfigurationManager.AppSettings["Provider"];     }       public static IDbConnection CreateDbConnection()     {         IDbConnection connection = null;         switch (databaseType)         {             case "system.data.sqlclient":                 connection = new System.Data.SqlClient.SqlConnection(connectionString);                 break;             case "mysql":                 //connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);                 break;             case "oracle":                 //connection = new Oracle.DataAccess.Client.OracleConnection(connectionString);                 //connection = new System.Data.OracleClient.OracleConnection(connectionString);                 break;             case "db2":                 connection = new System.Data.OleDb.OleDbConnection(connectionString);                 break;             default:                 connection = new System.Data.SqlClient.SqlConnection(connectionString);                 break;         }         return connection;     } }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107

public PagedDataTable GetDataList()        {              var pgMain = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };              //选择筛选            var pgCheck = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };            {                              if (getDeclareInfoListDto.hireDateStart != null)                {                    //pgCheck.Predicates.Add(Predicates.Field<employee>(p => p.hiredate, Operator.Ge, DateTime.Parse(getDeclareInfoListDto.hireDateStart)));                    pgCheck.Predicates.Add(Predicates.Field<employee>(p => p.hiredate, Operator.Ge, getDeclareInfoListDto.hireDateStart));                }                if (getDeclareInfoListDto.hireDateEnd != null)                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.hiredate, Operator.Le, getDeclareInfoListDto.hireDateEnd));                }                if (getDeclareInfoListDto.quitDateStart != null)                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.quitdate, Operator.Ge, getDeclareInfoListDto.quitDateStart));                  }                if (getDeclareInfoListDto.quitDataEnd != null)                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.quitdate, Operator.Le, getDeclareInfoListDto.quitDataEnd));                }                if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.department))                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.department, Operator.Eq, getDeclareInfoListDto.department));                }                  if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.position))                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.position, Operator.Eq, getDeclareInfoListDto.position));                }                if (userLevel != 0)                {                    pgCheck.Predicates.Add(Predicates.Field<v_employee_page>(p => p.assignto, Operator.Eq, getDeclareInfoListDto.userCode));                }                }            pgMain.Predicates.Add(pgCheck);              var pgStatus = new PredicateGroup { Operator = GroupOperator.And, Predicates = new List<IPredicate>() };            pgStatus.Predicates.Add(Predicates.Field<v_employee_page>(p => p.status, Operator.Lt, 2));            pgMain.Predicates.Add(pgStatus);            //模糊筛选            var input = getDeclareInfoListDto.filterInput;            if (!string.IsNullOrWhiteSpace(input))            {                var pgInput = new PredicateGroup { Operator = GroupOperator.Or, Predicates = new List<IPredicate>() };                {                      var str = string.Format("%{0}%", input);                    pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.usercode, Operator.Like, str));                    pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.empname, Operator.Like, str));                    pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.mobilephone, Operator.Like, str));                    pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.certnumber, Operator.Like, str));                    pgInput.Predicates.Add(Predicates.Field<v_employee_page>(p => p.empcode, Operator.Like, str));                }                pgMain.Predicates.Add(pgInput);            }            IList<ISort> sort = new List<ISort>();            bool existSort = false;            if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.usercodeSort))            {                existSort = true;                bool reorder = false;                if (getDeclareInfoListDto.usercodeSort == "DESC")                {                    reorder = true;                }                sort.Add(new Sort { PropertyName = "empcode", Ascending = reorder });            }            if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.departmentSort))            {                existSort = true;                bool reorder = false;                if (getDeclareInfoListDto.departmentSort == "DESC")                {                    reorder = true;                }                sort.Add(new Sort { PropertyName = "department", Ascending = reorder });            }            if (!string.IsNullOrWhiteSpace(getDeclareInfoListDto.hireDateSort))            {                existSort = true;                bool reorder = false;                if (getDeclareInfoListDto.hireDateSort == "DESC")                {                    reorder = true;                }                sort.Add(new Sort { PropertyName = "hiredate", Ascending = reorder });            }              if (!existSort)            {                sort.Add(new Sort { PropertyName = "lastmodifytime", Ascending = false });            }                IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, pgMain);              retVal = _employeeDao.GetPagedTable(predGroup, getDeclareInfoListDto.page, getDeclareInfoListDto.limit, sort);            return retVal;