Dapper 的应用和Dapper.Contrib 的方法封装（二）

在上一篇的基础上引入了开源项目包 SQLBuilder 的应用，SQLBuilder 能将Lambda表达式转换为Dapper想要的sql语句， 让dapper扩展更易用，各种增删改查均支持Lambda表达式的扩展，这里只是简单的使用了其中的一部分内容，如果想深入的研究的话，可以去

看SQLBuilder项目的开源地址，见下方

开源地址

• Gitee：https://gitee.com/zqlovejyc/SQLBuilder
• GitHub：https://github.com/zqlovejyc/SQLBuilder
• NuGet：https://www.nuget.org/packages/Zq.SQLBuilder
• MyGet：https://www.myget.org/feed/zq-myget/package/nuget/Zq.SQLBuilder

1.SQLBuilder 的安装

.NET Framework4.5版本Expression表达式转换为SQL语句，支持SqlServer、MySql、Oracle、Sqlite、PostgreSql；基于Dapper实现了不同数据库对应的数据仓储Repository；在NuGet 中安装 SQLBuilder包，最新版本的依赖项为.Net Framework框架4.5以上；

 

 

 

 

2.在上一篇的Dapper 方法的基础上，引入SQLBuilder之后，就可添加基于Lambda 条件的常见查询的扩展了。使用SQLBuilder将传入的Lambda查询条件转化为sql再通过dapper去获取想要查询的信息。

这里简要封装了查询单个实体和分页获取实体列表的方法如下

        /// <summary>
        /// 1.分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="total">总个数</param>
        /// <param name="sort">"ID desc"</param>
        /// <returns></returns>
        public List<T> GetPageList<T>(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, out int total,string sort = "") where T : class
        {
            var builder = SqlBuilder
                            .Select<T>()
                            .Where(expression).Page(pageSize, pageIndex, sort ?? " ID Desc "); 
            using (var conn = GetOpenConnection())
            {
                var query = conn.QueryMultiple(builder.Sql, builder.Parameters, commandTimeout: commandTimeout);
                total = query.Read<Int32>().FirstOrDefault();
                var res = query.Read<T>().ToList();
                return res;
            }
        }

根据Lambda条件获取单个实体，方法如下：

        /// <summary>
        ///2.根据Lambda条件获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时长</param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public T GetModel<T>(Expression<Func<T, bool>> expression, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            var builder = SqlBuilder
                             .Select<T>()
                             .Where(expression);
            return GetModel<T>(builder.Sql, builder.Parameters, transaction, commandTimeout, buffered);
        }

