.Net Core下使用Dapper的方法

一、前言

关于什么是dapper（详细入口），在此不做赘述；本文仅对dapper在.net core中的使用作扼要说明，所陈代码以示例讲解为主，乃抛砖引玉，开发者可根据自身需要进行扩展和调整；其中如有疏漏之处，望不吝斧正。

不了解dapper的朋友可以看这篇文章：orm框架之qq头像伤感带字女生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、lect、where、orderby等等；system.data.sqlclient：由于示例数据库为sql rver，如mysql则引用mysql.data；

对于dapper.contrib实体配置选项，以product类为例，作扼要说明如下：

[table("product")]public class product{    [key]      public int id { get; t; }    public string name{ get; t; }    public string description { get; t; }    public decimal price { get; t; }    public datetime createtime { get; t; }}

对于实体配置项，有如下几个主要项：

table：指定数据库表名，可忽略；key：指定为自动增长主键；explicitkey：指定非自动增长主键，如guid；computed：计算列属性，inrt、update操作将忽略此列；write：是否可写入，true/fal，如[write(fal)]，fal时inrt、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 t; }        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 = fal;            debugprint("transaction committed.");        }        public void rollback()        {            if (!istransactionstarted)                throw new invalidoperationexception("no transaction started.");            _transaction.rollback();            _transaction.dispo();            _transaction = null;            istransactionstarted = fal;            debugprint("transaction rollbacked and dispod.");        }        #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 inrt<t>(t model) where t : class, new()        {            return _connection.inrt<t>(model, _transaction, _commandtimeout);        }        public async task<int> inrtasync<t>(t model) where t : class, new()        {            return await _connection.inrtasync<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, tcond, treturn>(string sql, func<tfirst, tcond, 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, tcond, treturn>(string sql, func<tfirst, tcond, 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 dispo()        {            if (istransactionstarted)                rollback();            _connection.clo();            _connection.dispo();            _connection = null;            debugprint("connection clod and dispod.");        }        private void debugprint(string message)        {#if debug            debug.print(">>> unitofworkwithdapper - thread {0}: {1}", thread.currentthread.managedthreadid, message);#endif        }    }

以上代码涵盖了dapper访问数据库的基本操作，分同步和异步，其中大部分不作赘述，着重说下分页部分；

异步分页构建（pageasync）

分页这里为方便调用，只需传入要查询的sql语句（如：lect * from table，必须带order by）、页索引、页大小即可；

至于具体如何构建的，这里参照某小型orm工具petapoco，抽取相关代码如下，有兴趣的同学也可以自行改造：

