MySQLConnector.cs 4.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144
  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.ArticleModel>> SQLSelectArticles(string sql)
  51. {
  52. Dictionary<int, Models.ArticleModel> articleModels = new();
  53. Models.ArticleModel articleModel;
  54. bool connected = IsConnect();
  55. if (connected)
  56. {
  57. SQLcom = new(sql, Connection);
  58. MySqlDataReader rdr = SQLcom.ExecuteReader();
  59. while (rdr.Read())
  60. {
  61. //Console.WriteLine("{0} {1} {2} {3} {4}", rdr.GetInt32(0), rdr.GetString(1),
  62. // rdr.GetString(2), rdr.GetDateTime(3), rdr.GetString(4));
  63. articleModel = new();
  64. articleModel.Filename = rdr.GetString(1);
  65. articleModel.Name = rdr.GetString(2);
  66. articleModel.PublishDate = rdr.GetDateTime(3);
  67. articleModel.Authors = rdr.GetString(4);
  68. articleModels.Add(rdr.GetInt32(0), articleModel);
  69. }
  70. await Task.WhenAll();
  71. rdr.Close();
  72. //Console.WriteLine("End reading DB");
  73. return articleModels;
  74. }
  75. else
  76. Console.WriteLine("Not connected to DB.");
  77. return null;
  78. }
  79. public async Task<Models.ArticleModel> SQLSelectArticle(string sql)
  80. {
  81. Models.ArticleModel articleModel = new();
  82. bool connected = IsConnect();
  83. if (connected)
  84. {
  85. SQLcom = new(sql, Connection);
  86. MySqlDataReader rdr = SQLcom.ExecuteReader();
  87. while (rdr.Read()) //id, filename, article_name, authors, date_publish, annotation, keywords
  88. {
  89. articleModel.Filename = rdr.GetString(1);
  90. articleModel.Name = rdr.GetString(2);
  91. articleModel.Authors = rdr.GetString(3);
  92. articleModel.PublishDate = rdr.GetDateTime(4);
  93. articleModel.Annotation = rdr.GetString(5);
  94. articleModel.Keywords = rdr.GetString(6);
  95. }
  96. await Task.WhenAll();
  97. rdr.Close();
  98. return articleModel;
  99. }
  100. else
  101. Console.WriteLine("Not connected to DB.");
  102. return null;
  103. }
  104. public uint SQLGetID(string sql)
  105. {
  106. bool connected = IsConnect();
  107. if (connected)
  108. {
  109. SQLcom = new(sql, Connection);
  110. object obj = SQLcom.ExecuteScalar();
  111. return (uint)obj;
  112. }
  113. else return 0;
  114. }
  115. public void Close()
  116. {
  117. Connection.Close();
  118. }
  119. }
  120. }