3.最终得到的DapperRepositoryBase扩展方法如下

 /// <summary>
    /// 数据库操作类
    /// </summary>
    public class DapperRepositoryBase<TEntity> : IDapperRepositoryBase<TEntity> where TEntity : class
    {
        //static string connStrRead = ConfigurationManager.ConnectionStrings["Read"].ConnectionString;
        //static string connStrWrite = ConfigurationManager.ConnectionStrings["Write"].ConnectionString;

        static int commandTimeout = 30;
        private IDbConnection GetConnection(bool useWriteConn)
        {
            if (useWriteConn)
                return new SqlConnection(PubConstant.ConnectionString);
            return new SqlConnection(PubConstant.ConnectionString);
        }
        private SqlConnection GetOpenConnection()
        {
            var conn = new SqlConnection(PubConstant.ConnectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            return conn;
        }

        #region Query
        /// <summary>
        /// 根据id获取实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {
                    return conn.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Get<T>(id, transaction: transaction, commandTimeout: commandTimeout);
            }
        }

        /// <summary>
        /// 根据Lambda条件获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">条件</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时长</param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public T GetModel<T>(Expression<Func<T, bool>> expression, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            var builder = SqlBuilder
                             .Select<T>()
                             .Where(expression);
            return GetModel<T>(builder.Sql, builder.Parameters, transaction, commandTimeout, buffered);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="predicate"></param>
        /// <param name="sort"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public T GetModel<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            using (var conn = GetOpenConnection())
            {
                return conn.QueryFirst<T>(sql, param, transaction, commandTimeout);
            }
        }

        /// <summary>
        ///  执行sql返回一个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public T QueryFirstOrDefault<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {

                    return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.QueryFirstOrDefault<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction);
            }

        }

        /// <summary>
        /// 执行sql返回多个对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public List<T> QueryGetList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
        {
            using (IDbConnection conn = GetConnection(useWriteConn))
            {
                conn.Open();

                return conn.Query<T>(sql, param, commandTimeout: commandTimeout, transaction: transaction).ToList();
            }
        }

        /// <summary>
        /// 根据id获取实体--异步
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public async Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetConnection(useWriteConn))
                {

                    return await conn.GetAsync<T>(id, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.GetAsync<T>(id, transaction: transaction, commandTimeout: commandTimeout);
            }
        }

        /// <summary>
        /// 执行sql返回一个对象--异步
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, bool useWriteConn = false)
        {
            using (IDbConnection conn = GetConnection(useWriteConn))
            {
                conn.Open();
                return await conn.QueryFirstOrDefaultAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
            }
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(Expression<Func<T, bool>> expression, bool useWriteConn = false) where T : class
        {
            var builder = SqlBuilder
                            .Select<T>()
                            .Where(expression);
            return await QueryFirstOrDefaultAsync<T>(builder.Sql, builder.Parameters, useWriteConn);
        }

        /// <summary>
        /// 执行sql返回多个对象--异步
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="useWriteConn"></param>
        /// <returns></returns>
        public async Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false)
        {
            using (IDbConnection conn = GetConnection(useWriteConn))
            {
                conn.Open();
                var list = await conn.QueryAsync<T>(sql, param, commandTimeout: commandTimeout).ConfigureAwait(false);
                return list.ToList();
            }
        }


        public async Task<List<T>> ExecuteReaderRetListAsync<T>(Expression<Func<T, bool>> expression, bool useWriteConn = false) where T : class
        {
            var builder = SqlBuilder
                            .Select<T>()
                            .Where(expression);
            return await ExecuteReaderRetListAsync<T>(builder.Sql, builder.Parameters, useWriteConn);
        }
        //示例：
        //无参查询
        //var qqModeList = conn.Query<UserModel>("select Id,Name,Count from User");
        //带参查询 var qqModeList = conn.Query<UserModel>("select Id,Name,Count from User where Id in @id and Count>@count", new { id = new int[] { 1, 2, 3}, count = 1 });
        /// <summary>
        /// 根据sql获取实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="predicate"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        public List<T> GetList<T>(string sql, object param = null, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            using (IDbConnection conn = GetOpenConnection())
            {
                return conn.Query<T>(sql, param, transaction, buffered, commandTimeout).ToList();
            }
        }

        public List<T> GetList<T>(Expression<Func<T, bool>> expression, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            var builder = SqlBuilder
                          .Select<T>()
                          .Where(expression);
            return GetList<T>(builder.Sql, builder.Parameters, predicate, transaction, commandTimeout, buffered);
        }
        /// <summary>
        /// 获取实体IEnumerable列表（查询全部,慎用）
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="buffered"></param>
        /// <returns></returns>
        //public static List<T> GetList<T>(IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        //{
        //    using (IDbConnection conn = GetOpenConnection())
        //    {
        //        return conn.GetAll<T>(transaction, commandTimeout).ToList();
        //    }
        //}


        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">主sql 不带 order by</param>
        /// <param name="sort">排序内容 id desc，add_time asc</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">每页多少条</param>
        /// <param name="useWriteConn">是否主库</param>
        /// <returns></returns>
        public List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null)
        {
            string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,*  FROM 
              ({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
            string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
            using (var conn = GetOpenConnection())
            {
                return conn.Query<T>(execSql, param, commandTimeout: commandTimeout).ToList();
            }
        }

        /// <summary>
        /// 分页查询(返回总个数)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="sort"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="total"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, out int total, object param = null)
        {
            string pageSql = @"SELECT TOP {0} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) _row_number_,*  FROM 
              ({2})temp )temp1 WHERE temp1._row_number_>{3} ORDER BY _row_number_";
            string execSql = string.Format(pageSql, pageSize, sort, sql, pageSize * (pageIndex - 1));
            using (var conn = GetOpenConnection())
            {
                var query = conn.Query<T>(execSql, param, commandTimeout: commandTimeout);
                total = query.Count();
                return query.ToList();
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="expression">表达式</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="total">总个数</param>
        /// <param name="sort">"ID desc"</param>
        /// <returns></returns>
        public List<T> GetPageList<T>(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, out int total,string sort = "") where T : class
        {
            var builder = SqlBuilder
                            .Select<T>()
                            .Where(expression).Page(pageSize, pageIndex, sort ?? " ID Desc "); 
            using (var conn = GetOpenConnection())
            {
                var query = conn.QueryMultiple(builder.Sql, builder.Parameters, commandTimeout: commandTimeout);
                total = query.Read<Int32>().FirstOrDefault();
                var res = query.Read<T>().ToList();
                return res;
            }
        }

        #endregion

        #region Add

        /// <summary>
        /// 插入实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Insert<T>(T item, IDbTransaction transaction = null) where T : class
        {
            string table = SqlBuilderHelper.GetTableName<T>();
            var sql = SqlBuilderHelper.CreateInsertSql<T>(table, null);
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    var res = int.Parse(conn.Execute(sql, item, transaction, commandTimeout: commandTimeout).ToString());
                    //var res = conn.Insert<T>(item, commandTimeout: commandTimeout);
                    return res > 0;
                }
            }
            else
            {
                var conn = transaction.Connection;
                var res = int.Parse(conn.Execute(sql, item, transaction, commandTimeout: commandTimeout).ToString());
                return res > 0;
                //return conn.Insert(item, transaction: transaction, commandTimeout: commandTimeout) > 0;
            }
        }

        /// <summary>
        /// 插入实体返回Id
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="isReturnId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int Insert<T>(T item, bool isReturnId, IDbTransaction transaction = null) where T : class
        {
            if (!isReturnId)
            {
                Insert<T>(item, transaction);
                return 0;
            }
            string table = SqlBuilderHelper.GetTableName<T>();
            var sql = SqlBuilderHelper.CreateInsertSql<T>(table, null);

            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    //var res = int.Parse(conn.ExecuteScalar(sql, item, transaction, commandTimeout: commandTimeout).ToString());
                    var res = (int)conn.Insert<T>(item, commandTimeout: commandTimeout);
                    return res;
                }
            }
            else
            {
                var conn = transaction.Connection;
                var res = (int)conn.Insert<T>(item, transaction: transaction, commandTimeout: commandTimeout);
                //var res = int.Parse(conn.ExecuteScalar(sql, item, transaction, commandTimeout: commandTimeout).ToString());
                return res;
            }
        }


        /// <summary>
        /// 批量插入实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="transaction"></param>
        public bool BulkInsert<T>(IEnumerable<T> list, SqlTransaction transaction = null) where T : class
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    var res = conn.Insert(list, commandTimeout: commandTimeout);
                    return true;
                    //conn.BulkInsert(list, transaction);
                }
            }
            else
            {
                var conn = transaction.Connection;
                //conn.BulkInsert(list, transaction: transaction);
                var res = conn.Insert(list, transaction: transaction, commandTimeout: commandTimeout);
                return true;
            }
        }

        /// <summary>
        /// 批量新增事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="enetiy"></param>
        /// <param name="entityItems"></param>
        /// <returns></returns>
        public bool BulkInsertTrans<T, T2>(T enetiy, List<T2> entityItems)
         where T : class, new()
         where T2 : class, new()
        {
            using (var conn = GetOpenConnection())
            {
                var trans = conn.BeginTransaction("SampleTransaction");
                try
                {
                    string tTable = SqlBuilderHelper.GetTableName<T>();
                    string sqlStr = SqlBuilderHelper.CreateInsertSql<T>(tTable);
                    conn.Execute(sqlStr, enetiy, trans, null, null);

                    string t2Table = SqlBuilderHelper.GetTableName<T2>();
                    //写入子表
                    for (int i = 0; i < entityItems.Count; i++)
                    {
                        string sqlItemStr = SqlBuilderHelper.CreateInsertSql<T2>(t2Table);
                        conn.Execute(sqlItemStr, entityItems[i], trans, null, CommandType.Text);
                    }
                    //conn.Insert(entityItems, trans);
                    trans.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }

        #endregion

        #region Update

        /// <summary>
        /// 更新单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Update<T>(T item, IDbTransaction transaction = null) where T : class
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    return conn.Update(item, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
            }
        }

        /// <summary>
        /// 批量更新实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool BulkUpdate<T>(List<T> item, IDbTransaction transaction = null) where T : class
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    return conn.Update(item, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Update(item, transaction: transaction, commandTimeout: commandTimeout);
            }
        }

        /// <summary>
        /// 批量修改事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T2"></typeparam>
        /// <param name="enetiy"></param>
        /// <param name="entityItems"></param>
        /// <returns></returns>
        public bool BulkUpdateTrans<T, T2>(T enetiy, List<T2> entityItems)
            where T : class, new()
            where T2 : class, new()
        {
            using (var conn = GetOpenConnection())
            {
                IDbTransaction trans = conn.BeginTransaction("SampleTransaction");
                try
                {
                    string tTable = SqlBuilderHelper.GetTableName<T>();
                    string sqlStr = SqlBuilderHelper.CreateUpdateSql<T>(tTable, " ID=@ID");
                    conn.Execute(sqlStr, enetiy, trans, null, null);

                    string t2Table = SqlBuilderHelper.GetTableName<T2>();
                    //写入子表
                    for (int i = 0; i < entityItems.Count; i++)
                    {
                        string sqlItemStr = SqlBuilderHelper.CreateUpdateSql<T2>(t2Table, " ID=@ID");
                        conn.Execute(sqlItemStr, entityItems[i], trans, null, CommandType.Text);
                    }
                    trans.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    return false;
                }
                finally
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除单个实体(sql)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Delete(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    return ExecuteSqlInt(sql, param) > 0;
                }
            }
            else
            {
                var conn = transaction.Connection;
                return ExecuteSqlInt(sql, param, transaction) > 0;
            }
        }

        /// <summary>
        /// 删除单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="item"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool Delete<T>(T item, IDbTransaction transaction = null) where T : class
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    return conn.Delete(item, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Delete(item, transaction: transaction, commandTimeout: commandTimeout);
            }
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="items"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public bool BulkDelete<T>(List<T> items, IDbTransaction transaction = null) where T : class
        {
            if (transaction == null)
            {
                using (var conn = GetOpenConnection())
                {
                    return conn.Delete(items, commandTimeout: commandTimeout);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Delete(items, transaction: transaction, commandTimeout: commandTimeout);
            }
        }
        #endregion

        #region Other
        /// <summary>
        /// 执行sql，返回影响行数 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetOpenConnection())
                {

                    return conn.Execute(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return conn.Execute(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text);
            }
        }
        /// <summary>
        /// 执行sql，返回影响行数--异步
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public async Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetOpenConnection())
                {

                    return await conn.ExecuteAsync(sql, param, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
                }
            }
            else
            {
                var conn = transaction.Connection;
                return await conn.ExecuteAsync(sql, param, transaction: transaction, commandTimeout: commandTimeout, commandType: CommandType.Text).ConfigureAwait(false);
            }
        }

        /// <summary>
        /// 执行sql，返回数量
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetOpenConnection())
                {
                    var res = conn.ExecuteScalar<dynamic>(sql, param);
                    Type type = typeof(T);
                    if (type == typeof(ValueType) || type == typeof(Int32) || type == typeof(Decimal))
                        return res ?? 0;
                    else if (type == typeof(String))
                        return res == null ? "" : Convert.ToString(res);
                    else
                        return res ?? default(T);
                }
            }
            else
            {
                var conn = transaction.Connection;
                var res = conn.ExecuteScalar<dynamic>(sql, param, transaction);
                Type type = typeof(T);
                if (type == typeof(ValueType) || type == typeof(Int32) || type == typeof(Decimal))
                    return res ?? 0;
                else if (type == typeof(String))
                    return res == null ? "" : Convert.ToString(res);
                else
                    return res ?? default(T);
            }
        }


        public dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null)
        {
            if (transaction == null)
            {
                using (IDbConnection conn = GetOpenConnection())
                {
                    var res = conn.ExecuteScalar<dynamic>(sql, param);
                    return res ?? null;
                }
            }
            else
            {
                var conn = transaction.Connection;
                var res = conn.ExecuteScalar<dynamic>(sql, param, transaction);
                return res ?? null;
            }
        }

        #endregion

    }

