MySQLConnector.cs 5.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Threading.Tasks;
  4. using HyperCube.Models;
  5. using MySql.Data.MySqlClient;
  6. namespace HyperCube
  7. {
  8. public class MySQLConnector
  9. {
  10. private MySQLConnector(){ }
  11. private readonly string Server = "dmatter.net";
  12. private readonly string DatabaseName = "documents";
  13. private readonly string UserName = "promsystem";
  14. private readonly string Password = "PrmSystem212";
  15. MySqlCommand SQLcom;
  16. public MySqlConnection Connection { get; set; }
  17. private static MySQLConnector _instance = null;
  18. public static MySQLConnector Instance()
  19. {
  20. if (_instance == null)
  21. _instance = new MySQLConnector();
  22. return _instance;
  23. }
  24. public bool IsConnect()
  25. {
  26. if (Connection == null)
  27. {
  28. string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
  29. Connection = new MySqlConnection(connstring);
  30. Connection.Open();
  31. }
  32. else if (Connection.State != System.Data.ConnectionState.Open)
  33. Connection.Open();
  34. return true;
  35. }
  36. public long SQLInsert(string sql)
  37. {
  38. long lastID = 0;
  39. bool connected = IsConnect();
  40. if (connected)
  41. {
  42. SQLcom = new(sql, Connection);
  43. SQLcom.ExecuteNonQuery();
  44. SQLcom.Dispose();
  45. lastID = SQLcom.LastInsertedId;
  46. }
  47. else
  48. Console.WriteLine("Not connected to DB.");
  49. return lastID;
  50. }
  51. public async Task<Dictionary<int, SmartContract>> SQLSelectContracts()
  52. {
  53. string sql = "select * from smart_contracts";
  54. Dictionary<int, SmartContract> contracts = new();
  55. bool connected = IsConnect();
  56. if (connected)
  57. {
  58. SQLcom = new(sql, Connection);
  59. MySqlDataReader rdr = SQLcom.ExecuteReader();
  60. while (rdr.Read())
  61. {
  62. Models.SmartContract contract = new();
  63. contract.id = rdr.GetInt32(0);
  64. contract.Code = rdr.GetString(1);
  65. contract.ByteCode = rdr.GetString(2);
  66. contract.Name = rdr.GetString(3);
  67. if (!contracts.ContainsKey(contract.id))
  68. contracts.Add(contract.id, contract);
  69. }
  70. await Task.WhenAll();
  71. rdr.Close();
  72. return contracts;
  73. }
  74. else
  75. Console.WriteLine("Not connected to DB.");
  76. return null;
  77. }
  78. public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
  79. {
  80. Dictionary<int, ArticleModel> articleModels = new();
  81. Models.ArticleModel articleModel;
  82. bool connected = IsConnect();
  83. if (connected)
  84. {
  85. SQLcom = new(sql, Connection);
  86. MySqlDataReader rdr = SQLcom.ExecuteReader();
  87. while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
  88. {
  89. articleModel = new();
  90. int id = rdr.GetInt32(0);
  91. articleModel.Filename = rdr.GetString(1);
  92. articleModel.Name = rdr.GetString(2);
  93. articleModel.PublishDate = rdr.GetDateTime(3);
  94. articleModel.Authors = rdr.GetString(4);
  95. articleModel.Status = (ArticleStatus)rdr.GetInt32(5);
  96. if (articleModels.ContainsKey(id))
  97. continue;
  98. articleModels.Add(id, articleModel);
  99. }
  100. await Task.WhenAll();
  101. rdr.Close();
  102. return articleModels;
  103. }
  104. else
  105. Console.WriteLine("Not connected to DB.");
  106. return null;
  107. }
  108. public async Task<ArticleModel> SQLSelectArticle(string sql)
  109. {
  110. ArticleModel articleModel = new();
  111. bool connected = IsConnect();
  112. if (connected)
  113. {
  114. SQLcom = new(sql, Connection);
  115. MySqlDataReader rdr = SQLcom.ExecuteReader();
  116. while (rdr.Read()) ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status
  117. {
  118. articleModel.Filename = rdr.GetString(1);
  119. articleModel.Name = rdr.GetString(2);
  120. articleModel.Authors = rdr.GetString(3);
  121. articleModel.PublishDate = rdr.GetDateTime(4);
  122. articleModel.Annotation = rdr.GetString(5);
  123. articleModel.Keywords = rdr.GetString(6);
  124. articleModel.Status = (ArticleStatus)rdr.GetInt32(7);
  125. }
  126. await Task.WhenAll();
  127. rdr.Close();
  128. return articleModel;
  129. }
  130. else
  131. Console.WriteLine("Not connected to DB.");
  132. return null;
  133. }
  134. public uint SQLGetID(string sql)
  135. {
  136. bool connected = IsConnect();
  137. if (connected)
  138. {
  139. SQLcom = new(sql, Connection);
  140. object obj = SQLcom.ExecuteScalar();
  141. return (uint)obj;
  142. }
  143. else return 0;
  144. }
  145. public void Close()
  146. {
  147. Connection.Close();
  148. }
  149. }
  150. }