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
}
}