4.另外附上完整的IDapperRepositoryBase接口和DapperService、IDapperService具体封装如下

public interface IDapperRepositoryBase<T> where T : class
    {

        //IDbConnection GetConnection(bool useWriteConn);

        #region Query
        T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class;


        T GetModel<T>(Expression<Func<T, bool>> expression, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        T GetModel<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        T QueryFirstOrDefault<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null);

        List<T> QueryGetList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null);

        Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class;

        Task<T> QueryFirstOrDefaultAsync<T>(Expression<Func<T, bool>> expression,  bool useWriteConn = false) where T : class;

        Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, bool useWriteConn = false);

        Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false);

        Task<List<T>> ExecuteReaderRetListAsync<T>(Expression<Func<T, bool>> expression, bool useWriteConn = false) where T : class;

        List<T> GetList<T>(Expression<Func<T, bool>> expression, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        List<T> GetList<T>(string sql, object param = null, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        List<T> GetPageList<T>(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, out int total,string sort = "") where T : class;

        List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null);

        List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, out int total, object param = null);
        #endregion

        #region Add
        bool Insert<T>(T item, IDbTransaction transaction = null) where T : class;

        int Insert<T>(T item, bool isReturnId, IDbTransaction transaction = null) where T : class;

        bool BulkInsert<T>(IEnumerable<T> list, SqlTransaction transaction = null) where T : class;
        bool BulkInsertTrans<T, T2>(T enetiy, List<T2> entityItems)
         where T : class, new()
         where T2 : class, new();
        #endregion

        #region Update
        bool Update<T>(T item, IDbTransaction transaction = null) where T : class;
        bool BulkUpdate<T>(List<T> item, IDbTransaction transaction = null) where T : class;
        bool BulkUpdateTrans<T, T2>(T enetiy, List<T2> entityItems)
           where T : class, new()
           where T2 : class, new();
        #endregion

        #region Delete
        bool Delete(string sql, object param = null, IDbTransaction transaction = null);
        bool Delete<T>(T item, IDbTransaction transaction = null) where T : class;

        bool BulkDelete<T>(List<T> items, IDbTransaction transaction = null) where T : class;
        #endregion

        #region Other
        int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null);

        Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null);

        T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null);

        dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null);
        #endregion

    }

  /// <summary>
    /// Dapper 服务封装
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DapperService<T> : IDapperService<T> where T : class
    {

        public IDapperRepositoryBase<T> repositoryBase;

        public DapperService()
        {
            repositoryBase = new DapperRepositoryBase<T>();
        }

        #region Query
        public T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
        {
            return repositoryBase.GetById<T>(id, transaction, useWriteConn);
        }

        public T GetModel<T>(Expression<Func<T, bool>> expression, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            return repositoryBase.GetModel<T>(expression, transaction, commandTimeout, buffered);
        }


        public T GetModel<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            return repositoryBase.GetModel<T>(sql, param, transaction, commandTimeout, buffered);
        }

        public T QueryFirstOrDefault<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
        {
            return repositoryBase.QueryFirstOrDefault<T>(sql, param, useWriteConn, transaction);
        }

        public List<T> QueryGetList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null)
        {
            return repositoryBase.QueryGetList<T>(sql, param, useWriteConn, transaction);
        }

        public Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class
        {
            return repositoryBase.GetByIdAsync<T>(id, transaction, useWriteConn);
        }

        public Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, bool useWriteConn = false)
        {
            return repositoryBase.QueryFirstOrDefaultAsync<T>(sql, param, useWriteConn);
        }


        public Task<T> QueryFirstOrDefaultAsync<T>(Expression<Func<T, bool>> expression, bool useWriteConn = false) where T : class
        {
            return repositoryBase.QueryFirstOrDefaultAsync<T>(expression, useWriteConn);
        }

        public Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false)
        {
            return repositoryBase.ExecuteReaderRetListAsync<T>(sql, param, useWriteConn);
        }

        public Task<List<T>> ExecuteReaderRetListAsync<T>(Expression<Func<T, bool>> expression, bool useWriteConn = false) where T : class
        {
            return repositoryBase.ExecuteReaderRetListAsync<T>(expression, useWriteConn);
        }

        public List<T> GetList<T>(string sql, object param = null, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            return repositoryBase.GetList<T>(sql, param, predicate, transaction, commandTimeout, buffered);
        }

        public List<T> GetList<T>(Expression<Func<T, bool>> expression, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class
        {
            return repositoryBase.GetList<T>(expression, predicate, transaction, commandTimeout, buffered);
        }

        public List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null)
        {
            return repositoryBase.GetPageList<T>(sql, sort, pageIndex, pageSize, useWriteConn, param);
        }

        public List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, out int total, object param = null)
        {
            return repositoryBase.GetPageList<T>(sql, sort, pageIndex, pageSize, out total, param);
        }

        public List<T> GetPageList<T>(Expression<Func<T, bool>> expression, int pageIndex, int pageSize, out int total, string sort = "") where T : class
        {
            return repositoryBase.GetPageList<T>(expression, pageIndex, pageSize, out total, sort);
        }
        #endregion

        #region Add
        public bool Insert<T>(T item, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.Insert<T>(item, transaction);
        }

        public int Insert<T>(T item, bool isReturnId, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.Insert<T>(item, isReturnId, transaction);
        }

        public bool BulkInsert<T>(IEnumerable<T> list, SqlTransaction transaction = null) where T : class
        {
            return repositoryBase.BulkInsert(list, transaction);
        }

        public bool BulkInsertTrans<T, T2>(T enetiy, List<T2> entityItems)
             where T : class, new()
             where T2 : class, new()
        {
            return repositoryBase.BulkInsertTrans(enetiy, entityItems);
        }
        #endregion

        #region Update
        public bool Update<T>(T item, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.Update(item, transaction);
        }

        public bool BulkUpdate<T>(List<T> item, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.BulkUpdate(item, transaction);
        }
        public bool BulkUpdateTrans<T, T2>(T enetiy, List<T2> entityItems)
                where T : class, new()
                where T2 : class, new()
        {
            return repositoryBase.BulkInsertTrans(enetiy, entityItems);
        }
        #endregion

        #region Delete
        public bool Delete(string sql, object param = null, IDbTransaction transaction = null)
        {
            return repositoryBase.Delete(sql, param, transaction);
        }
        public bool Delete<T>(T item, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.Delete(item, transaction);
        }

        public bool BulkDelete<T>(List<T> items, IDbTransaction transaction = null) where T : class
        {
            return repositoryBase.Delete(items, transaction);
        }
        #endregion

        #region Other
        public int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null)
        {
            return repositoryBase.ExecuteSqlInt(sql, param, transaction);
        }

        public Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null)
        {
            return repositoryBase.ExecuteSqlIntAsync(sql, param, transaction);
        }

        public T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null)
        {
            return repositoryBase.ExecuteScalar(sql, param, transaction);
        }

        public dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null)
        {
            return repositoryBase.ExecuteScalar(sql, param, transaction);
        }

        #endregion
    }

