一、前言

上一篇【分层架构设计 <https://www.cnblogs.com/snailblog/p/11515987.html>
】我们已经有了架构的轮廓,现在我们就在这个轮廓里面造轮子。项目要想开始,肯定先得确定ORM框架,目前市面上的ORM框架有很多,对于.net人员来说很容易就想到以ADO.NET为基础所发展出来的ORM框架EntityFramework。不得不说EntityFramework是一个功能很强大的ORM框架,到现在最新的EF分别是EF6(.Net
Framework)和EF Core(.Net Core)两个版本。


但是这里我使用的另一个叫Dapper的ORM框架,原因是因为EF太重了,而Dapper是一个轻量级开源的ORM类,他是通过扩展IDbConnection提供一些有用的扩展方法去查询您的数据库,所以Ado.Net支持的数据库,他都可以支持。在速度方面具有“King
of Micro ORM”的头衔,几乎与使用原始的ADO.NET数据读取器一样快。第一次使用了他之后,就深深的喜欢上他了。

github地址:https://github.com/StackExchange/Dapper
<https://github.com/StackExchange/Dapper>

Dapper文档:https://dapper-tutorial.net/dapper
<https://dapper-tutorial.net/dapper>

二、Dapper实现

2.1、Dapper的方法

从Dapper文档中,我们知道,Dapper支持一下的方法:

Execute:执行一次或多次命令并返回受影响的行数,包括存储过程
Query:执行查询,返回类型为t的数据(返回数据为集合)
QueryFirst:执行查询并映射第一个结果,如果没有就为null
QueryFirstOrDefault:行查询并映射第一个结果,如果序列不包含任何元素,则可以映射默认值
QuerySingle:行查询并映射第一个结果,如果序列中没有一个元素,则抛出异常
QuerySingleOrDefault:执行查询并映射第一个结果,如果序列为空则映射默认值;如果序列中有多个元素,则此方法抛出异常
QueryMultiple:在同一命令中执行多个查询并映射结果

Dapper还提供了上面方法相同功能的异步方法,同时每个方法也支持多种形式的传参。

2.2、Dapper的使用

在前面的项目中,我们在
“04-DataLayer”文件下面添加类库“PFT.Snail.DataAccess”。PFT.Snail.DataAccess主要是
用来完成Dapper方法的使用。

