server/CSserver/Lib1/utils/MysqlUtil.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using Newtonsoft.Json.Linq;
using static System.Diagnostics.Trace;
using static System.String;

namespace CSharpUtil
{
    /// <summary>
    /// MySQL操作辅助类.
    /// </summary>
    public class MysqlUtil : IDisposable
    {

        /// <summary>
        /// MySQL字段详细信息,(用于GetFiledInfo方法返回值中取信息，可以用nameof来去掉硬编码字符串以防止拼写错误)
        /// </summary>
        public enum EFieldInfo
        {
            /// <summary>
            /// 字段类型 in .net
            /// </summary>
            Type,
            /// <summary>
            /// 是否可空
            /// </summary>
            Null,
            /// <summary>
            /// 索引类型
            /// </summary>
            Key,
            /// <summary>
            /// 默认值
            /// </summary>
            Default,
            /// <summary>
            /// 注释
            /// </summary>
            Comment,
            /// <summary>
            /// 字符集
            /// </summary>
            Collation,
            /// <summary>
            /// 额外（自增等）
            /// </summary>
            Extra,
            /// <summary>
            /// 权限
            /// </summary>
            Privileges
        }

        #region `    基础代码   `
        static MysqlUtil mInst;
        public static MysqlUtil Ins
        {
            get
            {
                if (null == mInst)
                {
                    mInst = new MysqlUtil();
                }
                return mInst;
            }
        }

        private MysqlUtil()
        {
            conn = new MySqlConnection(Config.Ins.mysql.ConnectionString);
            conn.Open();
            Assert(conn.State == ConnectionState.Open, "连接到数据库失败");
        }
  
        private MySqlConnection conn = null;
        /// <summary>
        /// 建立连接
        /// </summary>
        /// <param name="host"></param>
        /// <param name="port"></param>
        /// <param name="user"></param>
        /// <param name="pwd"></param>
        /// <param name="dbName"></param>
        public void Connect(string host, string port, string user, string pwd, string dbName)
        {
            var connStr = string.Format(
                              "server={0};port={1};user={2};password={3};database={4};Charset=utf8",
                              host, port, user, pwd, dbName);
            conn = new MySqlConnection(connStr);
            conn.Open();
            Assert(conn.State == ConnectionState.Open, "连接到数据库失败");
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public void Close()
        {
            conn.Close();
        }
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            conn.Dispose();
        }
        #endregion

        #region `    数据读    `

        /// <summary>
        /// 读取数据库表-数据操作部分逻辑需要参数中定义
        /// 一般情况下,使用其余八个读取函数即可,如果需要自己定制数据结构,可以通过在此函数回调中自行遍历整个数据表.
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="working">action to MysqlDataReader</param>
        public void ReadTabel(string tableName, Action<MySqlDataReader> working)
        {
            Assert(TableExist(tableName), "表 " + tableName + " 不存在！");
            var sql = string.Format("SELECT * FROM {0};", tableName);
            using (var cmd = new MySqlCommand(sql, conn))
            using (var rdr = cmd.ExecuteReader())
            {
                if (null != working) working.Invoke(rdr);
            }
        }


