MySQLConnector.cs 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369
  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 async Task<bool> IsConnect()
  26. {
  27. string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
  28. if (Connection != null)
  29. {
  30. Console.WriteLine($"IsConnect {Connection.State}");
  31. //try
  32. //{
  33. await SQLSelectComplex("select COUNT(*) from accounts", false);
  34. // }
  35. // catch (Exception e)
  36. // {
  37. // Console.WriteLine("SQL Exception " + e.Message + "stack trace" + e.StackTrace);
  38. // Console.WriteLine($"catch SQL IsConnect making NEW Connection!");
  39. // Connection = new MySqlConnection(connstring);
  40. // Connection.Open();
  41. // }
  42. // //finally
  43. // //{
  44. // //}
  45. // Console.WriteLine($"SQL IsConnect state: {Connection.State}");
  46. }
  47. else
  48. Console.WriteLine($"SQL IsConnect Connection null");
  49. if (Connection == null || Connection.State != System.Data.ConnectionState.Open)
  50. {
  51. Console.WriteLine($"SQL IsConnect making NEW Connection!");
  52. Connection = new MySqlConnection(connstring);
  53. Connection.Open();
  54. }
  55. await Task.WhenAll();
  56. return true;
  57. }
  58. public async Task<long> SQLInsert(string sql)
  59. {
  60. Console.WriteLine($"SQLInsert {sql}");
  61. long lastID = 0;
  62. bool connected = await IsConnect();
  63. if (connected)
  64. {
  65. MySqlCommand SQLcom3 = new(sql, Connection);
  66. //SQLcom3.ExecuteNonQuery();
  67. await SQLcom3.ExecuteNonQueryAsync();
  68. lastID = SQLcom3.LastInsertedId;
  69. SQLcom3.Dispose();
  70. Console.WriteLine($"SQLInsert end");
  71. }
  72. else
  73. Console.WriteLine("Not connected to DB.");
  74. await Task.WhenAll();
  75. return lastID;
  76. }
  77. public async Task<Dictionary<string, AccountModel>> SQLSelectASPUsers()
  78. {
  79. Console.WriteLine($"SQLSelectASPUsers");
  80. string sql = "select * from aspnetusers";
  81. Dictionary<string, AccountModel> accs = new();
  82. bool connected = await IsConnect();
  83. if (connected)
  84. {
  85. SQLcom = new(sql, Connection);
  86. MySqlDataReader rdr = SQLcom.ExecuteReader();
  87. while (rdr.Read())
  88. {
  89. AccountModel newacc = new ();
  90. newacc.UUID = rdr.GetString(0);
  91. newacc.Name = rdr.GetString(1);
  92. newacc.Email = rdr.GetString(3);
  93. newacc.PWDHash = rdr.GetString(6);
  94. //if (!rdr.IsDBNull(15))
  95. // newacc.eth_address = rdr.GetString(15);
  96. if (!accs.ContainsKey(newacc.UUID))
  97. accs.Add(newacc.UUID, newacc);
  98. newacc.bsel = rdr.GetByte(17);
  99. }
  100. await Task.WhenAll();
  101. rdr.Close();
  102. return accs;
  103. }
  104. else
  105. Console.WriteLine("Not connected to DB.");
  106. return null;
  107. }
  108. public async Task<Dictionary<int, SmartContract>> SQLSelectContracts(int blockchainId)
  109. {
  110. Console.WriteLine($"SQLSelectContracts");
  111. var bc = Blockchain.Find(blockchainId);
  112. if (bc != null)
  113. {
  114. string sql = "select * from smart_contracts where blockchain_id =" + blockchainId;
  115. Dictionary<int, SmartContract> contracts = new();
  116. bool connected = await IsConnect();
  117. if (connected)
  118. {
  119. SQLcom = new(sql, Connection);
  120. MySqlDataReader rdr = SQLcom.ExecuteReader();
  121. while (rdr.Read())
  122. {
  123. var id = rdr.GetInt32(0);
  124. var code = rdr.GetString(1);
  125. var bytecode = rdr.GetString(2);
  126. var name = rdr.GetString(3);
  127. var gas = rdr.GetString(8);
  128. SmartContract contract;
  129. if (name == "Verify")
  130. contract = new VerifyContract(name, code, bytecode, blockchainId, gas);
  131. else
  132. contract = new SmartContract(name, code, bytecode, blockchainId, gas);
  133. contract.ID = id;
  134. if (!rdr.IsDBNull(6))
  135. {
  136. contract.Address = rdr.GetString(6);
  137. }
  138. Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
  139. if (!contracts.ContainsKey(contract.ID))
  140. contracts.Add(contract.ID, contract);
  141. if (!bc.contractNames.ContainsKey(name))
  142. bc.contractNames.Add(name, contract);
  143. }
  144. await Task.WhenAll();
  145. rdr.Close();
  146. return contracts;
  147. }
  148. else
  149. Console.WriteLine("Not connected to DB.");
  150. }
  151. else
  152. Console.WriteLine($"Error: blockchain {blockchainId} not found");
  153. return null;
  154. }
  155. public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
  156. {
  157. //return null;
  158. Console.WriteLine($"SQLSelectArticles {sql}");
  159. Dictionary<int, ArticleModel> articleModels = new();
  160. Models.ArticleModel articleModel;
  161. bool connected = await IsConnect();
  162. if (connected)
  163. {
  164. var SQLcom = new MySqlCommand(sql, Connection);
  165. var rdr = await SQLcom.ExecuteReaderAsync();
  166. int count = 0;
  167. bool stop = false;
  168. while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
  169. {
  170. for (int i = 0; i < rdr.FieldCount; i++)
  171. {
  172. if (rdr.IsDBNull(i))
  173. {
  174. Console.WriteLine($"SQLSelectArticles NULL detected {i}");
  175. stop = true;
  176. break;
  177. }
  178. }
  179. if (stop)
  180. {
  181. stop = false;
  182. continue;
  183. }
  184. articleModel = new();
  185. articleModel.ID = rdr.GetInt32(0);
  186. articleModel.Filename = rdr.GetString(1);
  187. articleModel.Name = rdr.GetString(2);
  188. articleModel.PublishDate = rdr.GetDateTime(3);
  189. articleModel.Authors = rdr.GetString(4);
  190. articleModel.Status = (ArticleStatus)rdr.GetInt32(5);
  191. if (articleModels.ContainsKey(articleModel.ID))
  192. continue;
  193. Console.WriteLine($"SQLSelectArticles count {count} model id {articleModel.ID}");
  194. count++;
  195. articleModels.Add(articleModel.ID, articleModel);
  196. }
  197. Console.WriteLine($"SQLSelectArticles2");
  198. rdr.Close();
  199. //await Task.WhenAll();
  200. return articleModels;
  201. }
  202. else
  203. Console.WriteLine("Not connected to DB.");
  204. return null;
  205. }
  206. const string typeguid = "System.Guid";
  207. public async Task<List<Dictionary<string, object>>> SQLSelectComplex(string request, bool check = true)
  208. {
  209. Console.WriteLine($"SQLSelectComplex {request}");
  210. bool connected = true;
  211. if (check)
  212. {
  213. Console.WriteLine($"SQLSelectComplex check");
  214. connected = await IsConnect();
  215. }
  216. if (connected)
  217. {
  218. Console.WriteLine($"SQLSelectComplex connected");
  219. List<Dictionary<string, object>> retval = new List<Dictionary<string, object>>();
  220. MySqlCommand SQLcom2 = new(request, Connection);
  221. Console.WriteLine($"SQLSelectComplex new SQLcom");
  222. //try
  223. //{
  224. var Reader = await SQLcom2.ExecuteReaderAsync();
  225. Console.WriteLine($"SQLSelectComplex ExecuteReader");
  226. while (Reader.Read())
  227. {
  228. Console.WriteLine($"SQLSelectComplex Reader.Read");
  229. Dictionary<string, object> data = new Dictionary<string, object>();
  230. for (int i = 0; i < Reader.FieldCount; i++)
  231. {
  232. if (!Reader.IsDBNull(i))
  233. {
  234. data.Add(Reader.GetName(i), Reader.GetValue(i));
  235. }
  236. else
  237. {
  238. //Console.WriteLine(Reader.GetName(i)+": GetDataTypeName " + Reader.GetDataTypeName(i) + " GetFieldType " + Reader.GetFieldType(i).ToString());
  239. if (Reader.GetFieldType(i).ToString() == typeguid)
  240. {
  241. data.Add(Reader.GetName(i), "00000000-0000-0000-0000-000000000000");
  242. }
  243. else if (Reader.GetName(i) == "mac_address")
  244. {
  245. data.Add(Reader.GetName(i), "00-00-00-00-00-00");
  246. }
  247. else
  248. {
  249. data.Add(Reader.GetName(i), null);
  250. }
  251. }
  252. }
  253. retval.Add(data);
  254. }
  255. Console.WriteLine($"SQLSelectComplex Reader.Close");
  256. Reader.Close();
  257. await Task.WhenAll();
  258. //}
  259. //catch (Exception e)
  260. //{
  261. // Console.WriteLine("SQL Exception 5.1 " + e.Message + " query " + request + "stack trace" + e.StackTrace);
  262. //}
  263. return retval;
  264. }
  265. return null;
  266. }
  267. public async Task<ArticleModel> SQLSelectArticle(string sql)
  268. {
  269. Console.WriteLine($"SQLSelectArticle");
  270. ArticleModel articleModel = new();
  271. bool connected = await IsConnect();
  272. if (connected)
  273. {
  274. SQLcom = new(sql, Connection);
  275. MySqlDataReader rdr = SQLcom.ExecuteReader();
  276. while (rdr.Read()) ///id, filename, article_name, authors, date_publish, annotation, keywords, action_type/status, rating
  277. {
  278. articleModel.ID = rdr.GetInt32(0);
  279. articleModel.Filename = rdr.GetString(1);
  280. articleModel.Name = rdr.GetString(2);
  281. articleModel.Authors = rdr.GetString(3);
  282. articleModel.PublishDate = rdr.GetDateTime(4);
  283. articleModel.Annotation = rdr.GetString(5);
  284. articleModel.Keywords = rdr.GetString(6);
  285. articleModel.Status = (ArticleStatus)rdr.GetInt16(7);
  286. if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);
  287. Console.WriteLine($"ID: {articleModel.ID}, rating: {articleModel.Rating}");
  288. }
  289. rdr.Close();
  290. await Task.WhenAll();
  291. return articleModel;
  292. }
  293. else
  294. Console.WriteLine("Not connected to DB.");
  295. return null;
  296. }
  297. public async Task<string> SQLSelectUUID(string sql)
  298. {
  299. Console.WriteLine($"SQLSelectUUID");
  300. bool connected = await IsConnect();
  301. if (connected)
  302. {
  303. SQLcom = new(sql, Connection);
  304. object obj = await SQLcom.ExecuteScalarAsync();
  305. return obj.ToString();
  306. }
  307. else return "";
  308. }
  309. public async Task<int> SQLSelectCount(string sql)
  310. {
  311. Console.WriteLine($"SQLSelectCount");
  312. bool connected = await IsConnect();
  313. if (connected)
  314. {
  315. SQLcom = new(sql, Connection);
  316. object obj = await SQLcom.ExecuteScalarAsync();
  317. int count = int.Parse(obj.ToString());
  318. return count;
  319. }
  320. else return 0;
  321. }
  322. public void Close()
  323. {
  324. Connection.Close();
  325. }
  326. }
  327. }