MySQLConnector.cs 5.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176
  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. contract.Name = rdr.GetString(3);
  66. if (!contracts.ContainsKey(contract.id))
  67. contracts.Add(contract.id, contract);
  68. }
  69. await Task.WhenAll();
  70. rdr.Close();
  71. return contracts;
  72. }
  73. else
  74. Console.WriteLine("Not connected to DB.");
  75. return null;
  76. }
  77. public async Task<Dictionary<int, Models.ArticleModel>> SQLSelectArticles(string sql)
  78. {
  79. Dictionary<int, Models.ArticleModel> articleModels = new();
  80. Models.ArticleModel articleModel;
  81. bool connected = IsConnect();
  82. if (connected)
  83. {
  84. SQLcom = new(sql, Connection);
  85. MySqlDataReader rdr = SQLcom.ExecuteReader();
  86. while (rdr.Read())
  87. {
  88. //Console.WriteLine("{0} {1} {2} {3} {4}", rdr.GetInt32(0), rdr.GetString(1),
  89. // rdr.GetString(2), rdr.GetDateTime(3), rdr.GetString(4));
  90. articleModel = new();
  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. articleModels.Add(rdr.GetInt32(0), articleModel);
  96. }
  97. await Task.WhenAll();
  98. rdr.Close();
  99. //Console.WriteLine("End reading DB");
  100. return articleModels;
  101. }
  102. else
  103. Console.WriteLine("Not connected to DB.");
  104. return null;
  105. }
  106. public async Task<Models.ArticleModel> SQLSelectArticle(string sql)
  107. {
  108. Models.ArticleModel articleModel = new();
  109. bool connected = IsConnect();
  110. if (connected)
  111. {
  112. SQLcom = new(sql, Connection);
  113. MySqlDataReader rdr = SQLcom.ExecuteReader();
  114. while (rdr.Read()) //id, filename, article_name, authors, date_publish, annotation, keywords
  115. {
  116. articleModel.Filename = rdr.GetString(1);
  117. articleModel.Name = rdr.GetString(2);
  118. articleModel.Authors = rdr.GetString(3);
  119. articleModel.PublishDate = rdr.GetDateTime(4);
  120. articleModel.Annotation = rdr.GetString(5);
  121. articleModel.Keywords = rdr.GetString(6);
  122. }
  123. await Task.WhenAll();
  124. rdr.Close();
  125. return articleModel;
  126. }
  127. else
  128. Console.WriteLine("Not connected to DB.");
  129. return null;
  130. }
  131. public uint SQLGetID(string sql)
  132. {
  133. bool connected = IsConnect();
  134. if (connected)
  135. {
  136. SQLcom = new(sql, Connection);
  137. object obj = SQLcom.ExecuteScalar();
  138. return (uint)obj;
  139. }
  140. else return 0;
  141. }
  142. public void Close()
  143. {
  144. Connection.Close();
  145. }
  146. }
  147. }