C#教程之[开源]Dapper Repository 一种实现方式

1. 接着上篇[开源]Entity Framework 6 Repository 一种实现方式
2. 由于Dapper 本身就是轻量级Orm特性，这里参考Creating a Data Repository using Dapper dynamic queries in dapper 代码，来解决实体类与Expression<Func<T, bool>> predicate问题；
3. 您可以通过Nuget：Install-Package MasterChief.DotNet.Core.Dapper；
4. 您可以通过GitHub：MasterChief 查看具体源码以及单元测试；
5. 欢迎Star，欢迎Issues；

---

插播一条求职#

1. 小弟拥有多年C#开发经验，从事过路灯，消防平台物联网平台开发，坐标上海；

1. 如果贵司在招聘，烦请大佬考虑下，联系邮箱:MeetYan@outlook.com；

基于Dapper 的Repository实现#

Copy

public abstract class DapperDbContextBase : IDbContext { #region Constructors /// <summary> /// 构造函数 /// </summary> /// <param name="connectString">连接字符串</param> protected DapperDbContextBase(string connectString) { ConnectString = connectString; } #endregion Constructors #region Properties /// <summary> /// 获取 是否开启事务提交 /// </summary> public IDbTransaction CurrentTransaction { get; private set; } #endregion Properties #region Fields /// <summary> /// 当前数据库连接 /// </summary> public IDbConnection CurrentConnection => TransactionEnabled ? CurrentTransaction.Connection : CreateConnection(); /// <summary> /// 获取 是否开启事务提交 /// </summary> public bool TransactionEnabled => CurrentTransaction != null; /// <summary> /// 连接字符串 /// </summary> protected readonly string ConnectString; #endregion Fields #region Methods /// <summary> /// 显式开启数据上下文事务 /// </summary> /// <param name="isolationLevel">指定连接的事务锁定行为</param> public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.Unspecified) { if (!TransactionEnabled) CurrentTransaction = CreateConnection().BeginTransaction(isolationLevel); } /// <summary> /// 提交当前上下文的事务更改 /// </summary> /// <exception cref="DataAccessException">提交数据更新时发生异常：" + msg</exception> public void Commit() { if (TransactionEnabled) try { CurrentTransaction.Commit(); } catch (Exception ex) { if (ex.InnerException?.InnerException is SqlException sqlEx) { var msg = DataBaseHelper.GetSqlExceptionMessage(sqlEx.Number); throw new DataAccessException("提交数据更新时发生异常：" + msg, sqlEx); } throw; } } /// <summary> /// 创建记录 /// </summary> /// <param name="entity">需要操作的实体类</param> /// <returns>操作是否成功</returns> public bool Create<T>(T entity) where T : ModelBase { ValidateOperator.Begin().NotNull(entity, "需要新增的数据记录"); // insert single data always return 0 but the data is inserted in database successfully //https://github.com/StackExchange/Dapper/issues/587 //List<T> data = new List<T>() { entity }; return CurrentConnection.Insert(new List<T> {entity}, CurrentTransaction) > 0; #region 测试代码 //string sql = @"INSERT INTO [dbo].[EFSample] // ([ID] // ,[CreateTime] // ,[ModifyTime] // ,[Available] // ,[UserName]) //VALUES // (@ID // ,@CreateTime // ,@ModifyTime // ,@Available // ,@UserName)"; //return CurrentConnection.Execute(sql, entity) > 0; #endregion 测试代码 } /// <summary> /// 创建数据库连接IDbConnection /// </summary> /// <returns></returns> public abstract IDbConnection CreateConnection(); /// <summary> /// 删除记录 /// </summary> /// <returns>操作是否成功</returns> /// <param name="entity">需要操作的实体类.</param> public bool Delete<T>(T entity) where T : ModelBase { ValidateOperator.Begin().NotNull(entity, "需要删除的数据记录"); return CurrentConnection.Delete(entity); } /// <summary> /// 执行与释放或重置非托管资源关联的应用程序定义的任务。 /// </summary> public void Dispose() { if (CurrentTransaction != null) { CurrentTransaction.Dispose(); CurrentTransaction = null; } CurrentConnection?.Dispose(); } /// <summary> /// 条件判断是否存在 /// </summary> /// <returns>是否存在</returns> /// <param name="predicate">判断条件委托</param> public bool Exist<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); var result = CurrentConnection.ExecuteScalar(queryResult.Sql, (object) queryResult.Param, CurrentTransaction); return result != null; } /// <summary> /// 根据id获取记录 /// </summary> /// <returns>记录</returns> /// <param name="id">id.</param> public T GetByKeyId<T>(object id) where T : ModelBase { ValidateOperator.Begin().NotNull(id, "Id"); return CurrentConnection.Get<T>(id, CurrentTransaction); } /// <summary> /// 条件获取记录集合 /// </summary> /// <returns>集合</returns> /// <param name="predicate">筛选条件.</param> public List<T> GetList<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); return CurrentConnection.Query<T>(queryResult.Sql, (object) queryResult.Param, CurrentTransaction).ToList(); } /// <summary> /// 条件获取记录第一条或者默认 /// </summary> /// <returns>记录</returns> /// <param name="predicate">筛选条件.</param> public T GetFirstOrDefault<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { var tableName = GetTableName<T>(); var queryResult = DynamicQuery.GetDynamicQuery(tableName, predicate); return CurrentConnection.QueryFirst<T>(queryResult.Sql, (object) queryResult.Param, CurrentTransaction); } /// <summary> /// 条件查询 /// </summary> /// <returns>IQueryable</returns> /// <param name="predicate">筛选条件.</param> public IQueryable<T> Query<T>(Expression<Func<T, bool>> predicate = null) where T : ModelBase { throw new NotImplementedException(); } /// <summary> /// 显式回滚事务，仅在显式开启事务后有用 /// </summary> public void Rollback() { if (TransactionEnabled) CurrentTransaction.Rollback(); } /// <summary> /// 执行Sql 脚本查询 /// </summary> /// <param name="sql">Sql语句</param> /// <param name="parameters">参数</param> /// <returns>集合</returns> public IEnumerable<T> SqlQuery<T>(string sql, IDbDataParameter[] parameters) { ValidateOperator.Begin() .NotNullOrEmpty(sql, "Sql语句"); var dataParameters = CreateParameter(parameters); return CurrentConnection.Query<T>(sql, dataParameters, CurrentTransaction); } /// <summary> /// 根据记录 /// </summary> /// <returns>操作是否成功.</returns> /// <param name="entity">实体类记录.</param> public bool Update<T>(T entity) where T : ModelBase { ValidateOperator.Begin().NotNull(entity, "需要更新的数据记录"); return CurrentConnection.Update(entity, CurrentTransaction); } private DapperParameter CreateParameter(IDbDataParameter[] parameters) { if (!(parameters?.Any() ?? false)) return null; var dataParameters = new DapperParameter(); foreach (var parameter in parameters) dataParameters.Add(parameter); return dataParameters; } private string GetTableName<T>() where T : ModelBase { var tableCfgInfo = AttributeHelper.Get<T, TableAttribute>(); return tableCfgInfo != null ? tableCfgInfo.Name.Trim() : typeof(T).Name; } #endregion Methods }

使用方法#

Copy

public class SampleService : ISampleService { private readonly IDatabaseContextFactory _contextFactory; public SampleService(IDatabaseContextFactory contextFactory) { _contextFactory = contextFactory; } /// <summary> /// 创建 /// </summary> /// <param name="sample">EFSample</param> /// <returns></returns> public bool Create(EfSample sample) { using (IDbContext context = _contextFactory.Create()) { return context.Create(sample); } } /// <summary> /// 条件查询 /// </summary> /// <param name="predicate">The predicate.</param> /// <returns></returns> /// <exception cref="NotImplementedException"></exception> public EfSample GetFirstOrDefault(Expression<Func<EfSample, bool>> predicate = null) { using (IDbContext context = _contextFactory.Create()) { return context.GetFirstOrDefault(predicate); } } /// <summary> /// 根据主键查询 /// </summary> /// <param name="id">The identifier.</param> /// <returns></returns> /// <exception cref="NotImplementedException"></exception> public EfSample GetByKeyId(Guid id) { using (IDbContext context = _contextFactory.Create()) { return context.GetByKeyId<EfSample>(id); } } /// <summary> /// 条件查询集合 /// </summary> /// <param name="predicate">The predicate.</param> /// <returns></returns> public List<EfSample> GetList(Expression<Func<EfSample, bool>> predicate = null) { using (IDbContext context = _contextFactory.Create()) { return context.GetList(predicate); } } /// <summary> /// 添加判断是否存在 /// </summary> /// <param name="predicate">The predicate.</param> /// <returns></returns> public bool Exist(Expression<Func<EfSample, bool>> predicate = null) { using (IDbContext context = _contextFactory.Create()) { return context.Exist(predicate); } } /// <summary> /// 脚本查询 /// </summary> /// <param name="sql">The SQL.</param> /// <param name="parameter">DbParameter[]</param> /// <returns></returns> public List<EfSample> SqlQuery(string sql, DbParameter[] parameter) { using (IDbContext context = _contextFactory.Create()) { return context.SqlQuery<EfSample>(sql, parameter)?.ToList(); } } /// <summary> /// 更新 /// </summary> /// <param name="sample">The sample.</param> /// <returns></returns> public bool Update(EfSample sample) { using (IDbContext context = _contextFactory.Create()) { return context.Update(sample); } } /// <summary> /// 事务 /// </summary> /// <param name="sample">The sample.</param> /// <param name="sample2">The sample2.</param> /// <returns></returns> public bool CreateWithTransaction(EfSample sample, EfSample sample2) { bool result; using (IDbContext context = _contextFactory.Create()) { try { context.BeginTransaction();//开启事务 context.Create(sample); context.Create(sample2); context.Commit(); result = true; } catch (Exception) { context.Rollback(); result = false; } } return result; } /// <summary> /// 删除 /// </summary> /// <param name="sample"></param> /// <returns></returns> public bool Delete(EfSample sample) { using (IDbContext context = _contextFactory.Create()) { return context.Delete(sample); } } }

结语#

1. Dapper与Entity Framework都是通过IRepository实现，所以您可以通过Ioc切换；
2. 该篇的单元测试写法与上篇一致；
3. 小弟不才，大佬轻拍；