MySQLConnector.cs 13 KB

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