在PFT.Snail.DataAccess项目下面创建文件“DapperSqlDB.cs”,代码如下:
public class DapperSqlDB { /// <summary> /// 连接字符串 /// </summary> public string
ConnectionString {get; set; } /// <summary> /// 数据库连接时间 /// </summary> private
int _commondTimeout { get { return 1200; } } public DapperSqlDB() { } public
DapperSqlDB(string connectionString) { ConnectionString = connectionString; }
#region 查询/// <summary> /// 查询列表 /// </summary> /// <typeparam
name="T"></typeparam> /// <param name="sql"></param> /// <param
name="param"></param> /// <returns></returns> public List<T> Query<T>(string
sql,object param) { using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open();return conn.Query<T>(sql, param,
commandTimeout: _commondTimeout).ToList(); } }/// <summary> /// 异步查询列表 ///
</summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param>
/// <param name="param"></param> /// <returns></returns> public async
Task<IEnumerable<T>> QueryAsync<T>(string sql, object param) { using
(IDbConnection conn =new SqlConnection(ConnectionString)) { conn.Open(); return
await conn.QueryAsync<T>(sql, param, commandTimeout: _commondTimeout); } }///
<summary> /// 查询列表第一条数据 /// </summary> /// <typeparam name="T"></typeparam> ///
<param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public T QueryFirstOrDefault<T>(string sql, object param) {
using (IDbConnection conn = new SqlConnection(ConnectionString)) { conn.Open();
return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout:
_commondTimeout); } }/// <summary> /// 异步查询列表第一条数据 /// </summary> ///
<typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param
name="param"></param> /// <returns></returns> public async Task<T>
QueryFirstOrDefaultAsync<T>(string sql, object param) { using (IDbConnection
conn =new SqlConnection(ConnectionString)) { conn.Open(); return await
conn.QueryFirstOrDefaultAsync<T>(sql, param, commandTimeout: _commondTimeout);
} }/// <summary> /// 查询选择单个值 /// </summary> /// <typeparam name="T"></typeparam>
/// <param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public T ExecuteScalar<T>(string sql, object param) { using
(IDbConnection conn =new SqlConnection(ConnectionString)) { conn.Open(); return
conn.ExecuteScalar<T>(sql, param, commandTimeout: _commondTimeout); } }///
<summary> /// 异步查询选择单个值 /// </summary> /// <typeparam name="T"></typeparam> ///
<param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public async Task<T> ExecuteScalarAsync<T>(string sql,
object param) { using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open();return await
conn.ExecuteScalarAsync<T>(sql, param, commandTimeout: _commondTimeout); } }
/*调用方式 * var multi=DBSqlHelper.QueryMultiple("",null); * var invoice =
multi.Read<Invoice>().First(); * var invoiceItems =
multi.Read<InvoiceItem>().ToList(); */ /// <summary> /// 执行多个查询并映射结果 ///
</summary> /// <param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public GridReader QueryMultiple(string sql, object param) {
using (IDbConnection conn = new SqlConnection(ConnectionString)) { conn.Open();
return conn.QueryMultiple(sql, param, commandTimeout: _commondTimeout); } } /*
调用方式 * await DBSqlHelper.QueryMultipleAsync(sql, para, async (reader) => * { *
rs.A= (await reader.ReadFirstAsync<SingleValue<decimal>>()).Value; * rs.B=
(await reader.ReadFirstAsync<SingleValue<decimal>>()).Value; * ... * }); */ ///
<summary> /// 异步执行多个查询并映射结果 /// </summary> /// <param name="sql"></param> ///
<param name="param"></param> /// <param name="readerCallback"></param> ///
<returns></returns> public async Task QueryMultipleAsync(string sql, object
param, Action<GridReader> readerCallback) {using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open();using (var reader = await
conn.QueryMultipleAsync(sql, param, commandTimeout: _commondTimeout)) {if
(readerCallback !=null) { readerCallback(reader); } } } } #endregion #region
执行sql/// <summary> /// 执行sql语句 /// </summary> /// <param name="sql"></param>
/// <param name="param"></param> /// <returns></returns> public int Execute(
string sql, object param) { using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open();return conn.Execute(sql, param,
commandTimeout: _commondTimeout); } }/// <summary> /// 异步执行sql语句 /// </summary>
/// <param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public async Task<int> ExecuteAsync(string sql, object
param) {using (IDbConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();return await conn.ExecuteAsync(sql, param, commandTimeout:
_commondTimeout); } }/// <summary> /// 执行sql语句(主要指存储过程) /// </summary> ///
<param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public int Execute(string sql, object param, CommandType
type) {using (IDbConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();return conn.Execute(sql, param, commandTimeout: _commondTimeout,
commandType: type); } }/// <summary> /// 异步执行sql语句(主要指存储过程) /// </summary> ///
<param name="sql"></param> /// <param name="param"></param> ///
<returns></returns> public async Task<int> ExecuteAsync(string sql, object
param, CommandType type) {using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open();return await
conn.ExecuteAsync(sql, param, commandTimeout: _commondTimeout, commandType:
type); } } #endregion }
同时Nuget添加System.Data.SqlClient和Dapper的引用。

上面已经实现了数据的增删改查操作,但是再使用过程就会发现,没有事务机制和分页功能。

添加事务功能

