MysqlUtil.cs 31 KB

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