MySQLConnector.cs 13 KB

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