HyperCube/MySQLConnector.cs

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using HyperCube.Models;
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, SmartContract>> SQLSelectContracts()
        {
            string sql = "select * from smart_contracts";
            Dictionary<int, SmartContract> contracts = new();

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

                while (rdr.Read())
                {
                    Models.SmartContract contract = new();
                    contract.id = rdr.GetInt32(0);
                    contract.Code = rdr.GetString(1);
                    contract.ByteCode = rdr.GetString(2);
                    contract.Name = rdr.GetString(3);
                    if (!contracts.ContainsKey(contract.id))
                        contracts.Add(contract.id, contract);
                }

                await Task.WhenAll();
                rdr.Close();
                return contracts;
            }
            else
                Console.WriteLine("Not connected to DB.");

            return null;
        }

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

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

                while (rdr.Read()) ///id, filename, article_name, date_publish, action_type/status
                {                    
                    articleModel = new();
                    int id = rdr.GetInt32(0);
                    articleModel.Filename = rdr.GetString(1);
                    articleModel.Name = rdr.GetString(2);
                    articleModel.PublishDate = rdr.GetDateTime(3);
                    articleModel.Authors = rdr.GetString(4);
                    articleModel.Status = (ArticleStatus)rdr.GetInt32(5);

                    if (articleModels.ContainsKey(id))
                        continue;

                    articleModels.Add(id, articleModel);
                }

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

            return null;
        }

        public async Task<ArticleModel> SQLSelectArticle(string sql)
        {
            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, action_type/status
                {
                    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);
                    articleModel.Status = (ArticleStatus)rdr.GetInt32(7);
                }
                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();
        }
    }
}