HyperCube/MySQLConnector.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace HyperCube
{
    public class MySQLConnector
    {
        private MySQLConnector(){ }

        private readonly string Server = "dmatter.net";
        private readonly string DatabaseName = "documents";
        private readonly string UserName = "promsystem";
        private readonly string Password = "PrmSystem212";

        MySqlCommand SQLcom;

        public MySqlConnection Connection { get; set; }

        private static MySQLConnector _instance = null;
        public static MySQLConnector Instance()
        {
            if (_instance == null)
                _instance = new MySQLConnector();
            return _instance;
        }

        public bool IsConnect()
        {
            if (Connection == null)
            {                
                string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
                Connection = new MySqlConnection(connstring);
                Connection.Open();
            }
            else if (Connection.State != System.Data.ConnectionState.Open)            
                Connection.Open();

            return true;
        }

        public long SQLInsert(string sql)
        {
            long lastID = 0;

            bool connected = IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                SQLcom.ExecuteNonQuery();
                SQLcom.Dispose();
                lastID = SQLcom.LastInsertedId;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return lastID;
        }

        public async Task<Dictionary<int, Models.ArticleModel>> SQLSelectArticles(string sql)
        {
            Dictionary<int, Models.ArticleModel> articleModels = new();
            Models.ArticleModel articleModel;

            bool connected = IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                MySqlDataReader rdr = SQLcom.ExecuteReader();

                while (rdr.Read())
                {
                    //Console.WriteLine("{0} {1} {2} {3} {4}", rdr.GetInt32(0), rdr.GetString(1),
                    //        rdr.GetString(2), rdr.GetDateTime(3), rdr.GetString(4));

                    articleModel = new();
                    articleModel.Filename = rdr.GetString(1);
                    articleModel.Name = rdr.GetString(2);
                    articleModel.PublishDate = rdr.GetDateTime(3);
                    articleModel.Authors = rdr.GetString(4);

                    articleModels.Add(rdr.GetInt32(0), articleModel);
                }

                await Task.WhenAll();
                rdr.Close();
                //Console.WriteLine("End reading DB");
                return articleModels;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }

        public async Task<Models.ArticleModel> SQLSelectArticle(string sql)
        {
            Models.ArticleModel articleModel = new();

            bool connected = IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                MySqlDataReader rdr = SQLcom.ExecuteReader();

                while (rdr.Read())  //id, filename, article_name, authors, date_publish, annotation, keywords
                {
                    articleModel.Filename = rdr.GetString(1);
                    articleModel.Name = rdr.GetString(2);
                    articleModel.Authors = rdr.GetString(3);
                    articleModel.PublishDate = rdr.GetDateTime(4);
                    articleModel.Annotation = rdr.GetString(5);
                    articleModel.Keywords = rdr.GetString(6);
                }
                await Task.WhenAll();
                rdr.Close();
                return articleModel;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }

        public uint SQLGetID(string sql)
        {
            bool connected = IsConnect();
            if (connected)
            {
                SQLcom = new(sql, Connection);
                object obj = SQLcom.ExecuteScalar();

                return (uint)obj;
            }
            else return 0;
        }

        public void Close()
        {
            Connection.Close();
        }
    }
}