.Net Core下使用Dapper的方法
一、前言
关于什么是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的方法,希望对大家有所帮助。在此也非常感谢大家对网站的支持!
推荐阅读