本篇我将带着大家一起来对 Dapper 进行下封装并实现基本的增删改查, 分页操作的同步异步方法的实现(已实现 MSSQL,MySQL,PgSQL). 同时我们再实现一下仓储层的代码生成器, 这样的话, 我们只需要结合业务来实现具体的业务部分的代码就可以了, 可以大大减少我们重复而又繁琐的增删改查操作, 多留点时间给生活充充电(不会偷懒的程序员不是一位好爸爸 / 好老公 / 好男朋友). 如果您觉得我的实现过程有所不妥的话, 您可以在评论区留言, 或者加入我们的千人. Net Core 实战项目交流群 637326624 交流. 另外如果您觉得我的文章对您有所帮助的话希望给个推荐以示支持. 项目的源代码我会托管在 GayHub 上, 地址在文章末尾会给出, 自认为代码写的很工整, 注释也很全, 你应该能看懂!
本文已收录至《.NET Core 实战项目之 CMS 第一章 入门篇 - 开篇及总体规划》
- /// <summary>
- /// yilezhu
- /// 2018.12.13
- /// 数据库连接工厂类
- /// </summary>
- public class ConnectionFactory
- {
- /// <summary>
- /// 获取数据库连接
- /// </summary>
- /// <param name="dbtype">数据库类型</param>
- /// <param name="conStr">数据库连接字符串</param>
- /// <returns > 数据库连接</returns>
- public static IDbConnection CreateConnection(string dbtype, string strConn)
- {
- if (dbtype.IsNullOrWhiteSpace())
- throw new ArgumentNullException("获取数据库连接居然不传数据库类型, 你想上天吗?");
- if (strConn.IsNullOrWhiteSpace())
- throw new ArgumentNullException("获取数据库连接居然不传数据库类型, 你想上天吗?");
- var dbType = GetDataBaseType(dbtype);
- return CreateConnection(dbType,strConn);
- }
- /// <summary>
- /// 获取数据库连接
- /// </summary>
- /// <param name="dbType">数据库类型</param>
- /// <param name="conStr">数据库连接字符串</param>
- /// <returns > 数据库连接</returns>
- public static IDbConnection CreateConnection(DatabaseType dbType, string strConn)
- {
- IDbConnection connection = null;
- if (strConn.IsNullOrWhiteSpace())
- throw new ArgumentNullException("获取数据库连接居然不传数据库类型, 你想上天吗?");
- switch (dbType)
- {
- case DatabaseType.SqlServer:
- connection = new SqlConnection(strConn);
- break;
- case DatabaseType.MySQL:
- connection = new MySqlConnection(strConn);
- break;
- case DatabaseType.PostgreSQL:
- connection = new NpgsqlConnection(strConn);
- break;
- default:
- throw new ArgumentNullException($"这是我的错, 还不支持的 {dbType.ToString()} 数据库类型");
- }
- if (connection.State == ConnectionState.Closed)
- {
- connection.Open();
- }
- return connection;
- }
- /// <summary>
- /// 转换数据库类型
- /// </summary>
- /// <param name="dbtype">数据库类型字符串</param>
- /// <returns > 数据库类型</returns>
- public static DatabaseType GetDataBaseType(string dbtype)
- {
- if (dbtype.IsNullOrWhiteSpace())
- throw new ArgumentNullException("获取数据库连接居然不传数据库类型, 你想上天吗?");
- DatabaseType returnValue = DatabaseType.SqlServer;
- foreach (DatabaseType dbType in Enum.GetValues(typeof(DatabaseType)))
- {
- if (dbType.ToString().Equals(dbtype, StringComparison.OrdinalIgnoreCase))
- {
- returnValue = dbType;
- break;
- }
- }
- return returnValue;
- }
- }
- /**
- *┌──────────────────────────────────────────────────────────────┐
- *│ 描 述:
- *│ 作 者: yilezhu
- *│ 版 本: 1.0
- *│ 创建时间: 2018/12/16 20:41:22
- *└──────────────────────────────────────────────────────────────┘
- *┌──────────────────────────────────────────────────────────────┐
- *│ 命名空间: Czar.Cms.Core.Repository
- *│ 接口名称: IBaseRepository
- *└──────────────────────────────────────────────────────────────┘
- */
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- namespace Czar.Cms.Core.Repository
- {
- public interface IBaseRepository<T,TKey> : IDisposable where T : class
- {
- #region 同步
- /// <summary>
- /// 通过主键获取实体对象
- /// </summary>
- /// <param name="id">主键 ID</param>
- /// <returns></returns>
- T Get(TKey id);
- /// <summary>
- /// 获取所有的数据
- /// </summary>
- /// <returns></returns>
- IEnumerable<T> GetList();
- /// <summary>
- /// 执行具有条件的查询, 并将结果映射到强类型列表
- /// </summary>
- /// <param name="whereConditions">条件</param>
- /// <returns></returns>
- IEnumerable<T> GetList(object whereConditions);
- /// <summary>
- /// 带参数的查询满足条件的数据
- /// </summary>
- /// <param name="conditions">条件</param>
- /// <param name="parameters">参数</param>
- /// <returns></returns>
- IEnumerable<T> GetList(string conditions, object parameters = null);
- /// <summary>
- /// 使用 where 子句执行查询, 并将结果映射到具有 Paging 的强类型 List
- /// </summary>
- /// <param name="pageNumber">页码</param>
- /// <param name="rowsPerPage">每页显示数据</param>
- /// <param name="conditions">查询条件</param>
- /// <param name="orderby">排序</param>
- /// <param name="parameters">参数</param>
- /// <returns></returns>
- IEnumerable<T> GetListPaged(int pageNumber, int rowsPerPage, string conditions, string orderby, object parameters = null);
- /// <summary>
- /// 插入一条记录并返回主键值(自增类型返回主键值, 否则返回 null)
- /// </summary>
- /// <param name="entity"></param>
- /// <returns></returns>
- int? Insert(T entity);
- /// <summary>
- /// 更新一条数据并返回影响的行数
- /// </summary>
- /// <param name="entity"></param>
- /// <returns > 影响的行数</returns>
- int Update(T entity);
- /// <summary>
- /// 根据实体主键删除一条数据
- /// </summary>
- /// <param name="id">主键</param>
- /// <returns > 影响的行数</returns>
- int Delete(TKey id);
- /// <summary>
- /// 根据实体删除一条数据
- /// </summary>
- /// <param name="entity">实体</param>
- /// <returns > 返回影响的行数</returns>
- int Delete(T entity);
- /// <summary>
- /// 条件删除多条记录
- /// </summary>
- /// <param name="whereConditions">条件</param>
- /// <param name="transaction">事务</param>
- /// <param name="commandTimeout">超时时间</param>
- /// <returns > 影响的行数</returns>
- int DeleteList(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null);
- /// <summary>
- /// 使用 where 子句删除多个记录
- /// </summary>
- /// <param name="conditions">wher 子句</param>
- /// <param name="parameters">参数</param>
- /// <param name="transaction">事务</param>
- /// <param name="commandTimeout">超时时间</param>
- /// <returns > 影响的行数</returns>
- int DeleteList(string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null);
- /// <summary>
- /// 满足条件的记录数量
- /// </summary>
- /// <param name="conditions"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- int RecordCount(string conditions = "", object parameters = null);
- #endregion
- #region 异步
- /// <summary>
- /// 通过主键获取实体对象
- /// </summary>
- /// <param name="id">主键 ID</param>
- /// <returns></returns>
- Task<T> GetAsync(TKey id);
- /// <summary>
- /// 获取所有的数据
- /// </summary>
- /// <returns></returns>
- Task<IEnumerable<T>> GetListAsync();
- /// <summary>
- /// 执行具有条件的查询, 并将结果映射到强类型列表
- /// </summary>
- /// <param name="whereConditions">条件</param>
- /// <returns></returns>
- Task<IEnumerable<T>> GetListAsync(object whereConditions);
- /// <summary>
- /// 带参数的查询满足条件的数据
- /// </summary>
- /// <param name="conditions">条件</param>
- /// <param name="parameters">参数</param>
- /// <returns></returns>
- Task<IEnumerable<T>> GetListAsync(string conditions, object parameters = null);
- /// <summary>
- /// 使用 where 子句执行查询, 并将结果映射到具有 Paging 的强类型 List
- /// </summary>
- /// <param name="pageNumber">页码</param>
- /// <param name="rowsPerPage">每页显示数据</param>
- /// <param name="conditions">查询条件</param>
- /// <param name="orderby">排序</param>
- /// <param name="parameters">参数</param>
- /// <returns></returns>
- Task<IEnumerable<T>> GetListPagedAsync(int pageNumber, int rowsPerPage, string conditions, string orderby, object parameters = null);
- /// <summary>
- /// 插入一条记录并返回主键值
- /// </summary>
- /// <param name="entity"></param>
- /// <returns></returns>
- Task<int?> InsertAsync(T entity);
- /// <summary>
- /// 更新一条数据并返回影响的行数
- /// </summary>
- /// <param name="entity"></param>
- /// <returns > 影响的行数</returns>
- Task<int> UpdateAsync(T entity);
- /// <summary>
- /// 根据实体主键删除一条数据
- /// </summary>
- /// <param name="id">主键</param>
- /// <returns > 影响的行数</returns>
- Task<int> DeleteAsync(TKey id);
- /// <summary>
- /// 根据实体删除一条数据
- /// </summary>
- /// <param name="entity">实体</param>
- /// <returns > 返回影响的行数</returns>
- Task<int> DeleteAsync(T entity);
- /// <summary>
- /// 条件删除多条记录
- /// </summary>
- /// <param name="whereConditions">条件</param>
- /// <param name="transaction">事务</param>
- /// <param name="commandTimeout">超时时间</param>
- /// <returns > 影响的行数</returns>
- Task<int> DeleteListAsync(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null);
- /// <summary>
- /// 使用 where 子句删除多个记录
- /// </summary>
- /// <param name="conditions">wher 子句</param>
- /// <param name="parameters">参数</param>
- /// <param name="transaction">事务</param>
- /// <param name="commandTimeout">超时时间</param>
- /// <returns > 影响的行数</returns>
- Task<int> DeleteListAsync(string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null);
- /// <summary>
- /// 满足条件的记录数量
- /// </summary>
- /// <param name="conditions"></param>
- /// <param name="parameters"></param>
- /// <returns></returns>
- Task<int> RecordCountAsync(string conditions = "", object parameters = null);
- #endregion
- }
- }
- /**
- *┌──────────────────────────────────────────────────────────────┐
- *│ 描 述: 仓储类的基类
- *│ 作 者: yilezhu
- *│ 版 本: 1.0
- *│ 创建时间: 2018/12/16 12:03:02
- *└──────────────────────────────────────────────────────────────┘
- *┌──────────────────────────────────────────────────────────────┐
- *│ 命名空间: Czar.Cms.Core.Repository
- *│ 类 名: BaseRepository
- *└──────────────────────────────────────────────────────────────┘
- */
- using Czar.Cms.Core.DbHelper;
- using Czar.Cms.Core.Options;
- using System;
- using System.Collections;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using System.Linq.Expressions;
- using System.Text;
- using System.Threading.Tasks;
- using Dapper;
- namespace Czar.Cms.Core.Repository
- {
- public class BaseRepository<T, TKey> : IBaseRepository<T, TKey> where T : class
- {
- protected DbOpion _dbOpion;
- protected IDbConnection _dbConnection;
- //public BaseRepository(DbOpion dbOpion)
- //{
- // _dbOpion = dbOpion ?? throw new ArgumentNullException(nameof(DbOpion));
- // _dbConnection = ConnectionFactory.CreateConnection(_dbOpion.DbType, _dbOpion.ConnectionString);
- //}
- #region 同步
- public T Get(TKey id) => _dbConnection.Get<T>(id);
- public IEnumerable<T> GetList() => _dbConnection.GetList<T>();
- public IEnumerable<T> GetList(object whereConditions) => _dbConnection.GetList<T>(whereConditions);
- public IEnumerable<T> GetList(string conditions, object parameters = null) => _dbConnection.GetList<T>(conditions, parameters);
- public IEnumerable<T> GetListPaged(int pageNumber, int rowsPerPage, string conditions, string orderby, object parameters = null)
- {
- return _dbConnection.GetListPaged<T>(pageNumber, rowsPerPage, conditions, orderby, parameters);
- }
- public int? Insert(T entity) => _dbConnection.Insert(entity);
- public int Update(T entity) => _dbConnection.Update(entity);
- public int Delete(TKey id) => _dbConnection.Delete<T>(id);
- public int Delete(T entity) => _dbConnection.Delete(entity);
- public int DeleteList(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null)
- {
- return _dbConnection.DeleteList<T>(whereConditions, transaction, commandTimeout);
- }
- public int DeleteList(string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)
- {
- return _dbConnection.DeleteList<T>(conditions, parameters, transaction, commandTimeout);
- }
- public int RecordCount(string conditions = "", object parameters = null)
- {
- return _dbConnection.RecordCount<T>(conditions, parameters);
- }
- #endregion
- #region 异步
- public async Task<T> GetAsync(TKey id)
- {
- return await _dbConnection.GetAsync<T>(id);
- }
- public async Task<IEnumerable<T>> GetListAsync()
- {
- return await _dbConnection.GetListAsync<T>();
- }
- public async Task<IEnumerable<T>> GetListAsync(object whereConditions)
- {
- return await _dbConnection.GetListAsync<T>(whereConditions);
- }
- public async Task<IEnumerable<T>> GetListAsync(string conditions, object parameters = null)
- {
- return await _dbConnection.GetListAsync<T>(conditions, parameters);
- }
- public async Task<IEnumerable<T>> GetListPagedAsync(int pageNumber, int rowsPerPage, string conditions, string orderby, object parameters = null)
- {
- return await _dbConnection.GetListPagedAsync<T>(pageNumber, rowsPerPage, conditions, orderby, parameters);
- }
- public async Task<int?> InsertAsync(T entity)
- {
- return await _dbConnection.InsertAsync(entity);
- }
- public async Task<int> UpdateAsync(T entity)
- {
- return await _dbConnection.UpdateAsync(entity);
- }
- public async Task<int> DeleteAsync(TKey id)
- {
- return await _dbConnection.DeleteAsync(id);
- }
- public async Task<int> DeleteAsync(T entity)
- {
- return await _dbConnection.DeleteAsync(entity);
- }
- public async Task<int> DeleteListAsync(object whereConditions, IDbTransaction transaction = null, int? commandTimeout = null)
- {
- return await _dbConnection.DeleteListAsync<T>(whereConditions, transaction, commandTimeout);
- }
- public async Task<int> DeleteListAsync(string conditions, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null)
- {
- return await DeleteListAsync(conditions, parameters, transaction, commandTimeout);
- }
- public async Task<int> RecordCountAsync(string conditions = "", object parameters = null)
- {
- return await _dbConnection.RecordCountAsync<T>(conditions, parameters);
- }
- #endregion
- #region IDisposable Support
- private bool disposedValue = false; // 要检测冗余调用
- protected virtual void Dispose(bool disposing)
- {
- if (!disposedValue)
- {
- if (disposing)
- {
- // TODO: 释放托管状态(托管对象).
- }
- // TODO: 释放未托管的资源 (未托管的对象) 并在以下内容中替代终结器.
- // TODO: 将大型字段设置为 null.
- disposedValue = true;
- }
- }
- // TODO: 仅当以上 Dispose(bool disposing) 拥有用于释放未托管资源的代码时才替代终结器.
- // ~BaseRepository() {
- // // 请勿更改此代码. 将清理代码放入以上 Dispose(bool disposing) 中.
- // Dispose(false);
- // }
- // 添加此代码以正确实现可处置模式.
- public void Dispose()
- {
- // 请勿更改此代码. 将清理代码放入以上 Dispose(bool disposing) 中.
- Dispose(true);
- // TODO: 如果在以上内容中替代了终结器, 则取消注释以下行.
- // GC.SuppressFinalize(this);
- }
- #endregion
- }
- }
- /**
- *┌──────────────────────────────────────────────────────────────┐
- *│ 描 述:{Comment}接口实现
- *│ 作 者:{Author}
- *│ 版 本: 1.0 模板代码自动生成
- *│ 创建时间:{GeneratorTime}
- *└──────────────────────────────────────────────────────────────┘
- *┌──────────────────────────────────────────────────────────────┐
- *│ 命名空间: {RepositoryNamespace}
- *│ 类 名: {ModelName}Repository
- *└──────────────────────────────────────────────────────────────┘
- */
- using Czar.Cms.Core.DbHelper;
- using Czar.Cms.Core.Options;
- using Czar.Cms.Core.Repository;
- using Czar.Cms.IRepository;
- using Czar.Cms.Models;
- using Microsoft.Extensions.Options;
- using System;
- namespace {RepositoryNamespace}
- {
- public class {ModelName}Repository:BaseRepository<{ModelName},{KeyTypeName}>, I{ModelName}Repository
- {
- public {ModelName}Repository(IOptionsSnapshot<DbOpion> options)
- {
- _dbOpion =options.Get("CzarCms");
- if (_dbOpion == null)
- {
- throw new ArgumentNullException(nameof(DbOpion));
- }
- _dbConnection = ConnectionFactory.CreateConnection(_dbOpion.DbType, _dbOpion.ConnectionString);
- }
- }
- }
- /**
- *┌──────────────────────────────────────────────────────────────┐
- *│ 描 述: 文章分类
- *│ 作 者: yilezhu
- *│ 版 本: 1.0 模板代码自动生成
- *│ 创建时间: 2018-12-18 13:28:43
- *└──────────────────────────────────────────────────────────────┘
- *┌──────────────────────────────────────────────────────────────┐
- *│ 命名空间: Czar.Cms.IRepository
- *│ 接口名称: IArticleCategoryRepository
- *└──────────────────────────────────────────────────────────────┘
- */
- using Czar.Cms.Core.Repository;
- using Czar.Cms.Models;
- using System;
- namespace Czar.Cms.IRepository
- {
- public interface IArticleCategoryRepository : IBaseRepository<ArticleCategory, Int32>
- {
- }
- }
- /**
- *┌──────────────────────────────────────────────────────────────┐
- *│ 描 述: 文章分类接口实现
- *│ 作 者: yilezhu
- *│ 版 本: 1.0 模板代码自动生成
- *│ 创建时间: 2018-12-18 13:28:43
- *└──────────────────────────────────────────────────────────────┘
- *┌──────────────────────────────────────────────────────────────┐
- *│ 命名空间: Czar.Cms.Repository.SqlServer
- *│ 类 名: ArticleCategoryRepository
- *└──────────────────────────────────────────────────────────────┘
- */
- using Czar.Cms.Core.DbHelper;
- using Czar.Cms.Core.Options;
- using Czar.Cms.Core.Repository;
- using Czar.Cms.IRepository;
- using Czar.Cms.Models;
- using Microsoft.Extensions.Options;
- using System;
- namespace Czar.Cms.Repository.SqlServer
- {
- public class ArticleCategoryRepository:BaseRepository<ArticleCategory,Int32>, IArticleCategoryRepository
- {
- public ArticleCategoryRepository(IOptionsSnapshot<DbOpion> options)
- {
- _dbOpion =options.Get("CzarCms");
- if (_dbOpion == null)
- {
- throw new ArgumentNullException(nameof(DbOpion));
- }
- _dbConnection = ConnectionFactory.CreateConnection(_dbOpion.DbType, _dbOpion.ConnectionString);
- }
- }
- }
- [Fact]
- public void TestBaseFactory()
- {
- IServiceProvider serviceProvider = BuildServiceForSqlServer();
- IArticleCategoryRepository categoryRepository = serviceProvider.GetService<IArticleCategoryRepository>();
- var category = new ArticleCategory
- {
- Title = "随笔",
- ParentId = 0,
- ClassList = "",
- ClassLayer = 0,
- Sort = 0,
- ImageUrl = "",
- SeoTitle = "随笔的 SEOTitle",
- SeoKeywords = "随笔的 SeoKeywords",
- SeoDescription = "随笔的 SeoDescription",
- IsDeleted = false,
- };
- var categoryId = categoryRepository.Insert(category);
- var list = categoryRepository.GetList();
- Assert.True(1 == list.Count());
- Assert.Equal("随笔", list.FirstOrDefault().Title);
- Assert.Equal("SQLServer", DatabaseType.SqlServer.ToString(), ignoreCase: true);
- categoryRepository.Delete(categoryId.Value);
- var count = categoryRepository.RecordCount();
- Assert.True(0 == count);
来源: https://www.cnblogs.com/yilezhu/p/10146311.html