ORM之Dapper運(yùn)用

一、前言

上一篇【分層架構(gòu)設(shè)計(jì) <https://www.cnblogs.com/snailblog/p/11515987.html>
】我們已經(jīng)有了架構(gòu)的輪廓，現(xiàn)在我們就在這個(gè)輪廓里面造輪子。項(xiàng)目要想開(kāi)始，肯定先得確定ORM框架，目前市面上的ORM框架有很多，對(duì)于.net人員來(lái)說(shuō)很容易就想到以ADO.NET為基礎(chǔ)所發(fā)展出來(lái)的ORM框架EntityFramework。不得不說(shuō)EntityFramework是一個(gè)功能很強(qiáng)大的ORM框架，到現(xiàn)在最新的EF分別是EF6（.Net
Framework）和EF Core（.Net Core）兩個(gè)版本。


但是這里我使用的另一個(gè)叫Dapper的ORM框架，原因是因?yàn)镋F太重了，而Dapper是一個(gè)輕量級(jí)開(kāi)源的ORM類，他是通過(guò)擴(kuò)展IDbConnection提供一些有用的擴(kuò)展方法去查詢您的數(shù)據(jù)庫(kù)，所以Ado.Net支持的數(shù)據(jù)庫(kù)，他都可以支持。在速度方面具有“King
of Micro ORM”的頭銜，幾乎與使用原始的ADO.NET數(shù)據(jù)讀取器一樣快。第一次使用了他之后，就深深的喜歡上他了。

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

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

二、Dapper實(shí)現(xiàn)

2.1、Dapper的方法

從Dapper文檔中，我們知道，Dapper支持一下的方法：

Execute：執(zhí)行一次或多次命令并返回受影響的行數(shù)，包括存儲(chǔ)過(guò)程
Query：執(zhí)行查詢，返回類型為t的數(shù)據(jù)(返回?cái)?shù)據(jù)為集合)
QueryFirst：執(zhí)行查詢并映射第一個(gè)結(jié)果，如果沒(méi)有就為null
QueryFirstOrDefault：行查詢并映射第一個(gè)結(jié)果，如果序列不包含任何元素，則可以映射默認(rèn)值
QuerySingle：行查詢并映射第一個(gè)結(jié)果，如果序列中沒(méi)有一個(gè)元素，則拋出異常
QuerySingleOrDefault：執(zhí)行查詢并映射第一個(gè)結(jié)果，如果序列為空則映射默認(rèn)值;如果序列中有多個(gè)元素，則此方法拋出異常
QueryMultiple：在同一命令中執(zhí)行多個(gè)查詢并映射結(jié)果

Dapper還提供了上面方法相同功能的異步方法，同時(shí)每個(gè)方法也支持多種形式的傳參。

2.2、Dapper的使用

在前面的項(xiàng)目中，我們?cè)?
“04-DataLayer”文件下面添加類庫(kù)“PFT.Snail.DataAccess”。PFT.Snail.DataAccess主要是
用來(lái)完成Dapper方法的使用。

在PFT.Snail.DataAccess項(xiàng)目下面創(chuàng)建文件“DapperSqlDB.cs”，代碼如下：
public class DapperSqlDB { /// <summary> /// 連接字符串 /// </summary> public string
ConnectionString {get; set; } /// <summary> /// 數(shù)據(jù)庫(kù)連接時(shí)間 /// </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> /// 查詢列表第一條數(shù)據(jù) /// </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> /// 異步查詢列表第一條數(shù)據(jù) /// </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> /// 查詢選擇單個(gè)值 /// </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> /// 異步查詢選擇單個(gè)值 /// </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); } }
/*調(diào)用方式 * var multi=DBSqlHelper.QueryMultiple("",null); * var invoice =
multi.Read<Invoice>().First(); * var invoiceItems =
multi.Read<InvoiceItem>().ToList(); */ /// <summary> /// 執(zhí)行多個(gè)查詢并映射結(jié)果 ///
</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); } } /*
調(diào)用方式 * await DBSqlHelper.QueryMultipleAsync(sql, para, async (reader) => * { *
rs.A= (await reader.ReadFirstAsync<SingleValue<decimal>>()).Value; * rs.B=
(await reader.ReadFirstAsync<SingleValue<decimal>>()).Value; * ... * }); */ ///
<summary> /// 異步執(zhí)行多個(gè)查詢并映射結(jié)果 /// </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
執(zhí)行sql/// <summary> /// 執(zhí)行sql語(yǔ)句 /// </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> /// 異步執(zhí)行sql語(yǔ)句 /// </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> /// 執(zhí)行sql語(yǔ)句（主要指存儲(chǔ)過(guò)程） /// </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> /// 異步執(zhí)行sql語(yǔ)句（主要指存儲(chǔ)過(guò)程） /// </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 }
同時(shí)Nuget添加System.Data.SqlClient和Dapper的引用。