        /// <summary>
        /// 读取数据库表 -> 到一个JArray中, []
        /// </summary>
        /// <param name="tableName">表名</param>
        /// /// <param name="excludeFields">排除的字段名列表</param>
        /// <returns>JArray</returns>
        public JArray ReadTabelToJArray(string tableName, params string[] excludeFields)
        {
            var ex = new List<string>(excludeFields);
            var arr = new JArray();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var jobj = new JObject();

                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            jobj.Add(rdr.GetName(i), JToken.FromObject(rdr.GetValue(i)));
                        }
                    }
                    arr.Add(jobj);
                }
            });
            return arr;
        }

        /// <summary>
        /// 读取数据库表 -> 到一个JObject中, {}
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexFieldName">用做索引的字段名</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <returns>{}</returns>
        public JObject ReadTabelToJObject(string tableName, string indexFieldName, out bool isArray, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, indexFieldName),
                Format("在表[{0}]中未找到key:[{1}]", tableName, indexFieldName));
            bool _isArray = false;
            var ex = new List<string>(excludeFields);
            //ex.Add(indexFieldName);
            var obj = new JObject();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var jobj = new JObject();
                    var id = "";
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == indexFieldName)
                        {
                            id = rdr.GetValue(i).ToString();
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            object v = rdr.GetValue(i);

                            if (DBNull.Value == v)
                            {
                                var t = rdr.GetFieldType(i).Name;
                                switch (t)
                                {
                                    case "Int32":
                                        v = 0;
                                        break;
                                    case "String":
                                        v = "";
                                        break;
                                    case "Single":
                                        v = 0;
                                        break;
                                }
                            }
                            jobj.Add(rdr.GetName(i), JToken.FromObject(v));
                        }
                    }
                    if (obj[id] == null)
                    {  // 这段代码智能转换多值对应为[]
                        obj.Add(id, jobj);
                    }
                    else
                    {
                        _isArray = true;
                        var arr = obj[id] as JArray;
                        if (null != arr)
                        {
                            arr.Add(jobj);
                        }
                        else
                        {
                            obj[id] = new JArray() { obj[id], jobj };
                        }
                    }
                }

            });
            if (_isArray)
            {
                foreach (var o in obj.Properties())
                {
                    if (null == (o.Value as JArray))
                    {
                        obj[o.Name] = new JArray() { o.Value };
                    }
                }
            }
            isArray = _isArray;
            return obj;
        }

        /// <summary>
        /// 读取数据库表 -> 到一个JObject中, {k->[],k->[],...}
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexFieldName">用做索引的字段名</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <returns>{}</returns>
        public JObject ReadTabelToJObjectWithArrayValues(string tableName, string indexFieldName, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, indexFieldName),
                Format("在表[{0}]中未找到key:[{1}]", tableName, indexFieldName));

            var ex = new List<string>(excludeFields);
            //ex.Add(indexFieldName);
            var obj = new JObject();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var jobj = new JObject();
                    var id = "";
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == indexFieldName)
                        {
                            id = rdr.GetValue(i).ToString();
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            object v = rdr.GetValue(i);

                            if (DBNull.Value == v)
                            {
                                var t = rdr.GetFieldType(i).Name;
                                switch (t)
                                {
                                    case "Int32":
                                        v = 0;
                                        break;
                                    case "String":
                                        v = "";
                                        break;
                                    case "Single":
                                        v = 0;
                                        break;
                                }
                            }
                            jobj.Add(rdr.GetName(i), JToken.FromObject(v));
                        }
                    }

                    var arr = obj[id] as JArray;
                    if (null != arr)
                    {
                        arr.Add(jobj);
                    }
                    else
                    {
                        obj[id] = new JArray() { jobj };
                    }

                }

            });

            return obj;
        }

        /// <summary>
        /// 读取数据库表 -> 到一个简单的key=>value结构中. 即只取表中的两个字段
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="keyName">用作属性名称的字段</param>
        /// <param name="valueName">用作值的字段</param>
        /// <returns>{k:v,k:v,...}</returns>
        public JObject ReadTabelToSimpleJObject(string tableName, string keyName, string valueName)
        {
            Assert(HasFiledInTable(tableName, keyName),
                    Format("在表[{0}]中未找到key:[{1}]", tableName, keyName));
            Assert(HasFiledInTable(tableName, valueName),
                Format("在表[{0}]中未找到字段:[{1}]", tableName, valueName));
            var jobj = new JObject();

            ReadTabel(tableName, rdr =>
           {
               while (rdr.Read())
               {
                   var key = "";
                   Object value = null;
                   for (int i = 0; i < rdr.FieldCount; i++)
                   {
                       if (rdr.GetName(i) == keyName)
                       {
                           key = rdr.GetValue(i).ToString();
                       }
                       if (rdr.GetName(i) == valueName)
                       {
                           value = rdr.GetValue(i);
                       }
                   }
                   jobj.Add(key, JToken.FromObject(value));
               }
           });

            return jobj;
        }

        /// <summary>
        /// 读取数据库表 -> 到一个由两个字段内容拼接成的字符串作为索引的JObject中
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="firstFieldName">第一个字段</param>
        /// <param name="secondFieldName">第二个字段</param>
        /// <param name="excludeFields">排除的字段名...</param>
        /// <returns> {field(1)-field(2):{},...} </returns>
        public JObject ReadTabelToJObjectWithCombinedIndex(string tableName, string firstFieldName, string secondFieldName, out bool isArray, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, firstFieldName),
                 Format("在表[{0}]中未找到字段:[{1}]", tableName, firstFieldName));
            Assert(HasFiledInTable(tableName, secondFieldName),
                Format("在表[{0}]中未找到字段:[{1}]", tableName, secondFieldName));
            bool _isArray = false;
            var ex = new List<string>(excludeFields);
            //ex.Add(firstFieldName);
            //ex.Add(secondFieldName);
            var obj = new JObject();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var jobj = new JObject();
                    var firstId = "";
                    var secondId = "";
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == firstFieldName)
                        {
                            firstId = rdr.GetValue(i).ToString();
                        }
                        if (rdr.GetName(i) == secondFieldName)
                        {
                            secondId = rdr.GetValue(i).ToString();
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            Object v = rdr.GetValue(i);
                            if (DBNull.Value == v)
                            {
                                var t = rdr.GetFieldType(i).Name;
                                switch (t)
                                {
                                    case "Int32":
                                        v = 0;
                                        break;
                                    case "String":
                                        v = "";
                                        break;
                                    case "Single":
                                        v = 0;
                                        break;
                                }
                            }
                            jobj.Add(rdr.GetName(i), JToken.FromObject(v));
                        }
                    }
                    var key = firstId + "-" + secondId;
                    if (obj[key] == null)
                    {  // 防止重复
                        obj.Add(key, jobj);
                    }
                    else
                    {  // 已有对象 => 智能转换多值对应为[]
                        _isArray = true;
                        var arr = obj[key] as JArray;
                        if (arr != null)
                        { // 已经是jarray
                            arr.Add(jobj);
                        }
                        else
                        {    // 创建Jarray
                            obj[key] = new JArray() { obj[key], jobj };
                        }
                    }
                }
            });

            if (_isArray)
            {
                foreach (var o in obj.Properties())
                {
                    if (null == (o.Value as JArray))
                    {
                        obj[o.Name] = new JArray() { o.Value };
                    }
                }
            }
            isArray = _isArray;
            return obj;
        }


        /// <summary>
        /// 读取数据库表 -> 到一个{key11:{key1_21:{},key1_22:{},...},key12:{key2_21:{},key2_22:{},...},...}结构数据中
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexFieldName">用做索引的字段名</param>
        /// <param name="secondaryIndexName">用做次级索引的字段名</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <returns>拥有二级索引的json对象,二级节点是{}</returns>
        public JObject ReadTabelToJObjectWithSecondaryIndex(string tableName,
            string indexFieldName, string secondaryIndexName, out bool isArray, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, indexFieldName),
            Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
            Assert(HasFiledInTable(tableName, secondaryIndexName),
                Format("在表[{0}]中未找到字段:[{1}]", tableName, secondaryIndexName));
            var _isArray = false;
            var ex = new List<string>(excludeFields);
            //ex.Add(indexFieldName);
            //ex.Add(secondaryIndexName);
            var obj = new JObject();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var jobj = new JObject();
                    var id = "";
                    var secondId = "";
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == indexFieldName)
                        {
                            id = rdr.GetValue(i).ToString();
                        }
                        if (rdr.GetName(i) == secondaryIndexName)
                        {
                            secondId = rdr.GetValue(i).ToString();
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            Object v = rdr.GetValue(i);
                            if (DBNull.Value == v)
                            {
                                var t = rdr.GetFieldType(i).Name;
                                switch (t)
                                {
                                    case "Int32":
                                        v = 0;
                                        break;
                                    case "String":
                                        v = "";
                                        break;
                                    case "Single":
                                        v = 0;
                                        break;
                                }
                            }
                            jobj.Add(rdr.GetName(i), JToken.FromObject(v));
                        }
                    }

                    if (obj[id] == null)
                    {  // 没有建立一级对象
                        var o = new JObject();
                        o.Add(secondId, jobj);
                        obj.Add(id, o);
                    }
                    else
                    {  // 已有一级对象
                        var o = obj[id] as JObject;  // 一级对象
                        if (o[secondId] == null)
                        {  // 添加新值
                            o.Add(secondId, jobj);
                        }
                        else
                        {    // 已有对象 => 智能转换多值对应为[]
                            _isArray = true;
                            var arr = o[secondId] as JArray;
                            if (arr != null)
                            { // 已经是jarray
                                arr.Add(jobj);
                            }
                            else
                            {    // 创建Jarray
                                o[secondId] = new JArray() { o[secondId], jobj };
                            }
                        }
                    }
                }
            });
            if (_isArray)
            {
                foreach (var o in obj.Properties())
                {
                    foreach (var o2 in (o.Value as JObject).Properties())
                    {
                        if (!(obj[o.Name][o2.Name] is JArray))
                        {
                            obj[o.Name][o2.Name] = new JArray() { obj[o.Name][o2.Name] };
                        }
                    }
                }
            }
            isArray = _isArray;
            return obj;
        }



        /// <summary>
        /// 读取数据库表 -> 到一个行数组[{},{}]
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <returns>普通行数组[{},{},...]</returns>
        public Dictionary<string, object>[] ReadTabelToArray(string tableName, params string[] excludeFields)
        {
            var ex = new List<string>(excludeFields);
            var arr = new List<Dictionary<string, object>>();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var r = new Dictionary<string, object>(rdr.FieldCount - 1);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            r.Add(rdr.GetName(i), rdr.GetValue(i));
                        }
                    }
                    arr.Add(r);
                }
            });
            return arr.ToArray();
        }

        /// <summary>
        /// 读取数据库表 -> 到一个dic中 {"key":{}}
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexFieldName">仅限索引字段(1对多的情况暂不支持)</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <typeparam name="T">string, number</typeparam>
        /// <returns>嵌套的dictionary:{k:{k,v},k:{k:v},...}</returns>
        public Dictionary<T, Dictionary<string, object>> ReadTabelToDic_Dic<T>(string tableName, string indexFieldName, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, indexFieldName),
                Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
            var ex = new List<string>(excludeFields);
            //ex.Add(indexFieldName);
            var dic = new Dictionary<T, Dictionary<string, object>>();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var r = new Dictionary<string, object>(rdr.FieldCount - 1);
                    var t = default(T);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == indexFieldName)
                        {
                            t = (T)rdr.GetValue(i);
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            r.Add(rdr.GetName(i), rdr.GetValue(i));
                        }
                    }
                    dic.Add(t, r);
                }

            });
            return dic;
        }

        /// <summary>
        /// 读取数据库表 -> 到一个dic中 {"key":[]}
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="indexFieldName">索引字段名</param>
        /// <param name="excludeFields">排除的字段名列表</param>
        /// <typeparam name="T">string, number</typeparam>
        /// <returns>复杂嵌套dictionary:{k:[{},{},..],k:[{},{},..]..}</returns>
        public Dictionary<T, List<Dictionary<string, object>>> ReadTabelToDic_List<T>(string tableName, string indexFieldName, params string[] excludeFields)
        {
            Assert(HasFiledInTable(tableName, indexFieldName),
                Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
            var ex = new List<string>(excludeFields);
            //ex.Add(indexFieldName);
            var dic = new Dictionary<T, List<Dictionary<string, object>>>();
            ReadTabel(tableName, rdr =>
            {
                while (rdr.Read())
                {
                    var r = new Dictionary<string, object>(rdr.FieldCount - 1);
                    var t = default(T);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == indexFieldName)
                        {
                            t = (T)rdr.GetValue(i);
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            r.Add(rdr.GetName(i), rdr.GetValue(i));
                        }
                    }
                    if (dic.ContainsKey(t))
                    {
                        dic[t].Add(r);
                    }
                    else
                    {
                        dic.Add(t, new List<Dictionary<string, object>> { r });
                    }
                }
            });
            return dic;
        }

        #endregion

        #region `   数据写   `

        /// <summary>
        /// 执行非查询类的SQL语句,比如插入/更新/删除之类的.
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int ExecuteSqlNonQuery(string sql) {
            using var cmd = new MySqlCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }



        #endregion

        #region '    辅助方法    '

        /// <summary>
        /// 查询某表中是否存在指定字段
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public bool HasFiledInTable(string tableName, string fieldName)
        {
            Trace.Assert(TableExist(tableName), tableName + " 未找到");
            return GetTableFieldsInfo(tableName).ContainsKey(fieldName);
        }

        /// <summary>
        /// 检查指定的表格是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool TableExist(string tableName)
        {
            try
            {
                new MySqlCommand("select * from " + tableName + " limit 1;", conn).ExecuteNonQuery();
            }
            catch (MySqlException ex)
            {
                switch (ex.Number)
                {
                    case 1146:
                        return false;
                    default:
                        Debug.WriteLine(ex.Message);
                        return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        public void Query(string sql)
        {
            try
            {
                using (var cmd = new MySqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }
        /// <summary>
        /// 执行sql 查询
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <param name="working"></param>
        public void QueryTable(string sqlcmd, Action<MySqlDataReader> working)
        {
            try
            {
                using (var cmd = new MySqlCommand(sqlcmd, conn))
                using (MySqlDataReader rdr = cmd.ExecuteReader())
                {

                    if (null != working)
                    {
                        working.Invoke(rdr);
                    }
                }
            }
            catch (MySqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }

        /// <summary>
        /// 查询表格的字段信息  FieldName :
        ///     <Type(字段类型 in .net)>
        ///     <Collation(字符集及排序)>
        ///     <Null(是否可空)>
        ///     <Key(索引类型)>
        ///     <Default(默认值)>
        ///     <Extra(自增)>
        ///     <Privileges(权限)>
        ///     <Comment(注释)>
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public Dictionary<string, Dictionary<string, object>> GetTableFieldsInfo(string tableName)
        {
            var ex = new List<string>();
            var dic = new Dictionary<string, Dictionary<string, object>>();
            QueryTable(string.Format("show full fields from {0}", tableName), rdr =>
            {
                while (rdr.Read())
                {

                    var r = new Dictionary<string, object>();
                    var fieldName = rdr.GetString(0);
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (rdr.GetName(i) == "Field")
                        {
                            fieldName = rdr.GetString(i);
                        }
                        if (!ex.Contains(rdr.GetName(i)))
                        {
                            r.Add(rdr.GetName(i), rdr.GetValue(i));
                        }

                    }
                    dic.Add(fieldName, r);
                }
            });
            ReadTabel(tableName, rdr =>               // 用。net类型替换掉myslq 类型字符串
            {
                for (var i = 0; i < rdr.FieldCount; i++)
                {

                    var filedName = rdr.GetName(i);               // 字段名
                    var filedType = rdr.GetFieldType(i).Name;     // 字段类型
                    if (dic.ContainsKey(filedName))
                    {
                        dic[filedName][nameof(EFieldInfo.Type)] = filedType;
                    }
                }
            });
            return dic;
        }

        /// <summary>
        /// 查询表格的最后修改时间
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns>最后修改时间或者创建时间, 异常: 返回当前时间</returns>
        public DateTime TableLastModified(string tableName)
        {
            var sql = string.Format("SELECT UPDATE_TIME, CREATE_TIME " +
                      "FROM information_schema.tables " +
                      "where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'",
                          this.conn.Database, tableName);
            using (var cmd = new MySqlCommand(sql, conn))
            using (MySqlDataReader rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {  // 上次更新时间为null的情况下使用表的创建时间,都找不到值的情况下返回当前时间   
                    return rdr.IsDBNull(0) ? (rdr.IsDBNull(1) ? DateTime.Now : rdr.GetDateTime(1)) : rdr.GetDateTime(0);
                }
            }
            throw new Exception("没有找到对应的表");
        }
        #endregion



    }
}