MySQLConnector.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291
  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 ();
  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.UUID))
  69. accs.Add(newacc.UUID, 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;
  95. if (name == "Verify")
  96. contract = new VerifyContract(id, name, code, bytecode);
  97. else
  98. contract = new SmartContract(id, name, code, bytecode);
  99. if (!rdr.IsDBNull(6))
  100. {
  101. contract.Address = rdr.GetString(6);
  102. }
  103. Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
  104. if (!contracts.ContainsKey(contract.ID))
  105. contracts.Add(contract.ID, contract);
  106. if (!Blockchain.GetMain().contractNames.ContainsKey(name))
  107. Blockchain.GetMain().contractNames.Add(name, contract);
  108. }
  109. await Task.WhenAll();
  110. rdr.Close();
  111. return contracts;
  112. }
  113. else
  114. Console.WriteLine("Not connected to DB.");
  115. return null;
  116. }
  117. public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
  118. {
  119. Dictionary<int, ArticleModel> articleModels = new();
  120. Models.ArticleModel articleModel;
  121. bool connected = IsConnect();
  122. if (connected)
  123. {
  124. SQLcom = new(sql, Connection);
  125. MySqlDataReader rdr = SQLcom.ExecuteReader();
  126. while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
  127. {
  128. articleModel = new();
  129. articleModel.ID = rdr.GetInt32(0);
  130. articleModel.Filename = rdr.GetString(1);
  131. articleModel.Name = rdr.GetString(2);
  132. articleModel.PublishDate = rdr.GetDateTime(3);
  133. articleModel.Authors = rdr.GetString(4);
  134. articleModel.Status = (ArticleStatus)rdr.GetInt32(5);
  135. if (articleModels.ContainsKey(articleModel.ID))
  136. continue;
  137. articleModels.Add(articleModel.ID, articleModel);
  138. }
  139. await Task.WhenAll();
  140. rdr.Close();
  141. return articleModels;
  142. }
  143. else
  144. Console.WriteLine("Not connected to DB.");
  145. return null;
  146. }
  147. const string typeguid = "System.Guid";
  148. public async Task<List<Dictionary<string, object>>> SQLSelectComplex(string request)
  149. {
  150. bool connected = IsConnect();
  151. if (connected)
  152. {
  153. List<Dictionary<string, object>> retval = new List<Dictionary<string, object>>();
  154. SQLcom = new(request, Connection);
  155. try
  156. {
  157. MySqlDataReader Reader = SQLcom.ExecuteReader();
  158. while (Reader.Read())
  159. {
  160. Dictionary<string, object> data = new Dictionary<string, object>();
  161. for (int i = 0; i < Reader.FieldCount; i++)
  162. {
  163. if (!Reader.IsDBNull(i))
  164. {
  165. data.Add(Reader.GetName(i), Reader.GetValue(i));
  166. }
  167. else
  168. {
  169. //Console.WriteLine(Reader.GetName(i)+": GetDataTypeName " + Reader.GetDataTypeName(i) + " GetFieldType " + Reader.GetFieldType(i).ToString());
  170. if (Reader.GetFieldType(i).ToString() == typeguid)
  171. {
  172. data.Add(Reader.GetName(i), "00000000-0000-0000-0000-000000000000");
  173. }
  174. else if (Reader.GetName(i) == "mac_address")
  175. {
  176. data.Add(Reader.GetName(i), "00-00-00-00-00-00");
  177. }
  178. else
  179. {
  180. data.Add(Reader.GetName(i), null);
  181. }
  182. }
  183. }
  184. retval.Add(data);
  185. }
  186. await Task.WhenAll();
  187. Reader.Close();
  188. }
  189. catch (Exception e)
  190. {
  191. Console.Write("SQL Exception 5.1 " + e.Message + " query " + request + "stack trace" + e.StackTrace);
  192. }
  193. return retval;
  194. }
  195. return null;
  196. }
  197. public async Task<ArticleModel> SQLSelectArticle(string sql)
  198. {
  199. ArticleModel articleModel = new();
  200. bool connected = IsConnect();
  201. if (connected)
  202. {
  203. SQLcom = new(sql, Connection);
  204. MySqlDataReader rdr = SQLcom.ExecuteReader();
  205. while (rdr.Read()) ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status, rating
  206. {
  207. articleModel.ID = rdr.GetInt32(0);
  208. articleModel.Filename = rdr.GetString(1);
  209. articleModel.Name = rdr.GetString(2);
  210. articleModel.Authors = rdr.GetString(3);
  211. articleModel.PublishDate = rdr.GetDateTime(4);
  212. articleModel.Annotation = rdr.GetString(5);
  213. articleModel.Keywords = rdr.GetString(6);
  214. articleModel.Status = (ArticleStatus)rdr.GetInt16(7);
  215. if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);
  216. }
  217. await Task.WhenAll();
  218. rdr.Close();
  219. return articleModel;
  220. }
  221. else
  222. Console.WriteLine("Not connected to DB.");
  223. return null;
  224. }
  225. public async Task<string> SQLSelectUUID(string sql)
  226. {
  227. bool connected = IsConnect();
  228. if (connected)
  229. {
  230. SQLcom = new(sql, Connection);
  231. object obj = await SQLcom.ExecuteScalarAsync();
  232. return obj.ToString();
  233. }
  234. else return "";
  235. }
  236. public async Task<int> SQLSelectCount(string sql)
  237. {
  238. bool connected = IsConnect();
  239. if (connected)
  240. {
  241. SQLcom = new(sql, Connection);
  242. object obj = await SQLcom.ExecuteScalarAsync();
  243. int count = int.Parse(obj.ToString());
  244. return count;
  245. }
  246. else return 0;
  247. }
  248. public void Close()
  249. {
  250. Connection.Close();
  251. }
  252. }
  253. }