SqlHelper工具类

/**
*┌──────────────────────────────────────────────────────────────┐
*│ 描    述:MongoDB相关的工具类(MongoDB基础操作帮助类-单库Helper)                                                   
*│ 作    者:执笔小白                                              
*│ 版    本:2.0                                       
*│ 创建时间:2023-6-24 15:40:56                  
*└──────────────────────────────────────────────────────────────┘
*┌──────────────────────────────────────────────────────────────┐
*│ 命名空间: Util.NoSQLHelpers                     
*│ 类    名:MongoDBHelper                                     
*└──────────────────────────────────────────────────────────────┘
*/
using MongoDB.Bson;
using MongoDB.Driver;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Util.NoSQLHelpers
{
    /// <summary>
    /// MongoDB基础操作帮助类-单库Helper
    /// </summary>
    public static class MongoDBHelper
    {
        #region 变量
        /// <summary>
        /// MongoDB Client
        /// </summary>
        private static MongoClient _client;

        /// <summary>
        /// MongoDB 数据库
        /// </summary>
        private static IMongoDatabase _db;

        /// <summary>
        /// 获取当前的MongoClient
        /// </summary>
        /// <returns></returns>
        public static MongoClient GetMongoClient() { return _client; }

        /// <summary>
        /// 获取当前的MongoDatabase
        /// </summary>
        /// <returns></returns>
        public static IMongoDatabase GetMongoDatabase() {  return _db; }

        //private string mongoDBConStr= ConfigurationSettings.AppSettings["MongoDBConStr"].Trim();
        //private string mongoDBName = ConfigurationSettings.AppSettings["MongoDBName"].Trim();
        #endregion 变量

        #region 初始化
        /// <summary>
        /// 初始化数据库链接-数据库不存在则创建
        /// mongoDBConStr:mongodb://[username:password@]host1[:port1][,host2[:port2],…[,hostN[:portN]]][/[database][?options]]
        /// </summary>
        /// <param name="conStr">数据库连接字符串,如:mongodb://sa:123456@localhost:27017
        /// <param name="dbName">数据库名,如:TestDB
        /// <returns>返回当前的MongoDatabase</returns>
        public static IMongoDatabase Initial(string conStr, string dbName)
        {
            _client = new MongoClient(conStr);  // = new MongoClient("mongodb://sa:123456@localhost:27017"); 

            _db = _client.GetDatabase(dbName);

            return _db;
        }
        #endregion 初始化

        #region 数据库增删查
        /// <summary>
        /// 获取连接的配置
        /// </summary>
        /// <returns></returns>
        public static MongoClientSettings Settings => _client.Settings;

        /// <summary>
        /// 获取数据库的配置
        /// </summary>
        /// <returns></returns>
        public static MongoDatabaseSettings DBSettings => _db.Settings;

        /// <summary>
        /// 获取连接中所有的数据库
        /// </summary>
        /// <returns></returns>
        public static IAsyncCursor<BsonDocument> ListDatabases() => _client.ListDatabases();

        /// <summary>
        /// 获取连接中所有的数据库名
        /// </summary>
        /// <returns></returns>
        public static IAsyncCursor<string> ListDatabaseNames() => _client.ListDatabaseNames();

        /// <summary>
        /// 获取连接中的某个数据库
        /// </summary>
        /// <param name="name"></param>
        /// <param name="settings"></param>
        /// <returns></returns>
        public static IMongoDatabase GetDatabase(string name, MongoDatabaseSettings settings = null) => _client.GetDatabase(name, settings);

        /// <summary>
        /// 删除连接中的某个数据库
        /// </summary>
        /// <param name="name">数据库名</param>
        /// <param name="cancellationToken">令牌</param>
        public static void DropDatabase(string name, CancellationToken cancellationToken = default(CancellationToken)) => _client.DropDatabase(name, cancellationToken);
        #endregion 数据库增删查

        #region 集合(表、视图)增删改查;field为字段、index为索引、primary key为主键(_id)
        /* 
         * MongoDB支持的类型:主键类型(unix时间戳4个字节 + 机器标识码3个字节 + 进程PID2个字节 + 随机数3字节)ObjectId、Integer、String、Boolean、Double、Array、Date、Timestamp、Object、Null、二进制数据Binary Data、符号Symbol、代码Code、正则表达式类型Regular expression
        */
        /// <summary>
        /// 获取数据库中的所有数据集合
        /// 一个Collection为一个BsonDocument
        /// </summary>
        /// <returns></returns>
        public static IAsyncCursor<BsonDocument> ListCollections() => _db.ListCollections();

        /// <summary>
        /// 获取数据库中的所有数据集合名
        /// </summary>
        /// <returns></returns>
        public static IAsyncCursor<string> ListCollectionNames() => _db.ListCollectionNames();

        /// <summary>
        /// 获取数据库中某个数据集合
        /// .ToList()
        /// </summary>
        /// <param name="name">集合名</param>
        /// <param name="settings">配置</param>
        /// <returns></returns>
        public static IMongoCollection<TDocument> GetCollection<TDocument>(string name, MongoCollectionSettings settings = null) => _db.GetCollection<TDocument>(name, settings);

        /// <summary>
        /// 创建数据集合
        /// </summary>
        /// <param name="name">集合名</param>
        /// <param name="options">配置</param>
        /// <param name="cancellationToken">令牌</param>
        public static void CreateCollection(string name, MongoDB.Driver.CreateCollectionOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => _db.CreateCollection(name, options, cancellationToken);

        /// <summary>
        /// 修改集合的名称
        /// </summary>
        /// <param name="oldName">原集合名</param>
        /// <param name="newName">新集合名</param>
        /// <param name="options">配置</param>
        /// <param name="cancellationToken">令牌</param>
        public static void RenameCollection(string oldName, string newName, RenameCollectionOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => _db.RenameCollection(oldName, newName, options, cancellationToken);

        /// <summary>
        /// 删除数据集合
        /// </summary>
        /// <param name="name">集合名</param>
        /// <param name="cancellationToken">令牌</param>
        public static void DropCollection(string name, CancellationToken cancellationToken = default(CancellationToken))
            => _db.DropCollection(name, cancellationToken);

        /// <summary>
        /// 聚合到数据集合
        /// </summary>
        /// <param name="pipeline">管道</param>
        /// <param name="options">配置</param>
        /// <param name="cancellationToken">令牌</param>
        public static void AggregateToCollection<TResult>(PipelineDefinition<NoPipelineInput, TResult> pipeline, AggregateOptions options = null, CancellationToken cancellationToken = default(CancellationToken)) =>
            _db.AggregateToCollection<TResult>(pipeline, options, cancellationToken);

        /// <summary>
        /// 聚合
        /// </summary>
        /// <param name="session">会话</param>
        /// <param name="pipeline">管道</param>
        /// <param name="options">配置</param>
        /// <param name="cancellationToken">令牌</param>
        /// <returns></returns>
        public static IAsyncCursor<TResult> Aggregate<TResult>(IClientSessionHandle session, PipelineDefinition<NoPipelineInput, TResult> pipeline, AggregateOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => _db.Aggregate<TResult>(session, pipeline, options, cancellationToken);

        /// <summary>
        /// 创建视图
        /// </summary>
        /// <param name="viewName">视图名</param>
        /// <param name="viewOn"></param>
        /// <param name="pipeline">管道</param>
        /// <param name="options">配置</param>
        /// <param name="cancellationToken">令牌</param>
        public static void CreateView<TDocument, TResult>(string viewName, string viewOn, PipelineDefinition<TDocument, TResult> pipeline, CreateViewOptions<TDocument> options = null, CancellationToken cancellationToken = default(CancellationToken))
            => _db.CreateView<TDocument, TResult>(viewName, viewOn, pipeline, options, cancellationToken);

        /// <summary>
        /// 运行命令
        /// </summary>
        /// <param name="command">命令</param>
        /// <param name="readPreference">读取首选项</param>
        /// <param name="cancellationToken">令牌</param>
        /// <returns></returns>
        public static TResult RunCommand<TResult>(Command<TResult> command, ReadPreference readPreference = null, CancellationToken cancellationToken = default(CancellationToken))
            => _db.RunCommand<TResult>(command, readPreference, cancellationToken);
        #endregion 集合(表、视图)增删改查;field为字段、index为索引、primary key为主键(_id)

        #region 表数据(document)增删改查(推荐使用Linq)
        /* 
         * BsonDocument list1 = database.GetCollection("userlist");
         * var list = database.GetCollection<UserModel>("userlist");
         * 推荐查询使用Linq:  
         *   var result = list.AsQueryable().Where(p => p.Name == "a").ToList();  // 方式一
         *   var result = list.Find(p => p.Name == "a").limit(5).skip(10).ToList();  // 方式二
         *   SELECT Name FROM people LIMIT 5 SKIP 10
         *   var result = list.Find({}).ToList();  // 查看所有;或.find()、.FindAll()
         *   SELECT * FROM userlist
         *   db.people.find({ },{ user_id: 1, status: 1 })
         *   SELECT id,user_id,statusFROM people
        */

        /// <summary>
        /// 添加数据
        /// list1.InsertOne(new BsonDocument()
        /// {
        ///   {"_id",13},
        ///   {"Name","名字"},
        ///   {"Number",5}
        /// });
        /// list.InsertOne(new UserModel { Name = "a", Number = 1 });
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="document"></param>
        /// <param name="options"></param>
        /// <param name="cancellationToken"></param>
        public static void InsertOne<TDocument>(MongoDB.Driver.IMongoCollection<TDocument> collection, TDocument document, InsertOneOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => collection.InsertOne(document, options, cancellationToken);

        /// <summary>
        /// 添加数据-批量
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="documents"></param>
        /// <param name="options"></param>
        /// <param name="cancellationToken"></param>
        public static void InsertMany<TDocument>(MongoDB.Driver.IMongoCollection<TDocument> collection, IEnumerable<TDocument> documents, InsertManyOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => collection.InsertMany(documents, options, cancellationToken);

        /// <summary>
        /// 更新数据
        /// list.UpdateOne(p => p.Name == "a", update.Set("Number", 2));  // 或.Update(Builders<UserModel_MongoDB>.Filter.Eq("_id", model._id), new UpdateDocument(newModel))
        /// list.UpdateOne({ _id: 3 }, [ { $set: { "test3": 98, modified: "$$NOW"} });
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="filter">Builders<T>.Filter.方法;如:Builders<T>.Filter.Eq("_id","")</param>
        /// <param name="update">Builders<T>.Update.方法;如:Builders<T>.Update.Combine(list); 或Update.Set(item.Name, item.GetValue(model))</param>
        /// <param name="options"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public static UpdateResult UpdateOne<TDocument>(IMongoCollection<TDocument> collection, FilterDefinition<TDocument> filter, UpdateDefinition<TDocument> update, UpdateOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
        {
            // collection.UpdateOne<TDocument>(filter, update, options, cancellationToken);
            return collection.UpdateOne(filter, update, options, cancellationToken);
        }

        /// <summary>
        /// 更新数据-批量
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="filter">Builders<T>.Filter.方法;如:Builders<T>.Filter.Eq("_id","")</param>
        /// <param name="update">Builders<T>.Update.方法;如:Builders<T>.Update.Combine(list); 或Update.Set(item.Name, item.GetValue(model))</param>
        /// <param name="options"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public static UpdateResult UpdateMany<TDocument>(IMongoCollection<TDocument> collection, FilterDefinition<TDocument> filter, UpdateDefinition<TDocument> update, UpdateOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => collection.UpdateMany(filter, update, options, cancellationToken);

        /// <summary>
        /// 删除数据
        /// list.DeleteOne(p => p.Name == "a");
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="filter">Builders<T>.Filter.方法;如:Builders<T>.Filter.Eq("_id","")</param>
        /// <param name="cancellationToken"></param>
        public static void DeleteOne<TDocument>(IMongoCollection<TDocument> collection, FilterDefinition<TDocument> filter, CancellationToken cancellationToken = default(CancellationToken))
            => collection.DeleteOne(filter, cancellationToken);

        /// <summary>
        /// 删除所有数据
        /// </summary>
        /// <param name="collection"></param>
        public static void DeleteAll<TDocument>(IMongoCollection<TDocument> collection) => collection.DeleteMany(x => true);

        /// <summary>
        /// 根据条件批量删除
        /// FilterDefinition<BsonDocument> filter = Builders<BsonDocument>.Filter.In("Name", namelist_Del);  // 根据主键进行删除;BsonDocument可为实体
        /// </summary>
        public static void DeleteBatch<TDocument>(IMongoCollection<TDocument> collection, FilterDefinition<TDocument> filter)
        {
            collection.DeleteMany(filter);
        }

        /// <summary>
        /// 更换数据
        /// </summary>
        /// <param name="collection"></param>
        /// <param name="filter"></param>
        /// <param name="replacement"></param>
        /// <param name="options"></param>
        /// <param name="cancellationToken"></param>
        public static void ReplaceOne<TDocument>(IMongoCollection<TDocument> collection, FilterDefinition<TDocument> filter, TDocument replacement, ReplaceOptions options = null, CancellationToken cancellationToken = default(CancellationToken))
            => collection.ReplaceOne(filter, replacement, options, cancellationToken);
        #endregion 表数据(document)增删改查(推荐使用Linq)

        #region 表索引
        //Index
        #endregion 表索引

        /*
         * MongoDB使用sql见:https://www.mongodb.com/docs/manual/reference/sql-comparison/
         */
    }

    /// <summary>
    /// 示例类
    /// </summary>
    //[BsonIgnoreExtraElements]
    public class TestModel_MongoDB
    {
        /// <summary>
        /// 主键(正常情况下必须要有;若不使用时,该类需要加[BsonIgnoreExtraElements]特性标记)
        /// </summary>
        public ObjectId _id { get; set; }

        /// <summary>
        /// 名字
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 学号
        /// </summary>
        public int Number { get; set; }
    }
}
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.
  • 64.
  • 65.
  • 66.
  • 67.
  • 68.
  • 69.
  • 70.
  • 71.
  • 72.
  • 73.
  • 74.
  • 75.
  • 76.
  • 77.
  • 78.
  • 79.
  • 80.
  • 81.
  • 82.
  • 83.
  • 84.
  • 85.
  • 86.
  • 87.
  • 88.
  • 89.
  • 90.
  • 91.
  • 92.
  • 93.
  • 94.
  • 95.
  • 96.
  • 97.
  • 98.
  • 99.
  • 100.
  • 101.
  • 102.
  • 103.
  • 104.
  • 105.
  • 106.
  • 107.
  • 108.
  • 109.
  • 110.
  • 111.
  • 112.
  • 113.
  • 114.
  • 115.
  • 116.
  • 117.
  • 118.
  • 119.
  • 120.
  • 121.
  • 122.
  • 123.
  • 124.
  • 125.
  • 126.
  • 127.
  • 128.
  • 129.
  • 130.
  • 131.
  • 132.
  • 133.
  • 134.
  • 135.
  • 136.
  • 137.
  • 138.
  • 139.
  • 140.
  • 141.
  • 142.
  • 143.
  • 144.
  • 145.
  • 146.
  • 147.
  • 148.
  • 149.
  • 150.
  • 151.
  • 152.
  • 153.
  • 154.
  • 155.
  • 156.
  • 157.
  • 158.
  • 159.
  • 160.
  • 161.
  • 162.
  • 163.
  • 164.
  • 165.
  • 166.
  • 167.
  • 168.
  • 169.
  • 170.
  • 171.
  • 172.
  • 173.
  • 174.
  • 175.
  • 176.
  • 177.
  • 178.
  • 179.
  • 180.
  • 181.
  • 182.
  • 183.
  • 184.
  • 185.
  • 186.
  • 187.
  • 188.
  • 189.
  • 190.
  • 191.
  • 192.
  • 193.
  • 194.
  • 195.
  • 196.
  • 197.
  • 198.
  • 199.
  • 200.
  • 201.
  • 202.
  • 203.
  • 204.
  • 205.
  • 206.
  • 207.
  • 208.
  • 209.
  • 210.
  • 211.
  • 212.
  • 213.
  • 214.
  • 215.
  • 216.
  • 217.
  • 218.
  • 219.
  • 220.
  • 221.
  • 222.
  • 223.
  • 224.
  • 225.
  • 226.
  • 227.
  • 228.
  • 229.
  • 230.
  • 231.
  • 232.
  • 233.
  • 234.
  • 235.
  • 236.
  • 237.
  • 238.
  • 239.
  • 240.
  • 241.
  • 242.
  • 243.
  • 244.
  • 245.
  • 246.
  • 247.
  • 248.
  • 249.
  • 250.
  • 251.
  • 252.
  • 253.
  • 254.
  • 255.
  • 256.
  • 257.
  • 258.
  • 259.
  • 260.
  • 261.
  • 262.
  • 263.
  • 264.
  • 265.
  • 266.
  • 267.
  • 268.
  • 269.
  • 270.
  • 271.
  • 272.
  • 273.
  • 274.
  • 275.
  • 276.
  • 277.
  • 278.
  • 279.
  • 280.
  • 281.
  • 282.
  • 283.
  • 284.
  • 285.
  • 286.
  • 287.
  • 288.
  • 289.
  • 290.
  • 291.
  • 292.
  • 293.
  • 294.
  • 295.
  • 296.
  • 297.
  • 298.
  • 299.
  • 300.
  • 301.
  • 302.
  • 303.
  • 304.
  • 305.
  • 306.
  • 307.
  • 308.
  • 309.
  • 310.
  • 311.
  • 312.
  • 313.
  • 314.
  • 315.
  • 316.
  • 317.
  • 318.
  • 319.
  • 320.
  • 321.
  • 322.
  • 323.
  • 324.
  • 325.
  • 326.
  • 327.
  • 328.
  • 329.
  • 330.
  • 331.
  • 332.
  • 333.
  • 334.
  • 335.
  • 336.
  • 337.
  • 338.
  • 339.
  • 340.
  • 341.
  • 342.
  • 343.
  • 344.
  • 345.
  • 346.
  • 347.
  • 348.
  • 349.
  • 350.

作者:꧁执笔小白꧂