using MySql.Data.MySqlClient; using Newtonsoft.Json.Linq; using System; using System.Collections.Generic; using System.Data; using System.Diagnostics; using static System.Diagnostics.Trace; using static System.String; namespace BossServer.db { /// /// MySQL操作辅助类. /// public class MysqlUtil : IDisposable { /// /// MySQL字段详细信息,(用于GetFiledInfo方法返回值中取信息,可以用nameof来去掉硬编码字符串以防止拼写错误) /// public enum EFieldInfo { /// /// 字段类型 in .net /// Type, /// /// 是否可空 /// Null, /// /// 索引类型 /// Key, /// /// 默认值 /// Default, /// /// 注释 /// Comment, /// /// 字符集 /// Collation, /// /// 额外(自增等) /// Extra, /// /// 权限 /// 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; /// /// 建立连接 /// /// /// /// /// /// 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, "连接到数据库失败"); } /// /// 关闭连接 /// public void Close() { conn.Close(); } /// /// 释放资源 /// public void Dispose() { conn.Dispose(); } #endregion #region ` 数据读 ` /// /// 读取数据库表-数据操作部分逻辑需要参数中定义 /// 一般情况下,使用其余八个读取函数即可,如果需要自己定制数据结构,可以通过在此函数回调中自行遍历整个数据表. /// /// 表名 /// action to MysqlDataReader public void ReadTabel(string tableName, Action 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); } } /// /// 读取数据库表 -> 到一个JArray中, [] /// /// 表名 /// /// 排除的字段名列表 /// JArray public JArray ReadTabelToJArray(string tableName, params string[] excludeFields) { var ex = new List(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; } /// /// 读取数据库表 -> 到一个JObject中, {} /// /// 表名 /// 用做索引的字段名 /// 排除的字段名列表 /// {} 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(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; } /// /// 读取数据库表 -> 到一个JObject中, {k->[],k->[],...} /// /// 表名 /// 用做索引的字段名 /// 排除的字段名列表 /// {} public JObject ReadTabelToJObjectWithArrayValues(string tableName, string indexFieldName, params string[] excludeFields) { Assert(HasFiledInTable(tableName, indexFieldName), Format("在表[{0}]中未找到key:[{1}]", tableName, indexFieldName)); var ex = new List(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; } /// /// 读取数据库表 -> 到一个简单的key=>value结构中. 即只取表中的两个字段 /// /// 表名 /// 用作属性名称的字段 /// 用作值的字段 /// {k:v,k:v,...} 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; } /// /// 读取数据库表 -> 到一个由两个字段内容拼接成的字符串作为索引的JObject中 /// /// 表名称 /// 第一个字段 /// 第二个字段 /// 排除的字段名... /// {field(1)-field(2):{},...} 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(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; } /// /// 读取数据库表 -> 到一个{key11:{key1_21:{},key1_22:{},...},key12:{key2_21:{},key2_22:{},...},...}结构数据中 /// /// 表名 /// 用做索引的字段名 /// 用做次级索引的字段名 /// 排除的字段名列表 /// 拥有二级索引的json对象,二级节点是{} 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(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; } /// /// 读取数据库表 -> 到一个行数组[{},{}] /// /// 表名 /// 排除的字段名列表 /// 普通行数组[{},{},...] public Dictionary[] ReadTabelToArray(string tableName, params string[] excludeFields) { var ex = new List(excludeFields); var arr = new List>(); ReadTabel(tableName, rdr => { while (rdr.Read()) { var r = new Dictionary(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(); } /// /// 读取数据库表 -> 到一个dic中 {"key":{}} /// /// 表名 /// 仅限索引字段(1对多的情况暂不支持) /// 排除的字段名列表 /// string, number /// 嵌套的dictionary:{k:{k,v},k:{k:v},...} public Dictionary> ReadTabelToDic_Dic(string tableName, string indexFieldName, params string[] excludeFields) { Assert(HasFiledInTable(tableName, indexFieldName), Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName)); var ex = new List(excludeFields); //ex.Add(indexFieldName); var dic = new Dictionary>(); ReadTabel(tableName, rdr => { while (rdr.Read()) { var r = new Dictionary(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; } /// /// 读取数据库表 -> 到一个dic中 {"key":[]} /// /// 表名 /// 索引字段名 /// 排除的字段名列表 /// string, number /// 复杂嵌套dictionary:{k:[{},{},..],k:[{},{},..]..} public Dictionary>> ReadTabelToDic_List(string tableName, string indexFieldName, params string[] excludeFields) { Assert(HasFiledInTable(tableName, indexFieldName), Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName)); var ex = new List(excludeFields); //ex.Add(indexFieldName); var dic = new Dictionary>>(); ReadTabel(tableName, rdr => { while (rdr.Read()) { var r = new Dictionary(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> { r }); } } }); return dic; } #endregion #region ` 数据写 ` /// /// 执行非查询类的SQL语句,比如插入/更新/删除之类的. /// /// /// public int ExecuteSqlNonQuery(string sql) { using var cmd = new MySqlCommand(sql, conn); return cmd.ExecuteNonQuery(); } #endregion #region ' 辅助方法 ' /// /// 查询某表中是否存在指定字段 /// /// /// /// public bool HasFiledInTable(string tableName, string fieldName) { Trace.Assert(TableExist(tableName), tableName + " 未找到"); return GetTableFieldsInfo(tableName).ContainsKey(fieldName); } /// /// 检查指定的表格是否存在 /// /// /// 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; } /// /// 执行sql /// /// public void Query(string sql) { try { using (var cmd = new MySqlCommand(sql, conn)) { cmd.ExecuteNonQuery(); } } catch (MySqlException ex) { Debug.WriteLine(ex.Message); } } /// /// 执行sql 查询 /// /// /// public void QueryTable(string sqlcmd, Action 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); } } /// /// 查询表格的字段信息 FieldName : /// /// /// /// /// /// /// /// /// /// /// public Dictionary> GetTableFieldsInfo(string tableName) { var ex = new List(); var dic = new Dictionary>(); QueryTable(string.Format("show full fields from {0}", tableName), rdr => { while (rdr.Read()) { var r = new Dictionary(); 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; } /// /// 查询表格的最后修改时间 /// /// /// 最后修改时间或者创建时间, 异常: 返回当前时间 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 } }