兼容标准sql语法的mongodb数据库操作类
1、背景：
近期因为业务需求，需要将原来一个基于sqlserver的业务，切换到mongodb上。由于前端系统上线时间较长，做大范围的修改风险较高，就考虑能不能封装一个mongodb的sql操作类，解决日常的sql操作，以避免前台的大范围修改和测试。
（当然，为啥要sqlserver切mongodb？我们也很清楚他们是两种完全不同类型的数据库，但业务端就是这个样子了，so，世界是动态发展，业务也是千变万化的，技术只能吃药啊啊啊…）
2、依赖平台介绍
开发语言 ：C# 5.0
框架版本： .net framework 4.5.2（本来是3.5，这次升到了4.5.2）
mongodb： 3.6-4.2 （服务器是win2008 ，所以不能太高版本）
mongodb driver：2.11.5 （嗯，依赖于mongdb 版本、.net framework版本，各种百度加尝试后，最终确认这个版本）
3、程序结构
示例程序：MongoSqlExample.cs
封装接口类：MongoSqlHelper.cs // 裸露的前端接口
基础类：MongoSqlClient.cs //封装所有sql 转mongodb 操作json动作
异常处理类：LogAndException.cs // throw exception 同时记error日志
依赖：MongoDB.Bson ,MongoDB.Driver,MongoDB.Driver.Core,Newtonsoft.Json,log4.net
4、效果&用法
//查询返回 DataTable
//DataTable dt = MongoSqlHelper.Select("SELECT pkid,col1,col2 FROM table1 WHERE pkid=1 ORDER BY pkid ASC");;
//插入
//int rows = MongoSqlHelper.Insert("INSERT INTO table1(pkid,col1,col2) VALUES (1,'test','test2')");
//更新
//int rows = MongoSqlHelper.Update("UPDATE table1 SET col1='new name' WHERE pkid=1");
//删除
//int rows = MongoSqlHelper.Delete("DELETE FROM table WHERE pkid=1");
//计数
//int cnt = MongoSqlHelper.Count("SELECT COUNT(*) FROM table1 WHERE pkid=1");
//单行单列
//object name = MongoSqlHelper.ExecuteGetSingle("SELECT col1 FROM table1 WHERE pkid=1");
//多行单列
//List<object> list = MongoSqlHelper.ExecuteGetList("SELECT distinct pkid FROM table1 WHERE col2='test2' ");
//第一行第一列标准 Scalar 兼容（C# 5.0）
//object cnt = MongoSqlHelper.ExecuteScalar("SELECT COUNT(*) FROM table1 WHERE col1='test1' ");
//泛型 Scalar
//int cnt = MongoSqlHelper.ExecuteScalar<int>("SELECT COUNT(*) FROM table1 WHERE col1='test1' ");
5、下载链接
不给了，已经上传到github。
6、代码
1）MongoSqlExample.cs
// =================================================================================== // MongoSqlClient C# 5.0 示例程序 // =================================================================================== 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. 设置日志输出到控制台 */ //BasicConfigurator.Configure(); /* 2. 设置日志输出到Log.txt */ // 获取当前日志库的根日志器 var hierarchy = (Hierarchy)LogManager.GetRepository(); // 创建一个文件Appender var fileAppender = new FileAppender { File = "Logs/Log.txt", // 指定日志文件路径 AppendToFile = true, // 是否追加到文件 Layout = new PatternLayout("%date [%thread] %-5level %logger - %message%newline"), // 日志格式 Threshold = Level.Info, // 设置日志级别为 INFO Encoding = Encoding.UTF8 // 设置文件编码为 UTF-8 }; fileAppender.ActivateOptions(); // 激活Appender // 将文件Appender添加到根日志器 hierarchy.Root.AddAppender(fileAppender); // 设置根日志器的级别为 INFO hierarchy.Root.Level = Level.Info; /* 3. 自动初始化一些数据（不硬编码） */ InitDemoData(); /* 4. 键盘交互菜单 */ string cmd; Console.WriteLine("=== MongoSql 交互演示 ==="); while (true) { Console.WriteLine("\n【1】查询 【2】插入 【3】更新 【4】删除 【5】统计 【6】查询单行单列 【7】查询单列 【0】退出"); Console.Write("请输入操作编号："); 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("无效编号"); break; } } catch (MongoSqlException mex) { Console.WriteLine("⚠️ SQL 错误：" + mex.Message); } catch (Exception ex) { Console.WriteLine("⚠️ 异常：" + ex.Message); } } } #region 初始化演示数据（零硬编码） private static void InitDemoData() { Console.Write("首次运行，是否插入演示数据？(y/n 默认y)："); if (Console.ReadLine().ToLower() == "n") return; /* 完全动态字段，实体仅做文档 */ var sql = string.Format( "INSERT INTO {0}(Name,Price,Stock,CreateTime) VALUES " + "('键盘',99,200,'{1}')," + "('鼠标',59,150,'{1}')," + "('显示器',999,30,'{1}')", table, DateTime.Now.ToString("yyyy-MM-dd")); int n = MongoSqlHelper.Insert(sql); Console.WriteLine("已插入 {0} 条演示数据", n); } #endregion #region 各操作（均读键盘，零硬编码） private static void Query() { Console.Write("请输入 WHERE 子句（直接回车=全表）："); string where = Console.ReadLine(); Console.Write("请输入 ORDER BY 子句（直接回车=不排序）："); string order = Console.ReadLine(); Console.Write("请输入 TOP 数量（直接回车=不限）："); 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; } /* 动态打印表头、行 */ 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("请输入列名，用逗号分隔（例：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("成功插入 {0} 条", n); } private static void Update() { Console.Write("请输入 SET 子句（例：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("成功更新 {0} 条", n); } private static void Delete() { Console.Write("请输入 WHERE 子句（例：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("满足条件记录数：" + c); } private static void GetColValueSingle() { /* 列 */ Console.Write("请输入列名，用逗号分隔（例：Name,Price,Stock）："); string col = Console.ReadLine(); Console.Write("请输入 WHERE 子句（直接回车=取第一行）："); 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("获取到字段{0}值 {1} ", col,obj); } private static void GetColValueMulti() { /* 列 */ Console.Write("请输入列名，用逗号分隔（例：Name,Price,Stock）："); string col = Console.ReadLine(); Console.Write("请输入 WHERE 子句（直接回车=全表）："); 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 } }
2）MongoSqlHelper.cs
// =================================================================================== // MongoSqlClient C# 5.0 兼容完整版 // 封装类：MongoSqlHelper // 说明：数据库连接参数读取config文件 // 依赖：log4.net // =================================================================================== using System; using System.Collections.Generic; using System.Data; using System.Text.RegularExpressions; using System.Configuration; using log4net; /* 相关版本：MongoDB 驱动 2.11.5 + MongoDB 3.6、4.2 + C# 5.0 + .NET 4.5.2 一、已支持 1. 等值/范围/IN 过滤 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 *****只允许单个查询，不允许同时查count 和max，要同时查多个聚合，请分别提交，譬如：**** "SELECT COUNT(*),MAX(price) FROM tbl" --- 不支持 4. DISTINCT SELECT DISTINCT uid FROM tbl 5. 一次插入多行 INSERT INTO tbl(a,b) VALUES (1,'a'),(2,'b') 7. 参数化写法（仅支持“拼好字符串”模式，不再传字典）： string sql = "SELECT * FROM tbl WHERE uid=1 AND status=2"; // 直接拼 DataTable dt = MongoSqlHelper.Select(sql); 二、不支持 · JOIN / 子查询 / UNION / 事务 / HAVING / 行锁 · 表达式索引（如 $toInt:field）—— 3.6 不支持，必须预存字段再建索引 · OFFSET / SKIP + LIMIT 组合（驱动支持，但本类未暴露 SKIP） · 视图、存储过程、触发器 . CAST（驱动 2.11.5 自动用 $toInt/$toDate，但 3.6 无表达式索引，仅聚合场景,） SELECT CAST(price AS INT) AS priceInt FROM tbl ORDER BY CAST(price AS INT) ASC —— 会走聚合管道，**无索引**，大表慎用，所以也取消支持。 三、能跑但会触发全表扫描，效率较低，数据量大时谨慎使用 · LIKE '%xx%' → MongoDB $regex，3.6 不支持索引 · 对 CAST/计算字段排序/过滤 → 走聚合，无索引 四、版本要求 · 驱动 2.11.5 最低要求 MongoDB 2.6，已测试 3.6+ · .NET Framework 不得低于 4.5.2（驱动硬性要求） · C# 5.0 无 await/async，本类全部同步接口，无异步版本 五、性能优化建议 1. 凡是用于 WHERE / ORDER BY 的字段，务必转为纯类型，并建索引。
譬如price原来是字符型，用下述脚本可以不重建改成int型： db.col.updateMany({},[{$set:{priceInt:{$toInt:"$price"}}}]) db.col.createIndex({priceInt:1}) 2. 内存缓存限额在 app.config 配置，可考虑 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 缺少 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("[执行sql结束]" + method + " || Return: " + (ret ?? "null")); } #endregion #region ------- SQL 语法安检 ------- // 语法预检：非法字符、永不支持语法、CAST+ORDER BY、LIKE 全表扫 全部处理。 // 正常引号允许出现，譬如：Name="abc' // 配置项可关闭 CAST 排序拦截。 private static void CheckSql(string sql) { if (string.IsNullOrWhiteSpace(sql)) throw new Exception("SQL 为空"); string upper = sql.ToUpper().Trim(); /* 1. 危险字符（控制符、分号、反斜杠）*/ if (Regex.IsMatch(sql, @"[\x00-\x08\x0B-\x0C\x0E-\x1F;\\]")) throw new Exception("SQL 含非法字符（控制符、分号、反斜杠）"); /* 2. 不支持的语法——整词匹配，防止字段名误杀 */ 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 含不支持的语法：" + kw); /* 3. CAST + ORDER BY——提示不支持 */ if ( Regex.IsMatch(upper, @"\bCAST\s*\(") && Regex.IsMatch(upper, @"\bORDER\s+BY\b")) throw new Exception("CAST+ORDER BY 无索引，暂不支持！"); /* 4. 性能陷阱——提示不支持*/ if (Regex.IsMatch(sql, @"\bLIKE\s+'%[^']*%'", RegexOptions.IgnoreCase)) throw new Exception("SQL 含 LIKE '%xx%'，会全表扫描，暂不支持！"); /* 5. 聚合函数数量——提示只允许 1 个，不支持同时count，sum 之类 */ 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("一条 SQL 只允许 1 个聚合函数（COUNT/SUM/MAX/MIN/AVG），请分别提交"); } #endregion //<summary> //查询返回 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 返回空结果集"); LogExit("Select", dt.Rows.Count + " 行"); return dt; } //<summary> //插入 //完整示例： //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> //更新 //完整示例： //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 + " 行更新"); 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 + " 行删除"); return rows; } //<summary> //计数 //完整示例： //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> //单行单列 //完整示例： //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> //多行单列 //完整示例： //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> //第一行第一列（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 } }
3）MongoSqlClient.cs
// =================================================================================== // MongoSqlClient C# 5.0 兼容完整版 // 基础类：MongoSqlClient // 说明： 含 Compass 可直接粘贴的执行脚本日志 // 依赖：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("表白名单：" + 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}] 不存在于数据库中", 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 去重 分支 ===== */ 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(); // 打印 Compass 可执行脚本 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 结果行数=" + 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语法，不支持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); // 构造 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; // 返回空 } } #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 可执行脚本 string docsJson = "[" + string.Join(", ", docs.Select(d => d.ToJson())) + "]"; logger.Info("ExecuteInsert 脚本（可直接粘贴 Compass）:\r\n" + string.Format("db.{0}.insertMany({1})", ii.Table, docsJson)); _db.GetCollection<BsonDocument>(ii.Table).InsertMany(docs); logger.Debug("返回ExecuteInsert 插入行数=" + 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(); //打印 Compass 可执行脚本 string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString(); string updateJson = upd.Combine(sets).Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString(); logger.Info("ExecuteUpdate 脚本（可直接粘贴 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 修改行数=" + 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 可执行脚本 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 删除行数=" + 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 脚本（可直接粘贴 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 带聚合查询，执行结果：" + restr); return restr; } if (qi.Fields == null || qi.Fields.Length != 1) { logger.Debug("字段数 ≠ 1"); throw new ArgumentException("只能查询单列"); } var coll = _db.GetCollection<BsonDocument>(qi.Table); var filter = BuildFilterAuto(qi.Where, parameters); var proj = Builders<BsonDocument>.Projection.Include(qi.Fields[0]).Exclude("_id"); //打印 Compass 可执行脚本 string filterJson = filter.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString(); string projJson = proj.Render(_docSerializer, BsonSerializer.SerializerRegistry).ToString(); logger.Info("ExecuteGetSingle 脚本（可直接粘贴 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 单行单列查询结果=" + (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 必须仅查询一个列，如 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(); //打印 Compass 可执行脚本 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("返回 ExecuteGetList 单列多行行数=" + 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; // 新增 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); } // 已有 top 捕获之后追加 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("找不到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("找不到select from 表名", 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> //解析 INSERT 语句 //</summary> private static InsertAuto ParseInsertAuto(string sql) { sql = CleanSql(sql); logger.Debug(string.Format("[ParseInsertAuto] 原始 SQL：{0}", sql)); sql = UpperKeyWords(sql); var m = _reInsert.Match(sql); if (!m.Success) LogAndException.Throw(new MongoSqlException("INSERT 语法错误", 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] 拆分成功 Table={0} Columns={1} 行数={2}", table, string.Join("|", cols), valRows.Count)); return new InsertAuto { Table = table, Columns = cols, Values = valRows }; } //<summary> //解析 UPDATE 语句 //</summary> private static UpdateAuto ParseUpdateAuto(string sql) { sql = CleanSql(sql); logger.Debug(string.Format("[ParseUpdateAuto] 原始 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] 拆分成功 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 语句 //</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 语法错误", sql)); logger.Debug(string.Format("[ParseDeleteAuto] 拆分成功 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 判断是否有效字段 ── //正则表达式来判断传入的字符串，是否符合编程规则 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. 空值保护：任何空串/缺失 → null（不抛异常） 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. 运算符映射 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. 特殊操作 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. 字段名加双引号 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 解析结果=" + 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开始，函数: " + 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("不支持的聚合函数", 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 可执行脚本 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 可执行脚本 string stagesJson = "[" + string.Join(", ", stages.Select(p => p.ToJson())) + "]"; logger.Info("ExecuteGroupBy 脚本（可直接粘贴 Compass）:\r\n" + string.Format("db.{0}.aggregate({1})", qi.Table, stagesJson)); var list = coll.Aggregate<BsonDocument>(stages).ToList(); logger.Debug("ExecuteGroupBy 返回行数=" + 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); //打印 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 返回行数=" + 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> //解析 ORDER BY 中的 CAST(expr AS type)，返回 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; 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> //返回 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; // 尝试解析 CAST BsonValue sortKey = ParseCastInOrderBy(fld); if (sortKey == null) { // 普通字段 sortKey = fld; } // MongoDB 排序键必须是字符串或表达式 if (sortKey.IsBsonDocument) { // 表达式排序，使用 $project + $sort（仅聚合场景） // 但 MongoDB 4.4+ 支持直接表达式排序 doc.Add(fld, new BsonDocument { { "$meta", "expression" }, // 占位，实际用表达式 { "value", sortKey }, { "direction", dir } }); } else { doc.Add(sortKey.AsString, dir); } } // 日志里可拷 logger.Debug("[BuildSortAuto] 排序 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: 建列完成，列数=" + 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 }
4、LogAndException.cs
// =================================================================================== // MongoSqlClient C# 5.0 兼容完整版 // 封装类：LogAndException // 说明：抛异常日志，自动记error日志，并且翻译系统错误信息成用户能理解的错误信息。 // 依赖：log4net类 // =================================================================================== 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返回[技术出错信息]，False返回翻译过的[用户出错信息]，默认False //</summary> public static bool DebugRaw { get; set; } public static bool ThrowTech { get; set; } //默认值 static LogAndException() { DebugRaw = false; // true代表调试期，直接裸抛，默认False不裸抛 ThrowTech = false; // 在调试期DebugRaw=false才生效，True返回[技术出错信息]，False返回翻译过的[用户出错信息]，默认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); //记录详细堆栈日志 logger.Error(mes,original); //记录精简日志 logger.Error(string.Format("LOC={0} \n MSG={1} \n USER={2} \n TECH={3}", loc, mes ?? "", user, tech)); if (DebugRaw) // 裸抛原始错误信息 throw original; else // 抛用户友好错误提示 throw new Exception(user, original); } public static string GetUserMessage(Exception ex) { if (IsConnectTimeout(ex)) return "网络繁忙，请稍后重试；若仍无法使用，请联系客服。"; SqlException sqlex = ex as SqlException; if (sqlex != null) { if (sqlex.Number == 18456) return "登录失败，请联系管理员确认账号密码。"; if (sqlex.Number == 4060 || sqlex.Number == 233 || sqlex.Number == 2) return "系统维护中，请稍后再试。"; if (sqlex.Number == 1205) return "操作冲突，请稍后重试。"; } return "系统繁忙，请稍后重试。"; } public static string GetTechMessage(Exception ex) { SqlException sqlex = ex as SqlException; if (sqlex != null) return string.Format("(SQL错误号:{0} | 服务器:{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错误码: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为null"; var st = new StackTrace(root, true); if (st == null) return "定位出错行失败，StackTrace(ex, true)返回null)"; // 理论上不会出现，但防御 var frames = st.GetFrames(); if (frames == null) return "定位出错行失败，st.GetFrames()返回null)"; foreach (var frame in frames) { if (frame == null) continue; // 单帧可能为null string file = frame.GetFileName(); if (!string.IsNullOrEmpty(file)) { return string.Format("定位出错信息成功:{0} at {1} in {2}:{3}", ex.GetType().Name, frame.GetMethod() == null ? "未知方法" : frame.GetMethod().Name, Path.GetFileName(file), frame.GetFileLineNumber()); } } return "定位出错行失败，可能是(无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; } } }
so，以上
// ===================================================================================// MongoSqlClient C# 5.0 兼容完整版
// 封装类：MongoSqlHelper
// 说明：数据库连接参数读取config文件
// 依赖：log4.net
// ===================================================================================
using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using System.Configuration;
using log4net;
/* 相关版本：MongoDB 驱动 2.11.5 + MongoDB 3.6、4.2 + C# 5.0 + .NET 4.5.2
一、已支持—— 已测试3.6，确定能跑
1. 等值/范围/IN 过滤
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
*****只允许单个查询，不允许同时查count 和max，要同时查多个聚合，请分别提交，譬如：****
"SELECT COUNT(*),MAX(price) FROM tbl" --- 不支持
4. DISTINCT
SELECT DISTINCT uid FROM tbl
5. 一次插入多行
INSERT INTO tbl(a,b) VALUES (1,'a'),(2,'b')
7. 参数化写法（仅支持“拼好字符串”模式，不再传字典）：
string sql = "SELECT * FROM tbl WHERE uid=1 AND status=2"; // 直接拼
DataTable dt = MongoSqlHelper.Select(sql);
二、不支持
· JOIN / 子查询 / UNION / 事务 / HAVING / 行锁
· 表达式索引（如 $toInt:field）—— 3.6 不支持，必须预存字段再建索引
· OFFSET / SKIP + LIMIT 组合（驱动支持，但本类未暴露 SKIP）
· 视图、存储过程、触发器
. CAST（驱动 2.11.5 自动用 $toInt/$toDate，但 3.6 无表达式索引，仅聚合场景,）
SELECT CAST(price AS INT) AS priceInt FROM tbl ORDER BY CAST(price AS INT) ASC
—— 会走聚合管道，**无索引**，大表慎用，所以也取消支持。
三、能跑但会触发全表扫描，数据量大时不建议使用
· LIKE '%xx%' → MongoDB $regex，3.6 不支持索引
· 对 CAST/计算字段排序/过滤 → 走聚合，无索引
四、版本要求
· 驱动 2.11.5 最低要求 MongoDB 2.6，已测试 3.6+
· .NET Framework 不得低于 4.5.2（驱动硬性要求）
· C# 5.0 无 await/async，本类全部同步接口，无异步版本
五、性能建议
1. 凡是用于 WHERE / ORDER BY 的字段，**务必预存为纯类型**并建索引：
db.col.updateMany({},[{$set:{priceInt:{$toInt:"$price"}}}])
db.col.createIndex({priceInt:1})
2. 日志级别调至 DEBUG 可在 log4net 中看到 Compass 可直接粘贴的执行脚本
3. 内存缓存限额在 app.config 配置（已加 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 缺少 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("[执行sql结束]" + method + " || Return: " + (ret ?? "null"));
}
#endregion
#region ------- SQL 语法安检 -------
// 语法预检：非法字符、永不支持语法、CAST+ORDER BY、LIKE 全表扫 全部处理。
// 正常引号允许出现，譬如：Name="abc'
// 配置项可关闭 CAST 排序拦截。
private static void CheckSql(string sql)
{
if (string.IsNullOrWhiteSpace(sql))
throw new Exception("SQL 为空");
string upper = sql.ToUpper().Trim();
/* 1. 危险字符（控制符、分号、反斜杠）*/
if (Regex.IsMatch(sql, @"[\x00-\x08\x0B-\x0C\x0E-\x1F;\\]"))
throw new Exception("SQL 含非法字符（控制符、分号、反斜杠）");
/* 2. 不支持的语法 */
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 含不支持的语法：" + kw);
/* 3. CAST + ORDER BY不支持的用法*/
if ( Regex.IsMatch(upper, @"\bCAST\s*\(") &&
Regex.IsMatch(upper, @"\bORDER\s+BY\b"))
throw new Exception("CAST+ORDER BY 无索引，不支持！");
/* 4. 性能陷阱——禁止 */
if (Regex.IsMatch(sql, @"\bLIKE\s+'%[^']*%'", RegexOptions.IgnoreCase))
throw new Exception("SQL 含 LIKE '%xx%'，会全表扫描，不支持！");
/* 5. 聚合函数数量——只允许 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("一条 SQL 只允许 1 个聚合函数（COUNT/SUM/MAX/MIN/AVG），请分别提交");
}
#endregion
//<summary>
//查询返回 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 返回空结果集");
LogExit("Select", dt.Rows.Count + " 行");
return dt;
}
//<summary>
//插入
//完整示例：
//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>
//更新
//完整示例：
//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 + " 行更新");
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 + " 行删除");
return rows;
}
//<summary>
//计数
//完整示例：
//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>
//单行单列
//完整示例：
//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>
//多行单列
//完整示例：
//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>
//第一行第一列（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
}
}
浙公网安备 33010602011771号