在PFT.Snail.DataAccess中添加接口“ITransaction.cs”
public interface ITransaction { List<T> Query<T>(string sql, object param); T
QueryFirstOrDefault<T>(string sql, object param); T ExecuteScalar<T>(string sql,
object param); int Execute(string sql, object param); }
在“DapperSqlDB.cs”添加事务对象
#region 事务方法 /// <summary> /// 事务执行方法对象 /// </summary> private class
Transaction : IDisposable, ITransaction {private IDbConnection conn = null;
private IDbTransaction tran = null; public Transaction(IDbConnection conn,
IDbTransaction tran) {this.conn = conn; this.tran = tran; } List<T>
ITransaction.Query<T>(string sql, object param) { return conn.Query<T>(sql,
param, tran).ToList(); } T ITransaction.QueryFirstOrDefault<T>(string sql,
object param) { return conn.QueryFirstOrDefault<T>(sql, param, tran); } T
ITransaction.ExecuteScalar<T>(string sql, object param) { return
conn.ExecuteScalar<T>(sql, param, tran); }int ITransaction.Execute(string sql,
object param) { return conn.Execute(sql, param, tran); } void
IDisposable.Dispose() {if (this.tran != null) { this.tran.Dispose(); } if (this
.conn !=null) { this.conn.Dispose(); } } } #endregion
添加事务方法
/*调用方式 * DBSqlHelper.ExecuteTransaction((trans) => * { *
trans.Execute(sqlinsertct, selectedClass); * trans.Execute(sqlinsertict,
selectedClass); * }); */ /// <summary> /// 事务方法 /// </summary> /// <param
name="tranAction"></param> /// <param name="level"></param> /// <param
name="newConnectionString"></param> public void
ExecuteTransaction(Action<ITransaction> tranAction, IsolationLevel? level =null,
string newConnectionString = null) { using (IDbConnection conn = new
SqlConnection(ConnectionString)) { conn.Open(); IDbTransaction tran =null; if
(level ==null) { tran = conn.BeginTransaction(); } else { tran =
conn.BeginTransaction(level.Value); }using (Transaction tranHelper = new
Transaction(conn, tran)) {try { tranAction(tranHelper); tran.Commit(); } catch
{ tran.Rollback();throw; } } } }
添加分页功能

在“PFT.Snail.Dto”中添加“Common”文件夹,下面在添加“PageResponse.cs”
/// <summary> /// 通用分页返回 /// </summary> /// <typeparam name="T"></typeparam>
public class PageResponse<T> { /// <summary> /// 总条数 /// </summary> public long
TotalCount {get; set; } /// <summary> /// 返回 /// </summary> public List<T>
Items {get; set; } /// <summary> /// 当前页 /// </summary> public long PageIndex {
get; set; } /// <summary> /// 每页条数 /// </summary> public long PageSize { get;
set; } /// <summary> /// 总页数 /// </summary> public long TotalPages { get; set; }
/// <summary> /// 返回筛选集合 /// </summary> public Dictionary<string, List<string>>
ResultFilter =new Dictionary<string, List<string>>(); }
在“DapperSqlDB.cs”添加分页功能
/// <summary> /// Sqlserver 分页查询(只支持Sqlserver2012以上数据库) /// </summary> ///
<typeparam name="T"></typeparam> /// <param name="page"></param> /// <param
name="sql"></param> /// <param name="param"></param> /// <returns></returns>
public PageResponse<T> QueryPageSql<T>(PageRequest page, string sql, object
param) { var pageResult =new PageResponse<T>(); pageResult.PageIndex =
page.PageIndex; var queryNumSql =new StringBuilder($"SELECT COUNT(1) FROM
({sql}) AS maptable"); pageResult.TotalCount = ExecuteScalar<int
>(queryNumSql.ToString(), param);if (pageResult.TotalCount == 0) { return
pageResult; } var pageMinCount = (page.PageIndex - 1) * page.PageSize + 1;if
(pageResult.TotalCount < pageMinCount) { page.PageIndex = (int
)((pageResult.TotalCount - 1) / page.PageSize) + 1; } var querySql =new
StringBuilder($@"SELECT maptable.* FROM ({sql}) AS maptable "); if (!string
.IsNullOrWhiteSpace(page.SortBy)) { querySql.AppendLine(page.GetOrderBySql());
} querySql.AppendLine($" OFFSET {(page.PageIndex - 1) * page.PageSize} ROWS
FETCH NEXT {page.PageSize} ROWS ONLY;"); pageResult.Items =
Query<T>(querySql.ToString(), param);return pageResult; }
分页的代码只支持SQl2012以上的版本,如果是一下的版本,可以自己重构代码。

到现在才算把Dapper框架的运用方法全部实现了。

三、总结


虽然实现了Dapper框架的运用方法,但是在真实的项目中,运用这样的方法或多或少有很多不便。同时在架构的设计来说,也不是太合理,因为Repository依赖DapperSqlDB的具体实现,需要抽象出接口。既然存在所说的问题,那为什么不直接写出最终方法呢?这是因为必须弄清楚Dapper的运用,我们才能够对他进行定制化的优化。