// ===================================================================================
//  MongoSqlClient  C# 5.0 示(shi)例程序
// ===================================================================================

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;
using log4net;
using log4net.Core;
using log4net.Appender;
using log4net.Layout;
using log4net.Config;
using log4net.Repository.Hierarchy;


namespace MongoSql.Demo
{
    internal class Program
    {
        private static string table="mongosql_table";

        private static void Main(string[] args)
        {
            /* 1. 設(she)置日志輸出(chu)到(dao)控制臺 */
            //BasicConfigurator.Configure();
            
            /* 2. 設置(zhi)日志輸出到Log.txt */
            // 獲取當前日志庫的根日志器
            var hierarchy = (Hierarchy)LogManager.GetRepository();    
            // 創(chuang)建一個文件(jian)Appender
            var fileAppender = new FileAppender
            {
                File = "Logs/Log.txt", // 指定(ding)日志文件路徑
                AppendToFile = true,  // 是否追(zhui)加(jia)到(dao)文(wen)件
                Layout = new PatternLayout("%date [%thread] %-5level %logger - %message%newline"), // 日志格式
                Threshold = Level.Info, // 設置日志級別為 INFO
                Encoding = Encoding.UTF8 // 設置文件編碼為 UTF-8
            };
            fileAppender.ActivateOptions(); // 激活Appender    
            // 將文(wen)件Appender添(tian)加到根日志器(qi)
            hierarchy.Root.AddAppender(fileAppender);    
            // 設置根日志器的級別(bie)為 INFO
            hierarchy.Root.Level = Level.Info;

            /* 3. 自動初始化(hua)一(yi)些(xie)數(shu)據（不硬編碼） */
            InitDemoData();
            

            /* 4. 鍵(jian)盤(pan)交互(hu)菜(cai)單 */
            string cmd;
            Console.WriteLine("=== MongoSql 交互演示(shi) ===");
            while (true)
            {
                Console.WriteLine("\n【1】查詢  【2】插入  【3】更新(xin)  【4】刪除(chu)  【5】統(tong)計(ji) 【6】查詢單行單列 【7】查詢單列 【0】退出");
                Console.Write("請輸入操作編(bian)號：");
                cmd = Console.ReadLine();
                try
                {
                    switch (cmd)
                    {
                        case "1": Query(); break;
                        case "2": Insert(); break;
                        case "3": Update(); break;
                        case "4": Delete(); break;
                        case "5": Count(); break;
                        case "6": GetColValueSingle(); break;
                        case "7": GetColValueMulti(); break;                        
                        case "0": return;
                        default: Console.WriteLine("無效編號(hao)"); break;
                    }
                }
                catch (MongoSqlException mex)
                {
                    Console.WriteLine("?? SQL 錯誤：" + mex.Message);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("?? 異常：" + ex.Message);
                }
            }
        }

        #region 初始化演示數據（零硬編碼）
        private static void InitDemoData()
        {
            Console.Write("首(shou)次運行，是否插入演(yan)示數據？(y/n 默認y)：");
            if (Console.ReadLine().ToLower() == "n") return;

            /* 完(wan)全(quan)動(dong)態字段(duan)，實體僅做文(wen)檔 */
            var sql = string.Format(
                "INSERT INTO {0}(Name,Price,Stock,CreateTime) VALUES " +
                "('鍵盤',99,200,'{1}')," +
                "('鼠(shu)標',59,150,'{1}')," +
                "('顯示器',999,30,'{1}')",
                table, DateTime.Now.ToString("yyyy-MM-dd"));

            int n = MongoSqlHelper.Insert(sql);
            Console.WriteLine("已插入(ru) {0} 條演(yan)示數據(ju)", n);
        }
        #endregion

        #region 各操作（均讀鍵盤，零硬編碼）
        private static void Query()
        {
            Console.Write("請輸入 WHERE 子句（直接回車=全表）：");
            string where = Console.ReadLine();
            Console.Write("請輸入 ORDER BY 子句（直接(jie)回車=不排序）：");
            string order = Console.ReadLine();
            Console.Write("請(qing)輸入 TOP 數量（直接(jie)回車=不限）：");
            string topStr = Console.ReadLine();
            int? top = null;
            if (!string.IsNullOrWhiteSpace(topStr)) top = int.Parse(topStr);

            /* 動態拼接 SELECT */
            string sql = string.Format("SELECT {0} * FROM {1} {2} {3}",
                                       top.HasValue ? "TOP " + top.Value : "",
                                       table,
                                       string.IsNullOrWhiteSpace(where) ? "" : "WHERE " + where,
                                       string.IsNullOrWhiteSpace(order) ? "" : "ORDER BY " + order);

            DataTable dt = MongoSqlHelper.Select(sql);
            if (dt.Rows.Count == 0)
            {
                Console.WriteLine("（無數據）");
                return;
            }

            /* 動態打印(yin)表頭、行 */
            foreach (DataColumn c in dt.Columns) Console.Write(c.ColumnName + "\t");
            Console.WriteLine();
            foreach (DataRow r in dt.Rows)
            {
                foreach (DataColumn c in dt.Columns) Console.Write(r[c] + "\t");
                Console.WriteLine();
            }
        }

        private static void Insert()
        {
            /* 支持動態列數 */
            Console.Write("請輸入列名(ming)，用逗號分隔（例：Name,Price,Stock）：");
            string cols = Console.ReadLine();
            Console.Write("請輸入 VALUES 子句（例：('鍵盤',99,200),('鼠標',59,150)）：");
            string vals = Console.ReadLine();

            string sql = string.Format("INSERT INTO {0}({1}) VALUES {2}",
                                       table, cols, vals);
            int n = MongoSqlHelper.Insert(sql);
            Console.WriteLine("成功插入(ru) {0} 條", n);
        }

        private static void Update()
        {
            Console.Write("請(qing)輸入 SET 子(zi)句（例：Price=88,Stock=300）：");
            string set = Console.ReadLine();
            Console.Write("請輸入 WHERE 子句（例：Name='鍵盤'）：");
            string where = Console.ReadLine();

            string sql = string.Format("UPDATE {0} SET {1} WHERE {2}",
                                       table, set, where);
            int n = MongoSqlHelper.Update(sql);
            Console.WriteLine("成功更(geng)新 {0} 條", n);
        }

        private static void Delete()
        {
            Console.Write("請輸入 WHERE 子(zi)句（例：Price<60）：");
            string where = Console.ReadLine();

            string sql = string.Format("DELETE FROM {0} WHERE {1}", table, where);
            int n = MongoSqlHelper.Delete(sql);
            Console.WriteLine("成功刪除 {0} 條", n);
        }

        private static void Count()
        {
            Console.Write("請輸入 WHERE 子句（直接回車=全表）：");
            string where = Console.ReadLine();
            string sql = string.Format("SELECT COUNT(*) FROM {0} {1}",
                                       table,
                                       string.IsNullOrWhiteSpace(where) ? "" : "WHERE " + where);
            long c = MongoSqlHelper.Count(sql);
            Console.WriteLine("滿足條(tiao)件記錄數：" + c);
        }
        
        private static void GetColValueSingle()
        {
            
            /* 列 */
            Console.Write("請(qing)輸入列(lie)名，用逗(dou)號分隔（例(li)：Name,Price,Stock）：");
            string col = Console.ReadLine();
            Console.Write("請輸入(ru) WHERE 子句（直接回車=取(qu)第(di)一行(xing)）：");
            string where = Console.ReadLine();
            string sql = string.Format("SELECT {0} FROM  {1} {2}",
                                       col, table,
                                       string.IsNullOrWhiteSpace(where) ? "" : "WHERE " + where);
            var obj = MongoSqlHelper.ExecuteGetSingle(sql);
            Console.WriteLine("獲(huo)取(qu)到字段{0}值 {1} ", col,obj);
        }
        
        private static void GetColValueMulti()
        {
                        /* 列 */
            Console.Write("請輸入列(lie)名，用(yong)逗號分(fen)隔（例：Name,Price,Stock）：");
            string col = Console.ReadLine();
            Console.Write("請輸入 WHERE 子句（直(zhi)接回車=全表）：");
            string where = Console.ReadLine();            
            string sql = string.Format("SELECT {0} FROM  {1} {2}",
                                       col, table,
                                       string.IsNullOrWhiteSpace(where) ? "" : "WHERE " + where);
            List<object> dlists = MongoSqlHelper.ExecuteGetList(sql);
            foreach (var val in dlists)
            {
                Console.Write(val + "\t");
                Console.WriteLine();
            }
        }
                
        #endregion
    }
}

// ===================================================================================
//  MongoSqlClient  C# 5.0 兼容完(wan)整版 
//  封裝類：MongoSqlHelper
//  說明：數據(ju)庫連接參數讀(du)取config文(wen)件
//  依賴：log4.net
// ===================================================================================

using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Configuration;
using log4net;


