MySQLConnector.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Threading.Tasks;
  4. using HyperCube.Models;
  5. using MySql.Data.MySqlClient;
  6. using Console = HyperCube.Utils.AdvConsole;
  7. namespace HyperCube
  8. {
  9. public class MySQLConnector
  10. {
  11. private MySQLConnector(){ }
  12. private readonly string Server = "dmatter.net";
  13. private readonly string DatabaseName = "documents";
  14. private readonly string UserName = "promsystem";
  15. private readonly string Password = "PrmSystem212";
  16. MySqlCommand SQLcom;
  17. public MySqlConnection Connection { get; set; }
  18. private static MySQLConnector _instance = null;
  19. public static MySQLConnector Instance()
  20. {
  21. if (_instance == null)
  22. _instance = new MySQLConnector();
  23. return _instance;
  24. }
  25. public bool IsConnect()
  26. {
  27. if (Connection == null)
  28. {
  29. string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
  30. Connection = new MySqlConnection(connstring);
  31. Connection.Open();
  32. }
  33. else if (Connection.State != System.Data.ConnectionState.Open)
  34. Connection.Open();
  35. return true;
  36. }
  37. public long SQLInsert(string sql)
  38. {
  39. long lastID = 0;
  40. bool connected = IsConnect();
  41. if (connected)
  42. {
  43. SQLcom = new(sql, Connection);
  44. SQLcom.ExecuteNonQuery();
  45. SQLcom.Dispose();
  46. lastID = SQLcom.LastInsertedId;
  47. }
  48. else
  49. Console.WriteLine("Not connected to DB.");
  50. return lastID;
  51. }
  52. public async Task<Dictionary<string, AccountModel>> SQLSelectASPUsers()
  53. {
  54. string sql = "select * from aspnetusers";
  55. Dictionary<string, AccountModel> accs = new();
  56. bool connected = IsConnect();
  57. if (connected)
  58. {
  59. SQLcom = new(sql, Connection);
  60. MySqlDataReader rdr = SQLcom.ExecuteReader();
  61. while (rdr.Read())
  62. {
  63. AccountModel newacc = new ();
  64. newacc.UUID = rdr.GetString(0);
  65. newacc.Name = rdr.GetString(1);
  66. newacc.Email = rdr.GetString(3);
  67. newacc.PWDHash = rdr.GetString(6);
  68. //if (!rdr.IsDBNull(15))
  69. // newacc.eth_address = rdr.GetString(15);
  70. if (!accs.ContainsKey(newacc.UUID))
  71. accs.Add(newacc.UUID, newacc);
  72. newacc.bsel = rdr.GetByte(17);
  73. }
  74. await Task.WhenAll();
  75. rdr.Close();
  76. return accs;
  77. }
  78. else
  79. Console.WriteLine("Not connected to DB.");
  80. return null;
  81. }
  82. public async Task<Dictionary<int, SmartContract>> SQLSelectContracts(int blockchainId)
  83. {
  84. var bc = Blockchain.Find(blockchainId);
  85. if (bc != null)
  86. {
  87. string sql = "select * from smart_contracts where blockchain_id =" + blockchainId;
  88. Dictionary<int, SmartContract> contracts = new();
  89. bool connected = IsConnect();
  90. if (connected)
  91. {
  92. SQLcom = new(sql, Connection);
  93. MySqlDataReader rdr = SQLcom.ExecuteReader();
  94. while (rdr.Read())
  95. {
  96. var id = rdr.GetInt32(0);
  97. var code = rdr.GetString(1);
  98. var bytecode = rdr.GetString(2);
  99. var name = rdr.GetString(3);
  100. var gas = rdr.GetString(8);
  101. SmartContract contract;
  102. if (name == "Verify")
  103. contract = new VerifyContract(name, code, bytecode, blockchainId, gas);
  104. else
  105. contract = new SmartContract(name, code, bytecode, blockchainId, gas);
  106. contract.ID = id;
  107. if (!rdr.IsDBNull(6))
  108. {
  109. contract.Address = rdr.GetString(6);
  110. }
  111. Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
  112. if (!contracts.ContainsKey(contract.ID))
  113. contracts.Add(contract.ID, contract);
  114. if (!bc.contractNames.ContainsKey(name))
  115. bc.contractNames.Add(name, contract);
  116. }
  117. await Task.WhenAll();
  118. rdr.Close();
  119. return contracts;
  120. }
  121. else
  122. Console.WriteLine("Not connected to DB.");
  123. }
  124. else
  125. Console.WriteLine($"Error: blockchain {blockchainId} not found");
  126. return null;
  127. }
  128. public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
  129. {
  130. Dictionary<int, ArticleModel> articleModels = new();
  131. Models.ArticleModel articleModel;
  132. bool connected = IsConnect();
  133. if (connected)
  134. {
  135. SQLcom = new(sql, Connection);
  136. MySqlDataReader rdr = SQLcom.ExecuteReader();
  137. while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
  138. {
  139. articleModel = new();
  140. articleModel.ID = rdr.GetInt32(0);
  141. articleModel.Filename = rdr.GetString(1);
  142. articleModel.Name = rdr.GetString(2);
  143. articleModel.PublishDate = rdr.GetDateTime(3);
  144. articleModel.Authors = rdr.GetString(4);
  145. articleModel.Status = (ArticleStatus)rdr.GetInt32(5);
  146. if (articleModels.ContainsKey(articleModel.ID))
  147. continue;
  148. articleModels.Add(articleModel.ID, articleModel);
  149. }
  150. await Task.WhenAll();
  151. rdr.Close();
  152. return articleModels;
  153. }
  154. else
  155. Console.WriteLine("Not connected to DB.");
  156. return null;
  157. }
  158. const string typeguid = "System.Guid";
  159. public async Task<List<Dictionary<string, object>>> SQLSelectComplex(string request)
  160. {
  161. bool connected = IsConnect();
  162. if (connected)
  163. {
  164. List<Dictionary<string, object>> retval = new List<Dictionary<string, object>>();
  165. SQLcom = new(request, Connection);
  166. try
  167. {
  168. MySqlDataReader Reader = SQLcom.ExecuteReader();
  169. while (Reader.Read())
  170. {
  171. Dictionary<string, object> data = new Dictionary<string, object>();
  172. for (int i = 0; i < Reader.FieldCount; i++)
  173. {
  174. if (!Reader.IsDBNull(i))
  175. {
  176. data.Add(Reader.GetName(i), Reader.GetValue(i));
  177. }
  178. else
  179. {
  180. //Console.WriteLine(Reader.GetName(i)+": GetDataTypeName " + Reader.GetDataTypeName(i) + " GetFieldType " + Reader.GetFieldType(i).ToString());
  181. if (Reader.GetFieldType(i).ToString() == typeguid)
  182. {
  183. data.Add(Reader.GetName(i), "00000000-0000-0000-0000-000000000000");
  184. }
  185. else if (Reader.GetName(i) == "mac_address")
  186. {
  187. data.Add(Reader.GetName(i), "00-00-00-00-00-00");
  188. }
  189. else
  190. {
  191. data.Add(Reader.GetName(i), null);
  192. }
  193. }
  194. }
  195. retval.Add(data);
  196. }
  197. await Task.WhenAll();
  198. Reader.Close();
  199. }
  200. catch (Exception e)
  201. {
  202. Console.WriteLine("SQL Exception 5.1 " + e.Message + " query " + request + "stack trace" + e.StackTrace);
  203. }
  204. return retval;
  205. }
  206. return null;
  207. }
  208. public async Task<ArticleModel> SQLSelectArticle(string sql)
  209. {
  210. ArticleModel articleModel = new();
  211. bool connected = IsConnect();
  212. if (connected)
  213. {
  214. SQLcom = new(sql, Connection);
  215. MySqlDataReader rdr = SQLcom.ExecuteReader();
  216. while (rdr.Read()) ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status, rating
  217. {
  218. articleModel.ID = rdr.GetInt32(0);
  219. articleModel.Filename = rdr.GetString(1);
  220. articleModel.Name = rdr.GetString(2);
  221. articleModel.Authors = rdr.GetString(3);
  222. articleModel.PublishDate = rdr.GetDateTime(4);
  223. articleModel.Annotation = rdr.GetString(5);
  224. articleModel.Keywords = rdr.GetString(6);
  225. articleModel.Status = (ArticleStatus)rdr.GetInt16(7);
  226. if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);
  227. Console.WriteLine($"ID: {articleModel.ID}, rating: {articleModel.Rating}");
  228. }
  229. await Task.WhenAll();
  230. rdr.Close();
  231. return articleModel;
  232. }
  233. else
  234. Console.WriteLine("Not connected to DB.");
  235. return null;
  236. }
  237. public async Task<string> SQLSelectUUID(string sql)
  238. {
  239. bool connected = IsConnect();
  240. if (connected)
  241. {
  242. SQLcom = new(sql, Connection);
  243. object obj = await SQLcom.ExecuteScalarAsync();
  244. return obj.ToString();
  245. }
  246. else return "";
  247. }
  248. public async Task<int> SQLSelectCount(string sql)
  249. {
  250. bool connected = IsConnect();
  251. if (connected)
  252. {
  253. SQLcom = new(sql, Connection);
  254. object obj = await SQLcom.ExecuteScalarAsync();
  255. int count = int.Parse(obj.ToString());
  256. return count;
  257. }
  258. else return 0;
  259. }
  260. public void Close()
  261. {
  262. Connection.Close();
  263. }
  264. }
  265. }