MysqlUtil.cs 28 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847
  1. using MySql.Data.MySqlClient;
  2. using Newtonsoft.Json.Linq;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Data;
  6. using System.Diagnostics;
  7. using static System.Diagnostics.Trace;
  8. using static System.String;
  9. /// <summary>
  10. /// MySQL操作辅助类.
  11. /// </summary>
  12. public class MysqlUtil : IDisposable
  13. {
  14. /// <summary>
  15. /// MySQL字段详细信息,(用于GetFiledInfo方法返回值中取信息,可以用nameof来去掉硬编码字符串以防止拼写错误)
  16. /// </summary>
  17. public enum EFieldInfo
  18. {
  19. /// <summary>
  20. /// 字段类型 in .net
  21. /// </summary>
  22. Type,
  23. /// <summary>
  24. /// 是否可空
  25. /// </summary>
  26. Null,
  27. /// <summary>
  28. /// 索引类型
  29. /// </summary>
  30. Key,
  31. /// <summary>
  32. /// 默认值
  33. /// </summary>
  34. Default,
  35. /// <summary>
  36. /// 注释
  37. /// </summary>
  38. Comment,
  39. /// <summary>
  40. /// 字符集
  41. /// </summary>
  42. Collation,
  43. /// <summary>
  44. /// 额外(自增等)
  45. /// </summary>
  46. Extra,
  47. /// <summary>
  48. /// 权限
  49. /// </summary>
  50. Privileges
  51. }
  52. #region ` 基础代码 `
  53. static MysqlUtil mInst;
  54. public static MysqlUtil Ins
  55. {
  56. get
  57. {
  58. if (null == mInst)
  59. {
  60. mInst = new MysqlUtil();
  61. }
  62. return mInst;
  63. }
  64. }
  65. private MysqlUtil()
  66. {
  67. conn = new MySqlConnection(Config.Ins.mysql.ConnectionString);
  68. conn.Open();
  69. Assert(conn.State == ConnectionState.Open, "连接到数据库失败");
  70. }
  71. private MySqlConnection conn = null;
  72. /// <summary>
  73. /// 建立连接
  74. /// </summary>
  75. /// <param name="host"></param>
  76. /// <param name="port"></param>
  77. /// <param name="user"></param>
  78. /// <param name="pwd"></param>
  79. /// <param name="dbName"></param>
  80. public void Connect(string host, string port, string user, string pwd, string dbName)
  81. {
  82. var connStr = string.Format(
  83. "server={0};port={1};user={2};password={3};database={4};Charset=utf8",
  84. host, port, user, pwd, dbName);
  85. conn = new MySqlConnection(connStr);
  86. conn.Open();
  87. Assert(conn.State == ConnectionState.Open, "连接到数据库失败");
  88. }
  89. /// <summary>
  90. /// 关闭连接
  91. /// </summary>
  92. public void Close()
  93. {
  94. conn.Close();
  95. }
  96. /// <summary>
  97. /// 释放资源
  98. /// </summary>
  99. public void Dispose()
  100. {
  101. conn.Dispose();
  102. }
  103. #endregion
  104. #region ` 数据读 `
  105. /// <summary>
  106. /// 读取数据库表-数据操作部分逻辑需要参数中定义
  107. /// 一般情况下,使用其余八个读取函数即可,如果需要自己定制数据结构,可以通过在此函数回调中自行遍历整个数据表.
  108. /// </summary>
  109. /// <param name="tableName">表名</param>
  110. /// <param name="working">action to MysqlDataReader</param>
  111. public void ReadTabel(string tableName, Action<MySqlDataReader> working)
  112. {
  113. Assert(TableExist(tableName), "表 " + tableName + " 不存在!");
  114. var sql = string.Format("SELECT * FROM {0};", tableName);
  115. using (var cmd = new MySqlCommand(sql, conn))
  116. using (var rdr = cmd.ExecuteReader())
  117. {
  118. if (null != working) working.Invoke(rdr);
  119. }
  120. }
  121. /// <summary>
  122. /// 读取数据库表 -> 到一个JArray中, []
  123. /// </summary>
  124. /// <param name="tableName">表名</param>
  125. /// /// <param name="excludeFields">排除的字段名列表</param>
  126. /// <returns>JArray</returns>
  127. public JArray ReadTabelToJArray(string tableName, params string[] excludeFields)
  128. {
  129. var ex = new List<string>(excludeFields);
  130. var arr = new JArray();
  131. ReadTabel(tableName, rdr =>
  132. {
  133. while (rdr.Read())
  134. {
  135. var jobj = new JObject();
  136. for (int i = 0; i < rdr.FieldCount; i++)
  137. {
  138. if (!ex.Contains(rdr.GetName(i)))
  139. {
  140. jobj.Add(rdr.GetName(i), JToken.FromObject(rdr.GetValue(i)));
  141. }
  142. }
  143. arr.Add(jobj);
  144. }
  145. });
  146. return arr;
  147. }
  148. /// <summary>
  149. /// 读取数据库表 -> 到一个JObject中, {}
  150. /// </summary>
  151. /// <param name="tableName">表名</param>
  152. /// <param name="indexFieldName">用做索引的字段名</param>
  153. /// <param name="excludeFields">排除的字段名列表</param>
  154. /// <returns>{}</returns>
  155. public JObject ReadTabelToJObject(string tableName, string indexFieldName, out bool isArray, params string[] excludeFields)
  156. {
  157. Assert(HasFiledInTable(tableName, indexFieldName),
  158. Format("在表[{0}]中未找到key:[{1}]", tableName, indexFieldName));
  159. bool _isArray = false;
  160. var ex = new List<string>(excludeFields);
  161. //ex.Add(indexFieldName);
  162. var obj = new JObject();
  163. ReadTabel(tableName, rdr =>
  164. {
  165. while (rdr.Read())
  166. {
  167. var jobj = new JObject();
  168. var id = "";
  169. for (int i = 0; i < rdr.FieldCount; i++)
  170. {
  171. if (rdr.GetName(i) == indexFieldName)
  172. {
  173. id = rdr.GetValue(i).ToString();
  174. }
  175. if (!ex.Contains(rdr.GetName(i)))
  176. {
  177. object v = rdr.GetValue(i);
  178. if (DBNull.Value == v)
  179. {
  180. var t = rdr.GetFieldType(i).Name;
  181. switch (t)
  182. {
  183. case "Int32":
  184. v = 0;
  185. break;
  186. case "String":
  187. v = "";
  188. break;
  189. case "Single":
  190. v = 0;
  191. break;
  192. }
  193. }
  194. jobj.Add(rdr.GetName(i), JToken.FromObject(v));
  195. }
  196. }
  197. if (obj[id] == null)
  198. { // 这段代码智能转换多值对应为[]
  199. obj.Add(id, jobj);
  200. }
  201. else
  202. {
  203. _isArray = true;
  204. var arr = obj[id] as JArray;
  205. if (null != arr)
  206. {
  207. arr.Add(jobj);
  208. }
  209. else
  210. {
  211. obj[id] = new JArray() { obj[id], jobj };
  212. }
  213. }
  214. }
  215. });
  216. if (_isArray)
  217. {
  218. foreach (var o in obj.Properties())
  219. {
  220. if (null == (o.Value as JArray))
  221. {
  222. obj[o.Name] = new JArray() { o.Value };
  223. }
  224. }
  225. }
  226. isArray = _isArray;
  227. return obj;
  228. }
  229. /// <summary>
  230. /// 读取数据库表 -> 到一个JObject中, {k->[],k->[],...}
  231. /// </summary>
  232. /// <param name="tableName">表名</param>
  233. /// <param name="indexFieldName">用做索引的字段名</param>
  234. /// <param name="excludeFields">排除的字段名列表</param>
  235. /// <returns>{}</returns>
  236. public JObject ReadTabelToJObjectWithArrayValues(string tableName, string indexFieldName, params string[] excludeFields)
  237. {
  238. Assert(HasFiledInTable(tableName, indexFieldName),
  239. Format("在表[{0}]中未找到key:[{1}]", tableName, indexFieldName));
  240. var ex = new List<string>(excludeFields);
  241. //ex.Add(indexFieldName);
  242. var obj = new JObject();
  243. ReadTabel(tableName, rdr =>
  244. {
  245. while (rdr.Read())
  246. {
  247. var jobj = new JObject();
  248. var id = "";
  249. for (int i = 0; i < rdr.FieldCount; i++)
  250. {
  251. if (rdr.GetName(i) == indexFieldName)
  252. {
  253. id = rdr.GetValue(i).ToString();
  254. }
  255. if (!ex.Contains(rdr.GetName(i)))
  256. {
  257. object v = rdr.GetValue(i);
  258. if (DBNull.Value == v)
  259. {
  260. var t = rdr.GetFieldType(i).Name;
  261. switch (t)
  262. {
  263. case "Int32":
  264. v = 0;
  265. break;
  266. case "String":
  267. v = "";
  268. break;
  269. case "Single":
  270. v = 0;
  271. break;
  272. }
  273. }
  274. jobj.Add(rdr.GetName(i), JToken.FromObject(v));
  275. }
  276. }
  277. var arr = obj[id] as JArray;
  278. if (null != arr)
  279. {
  280. arr.Add(jobj);
  281. }
  282. else
  283. {
  284. obj[id] = new JArray() { jobj };
  285. }
  286. }
  287. });
  288. return obj;
  289. }
  290. /// <summary>
  291. /// 读取数据库表 -> 到一个简单的key=>value结构中. 即只取表中的两个字段
  292. /// </summary>
  293. /// <param name="tableName">表名</param>
  294. /// <param name="keyName">用作属性名称的字段</param>
  295. /// <param name="valueName">用作值的字段</param>
  296. /// <returns>{k:v,k:v,...}</returns>
  297. public JObject ReadTabelToSimpleJObject(string tableName, string keyName, string valueName)
  298. {
  299. Assert(HasFiledInTable(tableName, keyName),
  300. Format("在表[{0}]中未找到key:[{1}]", tableName, keyName));
  301. Assert(HasFiledInTable(tableName, valueName),
  302. Format("在表[{0}]中未找到字段:[{1}]", tableName, valueName));
  303. var jobj = new JObject();
  304. ReadTabel(tableName, rdr =>
  305. {
  306. while (rdr.Read())
  307. {
  308. var key = "";
  309. Object value = null;
  310. for (int i = 0; i < rdr.FieldCount; i++)
  311. {
  312. if (rdr.GetName(i) == keyName)
  313. {
  314. key = rdr.GetValue(i).ToString();
  315. }
  316. if (rdr.GetName(i) == valueName)
  317. {
  318. value = rdr.GetValue(i);
  319. }
  320. }
  321. jobj.Add(key, JToken.FromObject(value));
  322. }
  323. });
  324. return jobj;
  325. }
  326. /// <summary>
  327. /// 读取数据库表 -> 到一个由两个字段内容拼接成的字符串作为索引的JObject中
  328. /// </summary>
  329. /// <param name="tableName">表名称</param>
  330. /// <param name="firstFieldName">第一个字段</param>
  331. /// <param name="secondFieldName">第二个字段</param>
  332. /// <param name="excludeFields">排除的字段名...</param>
  333. /// <returns> {field(1)-field(2):{},...} </returns>
  334. public JObject ReadTabelToJObjectWithCombinedIndex(string tableName, string firstFieldName, string secondFieldName, out bool isArray, params string[] excludeFields)
  335. {
  336. Assert(HasFiledInTable(tableName, firstFieldName),
  337. Format("在表[{0}]中未找到字段:[{1}]", tableName, firstFieldName));
  338. Assert(HasFiledInTable(tableName, secondFieldName),
  339. Format("在表[{0}]中未找到字段:[{1}]", tableName, secondFieldName));
  340. bool _isArray = false;
  341. var ex = new List<string>(excludeFields);
  342. //ex.Add(firstFieldName);
  343. //ex.Add(secondFieldName);
  344. var obj = new JObject();
  345. ReadTabel(tableName, rdr =>
  346. {
  347. while (rdr.Read())
  348. {
  349. var jobj = new JObject();
  350. var firstId = "";
  351. var secondId = "";
  352. for (int i = 0; i < rdr.FieldCount; i++)
  353. {
  354. if (rdr.GetName(i) == firstFieldName)
  355. {
  356. firstId = rdr.GetValue(i).ToString();
  357. }
  358. if (rdr.GetName(i) == secondFieldName)
  359. {
  360. secondId = rdr.GetValue(i).ToString();
  361. }
  362. if (!ex.Contains(rdr.GetName(i)))
  363. {
  364. Object v = rdr.GetValue(i);
  365. if (DBNull.Value == v)
  366. {
  367. var t = rdr.GetFieldType(i).Name;
  368. switch (t)
  369. {
  370. case "Int32":
  371. v = 0;
  372. break;
  373. case "String":
  374. v = "";
  375. break;
  376. case "Single":
  377. v = 0;
  378. break;
  379. }
  380. }
  381. jobj.Add(rdr.GetName(i), JToken.FromObject(v));
  382. }
  383. }
  384. var key = firstId + "-" + secondId;
  385. if (obj[key] == null)
  386. { // 防止重复
  387. obj.Add(key, jobj);
  388. }
  389. else
  390. { // 已有对象 => 智能转换多值对应为[]
  391. _isArray = true;
  392. var arr = obj[key] as JArray;
  393. if (arr != null)
  394. { // 已经是jarray
  395. arr.Add(jobj);
  396. }
  397. else
  398. { // 创建Jarray
  399. obj[key] = new JArray() { obj[key], jobj };
  400. }
  401. }
  402. }
  403. });
  404. if (_isArray)
  405. {
  406. foreach (var o in obj.Properties())
  407. {
  408. if (null == (o.Value as JArray))
  409. {
  410. obj[o.Name] = new JArray() { o.Value };
  411. }
  412. }
  413. }
  414. isArray = _isArray;
  415. return obj;
  416. }
  417. /// <summary>
  418. /// 读取数据库表 -> 到一个{key11:{key1_21:{},key1_22:{},...},key12:{key2_21:{},key2_22:{},...},...}结构数据中
  419. /// </summary>
  420. /// <param name="tableName">表名</param>
  421. /// <param name="indexFieldName">用做索引的字段名</param>
  422. /// <param name="secondaryIndexName">用做次级索引的字段名</param>
  423. /// <param name="excludeFields">排除的字段名列表</param>
  424. /// <returns>拥有二级索引的json对象,二级节点是{}</returns>
  425. public JObject ReadTabelToJObjectWithSecondaryIndex(string tableName,
  426. string indexFieldName, string secondaryIndexName, out bool isArray, params string[] excludeFields)
  427. {
  428. Assert(HasFiledInTable(tableName, indexFieldName),
  429. Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
  430. Assert(HasFiledInTable(tableName, secondaryIndexName),
  431. Format("在表[{0}]中未找到字段:[{1}]", tableName, secondaryIndexName));
  432. var _isArray = false;
  433. var ex = new List<string>(excludeFields);
  434. //ex.Add(indexFieldName);
  435. //ex.Add(secondaryIndexName);
  436. var obj = new JObject();
  437. ReadTabel(tableName, rdr =>
  438. {
  439. while (rdr.Read())
  440. {
  441. var jobj = new JObject();
  442. var id = "";
  443. var secondId = "";
  444. for (int i = 0; i < rdr.FieldCount; i++)
  445. {
  446. if (rdr.GetName(i) == indexFieldName)
  447. {
  448. id = rdr.GetValue(i).ToString();
  449. }
  450. if (rdr.GetName(i) == secondaryIndexName)
  451. {
  452. secondId = rdr.GetValue(i).ToString();
  453. }
  454. if (!ex.Contains(rdr.GetName(i)))
  455. {
  456. Object v = rdr.GetValue(i);
  457. if (DBNull.Value == v)
  458. {
  459. var t = rdr.GetFieldType(i).Name;
  460. switch (t)
  461. {
  462. case "Int32":
  463. v = 0;
  464. break;
  465. case "String":
  466. v = "";
  467. break;
  468. case "Single":
  469. v = 0;
  470. break;
  471. }
  472. }
  473. jobj.Add(rdr.GetName(i), JToken.FromObject(v));
  474. }
  475. }
  476. if (obj[id] == null)
  477. { // 没有建立一级对象
  478. var o = new JObject();
  479. o.Add(secondId, jobj);
  480. obj.Add(id, o);
  481. }
  482. else
  483. { // 已有一级对象
  484. var o = obj[id] as JObject; // 一级对象
  485. if (o[secondId] == null)
  486. { // 添加新值
  487. o.Add(secondId, jobj);
  488. }
  489. else
  490. { // 已有对象 => 智能转换多值对应为[]
  491. _isArray = true;
  492. var arr = o[secondId] as JArray;
  493. if (arr != null)
  494. { // 已经是jarray
  495. arr.Add(jobj);
  496. }
  497. else
  498. { // 创建Jarray
  499. o[secondId] = new JArray() { o[secondId], jobj };
  500. }
  501. }
  502. }
  503. }
  504. });
  505. if (_isArray)
  506. {
  507. foreach (var o in obj.Properties())
  508. {
  509. foreach (var o2 in (o.Value as JObject).Properties())
  510. {
  511. if (!(obj[o.Name][o2.Name] is JArray))
  512. {
  513. obj[o.Name][o2.Name] = new JArray() { obj[o.Name][o2.Name] };
  514. }
  515. }
  516. }
  517. }
  518. isArray = _isArray;
  519. return obj;
  520. }
  521. /// <summary>
  522. /// 读取数据库表 -> 到一个行数组[{},{}]
  523. /// </summary>
  524. /// <param name="tableName">表名</param>
  525. /// <param name="excludeFields">排除的字段名列表</param>
  526. /// <returns>普通行数组[{},{},...]</returns>
  527. public Dictionary<string, object>[] ReadTabelToArray(string tableName, params string[] excludeFields)
  528. {
  529. var ex = new List<string>(excludeFields);
  530. var arr = new List<Dictionary<string, object>>();
  531. ReadTabel(tableName, rdr =>
  532. {
  533. while (rdr.Read())
  534. {
  535. var r = new Dictionary<string, object>(rdr.FieldCount - 1);
  536. for (int i = 0; i < rdr.FieldCount; i++)
  537. {
  538. if (!ex.Contains(rdr.GetName(i)))
  539. {
  540. r.Add(rdr.GetName(i), rdr.GetValue(i));
  541. }
  542. }
  543. arr.Add(r);
  544. }
  545. });
  546. return arr.ToArray();
  547. }
  548. /// <summary>
  549. /// 读取数据库表 -> 到一个dic中 {"key":{}}
  550. /// </summary>
  551. /// <param name="tableName">表名</param>
  552. /// <param name="indexFieldName">仅限索引字段(1对多的情况暂不支持)</param>
  553. /// <param name="excludeFields">排除的字段名列表</param>
  554. /// <typeparam name="T">string, number</typeparam>
  555. /// <returns>嵌套的dictionary:{k:{k,v},k:{k:v},...}</returns>
  556. public Dictionary<T, Dictionary<string, object>> ReadTabelToDic_Dic<T>(string tableName, string indexFieldName, params string[] excludeFields)
  557. {
  558. Assert(HasFiledInTable(tableName, indexFieldName),
  559. Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
  560. var ex = new List<string>(excludeFields);
  561. //ex.Add(indexFieldName);
  562. var dic = new Dictionary<T, Dictionary<string, object>>();
  563. ReadTabel(tableName, rdr =>
  564. {
  565. while (rdr.Read())
  566. {
  567. var r = new Dictionary<string, object>(rdr.FieldCount - 1);
  568. var t = default(T);
  569. for (int i = 0; i < rdr.FieldCount; i++)
  570. {
  571. if (rdr.GetName(i) == indexFieldName)
  572. {
  573. t = (T)rdr.GetValue(i);
  574. }
  575. if (!ex.Contains(rdr.GetName(i)))
  576. {
  577. r.Add(rdr.GetName(i), rdr.GetValue(i));
  578. }
  579. }
  580. dic.Add(t, r);
  581. }
  582. });
  583. return dic;
  584. }
  585. /// <summary>
  586. /// 读取数据库表 -> 到一个dic中 {"key":[]}
  587. /// </summary>
  588. /// <param name="tableName">表名</param>
  589. /// <param name="indexFieldName">索引字段名</param>
  590. /// <param name="excludeFields">排除的字段名列表</param>
  591. /// <typeparam name="T">string, number</typeparam>
  592. /// <returns>复杂嵌套dictionary:{k:[{},{},..],k:[{},{},..]..}</returns>
  593. public Dictionary<T, List<Dictionary<string, object>>> ReadTabelToDic_List<T>(string tableName, string indexFieldName, params string[] excludeFields)
  594. {
  595. Assert(HasFiledInTable(tableName, indexFieldName),
  596. Format("在表[{0}]中未找到字段:[{1}]", tableName, indexFieldName));
  597. var ex = new List<string>(excludeFields);
  598. //ex.Add(indexFieldName);
  599. var dic = new Dictionary<T, List<Dictionary<string, object>>>();
  600. ReadTabel(tableName, rdr =>
  601. {
  602. while (rdr.Read())
  603. {
  604. var r = new Dictionary<string, object>(rdr.FieldCount - 1);
  605. var t = default(T);
  606. for (int i = 0; i < rdr.FieldCount; i++)
  607. {
  608. if (rdr.GetName(i) == indexFieldName)
  609. {
  610. t = (T)rdr.GetValue(i);
  611. }
  612. if (!ex.Contains(rdr.GetName(i)))
  613. {
  614. r.Add(rdr.GetName(i), rdr.GetValue(i));
  615. }
  616. }
  617. if (dic.ContainsKey(t))
  618. {
  619. dic[t].Add(r);
  620. }
  621. else
  622. {
  623. dic.Add(t, new List<Dictionary<string, object>> { r });
  624. }
  625. }
  626. });
  627. return dic;
  628. }
  629. #endregion
  630. #region ` 数据写 `
  631. /// <summary>
  632. /// 执行非查询类的SQL语句,比如插入/更新/删除之类的.
  633. /// </summary>
  634. /// <param name="sql"></param>
  635. /// <returns></returns>
  636. public int ExecuteSqlNonQuery(string sql)
  637. {
  638. using var cmd = new MySqlCommand(sql, conn);
  639. return cmd.ExecuteNonQuery();
  640. }
  641. #endregion
  642. #region ' 辅助方法 '
  643. /// <summary>
  644. /// 查询某表中是否存在指定字段
  645. /// </summary>
  646. /// <param name="tableName"></param>
  647. /// <param name="fieldName"></param>
  648. /// <returns></returns>
  649. public bool HasFiledInTable(string tableName, string fieldName)
  650. {
  651. Trace.Assert(TableExist(tableName), tableName + " 未找到");
  652. return GetTableFieldsInfo(tableName).ContainsKey(fieldName);
  653. }
  654. /// <summary>
  655. /// 检查指定的表格是否存在
  656. /// </summary>
  657. /// <param name="tableName"></param>
  658. /// <returns></returns>
  659. public bool TableExist(string tableName)
  660. {
  661. try
  662. {
  663. new MySqlCommand("select * from " + tableName + " limit 1;", conn).ExecuteNonQuery();
  664. }
  665. catch (MySqlException ex)
  666. {
  667. switch (ex.Number)
  668. {
  669. case 1146:
  670. return false;
  671. default:
  672. Debug.WriteLine(ex.Message);
  673. return false;
  674. }
  675. }
  676. return true;
  677. }
  678. /// <summary>
  679. /// 执行sql
  680. /// </summary>
  681. /// <param name="sql"></param>
  682. public void Query(string sql)
  683. {
  684. try
  685. {
  686. using (var cmd = new MySqlCommand(sql, conn))
  687. {
  688. cmd.ExecuteNonQuery();
  689. }
  690. }
  691. catch (MySqlException ex)
  692. {
  693. Debug.WriteLine(ex.Message);
  694. }
  695. }
  696. /// <summary>
  697. /// 执行sql 查询
  698. /// </summary>
  699. /// <param name="sqlcmd"></param>
  700. /// <param name="working"></param>
  701. public void QueryTable(string sqlcmd, Action<MySqlDataReader> working)
  702. {
  703. try
  704. {
  705. using (var cmd = new MySqlCommand(sqlcmd, conn))
  706. using (MySqlDataReader rdr = cmd.ExecuteReader())
  707. {
  708. if (null != working)
  709. {
  710. working.Invoke(rdr);
  711. }
  712. }
  713. }
  714. catch (MySqlException ex)
  715. {
  716. Debug.WriteLine(ex.Message);
  717. }
  718. }
  719. /// <summary>
  720. /// 查询表格的字段信息 FieldName :
  721. /// <Type(字段类型 in .net)>
  722. /// <Collation(字符集及排序)>
  723. /// <Null(是否可空)>
  724. /// <Key(索引类型)>
  725. /// <Default(默认值)>
  726. /// <Extra(自增)>
  727. /// <Privileges(权限)>
  728. /// <Comment(注释)>
  729. /// </summary>
  730. /// <param name="tableName"></param>
  731. /// <returns></returns>
  732. public Dictionary<string, Dictionary<string, object>> GetTableFieldsInfo(string tableName)
  733. {
  734. var ex = new List<string>();
  735. var dic = new Dictionary<string, Dictionary<string, object>>();
  736. QueryTable(string.Format("show full fields from {0}", tableName), rdr =>
  737. {
  738. while (rdr.Read())
  739. {
  740. var r = new Dictionary<string, object>();
  741. var fieldName = rdr.GetString(0);
  742. for (int i = 0; i < rdr.FieldCount; i++)
  743. {
  744. if (rdr.GetName(i) == "Field")
  745. {
  746. fieldName = rdr.GetString(i);
  747. }
  748. if (!ex.Contains(rdr.GetName(i)))
  749. {
  750. r.Add(rdr.GetName(i), rdr.GetValue(i));
  751. }
  752. }
  753. dic.Add(fieldName, r);
  754. }
  755. });
  756. ReadTabel(tableName, rdr => // 用。net类型替换掉myslq 类型字符串
  757. {
  758. for (var i = 0; i < rdr.FieldCount; i++)
  759. {
  760. var filedName = rdr.GetName(i); // 字段名
  761. var filedType = rdr.GetFieldType(i).Name; // 字段类型
  762. if (dic.ContainsKey(filedName))
  763. {
  764. dic[filedName][nameof(EFieldInfo.Type)] = filedType;
  765. }
  766. }
  767. });
  768. return dic;
  769. }
  770. /// <summary>
  771. /// 查询表格的最后修改时间
  772. /// </summary>
  773. /// <param name="tableName"></param>
  774. /// <returns>最后修改时间或者创建时间, 异常: 返回当前时间</returns>
  775. public DateTime TableLastModified(string tableName)
  776. {
  777. var sql = string.Format("SELECT UPDATE_TIME, CREATE_TIME " +
  778. "FROM information_schema.tables " +
  779. "where TABLE_SCHEMA='{0}' and TABLE_NAME='{1}'",
  780. this.conn.Database, tableName);
  781. using (var cmd = new MySqlCommand(sql, conn))
  782. using (MySqlDataReader rdr = cmd.ExecuteReader())
  783. {
  784. if (rdr.Read())
  785. { // 上次更新时间为null的情况下使用表的创建时间,都找不到值的情况下返回当前时间
  786. return rdr.IsDBNull(0) ? (rdr.IsDBNull(1) ? DateTime.Now : rdr.GetDateTime(1)) : rdr.GetDateTime(0);
  787. }
  788. }
  789. throw new Exception("没有找到对应的表");
  790. }
  791. #endregion
  792. }