/* 相關版本(ben)(ben)(ben)：MongoDB 驅(qu)(qu)動(dong) 2.11.5 + MongoDB 3.6、4.2 + C# 5.0 + .NET 4.5.2  
 
 一、已支(zhi)持(chi)（?）—— 可直(zhi)接抄，3.6 保證能跑
  1. 等值/范圍/IN 過(guo)濾(lv)
     SELECT * FROM tbl WHERE status=1 AND price>100 AND city IN ('bj','sh')
  2. TOP 
     SELECT TOP 50 * FROM tbl WHERE uid=1 ORDER BY createTime DESC
  3. COUNT / SUM / MAX / MIN / AVG
     SELECT COUNT(*) FROM tbl WHERE uid=1
     SELECT SUM(amount) AS total FROM tbl WHERE uid=1
     *****只允(yun)許(xu)單(dan)個查(cha)詢，不允(yun)許(xu)同(tong)(tong)時(shi)查(cha)count 和max，要同(tong)(tong)時(shi)查(cha)多個聚(ju)合，請分別提交，譬如(ru)：****
     "SELECT COUNT(*),MAX(price) FROM tbl"   --- 不支(zhi)持(chi)
  4. DISTINCT
     SELECT DISTINCT uid FROM tbl
  5. 一次(ci)插入多行
     INSERT INTO tbl(a,b) VALUES (1,'a'),(2,'b')
  7. 參數化寫法（僅(jin)支(zhi)持(chi)“拼好字(zi)(zi)符(fu)串”模式，不再傳字(zi)(zi)典）：
     string sql = "SELECT * FROM tbl WHERE uid=1 AND status=2";   // 直(zhi)接拼
     DataTable dt = MongoSqlHelper.Select(sql);

 二、不支(zhi)持(chi)（?）—— 3.6 根本(ben)(ben)(ben)沒(mei)有，永遠跑不通
  · JOIN / 子查(cha)詢 / UNION / 事(shi)務 / HAVING / 行鎖
  · 表達式索引(yin)（如(ru) $toInt:field）—— 3.6 不支(zhi)持(chi)，必須預存(cun)(cun)字(zi)(zi)段(duan)(duan)再建索引(yin)
  · OFFSET / SKIP + LIMIT 組(zu)合（驅(qu)(qu)動(dong)支(zhi)持(chi)，但(dan)本(ben)(ben)(ben)類未暴露 SKIP）
  · 視圖、存(cun)(cun)儲(chu)過(guo)程、觸(chu)發器
  . CAST（驅(qu)(qu)動(dong) 2.11.5 自動(dong)用(yong) $toInt/$toDate，但(dan) 3.6 無表達式索引(yin)，僅(jin)聚(ju)合場(chang)景,）
     SELECT CAST(price AS INT) AS priceInt FROM tbl ORDER BY CAST(price AS INT) ASC
     —— 會(hui)走(zou)聚(ju)合管道，**無索引(yin)**，大表慎用(yong)，所以也取消支(zhi)持(chi)。

 三(san)、能跑但(dan)會(hui)全(quan)表掃（??）—— 數據量大時(shi)禁止
  · LIKE '%xx%'   →  MongoDB $regex，3.6 不支(zhi)持(chi)索引(yin)
  · 對 CAST/計算字(zi)(zi)段(duan)(duan)排(pai)序/過(guo)濾(lv)   →  走(zou)聚(ju)合，無索引(yin)

 四、版本(ben)(ben)(ben)紅線（??）
  · 驅(qu)(qu)動(dong) 2.11.5 最低(di)要求(qiu) MongoDB 2.6，已測試 3.6+
  · .NET Framework 不得低(di)于(yu)(yu) 4.5.2（驅(qu)(qu)動(dong)硬性(xing)要求(qiu)）
  · C# 5.0 無 await/async，本(ben)(ben)(ben)類全(quan)部(bu)同(tong)(tong)步(bu)接口，無異步(bu)版本(ben)(ben)(ben)

 五、性(xing)能錦囊
  1. 凡是用(yong)于(yu)(yu) WHERE / ORDER BY 的字(zi)(zi)段(duan)(duan)，**務必預存(cun)(cun)為純類型**并(bing)建索引(yin)：
        db.col.updateMany({},[{$set:{priceInt:{$toInt:"$price"}}}])
        db.col.createIndex({priceInt:1})
  2. 日志級別調至(zhi) DEBUG 可在 log4net 中(zhong)看到 Compass 可直(zhi)接粘貼(tie)的執行腳本(ben)(ben)(ben)
  3. 內存(cun)(cun)緩存(cun)(cun)限(xian)額在 app.config 配(pei)置（已加 MongoSql 100 MB）
 */
namespace MongoSql.Demo
{
    public static class MongoSqlHelper
    {
        private static readonly MongoSqlClient Client;
        private static readonly ILog logger = LogManager.GetLogger(typeof(MongoSqlHelper));
    
        static MongoSqlHelper()
        {
            try
            {
                string conn = ConfigurationManager.AppSettings["MongoConn"];
                string db   = ConfigurationManager.AppSettings["MongoDb"];
                if (string.IsNullOrEmpty(conn) || string.IsNullOrEmpty(db))
                    throw new Exception("AppSettings 缺少(shao) MongoConn 或 MongoDb");
    

                Client = new MongoSqlClient(conn, db,
                          new List<string> {
                              "table1",
                              "table2",
                              "mongosql_table"
                          });
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex,"MongoSqlClient 初始化失敗...");
            }
        }
    
        #region 通用日志模板
        private static void LogEnter([System.Runtime.CompilerServices.CallerMemberName] string method = "",
                                     string sql = "")
        {
            logger.Info("[執行sql開始]" + method + " || SQL: " + sql);
        }
    
        private static void LogExit(string method, object ret)
        {
            logger.Info("[執(zhi)行(xing)sql結束(shu)]" + method + " || Return: " + (ret ?? "null"));
        }
        #endregion
        
        #region ------- SQL 語法安檢（C# 5.0 合并版） -------
        // 一站(zhan)式(shi)預檢：非法(fa)字符、永不(bu)支持語法(fa)、CAST+ORDER BY、LIKE 全表掃 全部處理。
        // 正常引號允許出現，譬如：Name="abc'
        // 配置項可關閉 CAST 排(pai)序(xu)攔截。
        private static void CheckSql(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql))
                throw new Exception("SQL 為空");
        
            string upper = sql.ToUpper().Trim();
        
            /* 1. 危險字符(fu)（控制符(fu)、分號、反斜杠）*/
            if (Regex.IsMatch(sql, @"[\x00-\x08\x0B-\x0C\x0E-\x1F;\\]"))
                throw new Exception("SQL 含非法(fa)字符（控制符、分號(hao)、反(fan)斜杠）");
        
            /* 2. 永不支持的語法(fa)——整(zheng)詞匹(pi)配，防(fang)止(zhi)字段名誤殺(sha) */
            string[] never = { "JOIN", "INNER JOIN", "LEFT JOIN", "RIGHT JOIN", "FULL JOIN",
                               "HAVING", "UNION", "TRANSACTION", "BEGIN", "ROLLBACK", "COMMIT",
                               "CREATE", "DROP", "ALTER", "GRANT", "REVOKE","LIMIT" };
            foreach (string kw in never)
                if (Regex.IsMatch(upper, @"\b" + Regex.Escape(kw) + @"\b", RegexOptions.IgnoreCase))
                    throw new Exception("SQL 含不(bu)支持的語法：" + kw);
        
            /* 3. CAST + ORDER BY —— 直接拒（可配置關閉(bi)）*/
            if ( Regex.IsMatch(upper, @"\bCAST\s*\(") &&
                Regex.IsMatch(upper, @"\bORDER\s+BY\b"))
                throw new Exception("CAST+ORDER BY 無索引，已全局(ju)禁(jin)止（配置 ForbidCastSort=false 可(ke)放行(xing)）");
        
            /* 4. 性能陷(xian)阱——禁止 */
            if (Regex.IsMatch(sql, @"\bLIKE\s+'%[^']*%'", RegexOptions.IgnoreCase))
                throw new Exception("SQL 含 LIKE '%xx%'，會全表掃描，已禁止(zhi)");
            
            /* 5. 聚合(he)函數(shu)數(shu)量——只允許 1 個 */
            int aggCount = 0;
            string[] aggs = { "COUNT", "SUM", "MAX", "MIN", "AVG" };
            foreach (string a in aggs)
                aggCount += Regex.Matches(upper, @"\b" + a + @"\b", RegexOptions.IgnoreCase).Count;
            
            if (aggCount > 1)
                throw new Exception("一條(tiao) SQL 只(zhi)允許(xu) 1 個聚合函數(shu)（COUNT/SUM/MAX/MIN/AVG），請分(fen)別提交(jiao)");
        }
        #endregion
    
    
        //<summary>
        //查詢返回(hui) DataTable
        //完整示例：
        //DataTable dt = MongoSqlHelper.Select("SELECT pkid,col1,col2 FROM table1 WHERE pkid=1 ORDER BY pkid ASC");
        //</summary>
        public static DataTable Select(string sql)
        {
            CheckSql(sql);
            LogEnter("Select", sql);
            DataTable dt = Client.ExecuteDataTable(sql, null);
            if (dt == null || dt.Rows.Count == 0)
                logger.Warn("Select 返回(hui)空結果集");
            LogExit("Select", dt.Rows.Count + " 行");
            return dt;
        }
    
        //<summary>
        //插入(ru)
        //完整示例(li)：
        //int rows = MongoSqlHelper.Insert("INSERT INTO table1(pkid,col1,col2) VALUES (1,'test','test2')");
        //</summary>
        public static int Insert(string sql)
        {
            CheckSql(sql);
            LogEnter("Insert", sql);
            int rows = Client.ExecuteInsert(sql);
            LogExit("Insert", rows + " 行插入");
            return rows;
        }
    
        //<summary>
        //更新
        //完整(zheng)示例：
        //int rows = MongoSqlHelper.Update("UPDATE table1 SET col1='new name' WHERE pkid=1");
        //</summary>        
        public static int Update(string sql)
        {
            CheckSql(sql);
            LogEnter("Update", sql);
            int rows = Client.ExecuteUpdate(sql, null);
            LogExit("Update", rows + " 行(xing)更(geng)新(xin)");
            return rows;
        }
    
        //<summary>
        //刪除
        //完整示例：
        //int rows = MongoSqlHelper.Delete("DELETE FROM table WHERE pkid=1");
        //</summary>
        public static int Delete(string sql)
        {
            CheckSql(sql);
            LogEnter("Delete", sql);
            int rows = Client.ExecuteDelete(sql, null);
            LogExit("Delete", rows + " 行刪除(chu)");
            return rows;
        }
    
        //<summary>
        //計數(shu)
        //完整示例：
        //int cnt = MongoSqlHelper.Count("SELECT COUNT(*) FROM table1 WHERE pkid=1");
        //</summary>
        public static int Count(string sql)
        {
            CheckSql(sql);
            LogEnter("Count", sql);
            int cnt = Client.ExecuteCount(sql, null);
            LogExit("Count", cnt);
            return cnt;
        }
    
        //<summary>
        //單行單列
        //完(wan)整示例：
        //object name = MongoSqlHelper.ExecuteGetSingle("SELECT col1 FROM table1 WHERE pkid=1");
        //</summary>
        public static object ExecuteGetSingle(string sql)
        {
            CheckSql(sql);
            LogEnter("ExecuteGetSingle", sql);
            object val = Client.ExecuteGetSingle(sql, null);
            LogExit("ExecuteGetSingle", val);
            return val;
        }
    
        //<summary>
        //多行(xing)單列
        //完(wan)整示(shi)例：
        //List<object> list = MongoSqlHelper.ExecuteGetList("SELECT distinct pkid FROM table1 WHERE col2='test2' ");
        //</summary>
        public static List<object> ExecuteGetList(string sql)
        {
            CheckSql(sql);
            LogEnter("ExecuteGetList", sql);
            List<object> list = Client.ExecuteGetList(sql, null);
            LogExit("ExecuteGetList", list == null ? " 0 條" : list.Count + " 條");
            return list;
        }
    
        #region 標準 Scalar 兼容（C# 5.0）
        //<summary>
        //第(di)一行第(di)一列(lie)（DBNull → null）
        //完整示例：
        //object cnt = MongoSqlHelper.ExecuteScalar("SELECT COUNT(*) FROM table1 WHERE col1='test1' ");
        //</summary>
        public static object ExecuteScalar(string sql)
        {
            CheckSql(sql);
            LogEnter("ExecuteScalar", sql);
            DataTable dt = Select(sql);
            object val = (dt.Rows.Count == 0 || dt.Columns.Count == 0) ? null : dt.Rows[0][0];
            val = val == DBNull.Value ? null : val;
            LogExit("ExecuteScalar", val);
            return val;
        }
    
        //<summary>
        //泛型 Scalar
        //完整示例：
        //int cnt = MongoSqlHelper.ExecuteScalar<int>("SELECT COUNT(*) FROM table1 WHERE col1='test1' ");
        //</summary>
        public static T ExecuteScalar<T>(string sql)
        {
            CheckSql(sql);
            object raw = ExecuteScalar(sql);
            if (raw == null || raw == DBNull.Value)
                return default(T);
    
            Type u = Nullable.GetUnderlyingType(typeof(T));
            return u != null
                ? (T)Convert.ChangeType(raw, u)
                : (T)Convert.ChangeType(raw, typeof(T));
        }
        #endregion
    }
}

