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<string, AccountModel>> SQLSelectASPUsers()
        {
            string sql = "select * from aspnetusers";
            Dictionary<string, AccountModel> accs = new();

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

                while (rdr.Read())
                {
                    AccountModel newacc = new AccountModel();
                    newacc.UUID = rdr.GetString(0); 
                    newacc.Name = rdr.GetString(1);
                    newacc.Email = rdr.GetString(3);
                    if (!rdr.IsDBNull(15))
                        newacc.eth_address = rdr.GetString(15);
                    if (!accs.ContainsKey(newacc.Email))
                        accs.Add(newacc.Email, newacc);
                }

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

            return null;
        }

        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())
                {                   
                    var id = rdr.GetInt32(0);
                    var code = rdr.GetString(1);
                    var bytecode = rdr.GetString(2);
                    var name = rdr.GetString(3);

                    SmartContract contract = new SmartContract(id, name, code, bytecode);
                    if (!rdr.IsDBNull(6))
                    {
                        contract.Address = rdr.GetString(6);
                    }

                    
                    Console.WriteLine($"SQLSelectContracts rdr Read {contract.Name}");
                    if (!contracts.ContainsKey(contract.ID))
                        contracts.Add(contract.ID, contract);
                    if (!Blockchain.GetMain().contractNames.ContainsKey(name))
                        Blockchain.GetMain().contractNames.Add(name, 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, rating
                {
                    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.GetInt16(7);
                    if (!rdr.IsDBNull(8)) articleModel.Rating = rdr.GetInt16(8);
                }
                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();
        }
    }
}