.NET6之Mini API【二十六、封装Dapper】

内容纲要

在上一篇说过,Dapper是通过扩展IDbConnection来达到实现的,那带来的一个问题就是隔离性不好,比如在做单元测试时,mock就有点困难,所以在实践中,我对Dapper作了扩展,下面分享出来,以供大家参考,同时说明一下,这仅是自己根据自己的需要实现的一种方式而以,不一定是全面和完善的。

本扩展的思路是定义接口IDapperPlusDB和实现类DapperPlusDB,在实现类中调用Dapper的方法,使用多少实现多少,这样一来,我们就能在Service层去Mock这个实现类了(因为他有接口IDapperPlusDB)。同时我添加了DataBaseType和DataBaseMark两个属性,来扩展当应用中连接多种数据或,或连接读写分离的数据库时使用。

具体代码如下:

  1. /// <summary>
  2. /// 数据库类型
  3. /// </summary>
  4. public enum DataBaseType
  5. {
  6. None,
  7. Sqlite,
  8. Postgre,
  9. SqlServer,
  10. Oracle,
  11. MySql
  12. }
  13. /// <summary>
  14. /// IDapperPlusDB数据库类型
  15. /// </summary>
  16. public interface IDapperPlusDB : IDisposable
  17. {
  18. /// <summary>
  19. /// 连接对象
  20. /// </summary>
  21. /// <returns></returns>
  22. IDbConnection GetConnection();
  23. /// <summary>
  24. /// 数据库类型
  25. /// </summary>
  26. DataBaseType DataBaseType { get; }
  27. /// <summary>
  28. /// 数据库标志
  29. /// </summary>
  30. string? DataBaseMark { get; }
  31. /// <summary>
  32. /// 查询方法
  33. /// </summary>
  34. /// <typeparam name="T">映射实体类</typeparam>
  35. /// <param name="sql">sql语句</param>
  36. /// <param name="param">参数对象</param>
  37. /// <param name="transaction">事务</param>
  38. /// <param name="buffered">是否缓存结果</param>
  39. /// <param name="commandTimeout">command超时时间(秒)</param>
  40. /// <param name="commandType">command类型</param>
  41. /// <returns></returns>
  42. IEnumerable<T> Query<T>(string sql, object? param = null, IDbTransaction? transaction = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null);
  43. /// <summary>
  44. /// 异步查询方法
  45. /// </summary>
  46. /// <typeparam name="T">映射实体类</typeparam>
  47. /// <param name="sql">sql语句</param>
  48. /// <param name="param">参数对象</param>
  49. /// <param name="transaction">事务</param>
  50. /// <param name="buffered">是否缓存结果</param>
  51. /// <param name="commandTimeout">command超时时间(秒)</param>
  52. /// <param name="commandType">command类型</param>
  53. /// <returns></returns>
  54. Task<IEnumerable<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  55. /// <summary>
  56. /// 查询单个对象异步方法
  57. /// </summary>
  58. /// <typeparam name="T">映射实体类</typeparam>
  59. /// <param name="sql">sql语句</param>
  60. /// <param name="param">参数对象</param>
  61. /// <param name="transaction">事务</param>
  62. /// <param name="commandTimeout">command超时时间(秒)</param>
  63. /// <param name="commandType">command类型</param>
  64. Task<T> QuerySingleOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  65. /// <summary>
  66. /// 执行方法
  67. /// </summary>
  68. /// <param name="sql">映射实体类</param>
  69. /// <param name="param">参数对象</param>
  70. /// <param name="transaction">事务</param>
  71. /// <param name="commandTimeout">command超时时间(秒)</param>
  72. /// <param name="commandType">command类型</param>
  73. /// <returns></returns>
  74. int Execute(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  75. /// <summary>
  76. /// 异常执行方法
  77. /// </summary>
  78. /// <param name="sql">映射实体类</param>
  79. /// <param name="param">参数对象</param>
  80. /// <param name="transaction">事务</param>
  81. /// <param name="commandTimeout">command超时时间(秒)</param>
  82. /// <param name="commandType">command类型</param>
  83. /// <returns></returns>
  84. Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  85. /// <summary>
  86. /// 查询单值
  87. /// </summary>
  88. /// <typeparam name="T">映射实体类</typeparam>
  89. /// <param name="sql">sql语句</param>
  90. /// <param name="param">参数对象</param>
  91. /// <param name="transaction">事务</param>
  92. /// <param name="commandTimeout">command超时时间(秒)</param>
  93. /// <param name="commandType">command类型</param>
  94. /// <returns></returns>
  95. T ExecuteScalar<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  96. /// <summary>
  97. /// 异步查询单值
  98. /// </summary>
  99. /// <typeparam name="T">映射实体类</typeparam>
  100. /// <param name="sql">sql语句</param>
  101. /// <param name="param">参数对象</param>
  102. /// <param name="transaction">事务</param>
  103. /// <param name="commandTimeout">command超时时间(秒)</param>
  104. /// <param name="commandType">command类型</param>
  105. /// <returns></returns>
  106. Task<T> ExecuteScalarAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
  107. }
  108. /// <summary>
  109. /// IDapperPlusDB数据库类型
  110. /// </summary>
  111. public class DapperPlusDB : IDapperPlusDB
  112. {
  113. /// <summary>
  114. /// 连接对象
  115. /// </summary>
  116. IDbConnection _dbConnection;
  117. /// <summary>
  118. /// 构造
  119. /// </summary>
  120. /// <param name="dbConnection">连接对象</param>
  121. public DapperPlusDB(IDbConnection dbConnection)
  122. {
  123. switch (dbConnection.GetType().Name)
  124. {
  125. case "SqliteConnection":
  126. DataBaseType = DataBaseType.Sqlite;
  127. break;
  128. case "NpgsqlConnection":
  129. DataBaseType = DataBaseType.Postgre;
  130. break;
  131. case "SqlConnection":
  132. DataBaseType = DataBaseType.SqlServer;
  133. break;
  134. case "OracleConnection":
  135. DataBaseType = DataBaseType.Oracle;
  136. break;
  137. case "MySqlConnection":
  138. DataBaseType = DataBaseType.MySql;
  139. break;
  140. }
  141. _dbConnection = dbConnection;
  142. }
  143. /// <summary>
  144. /// 构造
  145. /// </summary>
  146. /// <param name="dbConnection">连接对象</param>
  147. /// <param name="dataBaseMark">数据库标志</param>
  148. public DapperPlusDB(IDbConnection dbConnection, string dataBaseMark)
  149. {
  150. DataBaseMark = dataBaseMark;
  151. switch (dbConnection.GetType().Name)
  152. {
  153. case "SqliteConnection":
  154. DataBaseType = DataBaseType.Sqlite;
  155. break;
  156. case "NpgsqlConnection":
  157. DataBaseType = DataBaseType.Postgre;
  158. break;
  159. case "SqlClientConnection":
  160. DataBaseType = DataBaseType.SqlServer;
  161. break;
  162. case "OracleConnection":
  163. DataBaseType = DataBaseType.Oracle;
  164. break;
  165. case "MySqlConnection":
  166. DataBaseType = DataBaseType.MySql;
  167. break;
  168. }
  169. _dbConnection = dbConnection;
  170. }
  171. /// <summary>
  172. /// 数据库标志
  173. /// </summary>
  174. public string? DataBaseMark { get; }
  175. /// <summary>
  176. /// 数据库类型
  177. /// </summary>
  178. public DataBaseType DataBaseType { get; }
  179. /// <summary>
  180. /// 连接对象
  181. /// </summary>
  182. /// <returns></returns>
  183. public IDbConnection GetConnection()
  184. {
  185. return _dbConnection;
  186. }
  187. /// <summary>
  188. /// 查询方法
  189. /// </summary>
  190. /// <typeparam name="T">映射实体类</typeparam>
  191. /// <param name="sql">sql语句</param>
  192. /// <param name="param">参数对象</param>
  193. /// <param name="transaction">事务</param>
  194. /// <param name="buffered">是否缓存结果</param>
  195. /// <param name="commandTimeout">command超时时间(秒)</param>
  196. /// <param name="commandType">command类型</param>
  197. /// <returns></returns>
  198. public IEnumerable<T> Query<T>(string sql, object? param = null, IDbTransaction? transaction = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null)
  199. {
  200. return _dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
  201. }
  202. /// <summary>
  203. /// 查询异步方法
  204. /// </summary>
  205. /// <typeparam name="T">映射实体类</typeparam>
  206. /// <param name="sql">sql语句</param>
  207. /// <param name="param">参数对象</param>
  208. /// <param name="transaction">事务</param>
  209. /// <param name="commandTimeout">command超时时间(秒)</param>
  210. /// <param name="commandType">command类型</param>
  211. public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  212. {
  213. return await _dbConnection.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
  214. }
  215. /// <summary>
  216. /// 查询单个对象异步方法
  217. /// </summary>
  218. /// <typeparam name="T">映射实体类</typeparam>
  219. /// <param name="sql">sql语句</param>
  220. /// <param name="param">参数对象</param>
  221. /// <param name="transaction">事务</param>
  222. /// <param name="commandTimeout">command超时时间(秒)</param>
  223. /// <param name="commandType">command类型</param>
  224. public async Task<T> QuerySingleOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  225. {
  226. return await _dbConnection.QuerySingleOrDefaultAsync<T>(sql, param, transaction, commandTimeout, commandType);
  227. }
  228. /// <summary>
  229. /// 执行方法
  230. /// </summary>
  231. /// <param name="sql">映射实体类</param>
  232. /// <param name="param">参数对象</param>
  233. /// <param name="transaction">事务</param>
  234. /// <param name="commandTimeout">command超时时间(秒)</param>
  235. /// <param name="commandType">command类型</param>
  236. /// <returns></returns>
  237. public int Execute(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  238. {
  239. return _dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
  240. }
  241. /// <summary>
  242. /// 异步执行方法
  243. /// </summary>
  244. /// <param name="sql">映射实体类</param>
  245. /// <param name="param">参数对象</param>
  246. /// <param name="transaction">事务</param>
  247. /// <param name="commandTimeout">command超时时间(秒)</param>
  248. /// <param name="commandType">command类型</param>
  249. /// <returns></returns>
  250. public async Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  251. {
  252. return await _dbConnection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
  253. }
  254. /// <summary>
  255. /// 查询单值
  256. /// </summary>
  257. /// <typeparam name="T">映射实体类</typeparam>
  258. /// <param name="sql">sql语句</param>
  259. /// <param name="param">参数对象</param>
  260. /// <param name="transaction">事务</param>
  261. /// <param name="commandTimeout">command超时时间(秒)</param>
  262. /// <param name="commandType">command类型</param>
  263. /// <returns></returns>
  264. public T ExecuteScalar<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  265. {
  266. return _dbConnection.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
  267. }
  268. /// <summary>
  269. /// 异步查询单值
  270. /// </summary>
  271. /// <typeparam name="T">映射实体类</typeparam>
  272. /// <param name="sql">sql语句</param>
  273. /// <param name="param">参数对象</param>
  274. /// <param name="transaction">事务</param>
  275. /// <param name="commandTimeout">command超时时间(秒)</param>
  276. /// <param name="commandType">command类型</param>
  277. /// <returns></returns>
  278. public async Task<T> ExecuteScalarAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
  279. {
  280. return await _dbConnection.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
  281. }
  282. public void Dispose()
  283. {
  284. if (_dbConnection != null)
  285. {
  286. _dbConnection.Dispose();
  287. }
  288. }
  289. }

使用场景一,单数据库使用:

  1. sing Dapper;
  2. using Microsoft.Data.SqlClient;
  3. using MySql.Data.MySqlClient;
  4. using System.Data;
  5. var builder = WebApplication.CreateBuilder(args);
  6. builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
  7. {
  8. return new DapperPlusDB(new SqlConnection(builder.Configuration.GetConnectionString("SqlServer")));
  9. });
  10. var app = builder.Build();
  11. app.MapGet("/answers/{QuestionID}", async (IDapperPlusDB db, int QuestionID) =>
  12. {
  13. return await db.QueryAsync<AnswerModel>("select * from answers where QuestionID=@QuestionID", new { QuestionID });
  14. });
  15. app.Run();

使用场景二,多种数据库使用:

  1. using Dapper;
  2. using Microsoft.Data.SqlClient;
  3. using MySql.Data.MySqlClient;
  4. using System.Data;
  5. var builder = WebApplication.CreateBuilder(args);
  6. builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
  7. {
  8. return new DapperPlusDB(new MySqlConnection(builder.Configuration.GetConnectionString("MySql")));
  9. });
  10. app.MapGet("/answers/{QuestionID}", async (IDapperPlusDB db, int QuestionID) =>
  11. {
  12. return await db.QueryAsync<AnswerModel>("select * from answers where QuestionID=@QuestionID", new { QuestionID });
  13. });
  14. var app = builder.Build();
  15. app.MapGet("/data/{id}", async (IEnumerable<IDapperPlusDB> dbs, int id) =>
  16. {
  17. IDapperPlusDB? mssqlDB = null, mysqldb = null;
  18. foreach (var db in dbs)
  19. {
  20. switch (db.DataBaseType)
  21. {
  22. case DataBaseType.SqlServer:
  23. mssqlDB = db;
  24. break;
  25. case DataBaseType.MySql:
  26. mysqldb = db;
  27. break;
  28. }
  29. }
  30. if (mssqlDB != null && mysqldb != null)
  31. {
  32. return new
  33. {
  34. MSSqlData = await mssqlDB.QuerySingleOrDefaultAsync<AnswerModel>("select * from answers where id=@id;", new { id }),
  35. MySqlData = await mysqldb.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id })
  36. };
  37. }
  38. return new
  39. {
  40. MSSqlData = new AnswerModel { },
  41. MySqlData = new CityModel { }
  42. };
  43. });
  44. app.Run();