// ===================================================================================
//  MongoSqlClient  C# 5.0 兼容(rong)完整版  
//  基礎類：MongoSqlClient  
//  說明：  含 Compass 可直接(jie)粘(zhan)貼的執行腳本日(ri)志（參(can)數化(hua)脫敏）
//  依賴：MongoDB.Bson ,MongoDB.Driver,MongoDB.Driver.Core,Newtonsoft.Json,log4.net
// ===================================================================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Runtime.Caching;
using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Bson.Serialization;
using Newtonsoft.Json;
using log4net;

namespace MongoSql.Demo
{
    public sealed class MongoSqlClient : IDisposable
    {
        #region ── 私有字段 ──
        private readonly IMongoDatabase _db;
        private HashSet<string> _allowedTables;
        private static List<string> _collectionNames;
        private static readonly object _cacheLock = new object();
        private static DateTime _cacheExpires = DateTime.MinValue;
        private static readonly IBsonSerializer<BsonDocument> _docSerializer =
            BsonSerializer.SerializerRegistry.GetSerializer<BsonDocument>();

        private bool _disposed;
        private static readonly MemoryCache _parseCache = new MemoryCache("MongoSql");
        private static readonly ILog logger = LogManager.GetLogger(typeof(MongoSqlClient));
        #endregion

        #region ── 構造 / 釋放 ──
        public MongoSqlClient(string connStr, string dbName, IEnumerable<string> allowedTables = null)
        {
            if (connStr == null) LogAndException.Throw(new ArgumentNullException("connStr"));
            if (dbName == null) LogAndException.Throw(new ArgumentNullException("dbName"));

            _db = new MongoClient(connStr).GetDatabase(dbName);

            _allowedTables = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
                "table1",
                "table2"
            };

            if (allowedTables != null)
                _allowedTables = new HashSet<string>(allowedTables, StringComparer.OrdinalIgnoreCase);

            logger.Debug("表白名單(dan)：" + string.Join(", ", _allowedTables));
        }

        public void Dispose()
        {
            if (!_disposed) _disposed = true;
        }
        #endregion

        #region ── 表名校驗 ──
        private void CheckTable(string table)
        {
            if (!_allowedTables.Contains(table))
                LogAndException.Throw(new Exception(string.Format("表 [{0}] 不在白名單內", table)));

            EnsureCollectionNamesUpToDate();
            if (!_collectionNames.Contains(table))
                //LogAndException.Throw(new Exception(string.Format("表 [{0}] 不存在于數(shu)據庫中", table)));
                logger.Debug(string.Format("表 [{0}] 不存在于數據庫中", table));
        }

        private void EnsureCollectionNamesUpToDate()
        {
            lock (_cacheLock)
            {
                if (DateTime.UtcNow >= _cacheExpires)
                {
                    _collectionNames = _db.ListCollectionNames().ToList();
                    _cacheExpires = DateTime.UtcNow.AddHours(1);
                }
            }
        }
        #endregion

        #region ── 1. 查詢 => DataTable ──
        public DataTable ExecuteDataTable(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteDataTable入口 sql=" + sql);
        
            try
            {
                var qi = CachedParse(sql, ParseSelect);
                CheckTable(qi.Table);
        
                if (qi.IsExists || qi.SubQuerySql != null)
                    return ExecuteExists(qi, parameters);
        
                if (qi.IsGroupBy)
                    return ExecuteGroupBy(qi, parameters);
        
                var coll = _db.GetCollection<BsonDocument>(qi.Table);
                var filter = BuildFilterAuto(qi.Where, parameters);
        
                /* ===== DISTINCT 去重(zhong) 分支 ===== */
                if (qi.IsDistinct)
                {
                    var idDoc = new BsonDocument();
                    if (qi.Fields != null && qi.Fields.Length > 0)
                    {
                        foreach (var f in qi.Fields)
                            idDoc[f] = "$" + f;
                    }
                    else
                    {
                        idDoc["$$ROOT"] = "$$ROOT";
                    }
        
                    var pipeline = new List<BsonDocument>();
                    if (filter != FilterDefinition<BsonDocument>.Empty)
                        pipeline.Add(new BsonDocument("$match", filter.ToBsonDocument()));
                    pipeline.Add(new BsonDocument("$group", new BsonDocument("_id", idDoc)));
                    pipeline.Add(new BsonDocument("$replaceRoot", new BsonDocument("newRoot", "$_id")));
        
                    var docs = coll.Aggregate<BsonDocument>(pipeline.ToArray()).ToList();
        
                    // 打印(yin) Compass 可執行腳(jiao)本
                    string pipelineJson = "[" + string.Join(", ", pipeline.Select(p => p.ToJson())) + "]";
                    logger.Info("ExecuteDataTable DISTINCT 腳本（可直接粘貼 Compass）:\r\n" +
                                 string.Format("db.{0}.aggregate({1})", qi.Table, pipelineJson));
        
                    logger.Debug("ExecuteDataTable DISTINCT 結果行(xing)數=" + docs.Count);
                    return ToDataTable(docs);
                }
                /* ============================== */
        
                var find = coll.Find(filter);
        
                bool isStar = qi.Fields != null && qi.Fields.Length == 1 && qi.Fields[0] == "*";
                if (isStar)
                    find = find.Project(Builders<BsonDocument>.Projection.Exclude("_noSuchField"));
                else if (qi.Fields != null && qi.Fields.Length > 0)
                {
                    var proj = Builders<BsonDocument>.Projection;
                    ProjectionDefinition<BsonDocument> p = null;
                    foreach (var f in qi.Fields)
                        p = p == null ? proj.Include(f) : p.Include(f);
                    find = find.Project(p);
                }
        
                // 排序
                BsonDocument sortDoc = null;
                if (!string.IsNullOrEmpty(qi.OrderBy))
                {
                    sortDoc = BuildSortAuto(qi.OrderBy);
                    find = find.Sort(sortDoc);
                }
                
                //支持top語(yu)法，不支持limit        
                if (qi.Top.HasValue && qi.Top.Value > 0)
                    find = find.Limit(qi.Top.Value);
                
                // 支持skip
                if (qi.Skip.HasValue && qi.Skip.Value > 0)
                    find = find.Skip(qi.Skip.Value);
        
                // 構造(zao) Compass 日志
                string projectionClause;
                if (isStar || (qi.Fields == null || qi.Fields.Length == 0))
                    projectionClause = "{}";
                else
                    projectionClause = "{ " + string.Join(", ", qi.Fields.Select(f => "\"" + f + "\": 1")) + " }";
        
                string sortClause = sortDoc == null ? "" : ".sort(" + sortDoc.ToJson() + ")";
                string limitClause = qi.Top.HasValue ? ".limit(" + qi.Top.Value + ")" : "";
        
                logger.Info("ExecuteDataTable 腳本（可直接粘貼 Compass）:\r\n" +
                             string.Format("db.{0}.find({1}, {2}){3}{4}",
                                           qi.Table,
                                           filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToJson(),
                                           projectionClause,
                                           sortClause,
                                           limitClause));
        
                var list = find.ToList();
                logger.Debug("ExecuteDataTable 返回Table行數=" + list.Count);
                return ToDataTable(list);
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return null; // 永遠(yuan)走不(bu)到
            }
        }
        #endregion      
        
