.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的方法，希望对大家有所帮助。在此也非常感谢大家对网站的支持！