Dapper，DapperHelper帮助类常用的CURD帮助类简单封装

部分函数，只要用来Mysql的操作，如果需要存储过程等请自行稍作修改为缺省值的通用方式，代码完全个人手敲已经测试验证ok

已经使用在项目中，有点开心！代码还可以进一步去优化，如封装为泛型委托的形式

using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Text;

namespace ZRF.ESB.Common.Helper
{
    using Dapper;
    using MySql.Data.MySqlClient;
    using System.Data;
    using System.Linq;
    using System.Threading.Tasks;
    using static Dapper.SqlMapper;

    /// <summary>
    /// dapper操作帮助类，部分函数
    /// @auth jason
    /// </summary>
    public class ZRF_DapperHelper
    {
        public static string strNormal = ZRF_ConfigHelper.GetDbConnectionStr();
        public static string strRscGallery = ZRF_ConfigHelper.GetRscDbConnectionStr();

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static IEnumerable<T> Query<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            try
            {
                using MySqlConnection conn = GetMySqlConnnetion(dbtype);
                Console.WriteLine("ConnectionString=" + conn.ConnectionString);
                return conn.Query<T>(sql, parames);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 查询   字典参数化
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="dic"></param>
        /// <param name="timeOut"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryByDicParams<T>(string sql, Dictionary<string, object> dic = null, int timeOut = 120, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            try
            {
                using (IDbConnection conn = GetMySqlConnnetion(dbtype))
                {
                    DynamicParameters param = new DynamicParameters();
                    if (dic != null)
                    {
                        foreach (var item in dic.Keys)
                        {
                            param.Add(item, dic[item]);
                        }
                        return conn.Query<T>(sql, param, commandTimeout: timeOut);
                    }
                    return conn.Query<T>(sql);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 没有约束的泛型查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static List<T> QueryList<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            try
            {
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    var glist = conn.Query<T>(sql, parames);
                    if (glist != null)
                    {
                        return glist.AsList<T>();
                    }
                }
                return null;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }



        /// <summary>
        /// 查询 异步操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> QueryAsync<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            try
            {
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    return await conn.QueryAsync<T>(sql, parames);


                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 查询 异步操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="dbtype"></param>
        /// <param name="parames"></param>
        /// <returns></returns>
        public static async Task<IEnumerable<T>> QueryAsyncNocheck<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            try
            {
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    return await conn.QueryAsync<T>(sql, parames);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        /// <summary>
        /// 查询 异步操作增加-分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<PageDataNew> QueryPageAsync<T>(string sql,
            object parames = null, int pageIndex = 1, int pageSize = 20, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            var model = new PageDataNew();
            try
            {
                int skip = 0;
                if (pageIndex > 0)
                {
                    skip = (pageIndex - 1) * pageSize;
                }
                var addSql = string.Format(" LIMIT {0},{1}", skip, pageSize);
                var newSql = sql + addSql;
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    var result = await conn.QueryAsync<T>(newSql, parames);

                    string sqlCont = $"select count(*) from ( {sql} ) as a";
                    var count = await QueryCountAsync<int>(sqlCont, parames);
                    model.total = count;
                    model.current = pageIndex;
                    model.totalPage = model.total % pageSize > 0 ? ((model.total / pageSize) + 1) : (model.total / pageSize);
                    model.list = result;
                    return model;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        ///  update  改良，泛型,赋值漏掉或者写错的情况
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<PageDataNew<T>> QueryPageTAsync<T>(string sql,
    object parames = null, int pageIndex = 0, int pageSize = 20, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            var model = new PageDataNew<T>();
            try
            {
                if (pageIndex == 0)
                    throw new Exception("请联系管理员，pageIndex参数必填！");
                int skip = 0;
                if (pageIndex > 0)
                    skip = (pageIndex - 1) * pageSize;
                var addSql = $" LIMIT {skip},{pageSize} ";
                var newSql = sql + addSql;
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    var result = await conn.QueryAsync<T>(newSql, parames);

                    string sqlCont = $"select count(*) from ( {sql} ) as temptabcount";
                    var count = await QueryCountAsync<int>(sqlCont, parames);
                    model.total = count;
                    model.current = pageIndex;
                    model.totalPage = model.total % pageSize > 0 ? ((model.total / pageSize) + 1) : (model.total / pageSize);
                    model.list = result;
                    return model;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        /// 统计次数 如：select  count(1) from personnel where isdel=0;
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static T QueryCount<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            try
            {
                using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
                {
                    return conn.ExecuteScalar<T>(sql, parames);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        /// <summary>
        ///  统计次数 如：select  count(1) from personnel where isdel=0;
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<T> QueryCountAsync<T>(string sql, object parames = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            try
            {
                using MySqlConnection conn = GetMySqlConnnetion(dbtype);
                return await conn.ExecuteScalarAsync<T>(sql, parames);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #region 过期的方法
        /// <summary>
        /// 查询两个实体的操作,过期的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <returns></returns>
        [Obsolete("过期的方法，建议使用QueryMultipeEntityAsync(***)函数")]
        public static Tuple<IEnumerable<T>, IEnumerable<M>> QueryTwoEntity<T, M>(string sql, object parames = null) where T : class, new() where M : class, new()
        {
            try
            {
                using MySqlConnection conn = GetMySqlConnnetion();
                var readData = conn.QueryMultiple(sql, parames);
                var obj = readData.Read<T>();//as IEnumerable<T>;
                var obj2 = readData.Read<M>();// as IEnumerable<M>;
                return (obj, obj2).ToTuple();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion



        /// <summary>
        /// 获取单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="dbtype"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static async Task<T> QuerySingleOrDefaultAsync<T>(string sql, object param = null, DBIndexType dbtype = DBIndexType.Normal) where T : class, new()
        {
            using (IDbConnection conn = GetMySqlConnnetion(dbtype))
            {
                return await conn.QuerySingleOrDefaultAsync<T>(sql, param);
            }
        }

        /// <summary>
        /// 查询多个实体
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="reader"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task QueryMultipeEntityAsync(string sql, object param, Action<GridReader> reader, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (IDbConnection conn = GetMySqlConnnetion(dbtype))
            {
                using (var read = await conn.QueryMultipleAsync(sql, param))
                {
                    reader.Invoke(read);
                }
            }
        }

        /// <summary>
        ///理论上 可以同时查询容易多个实体的操作 同时查询多个实体的异步操作 不需要添加await 当做同步方法来使用
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dicParams"></param>
        /// <param name="funcObj"></param>
        /// <param name="dbtype"></param>
        public static void QueryMultipeEntityAsync(string sql, Dictionary<string, object> dicParams, Action<GridReader> funcObj, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                if (dicParams != null)
                {
                    DynamicParameters ps = new DynamicParameters();
                    foreach (string item in dicParams.Keys)
                    {
                        ps.Add(item, dicParams[item]);
                    }
                    using (var readRsult = conn.QueryMultipleAsync(sql, ps).Result)
                    {
                        funcObj.Invoke(readRsult);
                    }
                }
            }
        }
        /// <summary>
        /// 新增 ,修改，删除操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dicParams">可空类型，有则为参数化</param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static int ExecuterNonQuery(string sql, Dictionary<string, object> dicParams = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                DynamicParameters parameters = new DynamicParameters();
                try
                {
                    if (dicParams != null && dicParams.Count > 0)
                    {
                        foreach (var item in dicParams.Keys)
                        {
                            parameters.Add(item, dicParams[item]);
                        }
                        return conn.ExecuteAsync(sql, dicParams).Result;
                    }
                    return conn.ExecuteAsync(sql).Result;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        /// <summary>
        /// 新增 ,修改，删除异步操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dicParams"></param>
        /// <param name="transaction"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<int> ExecuterNonQueryAsync(string sql, Dictionary<string, object> dicParams = null, IDbTransaction transaction = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                DynamicParameters parameters = new DynamicParameters();
                try
                {
                    if (dicParams != null && dicParams.Count > 0)
                    {
                        foreach (var item in dicParams.Keys)
                        {
                            parameters.Add(item, dicParams[item]);
                        }
                        return await conn.ExecuteAsync(sql, dicParams, transaction);
                    }
                    else { return await conn.ExecuteAsync(sql, transaction); }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        /// <summary>
        /// 新增 ,修改，删除异步操作
        /// </summary>
        /// <typeparam name="T">用来约束 字典的类型</typeparam>
        /// <param name="sql"></param>
        /// <param name="dicParams"></param>
        /// <param name="transaction"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<int> ExecuterNonQueryAsync<T>(string sql, Dictionary<string, T> dicParams = null, IDbTransaction transaction = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                DynamicParameters parameters = new DynamicParameters();
                try
                {
                    if (dicParams != null && dicParams.Count > 0)
                    {
                        foreach (var item in dicParams.Keys)
                        {
                            parameters.Add(item, dicParams[item]);
                        }
                        return await conn.ExecuteAsync(sql, dicParams, transaction);
                    }
                    return await conn.ExecuteAsync(sql, transaction);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 查询单个结果
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="dicParams"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(string sql, Dictionary<string, object> dicParams = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                DynamicParameters parameters = new DynamicParameters();
                try
                {
                    if (dicParams != null && dicParams.Count > 0)
                    {
                        foreach (var item in dicParams.Keys)
                        {
                            parameters.Add(item, dicParams[item]);
                        }
                        return conn.ExecuteScalar<T>(sql, dicParams);
                    }
                    return conn.ExecuteScalar<T>(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        /// <summary>
        /// 查询单个结果  Async
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="dicParams"></param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<T> ExecuteScalarAsync<T>(string sql, Dictionary<string, object> dicParams = null, DBIndexType dbtype = DBIndexType.Normal)
        {
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                DynamicParameters parameters = new DynamicParameters();
                try
                {
                    if (dicParams != null && dicParams.Count > 0)
                    {
                        foreach (var item in dicParams.Keys)
                        {
                            parameters.Add(item, dicParams[item]);
                        }
                        return await conn.ExecuteScalarAsync<T>(sql, dicParams);
                    }
                    return await conn.ExecuteScalarAsync<T>(sql);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }


        /// <summary>
        /// 增加，删除，修改使用的 事务方法  Sqllist为依次执行
        /// </summary>
        /// <param name="sqlList">sql 集合必填</param>
        /// <param name="dicParam">必填</param>
        /// <param name="timeOutSecond">超时时间</param>
        /// <param name="StrictMode">严格模式，默认为true，例如有10条新增操作的10条sql语句，就必须新增并返回结果等于10次成功</param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<string> DoTransaction(List<string> sqlList, List<Dictionary<string, object>> dicParam, int timeOutSecond = 60, bool StrictMode = true, DBIndexType dbtype = DBIndexType.Normal)
        {
            MySqlTransaction tranction = null;
            string msg = string.Empty;
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                try
                {
                    if (sqlList == null || dicParam == null)
                        throw new Exception("SQL集合与字典参数必填");
                    if (sqlList.Count <= 0 || dicParam.Count != sqlList.Count)
                        throw new Exception("sql语句与字典数量对不上，如两个sql集合字典就有两个实例！");
                    conn.Open();
                    tranction = conn.BeginTransaction();
                    int index = 0;
                    if (timeOutSecond > 180 || timeOutSecond < 10)//大于6分钟或者小于10秒重置超时时间
                        timeOutSecond = 60;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        DynamicParameters Parameters = new DynamicParameters();
                        if (dicParam != null && dicParam.Count > 0)
                            dicParam[i].Keys.AsList().ForEach(c => Parameters.Add(c, dicParam[i][c]));
                        if (await conn.ExecuteAsync(sqlList[i], Parameters, tranction, timeOutSecond, System.Data.CommandType.Text) > 0)
                            index += 1;
                    }
                    if (StrictMode)
                    {
                        if (index == sqlList.Count)
                        {
                            await tranction.CommitAsync();
                            msg = "操作成功！";
                        }
                        else
                        {
                            await tranction.RollbackAsync(); msg = "操作失败,由于不能全部操作成功，已执行事务回滚";
                        }
                    }
                    else
                    {
                        await tranction.CommitAsync();
                        msg = "操作成功！";
                    }
                }
                catch (Exception ex)
                {
                    await tranction.RollbackAsync();
                    msg = "操作失败,已执行事务回滚,原因：" + ex.Message;
                }
                finally
                {
                    if (tranction != null)
                    {
                        await tranction.DisposeAsync();
                    }
                }
                return msg;
            }
        }

        /// <summary>
        /// 增加，删除，修改使用的 事务方法  Sqllist为依次执行
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="timeOutSecond"></param>
        /// <param name="StrictMode">严格模式，默认为true，例如有10条新增操作的10条sql语句，就必须新增并返回结果等于10次成功</param>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        public static async Task<string> DoTransaction(List<string> sqlList, int timeOutSecond = 60, bool StrictMode = true, DBIndexType dbtype = DBIndexType.Normal)
        {
            MySqlTransaction tranction = null;
            string msg = string.Empty;
            using (MySqlConnection conn = GetMySqlConnnetion(dbtype))
            {
                try
                {
                    if (sqlList == null)
                        throw new Exception("SQL集合参数必填");
                    if (sqlList.Count <= 0)
                        throw new Exception("sql语句不可以为空");
                    conn.Open();
                    tranction = conn.BeginTransaction();
                    int index = 0;
                    if (timeOutSecond > 180 || timeOutSecond < 10)//大于6分钟或者小于10秒重置超时时间
                        timeOutSecond = 60;
                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        if ((await conn.ExecuteAsync(sqlList[i], null, tranction, timeOutSecond, System.Data.CommandType.Text)) > 0)
                        {
                            index += 1;
                        }
                        //else {
                        //    msg += sqlList[i]+" | ";
                        //}
                    }
                    if (StrictMode)
                    {
                        if (index == sqlList.Count)
                        {
                            await tranction.CommitAsync();
                            msg = "操作成功！";
                        }
                        else
                        {
                            await tranction.RollbackAsync(); msg = "操作失败,由于不能全部操作成功，已执行事务回滚";
                        }
                    }
                    else
                    {
                        await tranction.CommitAsync();
                        msg = "操作成功！";
                    }
                }
                catch (Exception ex)
                {
                    await tranction.RollbackAsync();
                    msg = "操作失败,已执行事务回滚,原因：" + ex.Message;
                }
                finally
                {
                    if (tranction != null)
                        await tranction.DisposeAsync();
                }
                return msg;
            }
        }

        /// <summary>
        /// 获取 MySql连接
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection GetMySqlConnnetion(DBIndexType dbtype = DBIndexType.Normal)
        {
            switch (dbtype)
            {
                case DBIndexType.Normal:
                    return new MySqlConnection(strNormal);
                case DBIndexType.RscGallery:
                    return new MySqlConnection(strRscGallery);
                default:
                    return new MySqlConnection(strNormal);
            }
        }

        /// <summary>
        /// 获取Dapper参数化对象,这里直接New来处理，不到处引入命名空间
        /// </summary>
        /// <returns></returns>
        public static DynamicParameters GetDynamicParameters()
        {
            return new DynamicParameters();
        }
    }

    /// <summary>
    ///
    /// </summary>
    public class ZRF_ConfigHelper
    {
        public static IConfiguration _configuration { get; set; }
        public static readonly string connectionStrNormalKey = "connectionStrNormalKey";
        public static readonly string connectionStrRscKey = "connectionStrRscKey";

        /// <summary>
        /// 获取连接数据库的字符串 Readonly rscGallery
        /// </summary>
        /// <returns></returns>
        public static string GetDbConnectionStr()
        {
            return GetConnectionStrByRedis(connectionStrNormalKey, "ConnectionStrings:ReadonlyConnection");
        }

        public static string GetRscDbConnectionStr()
        {
            return GetConnectionStrByRedis(connectionStrRscKey, "ConnectionStrings:rscGalleryConnection");
        }

        public static string GetConnectionStrByRedis(string key, string connectionJsonKey, int Minutes = 10)
        {
            string connectionVal = Utilities.redisManager.GetDatabase().StringGet(key);
            if (connectionVal.IsNullOrEmptyStr())
            {
                connectionVal = _configuration.GetSection(connectionJsonKey).Value;
                Utilities.redisManager.GetDatabase().StringSet(connectionStrNormalKey, connectionVal, TimeSpan.FromMinutes(Minutes));
            }
            return connectionVal;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string GetAppSettingValueByKey(string key)
        {
            return _configuration.GetSection(key).Value;
        }

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key"></param>
        /// <returns></returns>
        public static T GetAppSettingEntity<T>(string key) where T : class, new()
        {
            return _configuration.GetSection(key).Value as T;
        }
    }
}