        #region ── 2. 插入 ──
        public int ExecuteInsert(string sql)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteInsert sql=" + sql);

            try
            {
                var ii = CachedParse(sql, ParseInsertAuto);
                CheckTable(ii.Table);

                var docs = ii.Values.Select(row =>
                {
                    var doc = new BsonDocument();
                    for (int i = 0; i < ii.Columns.Count; i++)
                        doc[ii.Columns[i]] = BsonValue.Create(row[i]);
                    return doc;
                }).ToList();

                // 【補】打印 Compass 可執行腳(jiao)本（參(can)數化(hua)脫敏）
                string docsJson = "[" + string.Join(", ", docs.Select(d => d.ToJson())) + "]";
                logger.Info("ExecuteInsert 腳本（可直接(jie)粘貼 Compass）:\r\n" +
                             string.Format("db.{0}.insertMany({1})", ii.Table, docsJson));

                _db.GetCollection<BsonDocument>(ii.Table).InsertMany(docs);
                logger.Debug("返回ExecuteInsert 插(cha)入(ru)行數(shu)=" + docs.Count);
                return docs.Count;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return 0;
            }
        }
        #endregion

        #region ── 3. 更新 ──
        public int ExecuteUpdate(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteUpdate sql=" + sql);
            try
            {
                var ui = CachedParse(sql, ParseUpdateAuto);
                CheckTable(ui.Table);

                var coll = _db.GetCollection<BsonDocument>(ui.Table);
                var filter = BuildFilterAuto(ui.Where, parameters);
                var upd = Builders<BsonDocument>.Update;
                var sets = ui.SetList.Select(kv => upd.Set(kv.Key, BsonValue.Create(kv.Value))).ToList();

                // 【補】打(da)印(yin) Compass 可執(zhi)行(xing)腳(jiao)本(ben)
                string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                string updateJson = upd.Combine(sets).Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                logger.Info("ExecuteUpdate 腳本(ben)（可直接(jie)粘(zhan)貼 Compass）:\r\n" +
                             string.Format("db.{0}.updateMany({1}, {2})",
                                           ui.Table, filterJson, updateJson));

                var result = coll.UpdateMany(filter, upd.Combine(sets));
                logger.Debug("返回ExecuteUpdate 修改(gai)行數(shu)=" + result.ModifiedCount);
                return (int)result.ModifiedCount;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return 0;
            }
        }
        #endregion

        #region ── 4. 刪除 ──
        public int ExecuteDelete(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteDelete sql=" + sql);
            try
            {
                var di = CachedParse(sql, ParseDeleteAuto);
                CheckTable(di.Table);

                var coll = _db.GetCollection<BsonDocument>(di.Table);
                var filter = BuildFilterAuto(di.Where, parameters);

                // 【補】打印 Compass 可(ke)執行腳本
                string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                logger.Info("ExecuteDelete 腳本（可直接粘貼 Compass）:\r\n" +
                             string.Format("db.{0}.deleteMany({1})", di.Table, filterJson));

                var cnt = (int)coll.DeleteMany(filter).DeletedCount;
                logger.Debug("返回ExecuteDelete 刪除行(xing)數=" + cnt);
                return cnt;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return 0;
            }
        }
        #endregion

        #region ── 5. 統計 / 標量 ──
        public int ExecuteCount(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteCount sql=" + sql);
            try
            {
                var qi = CachedParse(sql, ParseSelect);
                CheckTable(qi.Table);

                var coll = _db.GetCollection<BsonDocument>(qi.Table);
                var filter = BuildFilterAuto(qi.Where, parameters);

                // 【補】打印 Compass 可執行腳本
                string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                logger.Info("ExecuteCount 腳本（可直接粘(zhan)貼(tie) Compass）:\r\n" +
                             string.Format("db.{0}.countDocuments({1})", qi.Table, filterJson));

                int cnt = (int)coll.CountDocuments(filter);
                logger.Debug("返回ExecuteCount count行數=" + cnt);
                return cnt;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return 0;
            }
        }
        #endregion

        #region ── 6. 單行單列查詢 ──
        public string ExecuteGetSingle(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteGetSingle sql=" + sql);

            try
            {
                var qi = CachedParse(sql, ParseSelect);
                CheckTable(qi.Table);

                if (qi.IsAggregate)
                {
                    object agg = ExecuteAggregate(qi, parameters);
                    string restr = agg == null ? null : agg.ToString();
                    logger.Debug("ExecuteGetSingle 帶聚合查(cha)詢，執行結果：" + restr);
                    return restr;
                }

                if (qi.Fields == null || qi.Fields.Length != 1)
                {
                    logger.Debug("字(zi)段數 ≠ 1");
                    throw new ArgumentException("只能查詢單列(lie)");
                }

                var coll = _db.GetCollection<BsonDocument>(qi.Table);
                var filter = BuildFilterAuto(qi.Where, parameters);
                var proj = Builders<BsonDocument>.Projection.Include(qi.Fields[0]).Exclude("_id");

                // 【補(bu)】打印 Compass 可執行腳本
                string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                string projJson = proj.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                logger.Info("ExecuteGetSingle 腳本（可直接粘貼(tie) Compass）:\r\n" +
                             string.Format("db.{0}.find({1}, {2}).limit(1)",
                                           qi.Table, filterJson, projJson));

                var doc = coll.Find(filter).Project(proj).Limit(1).FirstOrDefault();
                object value = SafeGetValue(doc, qi.Fields[0]);
                logger.Debug("返回 ExecuteGetSingle 單行(xing)單列(lie)查詢結果=" + (value ?? "null"));
                return value == null ? null : value.ToString();
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return null;
            }
        }
        #endregion
        
        #region ── 6.1. 多行單列查詢 ──
        public List<object> ExecuteGetList(string sql, IDictionary<string, object> parameters = null)
        {
            sql = CleanSql(sql);
            logger.Debug("ExecuteGetList 入口 sql=" + sql);
        
            try
            {
                var qi = CachedParse(sql, ParseSelect);
                CheckTable(qi.Table);
        
                if (qi.Fields == null || qi.Fields.Length != 1)
                {
                    logger.Debug("字段數 ≠ 1");
                    throw new ArgumentException("SQL 必須(xu)僅查詢(xun)一個列，如 SELECT Name FROM ...");
                }
        
                var coll   = _db.GetCollection<BsonDocument>(qi.Table);
                var filter = BuildFilterAuto(qi.Where, parameters);
                var proj   = Builders<BsonDocument>.Projection.Include(qi.Fields[0]).Exclude("_id");
                var find   = coll.Find(filter).Project(proj);
        
                if (qi.Top.HasValue && qi.Top.Value > 0)
                    find = find.Limit(qi.Top.Value);
               
        
                List<object> reli = find.ToList()
                                        .Select(doc => BsonTypeMapper.MapToDotNetValue(doc.GetValue(qi.Fields[0])))
                                        .ToList();
        
                //【補】打(da)印(yin) Compass 可執行腳本(ben)
                string limitClause = qi.Top.HasValue && qi.Top.Value > 0
                                         ? ".limit(" + qi.Top.Value + ")"
                                         : "";
                string filterJson  = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
                string projJson    = proj.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString();
        
                logger.Info("ExecuteGetList 腳本（可直接粘貼 Compass）:\r\n" +
                             string.Format("db.{0}.find({1}, {2}){3}",
                                           qi.Table, filterJson, projJson, limitClause));
        
                logger.Debug("返(fan)回 ExecuteGetList 單列多行(xing)行(xing)數=" + reli.Count);
                return reli;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(ex);
                return null;   // 永遠走不到
            }
        }
        #endregion

        #region ── 7. 解析輔助類 ──
        private class Qi
        {
            public string Table;
            public string Where;
            public string OrderBy;
            public string[] Fields;
            public int? Top;
            public bool IsCount;
            public string CountAlias;
            public bool IsAggregate;
            public string AggFunc;
            public string AggField;
            public string AggAlias;
            public bool IsGroupBy;
            public string[] GroupFields;
            public string Having;
            public bool IsExists;
            public string SubQuerySql;
            public string SubQuerySelect;
            public string SubQueryForeign;
            public bool IsDistinct;
            public int? Skip;   // 新(xin)增 skip
        }

        private class InsertAuto
        {
            public string Table;
            public List<string> Columns;
            public List<List<object>> Values;
        }

        private class UpdateAuto
        {
            public string Table;
            public string Where;
            public Dictionary<string, object> SetList;
        }

        private class DeleteAuto
        {
            public string Table;
            public string Where;
        }
        #endregion

        #region ── 8. 正則模板 ──
        private static readonly Regex _cleanSpace =
            new Regex(@"\s+", RegexOptions.Compiled | RegexOptions.Multiline);

        private static readonly Regex _reSelect =
            new Regex(@"SELECT\s+(?:TOP\s+(?<top>\d+)\s+)?(?<fields>.+?)\s+FROM\s+(?<table>\w+)(?:\s+WHERE\s+(?<where>.*?))?(?:\s+ORDER\s+BY\s+(?<order>.*?))?$",
                RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

        private static readonly Regex _reInsert =
            new Regex(@"INSERT\s+INTO\s+(\w+)\s*\(([^)]+)\)\s*VALUES\s*(.+)",
                RegexOptions.IgnoreCase | RegexOptions.Compiled);

        private static readonly Regex _reUpdate =
            new Regex(@"UPDATE\s+(\w+)\s+SET\s+(.+?)(?:\s+WHERE\s+(.+))?$",
                RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

        private static readonly Regex _reDelete =
            new Regex(@"DELETE\s+FROM\s+(\w+)(?:\s+WHERE\s+(.+))?$",
                RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Compiled);

        private static readonly Regex KeyWordRegex =
            new Regex(@"\b(SELECT|FROM|WHERE|AND|OR|GROUP\s+BY|HAVING|EXISTS|NOT\s+EXISTS|ORDER\s+BY|TOP|AS|IN|LIKE|IS|NULL|ASC|DESC|BETWEEN|DISTINCT|SKIP|CAST)\b",
                RegexOptions.IgnoreCase | RegexOptions.Compiled);

        private static readonly Regex ObjectIdRegex =
            new Regex(@"(\w+)\s*=\s*['""]([0-9a-fA-F]{24})['""]",
                RegexOptions.Compiled | RegexOptions.IgnoreCase);
        #endregion

        #region ── 9. 解析入口 ──
        private static string UpperKeyWords(string sql)
        {
            return KeyWordRegex.Replace(sql, delegate(Match m) { return m.Value.ToUpper(); });
        }

        private static string CleanSql(string sql)
        {
            if (string.IsNullOrEmpty(sql)) return sql;
            return _cleanSpace.Replace(sql, " ").Trim();
        }

        private static Qi ParseSelect(string sql)
        {
            sql = CleanSql(sql);
            logger.Debug(string.Format("[ParseSelect] 原始 SQL：{0}", sql));
            if (sql.Length > 5000) sql = sql.Substring(0, 5000);
            sql = UpperKeyWords(sql);

            bool isDistinct = Regex.IsMatch(sql, @"\bDISTINCT\b", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bDISTINCT\b", "", RegexOptions.IgnoreCase);

            int? top = null;
            var topM = Regex.Match(sql, @"TOP\s+(\d+)", RegexOptions.IgnoreCase);
            if (topM.Success)
            {
                top = int.Parse(topM.Groups[1].Value);
                sql = sql.Remove(topM.Index, topM.Length);
            }
            
            // 已(yi)有 top 捕獲之后追(zhui)加
            int? skip = null;
            var skipM = Regex.Match(sql, @"SKIP\s+(\d+)", RegexOptions.IgnoreCase);
            if (skipM.Success)
            {
                skip = int.Parse(skipM.Groups[1].Value);
                sql = sql.Remove(skipM.Index, skipM.Length);   // 去掉關鍵字
            }

            var fieldM = Regex.Match(sql, @"SELECT\s+(.+?)\s+FROM", RegexOptions.IgnoreCase);
            if (!fieldM.Success)
                LogAndException.Throw(new MongoSqlException("找(zhao)不到Select 字段列表 from table", sql));

            string rawFields = fieldM.Groups[1].Value.Trim();
            sql = sql.Remove(fieldM.Index, fieldM.Length).Insert(fieldM.Index, "SELECT  FROM");

            var tblM = Regex.Match(sql, @"FROM\s+(\w+)", RegexOptions.IgnoreCase);
            if (!tblM.Success)
                LogAndException.Throw(new MongoSqlException("找不(bu)到select from 表(biao)名", sql));
            string table = tblM.Groups[1].Value;

            string wheres = null;
            var whereM = Regex.Match(sql, @"WHERE\s+(.+?)(?:\s+ORDER\s+BY|$)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            if (whereM.Success) wheres = whereM.Groups[1].Value.Trim();

            string order = null;
            var ordM = Regex.Match(sql, @"ORDER\s+BY\s+(.+)$", RegexOptions.IgnoreCase);
            if (ordM.Success) order = ordM.Groups[1].Value.Trim();

            var qi = new Qi
            {
                Table = table,
                Where = wheres,
                OrderBy = order,
                Top = top,
                IsDistinct = isDistinct,
                Skip = skip
            };

            var countMatch = Regex.Match(rawFields, @"COUNT\s*\(\s*\*\s*\)(?:\s+AS\s+(\w+))?", RegexOptions.IgnoreCase);
            if (countMatch.Success)
            {
                qi.IsCount = true;
                qi.CountAlias = countMatch.Groups[1].Success ? countMatch.Groups[1].Value : "Count";
                qi.Fields = new[] { qi.CountAlias };
            }
            else
            {
                var aggMatch = Regex.Match(rawFields, @"\b(SUM|MAX|MIN|AVG)\s*\(\s*([^)]+)\s*\)(?:\s+AS\s+(\w+))?", RegexOptions.IgnoreCase);
                if (aggMatch.Success)
                {
                    qi.IsAggregate = true;
                    qi.AggFunc = aggMatch.Groups[1].Value.ToUpper();
                    qi.AggField = aggMatch.Groups[2].Value.Trim();
                    qi.AggAlias = aggMatch.Groups[3].Success ? aggMatch.Groups[3].Value : qi.AggFunc;
                    qi.Fields = new[] { qi.AggAlias };
                }
                else
                {
                    qi.Fields = rawFields.Split(',').Select(s => s.Trim()).ToArray();
                }
            }

            var groupM = Regex.Match(sql, @"GROUP\s+BY\s+([^H]+?)(?:\s+HAVING|$)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            if (groupM.Success)
            {
                qi.IsGroupBy = true;
                qi.GroupFields = groupM.Groups[1].Value.Split(',').Select(s => s.Trim()).ToArray();
            }
            var havM = Regex.Match(sql, @"HAVING\s+(.+)$", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            if (havM.Success) qi.Having = havM.Groups[1].Value.Trim();

            var existsM = Regex.Match(sql,
                @"\b(EXISTS|NOT\s+EXISTS)\s*\(\s*SELECT\s+\w+\s+FROM\s+(\w+)\s+WHERE\s+(\w+)\s*=\s*(\w+)\.(\w+)\s*\)",
                RegexOptions.IgnoreCase | RegexOptions.Singleline);
            if (existsM.Success)
            {
                qi.IsExists = existsM.Groups[1].Value.ToUpper() == "EXISTS";
                qi.SubQuerySql = existsM.Groups[0].Value;
                qi.SubQuerySelect = existsM.Groups[3].Value;
                qi.SubQueryForeign = existsM.Groups[5].Value;
            }

            logger.Debug(string.Format(
                "[ParseSelect] 拆分成功 Table={0} Cols={1} Where={2} Order by={3} IsCount={4} IsAggregate={5} IsDistinct={6}",
                table, rawFields, wheres, order, qi.IsCount, qi.IsAggregate, qi.IsDistinct));
            return qi;
        }
        
        
        //<summary>
        //解(jie)析 INSERT 語(yu)句
        //</summary>
        private static InsertAuto ParseInsertAuto(string sql)
        {
            sql = CleanSql(sql);
            logger.Debug(string.Format("[ParseInsertAuto] 原(yuan)始 SQL：{0}", sql));
            sql = UpperKeyWords(sql);
        
            var m = _reInsert.Match(sql);
            if (!m.Success)
                LogAndException.Throw(new MongoSqlException("INSERT 語(yu)法(fa)錯誤(wu)", sql));
        
            var cols = m.Groups[2].Value.Split(',').Select(s => s.Trim()).ToList();
            var valRows = Regex.Matches(m.Groups[3].Value, @"\(([^)]+)\)", RegexOptions.Compiled)
                               .Cast<Match>()
                               .Select(m2 => m2.Groups[1].Value.Split(',').Select(v => ParseValue(v.Trim())).ToList())
                               .ToList();
            var table = m.Groups[1].Value;
        
            logger.Debug(string.Format("[ParseInsertAuto] 拆分成功(gong) Table={0} Columns={1} 行數={2}",
                                       table, string.Join("|", cols), valRows.Count));
        
            return new InsertAuto
            {
                Table   = table,
                Columns = cols,
                Values  = valRows
            };
        }
        
        //<summary>
        //解析 UPDATE 語句(ju)
        //</summary>
        private static UpdateAuto ParseUpdateAuto(string sql)
        {
            sql = CleanSql(sql);
            logger.Debug(string.Format("[ParseUpdateAuto] 原始(shi) SQL：{0}", sql));
            sql = UpperKeyWords(sql);
        
            var m = _reUpdate.Match(sql);
            if (!m.Success)
                LogAndException.Throw(new MongoSqlException("UPDATE 語法錯誤", sql));
        
            var setDict = m.Groups[2].Value.Split(',')
                           .Select(s => s.Split('='))
                           .ToDictionary(a => a[0].Trim(), a => ParseValue(a[1].Trim()));
        
            logger.Debug(string.Format("[ParseUpdateAuto] 拆(chai)分成(cheng)功 Table={0} Set={1} Where={2}",
                                       m.Groups[1].Value,
                                       string.Join(";", setDict.Select(kv => kv.Key + "=" + kv.Value)),
                                       m.Groups[3].Value));
        
            return new UpdateAuto
            {
                Table   = m.Groups[1].Value,
                Where   = m.Groups[3].Value,
                SetList = setDict
            };
        }
        
        //<summary>
        //解析 DELETE 語(yu)句
        //</summary>
        private static DeleteAuto ParseDeleteAuto(string sql)
        {
            sql = CleanSql(sql);
            logger.Debug(string.Format("[ParseDeleteAuto] 原始 SQL：{0}", sql));
            sql = UpperKeyWords(sql);
        
            var m = _reDelete.Match(sql);
            if (!m.Success)
                LogAndException.Throw(new MongoSqlException("DELETE 語法錯誤(wu)", sql));
        
            logger.Debug(string.Format("[ParseDeleteAuto] 拆(chai)分成功 Table={0} Where={1}",
                                       m.Groups[1].Value, m.Groups[2].Value));
        
            return new DeleteAuto
            {
                Table = m.Groups[1].Value,
                Where = m.Groups[2].Value
            };
        }
        #endregion

        #region ── 14. 類型解析 ──
        private static object ParseValue(string raw)
        {
            if (string.IsNullOrWhiteSpace(raw)) return null;
            raw = raw.Trim();

            if ((raw.StartsWith("'") && raw.EndsWith("'")) ||
                (raw.StartsWith("\"") && raw.EndsWith("\"")))
                return raw.Substring(1, raw.Length - 2);

            if (raw.Equals("null", StringComparison.OrdinalIgnoreCase)) return null;

            bool b; if (bool.TryParse(raw, out b)) return b;

            if (raw.StartsWith("ObjectId(\"", StringComparison.OrdinalIgnoreCase) &&
                raw.EndsWith("\")") && raw.Length == 26)
                return new ObjectId(raw.Substring(10, 24));

            long l; if (Regex.IsMatch(raw, @"^-?\d+$") && long.TryParse(raw, out l)) return l;

            decimal d; if (Regex.IsMatch(raw, @"^-?\d+\.\d+$") && decimal.TryParse(raw, out d)) return d;

            DateTime dt; if (DateTime.TryParse(raw, out dt)) return dt;

            return raw;
        }
        #endregion

        #region ── 14.1 判斷是否有效字段 ──
        //正(zheng)則表達式來判斷傳入的字符串，是否符合(he)編程規則
        private static readonly Regex FieldRegex = new Regex(@"^[a-zA-Z_]\w*$", RegexOptions.Compiled);

        private bool IsValidField(string field)
        {
            return FieldRegex.IsMatch(field);
        }
        #endregion

        #region ── 15. SQL→MongoDB 過濾器構造 ──        
        private FilterDefinition<BsonDocument> BuildFilterAuto(string where,
                                              IDictionary<string, object> paras,
                                              bool isHaving = false)
        {
            if (string.IsNullOrEmpty(where)) return FilterDefinition<BsonDocument>.Empty;
        
            where = ReplaceParameters(where, paras);
        
            // 1. 空值保護：任何空串(chuan)/缺失 → null（不拋異(yi)常）
            where = Regex.Replace(where, @"(\w+)\s*=\s*$", "\"$1\":null");
            where = Regex.Replace(where, @"(\w+)\s*=\s*''", "\"$1\":null");
            where = Regex.Replace(where, @"(\w+)\s*=\s*""""", "\"$1\":null");
        
            // 2. 運算符映射(she)
            where = Regex.Replace(where, @"(\w+)\s*>=\s*([^,\s}]+)", "\"$1\":{ \"$gte\" : $2 }");
            where = Regex.Replace(where, @"(\w+)\s*<=\s*([^,\s}]+)", "\"$1\":{ \"$lte\" : $2 }");
            where = Regex.Replace(where, @"(\w+)\s*!=\s*([^,\s}]+)", "\"$1\":{ \"$ne\"  : $2 }");
            where = Regex.Replace(where, @"(\w+)\s*<\s*([^,\s}]+)", "\"$1\":{ \"$lt\"  : $2 }");
            where = Regex.Replace(where, @"(\w+)\s*>\s*([^,\s}]+)", "\"$1\":{ \"$gt\"  : $2 }");
            where = Regex.Replace(where, @"(\w+)\s*=\s*([^,\s}]+)", "\"$1\":$2");
        
            // 3. 特殊(shu)操作
            where = Regex.Replace(where, @"(\w+)\s+LIKE\s+'([^']+)'", "\"$1\":{ \"$regex\":\"$2\" }");
            where = Regex.Replace(where, @"(\w+)\s+BETWEEN\s+([^'""\s]+)\s+AND\s+([^'""\s]+)",
                                "\"$1\":{ \"$gte\":$2 , \"$lte\":$3 }", RegexOptions.IgnoreCase);
        
            where = Regex.Replace(where, @"(\w+)\s+IN\s*\(([^)]+)\)", m =>
            {
                string field = m.Groups[1].Value;
                string inner = m.Groups[2].Value;
                var items = Regex.Matches(inner, @"['""]([^'""]*)['""]|([^,]+)")
                                 .Cast<Match>()
                                 .Select(x => x.Groups[1].Success
                                                   ? string.Format("\"{0}\"",
                                                       x.Groups[1].Value.Replace("\"", "\\\""))
                                                   : x.Groups[2].Value.Trim())
                                 .ToArray();
                return string.Format("\"{0}\":{{ \"$in\" : [{1}] }}", field, string.Join(",", items));
            }, RegexOptions.IgnoreCase);
        
            where = Regex.Replace(where, @"(\w+)\s+IS\s+NULL", "\"$1\":{ \"$exists\" : false }", RegexOptions.IgnoreCase);
        
            // 4. ObjectId
            where = ObjectIdRegex.Replace(where, "\"$1\":ObjectId(\"$2\")");
        
            // 5. 字段名加(jia)雙引號
            where = Regex.Replace(where, @"\{([a-zA-Z]\w*):", "{\"$1\":");
            where = Regex.Replace(where, @"\s([a-zA-Z]\w*):", " \"$1\":");
        
            if (isHaving)
                where = Regex.Replace(where, @"\{""(\w+)"":", "{\"$$1\":");
        
            // 6. AND / OR
            if (Regex.IsMatch(where, @"\bAND\b", RegexOptions.IgnoreCase))
            {
                var parts = Regex.Split(where, @"\bAND\b", RegexOptions.IgnoreCase)
                                 .Select(p => "{" + p.Trim() + "}");
                where = "{ \"$and\" : [" + string.Join(",", parts) + "] }";
            }
            else if (Regex.IsMatch(where, @"\bOR\b", RegexOptions.IgnoreCase))
            {
                var parts = Regex.Split(where, @"\bOR\b", RegexOptions.IgnoreCase)
                                 .Select(p => "{" + p.Trim() + "}");
                where = "{ \"$or\" : [" + string.Join(",", parts) + "] }";
            }
            else
            {
                where = "{" + where + "}";
            }
        
            where = where.TrimEnd(' ', '\t', '\r', '\n', ';');        
            logger.Debug("[BuildFilterAuto] 生成過濾器 JSON：" + where);

            try
            {
                var filter = BsonDocument.Parse(where);
                logger.Debug("[BuildFilterAuto] 過濾器 JSON 解(jie)析結果=" + filter.ToBsonDocument().ToString());
                return filter;
            }
            catch (Exception ex)
            {
                LogAndException.Throw(new MongoSqlException("過濾器 JSON 解析失敗", where, ex));
                return null;
            }
        }
        #endregion

        #region ── 16. 聚合 / GROUP BY / EXISTS 執行 ──
        private object ExecuteAggregate(Qi qi, IDictionary<string, object> parameters)
        {
            logger.Debug("ExecuteAggregate開始，函數(shu): " + qi.AggFunc);
            var coll = _db.GetCollection<BsonDocument>(qi.Table);
            var filter = BuildFilterAuto(qi.Where, parameters);

            string op;
            switch (qi.AggFunc)
            {
                case "SUM": op = "$sum"; break;
                case "MAX": op = "$max"; break;
                case "MIN": op = "$min"; break;
                case "AVG": op = "$avg"; break;
                default:
                    logger.Error("[ExecuteAggregate] 不支持的聚合函數：" + qi.AggFunc);
                    throw new MongoSqlException("不支持(chi)的聚(ju)合函(han)數", qi.AggFunc);
            }

            var pipeline = new List<BsonDocument>
            {
                new BsonDocument("$match", filter.Render(_docSerializer, BsonSerializer.SerializerRegistry)),
                new BsonDocument("$group", new BsonDocument
                {
                    { "_id", 1 },
                    { qi.AggAlias, new BsonDocument(op, "$" + qi.AggField) }
                })
            };

            // 【補】打印 Compass 可執(zhi)行腳(jiao)本
            string pipelineJson = "[" + string.Join(", ", pipeline.Select(p => p.ToJson())) + "]";
            logger.Info("ExecuteAggregate 腳本（可直接粘貼 Compass）:\r\n" +
                         string.Format("db.{0}.aggregate({1})", qi.Table, pipelineJson));

            var result = coll.Aggregate<BsonDocument>(pipeline, new AggregateOptions()).FirstOrDefault();
            string value = result == null ? null : BsonTypeMapper.MapToDotNetValue(result[qi.AggAlias]).ToString();
            logger.Debug("返回 ExecuteAggregate 執行結果=" + (value ?? "null"));
            return value;
        }

        private DataTable ExecuteGroupBy(Qi qi, IDictionary<string, object> parameters)
        {
            logger.Debug("ExecuteGroupBy開始...");
            var coll = _db.GetCollection<BsonDocument>(qi.Table);
            var filter = BuildFilterAuto(qi.Where, parameters);

            var stages = new List<BsonDocument>
            {
                new BsonDocument("$match", filter.ToBsonDocument())
            };

            var groupId = new BsonDocument();
            foreach (var f in qi.GroupFields) groupId[f] = "$" + f;

            var accumulators = new BsonDocument { { "_id", groupId } };
            foreach (var field in qi.Fields)
            {
                if (qi.IsCount && field == qi.CountAlias)
                    accumulators[field] = new BsonDocument("$sum", 1);
                else if (qi.IsAggregate && field == qi.AggAlias)
                    accumulators[field] = new BsonDocument("$" + qi.AggFunc, "$" + qi.AggField);
                else if (Array.IndexOf(qi.GroupFields, field) >= 0)
                    accumulators[field] = new BsonDocument("$first", "$" + field);
            }
            stages.Add(new BsonDocument("$group", accumulators));

            if (!string.IsNullOrEmpty(qi.Having))
            {
                var havingBson = BuildFilterAuto(qi.Having, parameters, true);
                stages.Add(new BsonDocument("$match", havingBson.ToBsonDocument()));
            }

            if (!string.IsNullOrEmpty(qi.OrderBy))
            {
                var sortDoc = BsonDocument.Parse("{" + qi.OrderBy.Replace("DESC", "-1").Replace("ASC", "1") + "}");
                stages.Add(new BsonDocument("$sort", sortDoc));
            }

            if (qi.Top.HasValue && qi.Top.Value > 0)
                stages.Add(new BsonDocument("$limit", qi.Top.Value));

            // 【補】打印 Compass 可(ke)執行腳(jiao)本
            string stagesJson = "[" + string.Join(", ", stages.Select(p => p.ToJson())) + "]";
            logger.Info("ExecuteGroupBy 腳本（可直(zhi)接粘貼 Compass）:\r\n" +
                         string.Format("db.{0}.aggregate({1})", qi.Table, stagesJson));

            var list = coll.Aggregate<BsonDocument>(stages).ToList();
            logger.Debug("ExecuteGroupBy 返(fan)回(hui)行數=" + list.Count);
            return ToDataTable(list);
        }

        private DataTable ExecuteExists(Qi qi, IDictionary<string, object> parameters)
        {
            logger.Debug("ExecuteExists 開始...");
            var subQi = CachedParse(qi.SubQuerySql, ParseSelect);
            var subColl = _db.GetCollection<BsonDocument>(subQi.Table);
            var subFilter = BuildFilterAuto(subQi.Where, parameters);

            var fieldName = subQi.SubQuerySelect.ToLowerInvariant();
            var subList = subColl.Find(subFilter)
                                 .Project(Builders<BsonDocument>.Projection.Include(fieldName))
                                 .ToList()
                                 .Select(d => d.GetValue(fieldName))
                                 .Distinct()
                                 .ToList();

            var mainColl = _db.GetCollection<BsonDocument>(qi.Table);
            var mainFilter = BuildFilterAuto(qi.Where, parameters);

            if (subList.Any())
            {
                var inFilter = qi.IsExists
                    ? Builders<BsonDocument>.Filter.In(qi.SubQueryForeign, subList)
                    : Builders<BsonDocument>.Filter.Nin(qi.SubQueryForeign, subList);
                mainFilter = Builders<BsonDocument>.Filter.And(mainFilter, inFilter);
            }
            else
            {
                if (qi.IsExists)
                    mainFilter = Builders<BsonDocument>.Filter.And(mainFilter,
                        Builders<BsonDocument>.Filter.Eq("_id", ObjectId.Empty));
            }

            var find = mainColl.Find(mainFilter);

            bool isStar = qi.Fields != null && qi.Fields.Length == 1 && qi.Fields[0] == "*";
            if (isStar)
                find = find.Project(Builders<BsonDocument>.Projection.Exclude("_noSuchField"));
            else if (qi.Fields != null && qi.Fields.Length > 0)
            {
                var proj = Builders<BsonDocument>.Projection;
                ProjectionDefinition<BsonDocument> p = null;
                foreach (var f in qi.Fields)
                    p = p == null ? proj.Include(f) : p.Include(f);
                find = find.Project(p);
            }

            if (!string.IsNullOrEmpty(qi.OrderBy))
                find = find.Sort(BuildSortAuto(qi.OrderBy));

            if (qi.Top.HasValue && qi.Top.Value > 0)
                find = find.Limit(qi.Top.Value);

            // 【補(bu)】打印 Compass 可執行腳本
            string projectionClause = isStar ? "{}" 
                : "{ " + string.Join(", ", qi.Fields.Select(f => "\"" + f + "\": 1")) + " }";
            string sortClause = !string.IsNullOrEmpty(qi.OrderBy) 
                ? ".sort(" + BuildSortAuto(qi.OrderBy).ToJson() + ")" 
                : "";
            string limitClause = qi.Top.HasValue ? ".limit(" + qi.Top.Value + ")" : "";

            logger.Info("ExecuteExists 腳本（可直接粘貼 Compass）:\r\n" +
                         string.Format("db.{0}.find({1}, {2}){3}{4}",
                                       qi.Table,
                                       mainFilter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToJson(),
                                       projectionClause,
                                       sortClause,
                                       limitClause));

            var list = find.ToList();
            logger.Debug("ExecuteExists 返回(hui)行(xing)數(shu)=" + list.Count);
            return ToDataTable(list);
        }
        #endregion


        #region ── 17. 排序構造 ──
        private static readonly Regex CastRegex =
            new Regex(@"\bCAST\s*\(\s*([^)]+)\s+AS\s+(INT|LONG|DECIMAL|DOUBLE|STRING|DATE|BOOL)\s*\)",
                RegexOptions.IgnoreCase | RegexOptions.Compiled);
        
        private static readonly Dictionary<string, string> CastToMongo = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            {"INT", "$toInt"},
            {"LONG", "$toLong"},
            {"DECIMAL", "$toDecimal"},
            {"DOUBLE", "$toDouble"},
            {"STRING", "$toString"},
            {"DATE", "$toDate"},
            {"BOOL", "$toBool"}
        };

        //<summary>
        //解(jie)析 ORDER BY 中的 CAST(expr AS type)，返(fan)回 MongoDB 表達式
        //</summary>
        private BsonValue ParseCastInOrderBy(string expr)
        {
            var m = CastRegex.Match(expr);
            if (!m.Success) return null;
        
            string rawExpr = m.Groups[1].Value.Trim();
            string targetType = m.Groups[2].Value.ToUpper();
        
            string mongoOp; // ?? 提(ti)前聲明
            if (CastToMongo.TryGetValue(targetType, out mongoOp))
            {
                if (FieldRegex.IsMatch(rawExpr) && !rawExpr.StartsWith("$"))
                    rawExpr = "$" + rawExpr;
        
                return BsonDocument.Parse(
                    string.Format("{{ \"{0}\": \"{1}\" }}", mongoOp, rawExpr));
            }
        
            return null;
        }
        
        //<summary>
        //返回(hui) BsonDocument，供日志打印；外部再 .Sort(...) 即可
        //</summary>
        private BsonDocument BuildSortAuto(string orderBy)
        {
            if (string.IsNullOrWhiteSpace(orderBy))
                return null;
        
            var doc = new BsonDocument();
            foreach (var piece in orderBy.Split(','))
            {
                var tmp = piece.Trim().Split(' ');
                string fld = tmp[0];
                int  dir = (tmp.Length > 1 && tmp[1].ToUpper() == "DESC") ? -1 : 1;
                
                // 嘗試解(jie)析(xi) CAST
                BsonValue sortKey = ParseCastInOrderBy(fld);
                if (sortKey == null)
                {
                    // 普通字(zi)段
                    sortKey = fld;
                }
                
                // MongoDB 排序鍵必(bi)須(xu)是(shi)字(zi)符串或表達式
                if (sortKey.IsBsonDocument)
                {
                    // 表達式(shi)排序，使用 $project + $sort（僅聚合場景）
                    // 但 MongoDB 4.4+ 支(zhi)持直接表達式排序
                    doc.Add(fld, new BsonDocument
                    {
                        { "$meta", "expression" }, // 占位(wei)，實際用表達(da)式
                        { "value", sortKey },
                        { "direction", dir }
                    });
                }
                else
                {
                    doc.Add(sortKey.AsString, dir);
                }
            }
            // 日志里可拷
            logger.Debug("[BuildSortAuto] 排(pai)序 JSON：" + doc.ToJson());
            return doc;
        }
        #endregion


        #region ── 18. 參數替換（含脫敏）──
        private string ReplaceParameters(string text, IDictionary<string, object> paras)
        {
            if (text == null) return text;
            if (paras != null)
            {
                foreach (var kv in paras)
                {
                    object val = kv.Value;
                    string key = "@" + kv.Key;

                    if (val is IEnumerable && !(val is string))
                    {
                        text = text.Replace(key, JsonConvert.SerializeObject(val));
                        continue;
                    }

                    string jsonVal;
                    if (val == null) jsonVal = "null";
                    else if (val is string) jsonVal = "\"" + val.ToString().Replace("\"", "\\\"") + "\"";
                    else if (val is DateTime) jsonVal = "\"" + ((DateTime)val).ToString("o") + "\"";
                    else if (val is bool) jsonVal = val.ToString().ToLower();
                    else jsonVal = val.ToString();

                    text = text.Replace(key, jsonVal)
                               .Replace(":" + kv.Key, jsonVal);
                }
            }

            text = ObjectIdRegex.Replace(text, "\"$1\":ObjectId(\"$2\")");
            return text;
        }
        #endregion

        #region ── 19. BsonDocument→DataTable ──
        private static DataTable ToDataTable(List<BsonDocument> docs)
        {
            var dt = new DataTable();
            if (docs == null || docs.Count == 0)
            {
                logger.Debug("ToDataTable: 輸入 docs 為 null 或 0 條，返回空表。");
                return dt;
            }

            var cols = new SortedSet<string>();
            foreach (var d in docs)
            {
                foreach (var e in d.Elements)
                {
                    if (e.Name == "_id" && e.Value.IsBsonDocument)
                    {
                        foreach (var sub in e.Value.AsBsonDocument)
                            cols.Add(sub.Name);
                    }
                    else
                    {
                        cols.Add(e.Name);
                    }
                }
            }

            foreach (var c in cols) dt.Columns.Add(c, typeof(object));
            logger.Debug("ToDataTable: 建(jian)列完成，列數(shu)=" + dt.Columns.Count);

            foreach (var d in docs)
            {
                DataRow r = dt.NewRow();
                foreach (var e in d.Elements)
                {
                    if (e.Name == "_id" && e.Value.IsBsonDocument)
                    {
                        foreach (var sub in e.Value.AsBsonDocument)
                            r[sub.Name] = BsonTypeMapper.MapToDotNetValue(sub.Value) ?? DBNull.Value;
                    }
                    else
                    {
                        r[e.Name] = BsonTypeMapper.MapToDotNetValue(e.Value) ?? DBNull.Value;
                    }
                }
                dt.Rows.Add(r);
            }

            logger.Debug("ToDataTable: 填行完成，行數=" + dt.Rows.Count);
            return dt;
        }
        #endregion

        #region ── 20. 緩存包裝 ──
        private T CachedParse<T>(string sql, Func<string, T> parser) where T : class
        {
            var cached = _parseCache.Get(sql) as T;
            if (cached != null) return cached;

            var newValue = parser(sql);
            _parseCache.Set(sql, newValue, DateTimeOffset.UtcNow.AddHours(1));
            return newValue;
        }
        #endregion

        #region ── 21. 安全獲取值 ──
        private static object SafeGetValue(BsonDocument doc, string fieldName)
        {
            if (doc == null) return null;
            BsonValue val;
            return doc.TryGetValue(fieldName, out val) ? BsonTypeMapper.MapToDotNetValue(val) : null;
        }
        #endregion
    }

    #region ── 統一異常 ──
    public sealed class MongoSqlException : Exception
    {
        public string Sql { get; private set; }
        public MongoSqlException(string message, string sql, Exception inner = null)
            : base(message + "  SQL=" + sql, inner) { Sql = sql; }
    }
    #endregion
}

// ===================================================================================
//  MongoSqlClient  C# 5.0 兼容完整版 
//  封裝類：LogAndException
//  說明：拋異(yi)常日志，自(zi)動記error日志，并且翻譯(yi)系(xi)統錯誤信(xin)息成用戶能理解的錯誤信(xin)息。
//  依(yi)賴(lai)：log4net類(lei)
// ===================================================================================
using System;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using log4net;

namespace MongoSql.Demo
{
    public static class LogAndException
    {
        private static readonly ILog logger = LogManager.GetLogger(typeof(LogAndException));

        //<summary>
        //      LogAndException.DebugRaw = false; // true代表調試期，直接裸拋，默認False不裸拋
        //      LogAndException.ThrowTech = false;   // 在調試期DebugRaw=false才生效，True返回[技術出錯信息(xi)]，False返回翻譯(yi)過的[用戶(hu)出錯信息(xi)]，默認False
        //</summary>
        public static bool DebugRaw { get; set; }
        public static bool ThrowTech { get; set; }
        
        //默認值(zhi)
        static LogAndException()
        {
            DebugRaw = false;   // true代(dai)表調試(shi)期，直接裸(luo)拋，默認False不裸(luo)拋
            ThrowTech = false;   // 在調試期DebugRaw=false才生效，True返回(hui)[技術出錯信(xin)息]，False返回(hui)翻譯過的[用戶出錯信(xin)息]，默認False
        }


        public static void Throw(Exception original, string mes = null)
        {
            if (original == null) 
                throw new ArgumentNullException("original Exception is null ...");
        
            string user = GetUserMessage(original);
            string tech = GetTechMessage(original);
            string loc  = GetMyCodeLocation(original);
        
            //記錄詳細堆棧日(ri)志
            logger.Error(mes,original);
            //記錄精簡(jian)日志
            logger.Error(string.Format("LOC={0} \n MSG={1} \n USER={2} \n TECH={3}",
                                        loc, mes ?? "", user, tech));
        
            if (DebugRaw)
                // 裸拋原始錯誤(wu)信息
                throw original;
            else    
                // 拋用戶友(you)好錯誤提示
                throw new Exception(user, original);
        }
        
        public static string GetUserMessage(Exception ex)
        {
            if (IsConnectTimeout(ex))
                return "網絡繁忙，請(qing)稍后重試；若(ruo)仍無法使用(yong)，請(qing)聯(lian)系客服。";

            SqlException sqlex = ex as SqlException;
            if (sqlex != null)
            {
                if (sqlex.Number == 18456) return "登錄失敗，請聯(lian)系管(guan)理員確認(ren)賬號密碼。";
                if (sqlex.Number == 4060 || sqlex.Number == 233 || sqlex.Number == 2)
                    return "系統維護中，請稍后再(zai)試。";
                if (sqlex.Number == 1205) return "操作沖突(tu)，請稍后重試。";
            }


            return "系統繁忙，請(qing)稍后重試。";
        }

        public static string GetTechMessage(Exception ex)
        {
            SqlException sqlex = ex as SqlException;
            if (sqlex != null)
                return string.Format("(SQL錯誤號:{0} | 服(fu)務(wu)器:{1}) 消息:{2}",
                                      sqlex.Number, sqlex.Server, sqlex.Message);

            Win32Exception winex = ex as Win32Exception;
            if (winex != null)
            {
                uint code = (uint)winex.NativeErrorCode;
                return string.Format("(Win32錯誤(wu)碼:0x{0:X8}) 消息:{1}", code, winex.Message);
            }

            return string.Format("其他異常類型:{0} | 消息:{1}",
                                  ex.GetType().Name, ex.Message);
        }


        
        private static string GetMyCodeLocation(Exception ex)
        {
            Exception root = ex;
            while (root.InnerException != null)
                root = root.InnerException;
            
            if (root == null) return "定位出錯行失敗，傳入ex為(wei)null";
        
            var st = new StackTrace(root, true);
            if (st == null) return "定位出錯行(xing)失敗，StackTrace(ex, true)返回null)";   // 理(li)論上不會出(chu)現，但防(fang)御
        
            var frames = st.GetFrames();
            if (frames == null) return "定位(wei)出錯(cuo)行(xing)失(shi)敗，st.GetFrames()返回null)";        

            
            foreach (var frame in frames)
            {
                if (frame == null) continue;                 // 單幀可能為null
        
                string file = frame.GetFileName();
                if (!string.IsNullOrEmpty(file))
                {
                    return string.Format("定(ding)位出錯信息成(cheng)功(gong):{0} at {1} in {2}:{3}",
                                         ex.GetType().Name,
                                         frame.GetMethod() == null ? "未知(zhi)方法" : frame.GetMethod().Name,
                                         Path.GetFileName(file),
                                         frame.GetFileLineNumber());
                }
            }

            return "定位出錯行失敗，可能是(shi)(無PDB)";
        }        

        private static bool IsConnectTimeout(Exception ex)
        {
            SqlException sqlex = ex as SqlException;
            if (sqlex != null)
                return sqlex.Number == -2 || sqlex.Number == 258;

            Win32Exception winex = ex as Win32Exception;
            if (winex != null)
                return (uint)winex.NativeErrorCode == 258u;

            return false;
        }
    }
}