/// <summary>
    /// Dapper 服务接口层封装
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IDapperService<T> where T : class
    {
        #region Query
        T GetById<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class;
        T GetModel<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        T QueryFirstOrDefault<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null);

        List<T> QueryGetList<T>(string sql, object param = null, bool useWriteConn = false, IDbTransaction transaction = null);

        Task<T> GetByIdAsync<T>(int id, IDbTransaction transaction = null, bool useWriteConn = false) where T : class;

        Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, bool useWriteConn = false);

        Task<List<T>> ExecuteReaderRetListAsync<T>(string sql, object param = null, bool useWriteConn = false);

        List<T> GetList<T>(string sql, object param = null, object predicate = null, IDbTransaction transaction = null, int? commandTimeout = null, bool buffered = false) where T : class;

        List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, bool useWriteConn = false, object param = null);

        List<T> GetPageList<T>(string sql, string sort, int pageIndex, int pageSize, out int total, object param = null);
        #endregion

        #region Add
        bool Insert<T>(T item, IDbTransaction transaction = null) where T : class;

        int Insert<T>(T item, bool isReturnId, IDbTransaction transaction = null) where T : class;

        bool BulkInsert<T>(IEnumerable<T> list, SqlTransaction transaction = null) where T : class;
        bool BulkInsertTrans<T, T2>(T enetiy, List<T2> entityItems)
         where T : class, new()
         where T2 : class, new();
        #endregion

        #region Update
        bool Update<T>(T item, IDbTransaction transaction = null) where T : class;
        bool BulkUpdate<T>(List<T> item, IDbTransaction transaction = null) where T : class;
        bool BulkUpdateTrans<T, T2>(T enetiy, List<T2> entityItems)
           where T : class, new()
           where T2 : class, new();
        #endregion

        #region Delete
        bool Delete(string sql, object param = null, IDbTransaction transaction = null);
        bool Delete<T>(T item, IDbTransaction transaction = null) where T : class;

        bool BulkDelete<T>(List<T> items, IDbTransaction transaction = null) where T : class;
        #endregion

        #region Other
        int ExecuteSqlInt(string sql, object param = null, IDbTransaction transaction = null);

        Task<int> ExecuteSqlIntAsync(string sql, object param = null, IDbTransaction transaction = null);

        T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null);

        dynamic ExecuteScalar(string sql, object param = null, IDbTransaction transaction = null);
        #endregion

    }