欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

.Net Core下使用Dapper的方法

程序员文章站 2022-03-25 15:45:07
目录一、前言二、dapper环境搭建三、dapper封装定义dapperdbcontext类异步分页构建(pageasync)定义工作单元与事务定义数据仓储数据库连接四、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的方法,希望对大家有所帮助。在此也非常感谢大家对网站的支持!