Dapper 仓储接口类 实现总结
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using CustomerInterface; using Dapper; using System; using System.Collections.Generic; using System.Data; using System.Linq.Expressions; using System.Linq; using IITDeduction.CustomerDAL.Expressions; using DapperExtensions; using DapperExtensions.Mapper; using DapperExtensions.Sql; using IITDeduction.Common; namespace IITDeduction.CustomerDAL { 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; } } } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using DapperExtensions; using Spring.Web.UI; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Linq.Expressions; using System.Text; namespace CustomerInterface { 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; } }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Linq; using System.Text; namespace IITDeduction.CustomerDAL { public class DbConnectionFactory { private static readonly string connectionString; private static readonly string databaseType; static DbConnectionFactory() { connectionString = ConfigurationManager.AppSettings["IITDeductionConnection"]; databaseType = ConfigurationManager.AppSettings["IITDeductionProvider"]; } 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; } } }