Web_HotelServices_Prod/DB_Server/DbHelperSQL.cs

using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using UtilsSharp;
using Newtonsoft.Json;
using System.Data.Common;
using System.Diagnostics;
using COMMON;
using MySql.Data.MySqlClient;

namespace DB_Server
{
    public class DbHelperSQL
    {
        public DbHelperSQL(DBSel type)
        {
            DBType = type;
            connectionString = type == DBSel.BLV_RCU_DB ?
                ConfigEntity.Instance.Connection : (type == DBSel.DEBUGGER_DB ?
                ConfigEntity.Instance.DEBUGGER_DB : ConfigEntity.Instance.Connection_log);
        }

        public enum DBSel
        {
            /// <summary>
            /// 日志库 
            /// </summary>
            LogDB,
            /// <summary>
            /// 主要数据库
            /// </summary>
            BLV_RCU_DB,
            /// <summary>
            /// DEBUG 巫工 调试信息数据库
            /// </summary>
            DEBUGGER_DB

        }
        private DBSel DBType { get; set; }

        private string connectionString { get; set; }
        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString)
        {
             
            if (ConfigEntity.Instance.DBTYPE == 1 || DBType == DBSel.DEBUGGER_DB)
                return SQLSERVER_Helper.ExecuteSql(connectionString,SQLString);
            else
                return MYSQL_Helper.ExecuteSql(connectionString, SQLString);
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        cmd.Parameters.Add(cmdParms);
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }

        }

        /// <summary>
        /// DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="par"></param>
        /// <returns></returns>
        public DataTable ExecuteTable(string sql, params SqlParameter[] par)
        {
            
            try
            {
            if (ConfigEntity.Instance.DBTYPE == 2 && DBType != DBSel.DEBUGGER_DB)
                    return MYSQL_Helper.GetDatatable(this.connectionString, sql, DBLib.ConvertParameters<MySqlParameter>(par));
            else
                return SQLSERVER_Helper.GetDatatable(this.connectionString, sql, par);
            }
            finally 
            {
               
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="keys"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>

        public List<T> GetData<T>(string[,] keys, string TableName, int page, int length, out int COUNT, out int filtcount, string bysql = "",int sumhc = 1) where T : new()
        {
            
            COUNT = 0;
            filtcount = 0;
            string sql = string.Empty;
            List<SqlParameter> sqlparment = new List<SqlParameter>();
            
            var RDkey_filtcount = TableName;

            if (keys != null) {
            for (int i = 0; i < keys.GetLength(0); i++)
            {
                var data = keys[i, 2];
                var key = keys[i, 0];
                var moh = keys[i, 1];
                var BL = keys[i, 3];

                if (!string.IsNullOrEmpty(data))
                {
                        RDkey_filtcount += key;
                        RDkey_filtcount += moh;
                        RDkey_filtcount += BL;
                        RDkey_filtcount += data;
                        if (sql != string.Empty) { sql += " AND "; }
                    sql += $"({key} {moh} @{BL})";
                    sqlparment.Add(new SqlParameter { Value = moh.ToUpper().Trim() == "LIKE" ? $"%{data}%" : data, ParameterName = $"@{BL}" });
                }
            }
            }
            string sqlCONE = sql;

            if (ConfigEntity.Instance.DBTYPE == 1 || DBType == DBSel.DEBUGGER_DB)
            {
                //这种SQLSERVER sql  速度快 
                COUNT = XC_Redis.Redis.GET(RDkey_filtcount, () =>
                    {
                        return int.Parse(ExecuteTable($"SELECT  MAX(b.rows) FROM sys.sysobjects AS a INNER JOIN sys.sysindexes AS b ON a.id = b.id WHERE(a.xtype = 'u') and a.[NAME] = '{TableName}' ").Rows[0][0].ToString());
                    }, 2);
                
                sql = @$" SELECT TOP {length} * FROM
                (
                SELECT ROW_NUMBER() OVER({bysql}) AS RowNumber, * FROM [{TableName}]  {(sql != string.Empty ? $" where {sql} " : "")}
                ) as A
                WHERE RowNumber > {length * (page - 1)}";
            }
            else
            { 
                COUNT = XC_Redis.Redis.GET(RDkey_filtcount, () =>
                {
                    return int.Parse(ExecuteTable($"SELECT  count(1) FROM {TableName} ").Rows[0][0].ToString());
                }, 2);
                sql = $"SELECT * FROM {TableName} {(sql != string.Empty ? $" where {sql} " : "")}  {bysql}  limit {length * (page - 1)},{length}";
            }
            // 如果没有过滤就 会读取表的数量 不会查询  
            filtcount = XC_Redis.Redis.GET(RDkey_filtcount, () =>
            {
                return int.Parse(ExecuteTable($"select COUNT(1) from {TableName} {(sqlCONE != string.Empty ? $" where {sqlCONE} " : "")}", (SqlParameter[])sqlparment.ToArray().Clone()).Rows[0][0].ToString());
            }, sumhc);
                
            return DataTableHelper.ToEntities<T>(ExecuteTable(sql, sqlparment.ToArray()));
        }
    }
}