上面已經(jīng)實(shí)現(xiàn)了數(shù)據(jù)的增刪改查操作，但是再使用過(guò)程就會(huì)發(fā)現(xiàn)，沒(méi)有事務(wù)機(jī)制和分頁(yè)功能。

添加事務(wù)功能

在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”添加事務(wù)對(duì)象
#region 事務(wù)方法 /// <summary> /// 事務(wù)執(zhí)行方法對(duì)象 /// </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
添加事務(wù)方法
/*調(diào)用方式 * DBSqlHelper.ExecuteTransaction((trans) => * { *
trans.Execute(sqlinsertct, selectedClass); * trans.Execute(sqlinsertict,
selectedClass); * }); */ /// <summary> /// 事務(wù)方法 /// </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; } } } }
添加分頁(yè)功能

在“PFT.Snail.Dto”中添加“Common”文件夾，下面在添加“PageResponse.cs”
/// <summary> /// 通用分頁(yè)返回 /// </summary> /// <typeparam name="T"></typeparam>
public class PageResponse<T> { /// <summary> /// 總條數(shù) /// </summary> public long
TotalCount {get; set; } /// <summary> /// 返回 /// </summary> public List<T>
Items {get; set; } /// <summary> /// 當(dāng)前頁(yè) /// </summary> public long PageIndex {
get; set; } /// <summary> /// 每頁(yè)條數(shù) /// </summary> public long PageSize { get;
set; } /// <summary> /// 總頁(yè)數(shù) /// </summary> public long TotalPages { get; set; }
/// <summary> /// 返回篩選集合 /// </summary> public Dictionary<string, List<string>>
ResultFilter =new Dictionary<string, List<string>>(); }
在“DapperSqlDB.cs”添加分頁(yè)功能
/// <summary> /// Sqlserver 分頁(yè)查詢（只支持Sqlserver2012以上數(shù)據(jù)庫(kù)） /// </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; }
分頁(yè)的代碼只支持SQl2012以上的版本，如果是一下的版本，可以自己重構(gòu)代碼。

到現(xiàn)在才算把Dapper框架的運(yùn)用方法全部實(shí)現(xiàn)了。

三、總結(jié)


雖然實(shí)現(xiàn)了Dapper框架的運(yùn)用方法，但是在真實(shí)的項(xiàng)目中，運(yùn)用這樣的方法或多或少有很多不便。同時(shí)在架構(gòu)的設(shè)計(jì)來(lái)說(shuō)，也不是太合理，因?yàn)镽epository依賴DapperSqlDB的具體實(shí)現(xiàn)，需要抽象出接口。既然存在所說(shuō)的問(wèn)題，那為什么不直接寫出最終方法呢？這是因?yàn)楸仨毰宄﨑apper的運(yùn)用，我們才能夠?qū)λM(jìn)行定制化的優(yōu)化。

??

• ? 上一篇： OSS在線遷移服務(wù)剖析
• ? 下一篇：使用EF6簡(jiǎn)實(shí)現(xiàn)多租戶的應(yīng)用