DB help

using Dapper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using Microsoft.Extensions;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
using System.Linq;
using Dapper.Contrib.Extensions;
using HMXTAPI.Model;
using System.Data.SqlClient;

namespace HMXTAPI.DLL
{
    public class DapperHelper
    {
        private IDbConnection ConnectionObject = null;//连接数据类的对象
        private string ConnectionString = "";//连接的字符串
        public string DataBaseType { get; set; }
        //数据库的类型0=sqlserver,1=access,2=oracle,3=mysql
        private IConfiguration _config;
        /// <summary>
        /// 设置连接的字符串及数据库类型    
        /// </summary>
        /// <param name="str">连接的字符串</param>
        /// <param name="_type">数据库类型0=sqlserver,1=access,2=oracle,3=mysql</param>
        public DapperHelper(string Kuming="")
        {
            var Configuration = new ConfigurationBuilder()
               .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
               .Build();
            _config = Configuration;
            DataBaseType = "mssql";
            ConnectionString = _config["ConnectionStrings:DB"];
            if (Kuming != "")
            {
                ConnectionString = ConnectionString.Replace("ZZ_HMXT", Kuming);
            }
            SetConnection();
        }
        /// <summary>
        /// 重新初始化链接
        /// </summary>
        public void InitConnection(string lianjie, string sqllx)
        {
            ConnectionObject = null;
            ConnectionString = lianjie;
            DataBaseType = sqllx;
            SetConnection();
        }

        public void ReinitConnection()
        {
            CloseConnection();
            ConnectionObject = null;
            var Configuration = new ConfigurationBuilder()
                  .Add(new JsonConfigurationSource { Path = "appsettings.json", ReloadOnChange = true })
                  .Build();
            _config = Configuration;
            DataBaseType = "mssql";
            ConnectionString = _config["ConnectionStrings:DB"];
            SetConnection();
        }
        /// <summary>
        /// 获取事务
        /// </summary>
        /// <returns></returns>
        public IDbTransaction HuoQuTran()
        {
            OpenConnection();
            return ConnectionObject.BeginTransaction();
        }