使用场景三,读写分离:

  1. using Dapper;
  2. using Microsoft.Data.SqlClient;
  3. using MySql.Data.MySqlClient;
  4. using System.Data;
  5. var builder = WebApplication.CreateBuilder(args);
  6. builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
  7. {
  8. return new DapperPlusDB(dbConnection: new MySqlConnection(builder.Configuration.GetConnectionString("MySqlRead")), dataBaseMark: "read");
  9. });
  10. builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
  11. {
  12. return new DapperPlusDB(dbConnection: new MySqlConnection(builder.Configuration.GetConnectionString("MySqlWrite")), dataBaseMark: "write");
  13. });
  14. var app = builder.Build();
  15. app.MapGet("/data/{id}", async (IEnumerable<IDapperPlusDB> dbs, int id) =>
  16. {
  17. IDapperPlusDB? readDB = null, writedb = null;
  18. foreach (var db in dbs)
  19. {
  20. switch (db.DataBaseMark)
  21. {
  22. case "read":
  23. readDB = db;
  24. break;
  25. case "write":
  26. writedb = db;
  27. break;
  28. }
  29. }
  30. if (readDB != null && writedb != null)
  31. {
  32. return new
  33. {
  34. MSSqlData = await readDB.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id }),
  35. MySqlData = await writedb.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id })
  36. };
  37. }
  38. return new
  39. {
  40. MSSqlData = new CityModel { },
  41. MySqlData = new CityModel { }
  42. };
  43. });
  44. app.Run();

全部.NET6之MiniAPI-PDF版本下载地址 https://club.51aspx.com/circle/10569.html

.NET

.NET6之Mini API【二十五、Dapper】

2022-7-12 20:58:50

.NET

.NET Framework 2022 年 6 月安全和质量汇总更新

2022-7-12 21:13:34

0 条回复 A文章作者 M管理员
欢迎您,新朋友,感谢参与互动!
    暂无讨论,说说你的看法吧
个人中心
今日签到
私信列表
搜索