ShakerControl/ShakerManger/SqlManger.cs

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Conventions;
using MongoDB.Driver;
using Newtonsoft.Json;
using Shaker.Model;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace ShakerManger
{

    public class Sql
    { 
        private Shaker.Model.SqlModel _SqlModel = new SqlModel();
        private static string PATH = System.AppDomain.CurrentDomain.BaseDirectory + "SqlConfig.json";
        private Sql()
        {
            if (System.IO.File.Exists(PATH))
            {
                var config = System.IO.File.ReadAllText(PATH);
                try
                {
                    _SqlModel = JsonConvert.DeserializeObject<SqlModel>(config) ?? new SqlModel();
                }
                catch
                {
                    _SqlModel = new SqlModel();
                }
            }
            _SqlModel ??= new SqlModel();
            System.IO.File.WriteAllText(PATH, JsonConvert.SerializeObject(_SqlModel, Formatting.Indented));
        }
        static Sql()
        {

        }
        public void Save(SqlModel model)
        {
            _SqlModel.IP = model.IP;
            _SqlModel.Port = model.Port;
            _SqlModel.UserName = model.UserName;
            _SqlModel.Password = model.Password;
            _SqlModel.Anonymous = model.Anonymous;
            _SqlModel.DBName = model.DBName;
            System.IO.File.WriteAllText(PATH, JsonConvert.SerializeObject(_SqlModel, Formatting.Indented));
        }
        public static Sql Default { get; } = new Sql();

        public bool IsConnected { get; set; } = false;


        public string IP { get=>_SqlModel.IP; set=>_SqlModel.IP = value; } 
        public string UserName { get=>_SqlModel.UserName; set=>_SqlModel.UserName =  value; } 
        public string Password { get=>_SqlModel.Password; set=>_SqlModel.Password = value; } 
        public int Port { get=>_SqlModel.Port; set=>_SqlModel.Port =value; }
        public string DBName { get=>_SqlModel.DBName; set=>_SqlModel.DBName =value; } 
        public string Connectstring => Anonymous ? $"mongodb://{IP}:{Port}" : $"mongodb://{UserName}:{Password}@{IP}:{Port}";
        public bool Anonymous { get=>_SqlModel.Anonymous; set=>_SqlModel.Anonymous =value; } 


        #region


        public bool TestConnection(string connectstring)
        {
            try
            {
                var client = new MongoClient(connectstring);
                client = null;
                return true;
            }catch
            {
                return false;
            }
        }
        public IMongoDatabase? Database => database;
        private MongoClient? client;
        private IMongoDatabase? database;
        public bool Connect()
        {
            if (IsConnected) return true;
            try
            {
                client = new MongoClient(Connectstring);
                database = client.GetDatabase(DBName);
                IsConnected = true;
                InitDataBase();
            }
            catch 
            {
                IsConnected = false;
            }
            return IsConnected;
        }

        public void Disconnect()
        {
            Close();
        }
        private void Close()
        {
            database = null;
            client = null;
            IsConnected = false;
        }
        private void InitDataBase()
        {
            InitCollection(new Shaker.Model.UserModel()
            {
                UserName = "admin",
                PassWord = Shaker.Model.GlobalVariable.MD5Encrypt32("123456"),
                UserType = UserType.SystemAdministrator,
            });
           typeof(Shaker.Model.LogModel).Assembly.GetTypes().Where(x => x.BaseType == typeof(Shaker.Model.TableBase) && !x.IsAbstract).Select(x => x.Name).ToList().ForEach(x => InitCollection(x));
        }
        private void InitCollection<T>(T value,string name = "") where T : Shaker.Model.TableBase
        {
            if (database == null) return;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            if (!database.ListCollectionNames().ToList().Any(x => x == name))
            {
                database.CreateCollection(name);
                database.GetCollection<T>(name).InsertOne(value);
            }
        }

        private void InitCollection<T>(string name="") where T : Shaker.Model.TableBase
        {
            if (database == null) return;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            if (!database.ListCollectionNames().ToList().Any(x => x == name))
            {
                database.CreateCollection(name);
            }
        }
        private void InitCollection(string name)
        {
            if (database == null || string.IsNullOrEmpty(name)) return;
            if (!database.ListCollectionNames().ToList().Any(x => x == name))
            {
                database.CreateCollection(name);
            }
        }
        public bool Insert<T>(T enrity,string name="") where T : Shaker.Model.TableBase
        {
            if (enrity == null || database == null) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                database.GetCollection<T>(name).InsertOne(enrity);
                return true;
            }
            catch
            {
                return false;
            }
        }
        public int Insert<T>(ICollection<T> enrities,string name="") where T : Shaker.Model.TableBase
        {
            if (enrities == null || enrities.Count == 0 || database == null) return 0;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                database.GetCollection<T>(name).InsertMany(enrities);
                return enrities.Count;
            }
            catch
            {
                return 0;
            }
        }
        private static string GetPropertyName<T, TValue>(Expression<Func<T, TValue>> expr) where T : Shaker.Model.TableBase
        {
            var rtn = "";
            if (expr.Body is UnaryExpression)
            {
                MemberInfo memberInfo = ((MemberExpression)((UnaryExpression)expr.Body).Operand).Member;
                switch (memberInfo.MemberType)
                {
                    case MemberTypes.Field:
                    case MemberTypes.Property:
                        rtn = memberInfo.Name;
                        break;
                    default:
                        rtn = "";
                        break;
                }
            }
            else if (expr.Body is MemberExpression)
            {
                MemberInfo memberInfo = ((MemberExpression)expr.Body).Member;
                switch (memberInfo.MemberType)
                {
                    case MemberTypes.Field:
                    case MemberTypes.Property:
                        rtn = memberInfo.Name;
                        break;
                    default:
                        rtn = "";
                        break;
                }
            }
            return rtn;
        }
        public T? FindFirst<T>(IEnumerable<Expression<Func<T, object>>>? fields = null,string name="") where T : Shaker.Model.TableBase
        {
            T? result = default;
            if (database == null) return result;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                if (fields == null || fields.Count() == 0)
                {
                    result = database.GetCollection<T>(name).AsQueryable().First();
                }
                else
                {
                    ProjectionDefinition<T>? proj = null;
                    foreach (var item in fields)
                    {
                        if (proj == null)
                        {
                            proj = Builders<T>.Projection.Include(GetPropertyName(item));
                        }
                        else
                        {
                            proj = proj.Include(GetPropertyName(item));
                        }
                    }
                    if (proj == null)
                    {
                        result = database.GetCollection<T>(name).AsQueryable().First();
                    }
                    else
                    {
                        result = database.GetCollection<T>(name).Find(x => true).Project<T>(proj).First();
                    }
                }
            }
            catch
            {

            }
            return result;
        }
        public T? FindFirst<T>(Expression<Func<T, bool>> expr, IEnumerable<Expression<Func<T, object>>>? fields = null,string name="") where T : Shaker.Model.TableBase
        {
            T? result = default;
            if (database == null) return result;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                if (fields == null || fields.Count() == 0)
                {
                    result = database.GetCollection<T>(name).AsQueryable().First(expr);
                }
                else
                {
                    ProjectionDefinition<T>? proj = null;
                    foreach (var item in fields)
                    {
                        if (proj == null)
                        {
                            proj = Builders<T>.Projection.Include(GetPropertyName(item));
                        }
                        else
                        {
                            proj = proj.Include(GetPropertyName(item));
                        }
                    }
                    if (proj == null)
                    {
                        result = database.GetCollection<T>(name).AsQueryable().First(expr);
                    }
                    else
                    {
                        result = database.GetCollection<T>(name).Find(expr).Project<T>(proj).First();
                    }
                }
            }
            catch
            {

            }
            return result;
        }

        public IEnumerable<T> Find<T>(Expression<Func<T, bool>> expr, IEnumerable<Expression<Func<T, object>>>? fields = null,string name = "") where T : Shaker.Model.TableBase
        {
            IEnumerable<T> result = new List<T>();
            if (database == null) return result;
            try
            {
                name = string.IsNullOrEmpty(name) ? typeof(T).Name:name;
                if (fields == null || fields.Count() == 0)
                {
                    result = database.GetCollection<T>(name).AsQueryable().Where(expr).ToList();
                }
                else
                {
                    ProjectionDefinition<T>? proj = null;
                    foreach (var item in fields)
                    {
                        if (proj == null)
                        {
                            proj = Builders<T>.Projection.Include(GetPropertyName(item));
                        }
                        else
                        {
                            proj = proj.Include(GetPropertyName(item));
                        }
                    }
                    if (proj == null)
                    {
                        result = database.GetCollection<T>(name).AsQueryable().Where(expr).ToList();
                    }
                    else
                    {
                        result = database.GetCollection<T>(name).Find(expr).Project<T>(proj).ToList();
                    }
                }
            }
            catch
            {

            }
            return result;
        }
        public IMongoCollection<T> GetCollection<T>(string name="") where T : Shaker.Model.TableBase
        {
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            if (database == null) return null;
            try
            {
                return database.GetCollection<T>(name);
            }
            catch
            { }
            return null;
        }
        public bool PushEach<T, TValue, TValue1>(Expression<Func<T, TValue>> expr, TValue oldvalue, Expression<Func<T, IEnumerable<TValue1>>> expr1, IEnumerable<TValue1> newvalue,string name="") where T : Shaker.Model.TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;

            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.PushEach(expr1, newvalue);
                var result = database.GetCollection<T>(name).UpdateOne(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool Push<T, TValue, TValue1>(Expression<Func<T, TValue>> expr, TValue oldvalue, Expression<Func<T, IEnumerable<TValue1>>> expr1, TValue1 newvalue,string name="") where T : Shaker.Model.TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.Push(expr1, newvalue);
                var result = database.GetCollection<T>(name).UpdateOne(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool UpDate<T, TValue, TValue1>(Expression<Func<T, TValue>> expr, TValue oldvalue, Expression<Func<T, TValue1>> expr1, TValue1 newvalue,string name="") where T : Shaker.Model.TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.Set(expr1, newvalue);
                var result = database.GetCollection<T>(name).UpdateOne(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool Replace<T, TValue>(Expression<Func<T, TValue>> expr, TValue field, T newvalue,string name="") where T : TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, field);
                var result = database.GetCollection<T>(newvalue == null ? name : newvalue.GetType().Name).ReplaceOne(filter, newvalue);
                return true;
            }
            catch { }
            return false;
        }
        public bool UpDate<T, TValue>(Expression<Func<T, TValue>> expr, TValue oldvalue, TValue newvalue,string name="") where T : TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.Set(expr, newvalue);
                database.GetCollection<T>(name).UpdateOne(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool UpDateMany<T, TValue, TValue1>(Expression<Func<T, TValue>> expr, TValue oldvalue, Expression<Func<T, TValue1>> expr1, TValue1 newvalue,string name="") where T : TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.Set(expr1, newvalue);
                database.GetCollection<T>(name).UpdateMany(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool UpDateMany<T, TValue>(Expression<Func<T, TValue>> expr, TValue oldvalue, TValue newvalue,string name="") where T : TableBase
        {
            if (database == null) return false;
            string fieldname = GetPropertyName(expr);
            if (string.IsNullOrEmpty(fieldname)) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, oldvalue);
                var update = Builders<T>.Update.Set(expr, newvalue);
                database.GetCollection<T>(name).UpdateMany(filter, update);
                return true;
            }
            catch { }
            return false;
        }
        public bool Delete<T, TValue>(Expression<Func<T, TValue>> expr, TValue value,string name="") where T : TableBase
        {
            if (database == null) return false;
            name = string.IsNullOrEmpty(name) ? typeof(T).Name : name;
            try
            {
                var filter = Builders<T>.Filter.Eq(expr, value);
                database.GetCollection<T>(name).DeleteMany(filter);
            }
            catch { }
            return false;
        }

        #endregion
    }
}