MySQLConnector.cs 5.6 KB

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