public class page<t>    {        /// <summary>        /// the current page number contained in this page of result t         /// </summary>        public long currentpage { get; t; }        /// <summary>        /// the total number of pages in the full result t        /// </summary>        public long totalpages { get; t; }        /// <summary>        /// the total number of records in the full result t        /// </summary>        public long totalitems { get; t; }        /// <summary>        /// the number of items per page        /// </summary>        public long itemsperpage { get; t; }        /// <summary>        /// the actual records on this page        /// </summary>        public ienumerable<t> items { get; t; }        //public list<t> items { get; t; }    }    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 par 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.sqllectremoved = paginghelper.rxorderby.replace(parts.sqllectremoved, "", 1);            if (paginghelper.rxdistinct.ismatch(parts.sqllectremoved))         不一样的春节   {                parts.sqllectremoved = "peta_inner.* from (lect " + parts.sqllectremoved + ") peta_inner";            }            var sqlpage = string.format("lect * from (lect row_number() over ({0}) peta_rn, {1}) peta_paged where peta_rn>{2} and peta_rn<={3}",                                    parts.sqlorderby ?? "order by (lect null)", parts.sqllectremoved, skip, skip + take);            //args = args.concat(new object[] { skip, skip + take }).toarray();            return sqlpage;        }        //sqlrver 2012及以上        public static string buildpagequery2(long skip, long take, paginghelper.sqlparts parts)        {            parts.sqllectremoved = paginghelper.rxorderby.replace(parts.sqllectremoved, "", 1);            if (paginghelper.rxdistinct.ismatch(parts.sqllectremoved))            {                parts.sqllectremoved = "peta_inner.* from (lect " + parts.sqllectremoved + ") peta_inner";            }                var sqlorderby = parts.sqlord押韵口号erby ?? "order by (lect null)";            var sqlpage = $"lect {parts.sqllectremoved} {sqlorderby} offt {skip} rows fetch next {take} rows only";            return sqlpage;        }    }    static class paginghelper    {        public struct sqlparts        {            public string sql;            public string sqlcount;            public string sqllectremoved;            public string sqlorderby;        }        public static bool splitsql(string sql, out sqlparts parts)        {            parts.sql = sql;            parts.sqllectremoved = null;            parts.sqlcount = null;            parts.sqlorderby = null;            // extract the columns from "lect <whatever> from"            var m = rxcolumns.match(sql);            if (!m.success)                return fal;            // save column list and replace with count(*)            group g = m.groups[1];            parts.sqllectremoved = sql.substring(g.index);            if (rxdistinct.ismatch(parts.sqllectremoved))                parts.sqlcount = sql.substring(0, g.index) + "count(" + m.groups[1].tostring().trim() + ") " + sql.substring(g.index + g.length);            el                parts.sqlcount = sql.substring(0, g.index) + "count(*) " + sql.substring(g.index + g.length);            // look for the last "order by <whatever>" clau not part of a row_number expression            m = rxorderby.match(parts.sq研究生考试准考证入口lcount);            if (!m.success)            {                parts.sqlorderby = null;            }            el            {                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*lect\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bfrom\b", regexoptions.ignoreca | 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.ignoreca | regexoptions.multiline | regexoptions.singleline | regexoptions.compiled);        public static regex rxdistinct = new regex(@"\adistinct\s", regexoptions.ignoreca | regexoptions.multiline | regexoptions.singleline | regexoptions.compiled);    }

对于构建分页语句，分别示例buildpagequery和buildpagequery2，前者为通过row_number进行分页（针对sqlrver2005、2008），后者通过offt、fetch分页（针对sqlrver2012及以上版本），相关辅助操作类一览便知，如果使用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 dispod.");            _context.commit();        }        public void dispo()        {            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 inrt(product model);        task<int> inrtasync(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 inrt(product model)        {            return _context.inrt<product>(model);        }        public async task<int> inrtasync(product model)        {            return await _context.inrtasync<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 = $"lect count(1) from product {where}";            return _context.executescalar(strsql, param);        }        public async task<int> countasync(string where, object param = null)        {            string strsql = $"lect count(1) from product {where}";            return await _context.executescalarasync(strsql, param);        }        public bool exists(string where, object param = null)        {            string strsql = $"lect 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 = $"lect 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 = $"lect top 1 * from product {where}";            return _context.queryfirstordefault<product>(strsql, param);        }        public async task<product> firstordefaultasync(string where, object param = null)        {            string strsql = $"lect 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(@"lect * from product /**where**/ /**orderby**/");            return _context.query<product>(strsql.rawsql, strsql.parameters);        }        public async task<ienumerable<product>> fetchasync(sqlbuilder where)        {            var strsql = where.addtemplate(@"lect * 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 = "lect * 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模式读取配置文件appttings中连接字符串

public class mydbcontext : dapperdbcontext    {        public mydbcontext(ioptions<dapperdbcontextoptions> optionsaccessor) : ba(optionsaccessor)        {        }        protected override idbconnection createconnection(string connectionstring)        {            idbconnection conn = new sqlconnection(connectionstring);            return conn;        }    }

四、dapper使用

startup.cs注入并读取数据库连接字符串

{  "sqlconnstring": "data source=(local);initial catalog=databa;persist curity info=true;ur id=sa;password=123456;multipleactiveresultts=true;",    "logging": {    "loglevel": {      "default": "information",      "microsoft": "warning",      "microsoft.hosting.lifetime": "information"    }  },  "allowedhosts": "*"}

rvices.adddapperdbcontext<mydbcontext>(options =>            {                options.configuration = configuration["sqlconnstring"];            });

简单示例webapi或net core mvc下的调用示例：

public class productcontroller : bacontroller{    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.inrtasync(model);    }}

public partial interface iproductrvice    {         task<bool> addproduct(product productinfo, list<productstock> skulist);         }    public class productrvice: iproductrvice    {        private readonly dapperdbcontext _context;        private readonly iunitofworkfactory _uowfactory;        public productrvice(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 = fal;            using (var uow = _uowfactory.create())            {                //添加产品                await _context.inrtasync(productinfo);                //添加sku库存售价                               //await _context.inrtasync(skulist);                uow.savechanges();                result = true;            }            return result;        }            }

以上所述是www.887551.com给大家介绍的.net core下使用dapper的方法，希望对大家有所帮助。在此也非常感谢大家对www.887551.com网站的支持！