一、前期环境准备
1、创建一个core webapi的项目，基于core3.1版本创建一个项目。
2、Dapper安装，使用NuGet来安装Dapper程序包
Install-Package Dapper -Version 2.0.30
Install-Package Dapper.Contrib -Version 2.0.30
3、MySQL.Data的程序包（可以使用其他数据库，如：sqlserver,sqllite等）
Install-Package Dapper -Version 8.0.25
4、appsettings.json文件中添加链接数据库的字符串（其他数据库连接字符串，自行更改）：
"ConnectionStrings": {
"DefaultConnection": "server=服务器;port=端口号;database=regatta{0};SslMode=None;uid=userName;pwd=passWord;Allow User Variables=true"
}
二、封装
1、IDapperContext：
/// <summary> /// Dapper上下文 /// </summary> public interface IDapperContext : IDisposable { /// <summary> /// 数据库连接对象 /// </summary> IDbConnection ReadConnection { get; } /// <summary> /// 数据库连接对象 /// </summary> IDbConnection WriteConnection { get; } }
2、DapperContext：
public class DapperContext : IDapperContext { /// <summary> /// 读连接字符串 /// </summary> private string _readConnectionString; /// <summary> /// 写连接字符串 /// </summary> private string _writeConnectionString; private bool _useMiniProfiling; /// <summary> /// 读连接 /// </summary> private IDbConnection _readConnection; /// <summary> /// 写连接 /// </summary> private IDbConnection _wrteConnection; /// <summary> /// 配置 /// </summary> private readonly AppSetting _appSetting; /// <summary> /// 构造函数注入IOptions /// </summary> /// <param name="appSetting"></param> public DapperContext(IOptions<AppSetting> appSetting) { _appSetting = appSetting.Value; _readConnectionString = _appSetting.ReadOnlyConnectionString; _writeConnectionString = _appSetting.SetConnectionString; _useMiniProfiling = _appSetting.UseMiniProfiling; } /// <summary> /// 连接字符串 /// </summary> /// <param name="connectionString">读写连接字符串</param> public DapperContext(string connectionString) { _readConnectionString = connectionString; _writeConnectionString = connectionString; } #region 读 /// <summary> /// 获取连接 /// </summary> public IDbConnection ReadConnection { get { if (_readConnection == null || _readConnection.State == ConnectionState.Closed) { if (_useMiniProfiling) { _readConnection = new ProfiledDbConnection(new MySqlConnection(_readConnectionString), MiniProfiler.Current); } else { _readConnection = new MySqlConnection(_readConnectionString); } } if (_readConnection.State != ConnectionState.Open) { _readConnection.Open(); } return _readConnection; } } /// <summary> /// 释放连接 /// </summary> public void Dispose() { if (_readConnection != null && _readConnection.State == ConnectionState.Open) _readConnection.Close(); if (_wrteConnection != null && _wrteConnection.State == ConnectionState.Open) _wrteConnection.Close(); } #endregion #region 写 /// <summary> /// 获取连接 /// </summary> public IDbConnection WriteConnection { get { if (_wrteConnection == null || _wrteConnection.State == ConnectionState.Closed) { if (_useMiniProfiling) { _wrteConnection = new ProfiledDbConnection(new MySqlConnection(_writeConnectionString), MiniProfiler.Current); } else { _wrteConnection = new MySqlConnection(_writeConnectionString); } } if (_wrteConnection.State != ConnectionState.Open) { _wrteConnection.Open(); } return _wrteConnection; } } #endregion }
3、IRepository（仓储）：
/// <summary> /// 数据库CRUD等操作 /// </summary> /// <typeparam name="T"></typeparam> public interface IRepository<T> where T : class { /// <summary> /// 上下文 /// </summary> IDapperContext Context { get; } /// <summary> /// 只读连接 /// </summary> IDbConnection ReadConnection { get; } /// <summary> /// 读写连接 /// </summary> IDbConnection WriteConnection { get; } /// <summary> /// 获取分页数据 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="criteria">查询设置</param> /// <param name="param"></param> /// <returns></returns> PageDataView<TEntity> GetPageData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class; /// <summary> /// /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="criteria"></param> /// <param name="param"></param> /// <returns></returns> PageDataView<TEntity> GetAllData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class; /// <summary> /// 添加数据 /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 批量添加数据 /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> long BatchAdd(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> bool Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 删除单条数据 /// </summary> /// <param name="entity"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> bool Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 批量删除 /// </summary> /// <param name="list"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> bool BatchRemove(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 根据主键获取数据 /// </summary> /// <param name="key"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> T GetByKey(object key, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 获取所有数据 /// </summary> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 根据条件获取数据列表 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> IEnumerable<T> GetBy(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 查询数据列表 /// </summary> /// <param name="sql"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 多对象查询 /// </summary> /// <param name="sql"></param> /// <param name="param"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <param name="commandType"></param> /// <returns></returns> GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); /// <summary> /// 执行sql /// </summary> /// <param name="sql"></param> /// <param name="transaction"></param> /// <param name="commandTimeout"></param> /// <returns></returns> int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null); /// <summary> /// 执行是否存在数据 /// </summary> /// <param name="sqlStr">查询（例：SELECT COUNT(1) FROM XXX ）</param> /// <returns></returns> bool Exists(string sqlStr, object param = null); }
4、Repository（仓储实现）：
public class Repository<T> : IRepository<T> where T : class { public Repository(IDapperContext context) { Context = context; } public IDapperContext Context { get; private set; } public IDbConnection ReadConnection => Context.ReadConnection; public IDbConnection WriteConnection => Context.WriteConnection; public PageDataView<TEntity> GetPageData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class { var p = new DynamicParameters(); string proName = "ProcGetPageData"; p.Add("_tables", criteria.TableName); p.Add("_fields", criteria.Fields); p.Add("_where", criteria.Condition); p.Add("_pageIndex", criteria.CurrentPage); p.Add("_pageSize", criteria.PageSize); p.Add("_orderby", criteria.Sort); p.Add("_totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add("_pagecount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView<TEntity>(); pageData.Items = ReadConnection.Query<TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList(); pageData.TotalNum = p.Get<int>("_totalcount"); pageData.TotalPageCount = p.Get<int>("_pagecount"); //Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize)); pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage; return pageData; } public PageDataView<TEntity> GetAllData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class { var p = new DynamicParameters(); string proName = "ProcGetAllData"; p.Add("_tables", criteria.TableName); p.Add("_fields", criteria.Fields); p.Add("_where", criteria.Condition); p.Add("_orderby", criteria.Sort); p.Add("_totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output); var pageData = new PageDataView<TEntity>(); pageData.Items = ReadConnection.Query<TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList(); pageData.TotalNum = p.Get<int>("_totalcount"); return pageData; } public long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null) { if (entity == null) { throw new ArgumentNullException("entity", "Add to DB null entity"); } var res = WriteConnection.Insert(entity, transaction: transaction, commandTimeout: commandTimeout); return res; } public long BatchAdd(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null) { if (list == null) { throw new ArgumentNullException("list", "BatchAdd to DB null entity"); } var res = WriteConnection.Insert(list, transaction: transaction, commandTimeout: commandTimeout); return res; } public virtual bool Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null) { if (entity == null) { throw new ArgumentNullException("entity", "Update in DB null entity"); } return WriteConnection.Update(entity, transaction: transaction, commandTimeout: commandTimeout); } public virtual bool Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null) { if (entity == null) { throw new ArgumentNullException("entity", "Remove in DB null entity"); } return WriteConnection.Delete(entity, transaction: transaction, commandTimeout: commandTimeout); } public bool BatchRemove(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null) { if (list == null) { throw new ArgumentNullException("list", "BatchAdd to DB null entity"); } return WriteConnection.Delete(list, transaction: transaction, commandTimeout: commandTimeout); } public virtual T GetByKey(object queryId, IDbTransaction transaction = null, int? commandTimeout = null) { if (queryId == null) { throw new ArgumentNullException("queryId"); } return ReadConnection.Get<T>(queryId, transaction: transaction, commandTimeout: commandTimeout); } public virtual IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null) { return ReadConnection.GetAll<T>(transaction: transaction, commandTimeout: commandTimeout); } public virtual IEnumerable<T> GetBy(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) { return ReadConnection.Query<T>(sql.ToString(), param, commandTimeout: commandTimeout); } public IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) { return ReadConnection.Query<dynamic>(sql, param); } public GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) { return ReadConnection.QueryMultiple(sql, param, transaction, commandTimeout, commandType); } public int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null) { return WriteConnection.Execute(sql, param, transaction: transaction); } public bool Exists(string sqlStr, object param = null) { return ReadConnection.Query<dynamic>(sqlStr, param).Count() > 0 ? true : false; } }
利用工厂模式创建仓库
5、IFactoryRepository：
/// <summary> /// 创建仓库接口 /// </summary> public interface IFactoryRepository { /// <summary> /// 创建仓库 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="context"></param> /// <returns></returns> IRepository<T> CreateRepository<T>(IDapperContext context) where T : class; } /// <summary> /// 工厂 /// </summary> public class FactoryRepository : IFactoryRepository { /// <summary> /// 创建Repository /// </summary> /// <typeparam name="T"></typeparam> /// <param name="context"></param> /// <returns></returns> public IRepository<T> CreateRepository<T>(IDapperContext context) where T : class { IRepository<T> repository = new Repository<T>(context); return repository; } }