MySQLConnector.cs 7.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223
  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<string, AccountModel>> SQLSelectASPUsers()
  52. {
  53. string sql = "select * from aspnetusers";
  54. Dictionary<string, AccountModel> accs = 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. AccountModel newacc = new AccountModel();
  63. newacc.UUID = rdr.GetString(0);
  64. newacc.Name = rdr.GetString(1);
  65. newacc.Email = rdr.GetString(3);
  66. if (!rdr.IsDBNull(15))
  67. newacc.eth_address = rdr.GetString(15);
  68. if (!accs.ContainsKey(newacc.Email))
  69. accs.Add(newacc.Email, newacc);
  70. }
  71. await Task.WhenAll();
  72. rdr.Close();
  73. return accs;
  74. }
  75. else
  76. Console.WriteLine("Not connected to DB.");
  77. return null;
  78. }
  79. public async Task<Dictionary<int, SmartContract>> SQLSelectContracts()
  80. {
  81. string sql = "select * from smart_contracts";
  82. Dictionary<int, SmartContract> contracts = new();
  83. bool connected = IsConnect();
  84. if (connected)
  85. {
  86. SQLcom = new(sql, Connection);
  87. MySqlDataReader rdr = SQLcom.ExecuteReader();
  88. while (rdr.Read())
  89. {
  90. var id = rdr.GetInt32(0);
  91. var code = rdr.GetString(1);
  92. var bytecode = rdr.GetString(2);
  93. var name = rdr.GetString(3);
  94. SmartContract contract = new SmartContract(id, name, code, bytecode);
  95. if (!rdr.IsDBNull(6))
  96. {
  97. contract.Address = rdr.GetString(6);
  98. }
  99. Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
  100. if (!contracts.ContainsKey(contract.ID))
  101. contracts.Add(contract.ID, contract);
  102. if (!Blockchain.GetMain().contractNames.ContainsKey(name))
  103. Blockchain.GetMain().contractNames.Add(name, contract);
  104. }
  105. await Task.WhenAll();
  106. rdr.Close();
  107. return contracts;
  108. }
  109. else
  110. Console.WriteLine("Not connected to DB.");
  111. return null;
  112. }
  113. public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
  114. {
  115. Dictionary<int, ArticleModel> articleModels = new();
  116. Models.ArticleModel articleModel;
  117. bool connected = IsConnect();
  118. if (connected)
  119. {
  120. SQLcom = new(sql, Connection);
  121. MySqlDataReader rdr = SQLcom.ExecuteReader();
  122. while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
  123. {
  124. articleModel = new();
  125. int id = rdr.GetInt32(0);
  126. articleModel.Filename = rdr.GetString(1);
  127. articleModel.Name = rdr.GetString(2);
  128. articleModel.PublishDate = rdr.GetDateTime(3);
  129. articleModel.Authors = rdr.GetString(4);
  130. articleModel.Status = (ArticleStatus)rdr.GetInt32(5);
  131. if (articleModels.ContainsKey(id))
  132. continue;
  133. articleModels.Add(id, articleModel);
  134. }
  135. await Task.WhenAll();
  136. rdr.Close();
  137. return articleModels;
  138. }
  139. else
  140. Console.WriteLine("Not connected to DB.");
  141. return null;
  142. }
  143. public async Task<ArticleModel> SQLSelectArticle(string sql)
  144. {
  145. ArticleModel articleModel = new();
  146. bool connected = IsConnect();
  147. if (connected)
  148. {
  149. SQLcom = new(sql, Connection);
  150. MySqlDataReader rdr = SQLcom.ExecuteReader();
  151. while (rdr.Read()) ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status, rating
  152. {
  153. articleModel.Filename = rdr.GetString(1);
  154. articleModel.Name = rdr.GetString(2);
  155. articleModel.Authors = rdr.GetString(3);
  156. articleModel.PublishDate = rdr.GetDateTime(4);
  157. articleModel.Annotation = rdr.GetString(5);
  158. articleModel.Keywords = rdr.GetString(6);
  159. articleModel.Status = (ArticleStatus)rdr.GetInt16(7);
  160. if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);
  161. }
  162. await Task.WhenAll();
  163. rdr.Close();
  164. return articleModel;
  165. }
  166. else
  167. Console.WriteLine("Not connected to DB.");
  168. return null;
  169. }
  170. public uint SQLGetID(string sql)
  171. {
  172. bool connected = IsConnect();
  173. if (connected)
  174. {
  175. SQLcom = new(sql, Connection);
  176. object obj = SQLcom.ExecuteScalar();
  177. return (uint)obj;
  178. }
  179. else return 0;
  180. }
  181. public void Close()
  182. {
  183. Connection.Close();
  184. }
  185. }
  186. }