我要评论一、前言
关于什么是dapper（详细入口），在此不做赘述；本文仅对dapper在.net core中的使用作扼要说明，所陈代码以示例讲解为主，乃抛砖引玉，开发者可根据自身需要进行扩展和调整；其中如有疏漏之处，望不吝斧正。
不了解dapper的朋友可以看这篇文章：orm框架之dapper简介和性能测试
二、dapper环境搭建
当前以.net core webapi或mvc项目为例，框架版本为.net 5.0，相关nuget包引用如下：
install-package dapper
install-package dapper.contrib
install-package dapper.sqlbuilder
install-package system.data.sqlclient
其中dapper.contrib和dapper.sqlbuilder为dapper的扩展，当然，dapper的扩展还有如dapper.rainbow等其他包，根据自身需要引用，对相关引用作下说明：
- dapper：不言而喻；
- dapper.contrib：可使用对象进行数据表的增删改查，免却sql语句的编写；
- dapper.sqlbuilder：可以方便动态构建sql语句，如join、select、where、orderby等等；
- system.data.sqlclient：由于示例数据库为sql server，如mysql则引用mysql.data；
对于dapper.contrib实体配置选项，以product类为例，作扼要说明如下：
[table("product")]
public class product
{
[key]
public int id { get; set; }
public string name{ get; set; }
public string description { get; set; }
public decimal price { get; set; }
public datetime createtime { get; set; }
}对于实体配置项，有如下几个主要项：
- table：指定数据库表名，可忽略；
- key：指定为自动增长主键；
- explicitkey：指定非自动增长主键，如guid；
- computed：计算列属性，insert、update操作将忽略此列；
- write：是否可写入，true/false，如[write(false)]，false时insert、update操作将忽略此列，比如可扩展局部类作数据表额外查询字段使用；
对于数据表对象实体，可结合t4模板生成即可。
三、dapper封装
关于dapper数据访问，这里参考github上的某示例（入口：https://github.com/eloretec/unitofworkwithdapper），作修改调整封装如下：
定义dapperdbcontext类
public abstract class dapperdbcontext : icontext
{
private idbconnection _connection;
private idbtransaction _transaction;
private int? _commandtimeout = null;
private readonly dapperdbcontextoptions _options;
public bool istransactionstarted { get; private set; }
protected abstract idbconnection createconnection(string connectionstring);
protected dapperdbcontext(ioptions<dapperdbcontextoptions> optionsaccessor)
{
_options = optionsaccessor.value;
_connection = createconnection(_options.configuration);
_connection.open();
debugprint("connection started.");
}
#region transaction
public void begintransaction()
{
if (istransactionstarted)
throw new invalidoperationexception("transaction is already started.");
_transaction = _connection.begintransaction();
istransactionstarted = true;
debugprint("transaction started.");
}
public void commit()
{
if (!istransactionstarted)
throw new invalidoperationexception("no transaction started.");
_transaction.commit();
_transaction = null;
istransactionstarted = false;
debugprint("transaction committed.");
}
public void rollback()
{
if (!istransactionstarted)
throw new invalidoperationexception("no transaction started.");
_transaction.rollback();
_transaction.dispose();
_transaction = null;
istransactionstarted = false;
debugprint("transaction rollbacked and disposed.");
}
#endregion transaction
#region dapper.contrib.extensions
public async task<t> getasync<t>(int id) where t : class, new()
{
return await _connection.getasync<t>(id, _transaction, _commandtimeout);
}
public async task<t> getasync<t>(string id) where t : class, new()
{
return await _connection.getasync<t>(id, _transaction, _commandtimeout);
}
public async task<ienumerable<t>> getallasync<t>() where t : class, new()
{
return await _connection.getallasync<t>();
}
public long insert<t>(t model) where t : class, new()
{
return _connection.insert<t>(model, _transaction, _commandtimeout);
}
public async task<int> insertasync<t>(t model) where t : class, new()
{
return await _connection.insertasync<t>(model, _transaction, _commandtimeout);
}
public bool update<t>(t model) where t : class, new()
{
return _connection.update<t>(model, _transaction, _commandtimeout);
}
public async task<bool> updateasync<t>(t model) where t : class, new()
{
return await _connection.updateasync<t>(model, _transaction, _commandtimeout);
}
public async task<page<t>> pageasync<t>(long pageindex, long pagesize, string sql, object param = null)
{
dapperpage.buildpagequeries((pageindex - 1) * pagesize, pagesize, sql, out string sqlcount, out string sqlpage);
var result = new page<t>
{
currentpage = pageindex,
itemsperpage = pagesize,
totalitems = await _connection.executescalarasync<long>(sqlcount, param)
};
result.totalpages = result.totalitems / pagesize;
if ((result.totalitems % pagesize) != 0)
result.totalpages++;
result.items = await _connection.queryasync<t>(sqlpage, param);
return result;
}
#endregion
#region dapper execute & query
public int executescalar(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return _connection.executescalar<int>(sql, param, _transaction, _commandtimeout, commandtype);
}
public async task<int> executescalarasync(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return await _connection.executescalarasync<int>(sql, param, _transaction, _commandtimeout, commandtype);
}
public int execute(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return _connection.execute(sql, param, _transaction, _commandtimeout, commandtype);
}
public async task<int> executeasync(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return await _connection.executeasync(sql, param, _transaction, _commandtimeout, commandtype);
}
public ienumerable<t> query<t>(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return _connection.query<t>(sql, param, _transaction, true, _commandtimeout, commandtype);
}
public async task<ienumerable<t>> queryasync<t>(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return await _connection.queryasync<t>(sql, param, _transaction, _commandtimeout, commandtype);
}
public t queryfirstordefault<t>(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return _connection.queryfirstordefault<t>(sql, param, _transaction, _commandtimeout, commandtype);
}
public async task<t> queryfirstordefaultasync<t>(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return await _connection.queryfirstordefaultasync<t>(sql, param, _transaction, _commandtimeout, commandtype);
}
public ienumerable<treturn> query<tfirst, tsecond, treturn>(string sql, func<tfirst, tsecond, treturn> map, object param = null, string spliton = "id", commandtype commandtype = commandtype.text)
{
return _connection.query(sql, map, param, _transaction, true, spliton, _commandtimeout, commandtype);
}
public async task<ienumerable<treturn>> queryasync<tfirst, tsecond, treturn>(string sql, func<tfirst, tsecond, treturn> map, object param = null, string spliton = "id", commandtype commandtype = commandtype.text)
{
return await _connection.queryasync(sql, map, param, _transaction, true, spliton, _commandtimeout, commandtype);
}
public async task<sqlmapper.gridreader> querymultipleasync(string sql, object param = null, commandtype commandtype = commandtype.text)
{
return await _connection.querymultipleasync(sql, param, _transaction, _commandtimeout, commandtype);
}
#endregion dapper execute & query
public void dispose()
{
if (istransactionstarted)
rollback();
_connection.close();
_connection.dispose();
_connection = null;
debugprint("connection closed and disposed.");
}
private void debugprint(string message)
{
#if debug
debug.print(">>> unitofworkwithdapper - thread {0}: {1}", thread.currentthread.managedthreadid, message);
#endif
}
}以上代码涵盖了dapper访问数据库的基本操作，分同步和异步，其中大部分不作赘述，着重说下分页部分；
异步分页构建（pageasync）
分页这里为方便调用，只需传入要查询的sql语句（如：select * from table，必须带order by）、页索引、页大小即可；
至于具体如何构建的，这里参照某小型orm工具petapoco，抽取相关代码如下，有兴趣的同学也可以自行改造：
public class page<t>
{
/// <summary>
/// the current page number contained in this page of result set
/// </summary>
public long currentpage { get; set; }
/// <summary>
/// the total number of pages in the full result set
/// </summary>
public long totalpages { get; set; }
/// <summary>
/// the total number of records in the full result set
/// </summary>
public long totalitems { get; set; }
/// <summary>
/// the number of items per page
/// </summary>
public long itemsperpage { get; set; }
/// <summary>
/// the actual records on this page
/// </summary>
public ienumerable<t> items { get; set; }
//public list<t> items { get; set; }
}
public class dapperpage
{
public static void buildpagequeries(long skip, long take, string sql, out string sqlcount, out string sqlpage)
{
// split the sql
if (!paginghelper.splitsql(sql, out paginghelper.sqlparts parts))
throw new exception("unable to parse sql statement for paged query");
sqlpage = buildpagesql.buildpagequery(skip, take, parts);
sqlcount = parts.sqlcount;
}
}
static class buildpagesql
{
public static string buildpagequery(long skip, long take, paginghelper.sqlparts parts)
{
parts.sqlselectremoved = paginghelper.rxorderby.replace(parts.sqlselectremoved, "", 1);
if (paginghelper.rxdistinct.ismatch(parts.sqlselectremoved))
{
parts.sqlselectremoved = "peta_inner.* from (select " + parts.sqlselectremoved + ") peta_inner";
}
var sqlpage = string.format("select * from (select row_number() over ({0}) peta_rn, {1}) peta_paged where peta_rn>{2} and peta_rn<={3}",
parts.sqlorderby ?? "order by (select null)", parts.sqlselectremoved, skip, skip + take);
//args = args.concat(new object[] { skip, skip + take }).toarray();
return sqlpage;
}
//sqlserver 2012及以上
public static string buildpagequery2(long skip, long take, paginghelper.sqlparts parts)
{
parts.sqlselectremoved = paginghelper.rxorderby.replace(parts.sqlselectremoved, "", 1);
if (paginghelper.rxdistinct.ismatch(parts.sqlselectremoved))
{
parts.sqlselectremoved = "peta_inner.* from (select " + parts.sqlselectremoved + ") peta_inner";
}
var sqlorderby = parts.sqlorderby ?? "order by (select null)";
var sqlpage = $"select {parts.sqlselectremoved} {sqlorderby} offset {skip} rows fetch next {take} rows only";
return sqlpage;
}
}
static class paginghelper
{
public struct sqlparts
{
public string sql;
public string sqlcount;
public string sqlselectremoved;
public string sqlorderby;
}
public static bool splitsql(string sql, out sqlparts parts)
{
parts.sql = sql;
parts.sqlselectremoved = null;
parts.sqlcount = null;
parts.sqlorderby = null;
// extract the columns from "select <whatever> from"
var m = rxcolumns.match(sql);
if (!m.success)
return false;
// save column list and replace with count(*)
group g = m.groups[1];
parts.sqlselectremoved = sql.substring(g.index);
if (rxdistinct.ismatch(parts.sqlselectremoved))
parts.sqlcount = sql.substring(0, g.index) + "count(" + m.groups[1].tostring().trim() + ") " + sql.substring(g.index + g.length);
else
parts.sqlcount = sql.substring(0, g.index) + "count(*) " + sql.substring(g.index + g.length);
// look for the last "order by <whatever>" clause not part of a row_number expression
m = rxorderby.match(parts.sqlcount);
if (!m.success)
{
parts.sqlorderby = null;
}
else
{
g = m.groups[0];
parts.sqlorderby = g.tostring();
parts.sqlcount = parts.sqlcount.substring(0, g.index) + parts.sqlcount.substring(g.index + g.length);
}
return true;
}
public static regex rxcolumns = new regex(@"\a\s*select\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bfrom\b", regexoptions.ignorecase | regexoptions.multiline | regexoptions.singleline | regexoptions.compiled);
public static regex rxorderby = new regex(@"\border\s+by\s+(?!.*?(?:\)|\s+)as\s)(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:asc|desc))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:asc|desc))?)*", regexoptions.righttoleft | regexoptions.ignorecase | regexoptions.multiline | regexoptions.singleline | regexoptions.compiled);
public static regex rxdistinct = new regex(@"\adistinct\s", regexoptions.ignorecase | regexoptions.multiline | regexoptions.singleline | regexoptions.compiled);
}对于构建分页语句，分别示例buildpagequery和buildpagequery2，前者为通过row_number进行分页（针对sqlserver2005、2008），后者通过offset、fetch分页（针对sqlserver2012及以上版本），相关辅助操作类一览便知，如果使用mysql数据库，可酌情自行封装；
至于where查询的进一步封装，有兴趣的也可兑dapper lamada查询进行扩展。
定义工作单元与事务
public interface iunitofwork : idisposable
{
void savechanges();
}
public interface iunitofworkfactory
{
iunitofwork create();
}
public class unitofwork : iunitofwork
{
private readonly icontext _context;
public unitofwork(icontext context)
{
_context = context;
_context.begintransaction();
}
public void savechanges()
{
if (!_context.istransactionstarted)
throw new invalidoperationexception("transaction have already been commited or disposed.");
_context.commit();
}
public void dispose()
{
if (_context.istransactionstarted)
_context.rollback();
}
}
public class dapperunitofworkfactory : iunitofworkfactory
{
private readonly dapperdbcontext _context;
public dapperunitofworkfactory(dapperdbcontext context)
{
_context = context;
}
public iunitofwork create()
{
return new unitofwork(_context);
}
}定义数据仓储
#region product
public partial interface iproductrepository
{
task<product> getasync(int id);
task<ienumerable<product>> getallasync();
long insert(product model);
task<int> insertasync(product model);
bool update(product model);
task<bool> updateasync(product model);
int count(string where, object param = null);
task<int> countasync(string where, object param = null);
bool exists(string where, object param = null);
task<bool> existsasync(string where, object param = null);
product firstordefault(string where, object param = null);
task<product> firstordefaultasync(string where, object param = null);
t firstordefault<t>(string sql, object param = null);
task<t> firstordefaultasync<t>(string sql, object param = null);
ienumerable<product> fetch(sqlbuilder where);
task<ienumerable<product>> fetchasync(sqlbuilder where);
ienumerable<t> fetch<t>(string sql, sqlbuilder where, bool orderby = true);
task<ienumerable<t>> fetchasync<t>(string sql, sqlbuilder where, bool orderby = true);
task<page<product>> pageasync(long pageindex, long pagesize, sqlbuilder builder);
task<page<t>> pageasync<t>(string sql, long pageindex, long pagesize, sqlbuilder builder);
task<sqlmapper.gridreader> querymultipleasync(string sql, object param = null);
}
public partial class productrepository : iproductrepository
{
private readonly dapperdbcontext _context;
public productrepository(dapperdbcontext context)
{
_context = context;
}
public async task<product> getasync(int id)
{
return await _context.getasync<product>(id);
}
public async task<ienumerable<product>> getallasync()
{
return await _context.getallasync<product>();
}
public long insert(product model)
{
return _context.insert<product>(model);
}
public async task<int> insertasync(product model)
{
return await _context.insertasync<product>(model);
}
public bool update(product model)
{
return _context.update<product>(model);
}
public async task<bool> updateasync(product model)
{
return await _context.updateasync<product>(model);
}
public int count(string where, object param = null)
{
string strsql = $"select count(1) from product {where}";
return _context.executescalar(strsql, param);
}
public async task<int> countasync(string where, object param = null)
{
string strsql = $"select count(1) from product {where}";
return await _context.executescalarasync(strsql, param);
}
public bool exists(string where, object param = null)
{
string strsql = $"select top 1 1 from product {where}";
var count = _context.executescalar(strsql, param);
return count > 0;
}
public async task<bool> existsasync(string where, object param = null)
{
string strsql = $"select top 1 1 from product {where}";
var count = await _context.executescalarasync(strsql, param);
return count > 0;
}
public product firstordefault(string where, object param = null)
{
string strsql = $"select top 1 * from product {where}";
return _context.queryfirstordefault<product>(strsql, param);
}
public async task<product> firstordefaultasync(string where, object param = null)
{
string strsql = $"select top 1 * from product {where}";
return await _context.queryfirstordefaultasync<product>(strsql, param);
}
public t firstordefault<t>(string sql, object param = null)
{
return _context.queryfirstordefault<t>(sql, param);
}
public async task<t> firstordefaultasync<t>(string sql, object param = null)
{
return await _context.queryfirstordefaultasync<t>(sql, param);
}
public ienumerable<product> fetch(sqlbuilder where)
{
var strsql = where.addtemplate(@"select * from product /**where**/ /**orderby**/");
return _context.query<product>(strsql.rawsql, strsql.parameters);
}
public async task<ienumerable<product>> fetchasync(sqlbuilder where)
{
var strsql = where.addtemplate(@"select * from product /**where**/ /**orderby**/");
return await _context.queryasync<product>(strsql.rawsql, strsql.parameters);
}
public ienumerable<t> fetch<t>(string sql, sqlbuilder where, bool orderby = true)
{
var _sql = orderby ? $"{sql} /**where**/ /**orderby**/" : $"{sql} /**where**/";
var strsql = where.addtemplate(_sql);
return _context.query<t>(strsql.rawsql, strsql.parameters);
}
public async task<ienumerable<t>> fetchasync<t>(string sql, sqlbuilder where, bool orderby = true)
{
var _sql = orderby ? $"{sql} /**where**/ /**orderby**/" : $"{sql} /**where**/";
var strsql = where.addtemplate(_sql);
return await _context.queryasync<t>(strsql.rawsql, strsql.parameters);
}
public async task<page<product>> pageasync(long pageindex, long pagesize, sqlbuilder builder)
{
var strsql = "select * from product";
return await pageasync<product>(strsql, pageindex, pagesize, builder);
}
public async task<page<t>> pageasync<t>(string sql, long pageindex, long pagesize, sqlbuilder builder)
{
var strsql = builder.addtemplate($"{sql} /**where**/ /**orderby**/");
return await _context.pageasync<t>(pageindex, pagesize, strsql.rawsql, strsql.parameters);
}
public async task<sqlmapper.gridreader> querymultipleasync(string sql, object param = null)
{
return await _context.querymultipleasync(sql, param);
}
}
#endregion根据自身需要进行调整或扩展，一般借助t4模板生成
数据库连接
通过ioptions模式读取配置文件appsettings中连接字符串
public class mydbcontext : dapperdbcontext
{
public mydbcontext(ioptions<dapperdbcontextoptions> optionsaccessor) : base(optionsaccessor)
{
}
protected override idbconnection createconnection(string connectionstring)
{
idbconnection conn = new sqlconnection(connectionstring);
return conn;
}
}四、dapper使用
startup.cs注入并读取数据库连接字符串
{
"sqlconnstring": "data source=(local);initial catalog=database;persist security info=true;user id=sa;password=123456;multipleactiveresultsets=true;",
"logging": {
"loglevel": {
"default": "information",
"microsoft": "warning",
"microsoft.hosting.lifetime": "information"
}
},
"allowedhosts": "*"
}
services.adddapperdbcontext<mydbcontext>(options =>
{
options.configuration = configuration["sqlconnstring"];
});简单示例webapi或net core mvc下的调用示例：
public class productcontroller : basecontroller
{
private readonly iproductrepository _productrepository;
public productcontroller(
iproductrepository productrepository
)
{
_productrepository = productrepository;
}
//商品列表
[httpget]
public async task<iactionresult> productlist(datetime? startdate, datetime? enddate, int id = 1, int productstatus = 0, string keyword = "")
{
var model = new productmodels();
var builder = new dapper.sqlbuilder();
builder.where("productstatus!=@productstatus", new { productstatus = productstatus });
if (startdate.hasvalue)
{
builder.where("createtime>=@startdate", new { startdate = startdate.value});
}
if (enddate.hasvalue)
{
builder.where("createtime<@enddate", new { enddate = enddate.value.adddays(1)});
}
if (!string.isnullorwhitespace(keyword))
{
builder.where("name like @keyword", new { keyword = $"%{stringhelper.replacesql(keyword)}%" });
}
builder.orderby("sortnum desc,createtime desc");
var list = await _productrepository.pageasync(id, pagesize, builder);
model.productlist = new pagedlist<product>(list.items, id, pagesize, list.totalitems);
if (request.isajaxrequest())
return partialview("_productlist", model.productlist);
return view(model);
}
//添加商品
[httppost]
public async task<int> addproduct(productmodels model)
{
return await _productrepository.insertasync(model);
}
}
public partial interface iproductservice
{
task<bool> addproduct(product productinfo, list<productstock> skulist);
}
public class productservice: iproductservice
{
private readonly dapperdbcontext _context;
private readonly iunitofworkfactory _uowfactory;
public productservice(dapperdbcontext context, iunitofworkfactory uowfactory)
{
_context = context;
_uowfactory = uowfactory;
}
/// <summary>
/// 添加商品
/// </summary>
/// <param name="productinfo"></param>
/// <param name="skulist"></param>
/// <returns></returns>
public async task<bool> addproduct(product productinfo, list<productstock> skulist)
{
var result = false;
using (var uow = _uowfactory.create())
{
//添加产品
await _context.insertasync(productinfo);
//添加sku库存售价
//await _context.insertasync(skulist);
uow.savechanges();
result = true;
}
return result;
}
}以上所述是小编给大家介绍的.net core下使用dapper的方法，希望对大家有所帮助。在此也非常感谢大家对代码网网站的支持！
发表评论