Dapper简单使用读写分离

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Linq;
using CloudCubic.Common.LogHelper;
using Cloudcubic.Common;
using System.Diagnostics;

namespace CloudCubic.Common.Helper
{
    /// <summary>
    /// 多为异步操作 部分函数
    /// </summary>
    public class YlfDapperHelper
    {
        public static async Task<List<T>> SqlQueryListAsync<T>(string sql, object parames = null, int timeOutSencond = 20, CommandType sqltype = CommandType.Text, bool mainDb = false)
        {
            try
            {
                using (IDbConnection conn = GetDbConnnetion(mainDb))
                {
                    var data = await conn.QueryAsync<T>(sql, parames, commandTimeout: timeOutSencond, commandType: sqltype);
                    return data != null ? data.ToList() : new List<T>();
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="mainDb"></param>
        /// <returns></returns>
        public static async Task<T> SqlQueryOrProcedureAsync<T>(string sql, object parames = null, int timeOutSencond = 20, CommandType sqltype = CommandType.Text, bool mainDb = false)
        {
            try
            {
                using IDbConnection conn = GetDbConnnetion(mainDb);
                return await conn.ExecuteScalarAsync<T>(sql, parames, commandTimeout: timeOutSencond, commandType: sqltype);
            }
            catch (Exception)
            {
                return default(T);
            }
        }

        /// <summary>
        /// 查询  
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <returns></returns>
        public static async Task<T> SQlQueryFirstOrDefault<T>(string sql, object parames = null, int timeOutSencond = 20, CommandType sqltype = CommandType.Text, bool mainDb = false)
        {
            try
            {
                using IDbConnection conn = GetDbConnnetion(mainDb);
                return await conn.QueryFirstOrDefaultAsync<T>(sql, parames, commandTimeout: timeOutSencond, commandType: sqltype);
            }
            catch (Exception)
            {
                return default(T);
            }
        }
        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="timeOutSencond"></param>
        /// <param name="sqltype"></param>
        /// <param name="mainDb"></param>
        /// <returns></returns>
        public static async Task<T> SQlQuerySingleAsync<T>(string sql, object parames = null, int timeOutSencond = 20, CommandType sqltype = CommandType.Text, bool mainDb = false)
        {
            try
            {
                using IDbConnection conn = GetDbConnnetion(mainDb);
                return await conn.QuerySingleAsync<T>(sql, parames, commandTimeout: timeOutSencond, commandType: sqltype);
            }
            catch (Exception)
            {
                return default(T);
            }
        }

        ///// <summary>
        ///// 同时查询多个实体的操作
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="funcObj"></param>
        ///// <param name="dicParams"></param>
        ///// <returns></returns>
        //public static async void QueryMultipeEntityAsync(string sql, Dictionary<string, object> dicParams, Action<GridReader> funcObj, int timeOutSencond = 20, bool mainDb = false)
        //{
        //    using IDbConnection conn = await GetDbConnnetion(mainDb);
        //    if (dicParams != null)
        //    {
        //        DynamicParameters ps = new DynamicParameters();
        //        foreach (string item in dicParams.Keys)
        //        {
        //            ps.Add(item, dicParams[item]);
        //        }
        //        using (var readRsult = await conn.QueryMultipleAsync(sql, ps, commandTimeout: timeOutSencond))
        //        {
        //            funcObj.Invoke(readRsult);
        //        }
        //    }
        //}

        /// <summary>
        /// 执行sql 并返回受影响的行数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="parames"></param>
        /// <param name="mainDb">写入默认为主库true</param>
        /// <returns></returns>
        public static async Task<int> SQlExecuteAsync(string sql, object parames = null, int timeOutSencond = 20, CommandType sqltype = CommandType.Text, bool mainDb = true)
        {
            try
            {
                using IDbConnection conn = GetDbConnnetion(mainDb);
                return await conn.ExecuteAsync(sql, parames, commandTimeout: timeOutSencond, commandType: sqltype);
            }
            catch (Exception)
            {
                return 0;
            }
        }
        /// <summary>
        /// 获取 数据库连接 true:主库；false:从库
        /// </summary>
        /// <returns></returns>
        private static IDbConnection GetDbConnnetion(bool mainDb = false)
        {
            var listconnectionInfoEntity = YLF_ConfigHelper.listConfigEntity;
            // YLF_ConfigHelper._configuration.GetSection("DBS").Get<DBOptionsInFo[]>();
            if (listconnectionInfoEntity == null)
                throw new Exception("请检查是否存在数据库连接字符串");

            // List<string> listConnection = new List<string>();//第一个索引为主库，
            if (!mainDb) //从数据库，通常为读的数据库
            {
                if (listconnectionInfoEntity != null && listconnectionInfoEntity.Length <= 1)
                {
                    SerilogServer.WriteLog("dbconStr111", new string[] { listconnectionInfoEntity[0].Connection });
                    return new SqlConnection(listconnectionInfoEntity[0].Connection);
                }
                else if (listconnectionInfoEntity != null && listconnectionInfoEntity.Length > 2)//两个从库就开始随机
                {
                    int index = new Random().Next(1, listconnectionInfoEntity.Length - 1);
                    return new SqlConnection(listconnectionInfoEntity[index].Connection);
                }
                else
                {
                    //Console.WriteLine($"=====listconnectionInfoEntity>2===={listconnectionInfoEntity[1].Connection}====");
                    //SerilogServer.WriteLog("dbconStr_else", new string[] { listconnectionInfoEntity[1].Connection });
                    return new SqlConnection(listconnectionInfoEntity[1].Connection);
                }
            }
            else
            {
                return new SqlConnection(listconnectionInfoEntity[0].Connection);
            }
        }
        /// <summary>
        /// 返回的是
        /// </summary>
        /// <param name="mainDb"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        private static string GetDbConnnetionStr(bool mainDb = false)
        {
            var listconnectionInfoEntity = YLF_ConfigHelper.listConfigEntity;
            if (listconnectionInfoEntity == null)
                throw new Exception("请检查是否存在数据库连接字符串");
            if(mainDb)
            {
                if (listconnectionInfoEntity != null && listconnectionInfoEntity.Length <= 1)
                {
                    Console.WriteLine("=====listconnectionInfoEntity.Length <= 1======");
                    SerilogServer.WriteLog("dbconStr111", new string[] { listconnectionInfoEntity[0].Connection });

                    return  listconnectionInfoEntity[0].Connection;
                }
                else if (listconnectionInfoEntity != null && listconnectionInfoEntity.Length > 2)//两个从库就开始随机
                {
                    int index = new Random().Next(1, listconnectionInfoEntity.Length - 1);
                    Console.WriteLine($"=====listconnectionInfoEntity>2===={index}====");
                    return  listconnectionInfoEntity[index].Connection;
                }
                else
                {
                    return  listconnectionInfoEntity[1].Connection;
                }
            }
            else
            {
                return  listconnectionInfoEntity[0].Connection;
            }
        }

        /// <summary>
        /// 执行存储过程，迁移
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parms"></param>
        /// <param name="tableNames"></param>
        /// <param name="cmdType"></param>
        /// <param name="isMainDB"></param>
        /// <returns></returns>
        public static DataSet RunProcedure(string cmdText, SqlParameter[] parms, string tableNames, CommandType cmdType = CommandType.StoredProcedure, bool isMainDB = false)
        {
            DataSet ds = new DataSet();
            var listconnectionInfoEntity = YLF_ConfigHelper.listConfigEntity;
            string newConnectionString = listconnectionInfoEntity[0].Connection;// connectionString;
            if (isMainDB) newConnectionString = GetDbConnnetionStr(isMainDB);
            using (SqlConnection conn = new SqlConnection(newConnectionString))
            {
                using (SqlCommand comm = new SqlCommand())
                {
                    try
                    {
                        conn.Open();
                        comm.Connection = conn;
                        comm.CommandText = cmdText;
                        comm.CommandTimeout = 600;
                        comm.CommandType = cmdType;
                        if (parms != null)
                        {
                            foreach (SqlParameter parm in parms)
                            {
                                comm.Parameters.Add(parm);
                            }
                        }
                        Stopwatch sw = new Stopwatch();
                        sw.Start();
                        using (SqlDataAdapter sda = new SqlDataAdapter(comm))
                        {
                            sw.Stop();
                            try
                            {
                                if (sw.ElapsedMilliseconds > 5000)
                                {
                                    string parmsStr = "";
                                    foreach (var item in parms)
                                    {
                                        parmsStr += $"{item.ParameterName}={item.Value}";
                                    }
                                    Utils.LogFile("/Log/timeOut", $"/{DateTime.Now.ToString("yyyy-MM-dd")}.txt", $"执行存储时间过长，执行时间为：{sw.ElapsedMilliseconds}毫秒，存储过程名为：{cmdText},参数:{parmsStr}");
                                    //LogHelper.WriteLog($"执行存储时间过长，执行时间为：{sw.ElapsedMilliseconds}毫秒，存储过程名为：{cmdText}", new Exception("执行存储时间过长"));

                                }
                            }
                            catch
                            {
                            }
                            string[] tables = tableNames.Split('|');
                            string mapTableName = "Table";
                            for (int index = 0; index < tables.Length; index++)
                            {
                                if (tables[index] != null && tables[index].Length >= 0)
                                {
                                    sda.TableMappings.Add(mapTableName, tables[index]);
                                    mapTableName = "Table" + (index + 1).ToString();
                                }
                            }
                            sda.Fill(ds);
                            comm.Parameters.Clear();
                            return ds;
                        }
                    }
                    catch
                    {
                        string parmsStr = "";
                        foreach (var item in parms)
                        {
                            parmsStr += $"{item.ParameterName}={item.Value},";
                        }
                        // LogHelper.WriteLog($"执行存储过程出现异常,存储过程名为：{cmdText},参数param:{parmsStr}");
                        SerilogServer.WriteLog("dbconStr111", new string[] { listconnectionInfoEntity[0].Connection });
                        conn.Close();
                        throw new Exception("执行异常");
                    }
                }
            }
        }
       
    }

    /// <summary>
    /// 
    /// </summary>
    public static class YLF_ConfigHelper
    {
        // public static IConfiguration _configuration { get; set; }

        public static DBOptionsInFo[] listConfigEntity { get; set; }
    }



    public class DBOptionsInFo
    {
        public string ConnId { get; set; }
        public int DBType { get; set; }
        public bool Enabled { get; set; }
        public int HitRate { get; set; }
        public string Connection { get; set; }


        //"DBS": [

        //{
        //  "ConnId": "MYSQL1",
        //  "DBType": 1,
        //  "Enabled": true,
        //  "HitRate": 20,
        //  "Connection": "server=192.168.1.7\\ms2012;uid=sa;pwd=Promotech1;database=ccp;"
        //  //"Connection": "server=q39.cloudcubic.net;uid=sa;pwd=cloudcubicptdsa91kd219AD9D2K2D;database=CloudCubicPlatform;"
        //}

    }
}

    public class Startup
    {

        private IServiceCollection _services;

        public Startup(IConfiguration configuration, IWebHostEnvironment env)
        {
            Configuration = configuration;
            Env = env;
            // YLF_ConfigHelper._configuration = configuration;
            YLF_ConfigHelper.listConfigEntity = Configuration.GetSection("DBS").Get<DBOptionsInFo[]>();
        }
    }