        public void InitConnection(string id)
        {
            //根据id查询数据库信息 
            string sjkxinxi = "select [ID],[Zdyh],[Zdrq],[gbyh],[gbrq],[Sjkm],[Ms],[Bz],[Sjklb],[Sjkip],[Sjkyh],"
                + "[sjkmm],[ZtfgCS],[ZtfgBS],[xt_zif1],[xt_zif2],[xt_zif3],[xt_zif4],[xt_zif5],[xt_shuz1],[xt_shuz2]"
                + ",[xt_shuz3] from [HMXT_A010000]  where [ID]=" + id + "";
            DataTable a01000jihe = ExecuteTableSQL(sjkxinxi, null);
            string ShuJuKuLianJie = string.Empty;

            //判断数据库ip是否为空
            if (a01000jihe != null && a01000jihe.Rows.Count > 0)
            {
                //根据Sjklb判断数据库类别
                switch (a01000jihe.Rows[0]["Sjklb"].ToString())
                {
                    case "mmsql":
                        ShuJuKuLianJie = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3};MultipleActiveResultSets=true",
                    a01000jihe.Rows[0]["Sjkmm"].ToString(), a01000jihe.Rows[0]["Sjkyh"].ToString(), a01000jihe.Rows[0]["Sjkm"], a01000jihe.Rows[0]["sjkip"]);
                        break;
                    case "access":
                        ShuJuKuLianJie = string.Format("Provider=microsoft.jet.oledb.4.0;data source={0};user id={1};password={2};",
                   a01000jihe.Rows[0]["Sjkip"], a01000jihe.Rows[0]["Sjkyh"], a01000jihe.Rows[0]["Sjkmm"]);
                        break;
                    case "oracle":
                        ShuJuKuLianJie = string.Format("data source={0};user id={1};password={2}",
                    a01000jihe.Rows[0]["sjkip"], a01000jihe.Rows[0]["Sjkyh"], a01000jihe.Rows[0]["Sjkmm"]);
                        break;
                    case "mysql":
                        ShuJuKuLianJie = string.Format("server={0};user={1};pwd={2};database={3}",
                   a01000jihe.Rows[0]["sjkip"], a01000jihe.Rows[0]["Sjkyh"], a01000jihe.Rows[0]["Sjkmm"], a01000jihe.Rows[0]["Sjkm"]);
                        break;
                    default:
                        ShuJuKuLianJie = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3};MultipleActiveResultSets=true",
                   a01000jihe.Rows[0]["Sjkmm"], a01000jihe.Rows[0]["Sjkyh"], a01000jihe.Rows[0]["Sjkm"], a01000jihe.Rows[0]["Sjkip"]);
                        break;
                }
                ConnectionObject = null;
                ConnectionString = ShuJuKuLianJie;
                DataBaseType = a01000jihe.Rows[0]["sjklb"].ToString();
                SetConnection();
            }
        }
        /// <summary>
        /// 设置连接类的对象
        /// </summary>
        private void SetConnection()
        {
            switch (DataBaseType)
            {
                case "mssql":
                    ConnectionObject = new System.Data.SqlClient.SqlConnection(ConnectionString);//连接sqlserver
                    break;
                case "access":
                    ConnectionObject = new System.Data.OleDb.OleDbConnection(ConnectionString);//连接access
                    break;
                case "oracle":
                    ConnectionObject = new System.Data.OracleClient.OracleConnection(ConnectionString);//连接oracle
                    //处理办法：
                    //在oracle 安装目录下 找到 Oracle.DataAccess.dll添加引用，然后 using Oracle.DataAccess.Client;
                    //其他的都不用动，即可。
                    //连接字符串中 如有 用的是 user=xxx 就改成user id=xxx
                    //把原来 Using 的System.Data.OracleClient去掉即可
                    break;
                case "mysql":
                    ConnectionObject = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);//连接mysql
                    break;
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void OpenConnection()
        {
            if (ConnectionObject.State == System.Data.ConnectionState.Closed)
            {
                ConnectionObject.Open();
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void CloseConnection()
        {
            if (ConnectionObject.State == System.Data.ConnectionState.Open)
            {
                ConnectionObject.Close();
            }
        }

        /// <summary>
        /// 执行sql并且返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string sql, object _param, IDbTransaction _tran = null,bool isEnd=false)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    //_tran = ConnectionObject.BeginTransaction();
                    return ConnectionObject.Execute(sql, _param, _tran);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (_tran != null && isEnd)
                    {
                        _tran.Commit();
                        CloseConnection();
                    }
                    else if(_tran==null)
                    {
                        CloseConnection();
                    }
                }
            }
        }



        /// <summary>
        /// 执行sql并且返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object ExecuteScalar(string sql, object _object, IDbTransaction _tran = null, bool isEnd = false)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    return ConnectionObject.ExecuteScalar(sql, _object);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (_tran != null && isEnd)
                    {
                        _tran.Commit();
                        CloseConnection();
                    }
                    else if (_tran == null)
                    {
                        CloseConnection();
                    }
                }
            }
        }
        /// <summary>
        /// 执行查询的sql语句，并且返回datatable结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public DataTable ExecuteTableSQL(string sql, object _param=null, IDbTransaction _tran = null, bool isEnd = false)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    IDataReader idr = ConnectionObject.ExecuteReader(sql, _param);
                    return IDataReaderToDataTable(idr);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    if (_tran != null && isEnd)
                    {
                        _tran.Commit();
                        CloseConnection();
                    }
                    else if (_tran == null)
                    {
                        CloseConnection();
                    }
                }
            }

        }
        /// <summary>
        /// 执行查询的sql语句，并且返回datatable结果
        /// </summary>
        /// <param name="columns">列</param>
        /// <param name="tableName">表名</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页尺寸</param>
        /// <param name="total">总行数</param>
        /// <param name="_param">参数</param>
        /// <param name="_tran">事务</param>
        /// <returns></returns>
        public DataTable HuoQuExecuteTable(string columns, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total,object _param = null)
        {
            //声明变量
            DataTable dtJieGuo = new DataTable();
            total = 0;
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    StringBuilder sb = new StringBuilder();
                    int skip = 1;
                    //当前行数
                    if (pageIndex > 0)
                    {
                        skip = (pageIndex - 1) * pageSize + 1;
                    }
                    switch (DataBaseType)
                    {
                        case "mssql":
                            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
                            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", columns, tableName, where, orderby, skip, pageIndex * pageSize);
                            break;
                        case "access":
                            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
                            int maxrows = (int)ExecuteScalar(sb.ToString(),null);
                            int maxpage = (maxrows % pageSize == 0) ? (maxrows / pageSize) : (maxrows / pageSize + 1);
                            int lastcount = (maxrows % pageSize == 0) ? (pageSize) : (maxrows % pageSize);
                            int rang = (maxpage + 1 - pageIndex) * pageSize;
                            if (pageIndex < maxpage)
                            {
                                sb.AppendFormat("select top {0} {1} from (select top {2} {3} from {4} where {5} order by {6}) order by {6}",
                                    pageSize,columns,rang,columns,tableName, where,orderby);
                                                       
                            }
                            else
                            {
                                sb.AppendFormat("select top {0} {1} from (select top {2} {3} from {4}  where {5} order by {6}) order by {7}",
                                   lastcount, columns, lastcount, columns, tableName, where, orderby);
                            }
                            break;
                        case "oracle":
                            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
                            sb.AppendFormat(@"SELECT *
                                FROM (SELECT {0}, ROWNUM AS rowno
                                FROM (SELECT {0}
                                FROM {1}
                                WHERE {2} ORDER BY {3}) tt
                                WHERE ROWNUM <={5} ) table_alias
                                WHERE table_alias.rowno >= {4};", columns, tableName, where, orderby, skip, pageIndex * pageSize); ;//连接oracle
                            break;
                        case "mysql":
                            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
                            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", columns, tableName, where, orderby, skip, pageIndex * pageSize);
                            break;
                    }
                    IDataReader idr = ConnectionObject.ExecuteReader(sb.ToString(), _param);
                    DataSet dt = new DataSet();
                    dt.Load(idr,LoadOption.PreserveChanges, new String[] { "totolCount", "totolList" });
                    if (dt != null && dt.Tables != null && dt.Tables.Count > 0)
                    {
                        total = int.Parse(dt.Tables[0].Rows[0][0].ToString());
                        if (dt.Tables.Count > 1)
                        {
                            dtJieGuo = dt.Tables[1];
                        }
                    }
                    return dtJieGuo;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    CloseConnection();
                }
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="_object"></param>
        /// <returns></returns>
        public List<T> ExecuteQuerySQL<T>(string sql, object _object)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    var r = ConnectionObject.Query<T>(sql, _object);
                    return r.ToList<T>();
                }
                catch
                {
                    throw;
                }
                finally
                {
                    CloseConnection();
                }
            }
        }
        /// <summary>
        /// 把idatareader转换成datatable
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private DataTable IDataReaderToDataTable(IDataReader reader)
        {

            DataTable objDataTable = new DataTable();

            int intFieldCount = reader.FieldCount;
            for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
            {
                objDataTable.Columns.Add(reader.GetName(intCounter), typeof(string));
            }

            objDataTable.BeginLoadData();
            object[] objValues = new object[intFieldCount];


            while (reader.Read())
            {
                reader.GetValues(objValues);
                objDataTable.LoadDataRow(objValues, true);

            }
            reader.Close();
            objDataTable.EndLoadData();

            return objDataTable;
        }



        #region 2020-09-27 lzy 新加sql查询方法，主要是参数化查询

        private  static void PrepareCommand(SqlCommand sqlCommand, SqlParameter[] commandParms)
        {
            if (commandParms != null)
            {
                foreach (SqlParameter parameter in commandParms)
                {
                    if (parameter.Value == null)
                    {
                        parameter.Value = DBNull.Value;
                    }
                    sqlCommand.Parameters.Add(parameter);
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <param name="cmdParms">SqlParameter对象可以为空</param>
        /// <returns>DataSet</returns>
        public DataSet Query(string SQLString, params SqlParameter[] cmdParms)
        {
            //创建连接对象
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                //设置要运行的sql语句或存储过程
                cmd.CommandText = SQLString;
                //设置cmd的连接
                cmd.Connection = con;
                //设置cmd运行的命令的类型
                cmd.CommandType = CommandType.Text;
                //参数不为空的话，添加参数
                PrepareCommand(cmd, cmdParms);
                //if (cmdParms != null)
                //    cmd.Parameters.AddRange(cmdParms);
                con.Open();
                //创建数据适配器
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public  int ExecuteSqlTran(List<String> SQLStringList)
        {
            //if (sqlcon.State.ToString().ToUpper() == "CLOSED")
            //{
            //    sqlcon = GetNewConnection();
            //}
            using (SqlConnection sqlcon = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = sqlcon;
                sqlcon.Open();
                SqlTransaction tx = sqlcon.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    sqlcon.Close();
                    return count;
                }
                catch
                {
                    tx.Rollback();
                    return 0;
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回是否成功。
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="commandParm">SqlParameter对象可以为空</param>
        public int ExecuteNonQuery(string commandText, SqlParameter[] commandParm)
        {
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
                cmd.Connection = con;
                PrepareCommand(cmd, commandParm);
                //if (commandParm != null)
                //    cmd.Parameters.AddRange(commandParm);
                con.Open();
                // cmd.Parameters["@Info"].Direction = ParameterDirection.Output;
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
        }


        /// <summary>
        /// 执行存储过程，返回影响的行数		
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <param name="rowsAffected">影响的行数</param>
        /// <returns></returns>
        public  int RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }
        /// <summary>
        /// 创建 SqlCommand 对象实例(用来返回一个整数值)	
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand 对象实例</returns>
        private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }

        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        /// <summary>
        /// dapper通用分页方法
        /// </summary>
        /// <typeparam name="T">泛型集合实体类</typeparam>
        /// <param name="conn">数据库连接池连接对象</param>
        /// <param name="files">列</param>
        /// <param name="tableName">表</param>
        /// <param name="where">条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">当前页显示条数</param>
        /// <param name="total">结果集总数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetPageList<T>(IDbConnection conn, string files, string tableName, string where, string orderby, int pageIndex, int pageSize, out int total)
        {
            int skip = 1;
            if (pageIndex > 0)
            {
                skip = (pageIndex - 1) * pageSize + 1;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("SELECT COUNT(1) FROM {0} where {1};", tableName, where);
            sb.AppendFormat(@"SELECT  {0}
                                FROM(SELECT ROW_NUMBER() OVER(ORDER BY {3}) AS RowNum,{0}
                                          FROM  {1}
                                          WHERE {2}
                                        ) AS result
                                WHERE  RowNum >= {4}   AND RowNum <= {5}
                                ORDER BY {3}", files, tableName, where, orderby, skip, pageIndex * pageSize);
            using (var reader = conn.QueryMultiple(sb.ToString()))
            {
                total = reader.ReadFirst<int>();
                return reader.Read<T>();
            }
        }


        #endregion

    }
}