123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847 |
- 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 Chat
- {
- /// <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
- }
- }
|