using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using HyperCube.Models;
using MySql.Data.MySqlClient;
using Console = HyperCube.Utils.AdvConsole;
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 async Task<bool> IsConnect()
{
string connstring = $"Server={Server}; database={DatabaseName}; UID={UserName}; password={Password}";
if (Connection != null)
{
Console.WriteLine($"IsConnect {Connection.State}");
//try
//{
await SQLSelectComplex("select COUNT(*) from accounts", false);
// }
// catch (Exception e)
// {
// Console.WriteLine("SQL Exception " + e.Message + "stack trace" + e.StackTrace);
// Console.WriteLine($"catch SQL IsConnect making NEW Connection!");
// Connection = new MySqlConnection(connstring);
// Connection.Open();
// }
// //finally
// //{
// //}
// Console.WriteLine($"SQL IsConnect state: {Connection.State}");
}
else
Console.WriteLine($"SQL IsConnect Connection null");
if (Connection == null || Connection.State != System.Data.ConnectionState.Open)
{
Console.WriteLine($"SQL IsConnect making NEW Connection!");
Connection = new MySqlConnection(connstring);
Connection.Open();
}
return true;
}
public async Task<long> SQLInsert(string sql)
{
Console.WriteLine($"SQLInsert {sql}");
long lastID = 0;
bool connected = await IsConnect();
if (connected)
{
SQLcom = new(sql, Connection);
SQLcom.ExecuteNonQuery();
SQLcom.Dispose();
lastID = SQLcom.LastInsertedId;
}
else
Console.WriteLine("Not connected to DB.");
await Task.WhenAll();
return lastID;
}
public async Task<Dictionary<string, AccountModel>> SQLSelectASPUsers()
{
Console.WriteLine($"SQLSelectASPUsers");
string sql = "select * from aspnetusers";
Dictionary<string, AccountModel> accs = new();
bool connected = await IsConnect();
if (connected)
{
SQLcom = new(sql, Connection);
MySqlDataReader rdr = SQLcom.ExecuteReader();
while (rdr.Read())
{
AccountModel newacc = new ();
newacc.UUID = rdr.GetString(0);
newacc.Name = rdr.GetString(1);
newacc.Email = rdr.GetString(3);
newacc.PWDHash = rdr.GetString(6);
//if (!rdr.IsDBNull(15))
// newacc.eth_address = rdr.GetString(15);
if (!accs.ContainsKey(newacc.UUID))
accs.Add(newacc.UUID, newacc);
newacc.bsel = rdr.GetByte(17);
}
await Task.WhenAll();
rdr.Close();
return accs;
}
else
Console.WriteLine("Not connected to DB.");
return null;
}
public async Task<Dictionary<int, SmartContract>> SQLSelectContracts(int blockchainId)
{
Console.WriteLine($"SQLSelectContracts");
var bc = Blockchain.Find(blockchainId);
if (bc != null)
{
string sql = "select * from smart_contracts where blockchain_id =" + blockchainId;
Dictionary<int, SmartContract> contracts = new();
bool connected = await 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);
var gas = rdr.GetString(8);
SmartContract contract;
if (name == "Verify")
contract = new VerifyContract(name, code, bytecode, blockchainId, gas);
else
contract = new SmartContract(name, code, bytecode, blockchainId, gas);
contract.ID = id;
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 (!bc.contractNames.ContainsKey(name))
bc.contractNames.Add(name, contract);
}
await Task.WhenAll();
rdr.Close();
return contracts;
}
else
Console.WriteLine("Not connected to DB.");
}
else
Console.WriteLine($"Error: blockchain {blockchainId} not found");
return null;
}
public async Task<Dictionary<int, ArticleModel>> SQLSelectArticles(string sql)
{
Console.WriteLine($"SQLSelectArticles");
Dictionary<int, ArticleModel> articleModels = new();
Models.ArticleModel articleModel;
bool connected = await 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();
articleModel.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(articleModel.ID))
continue;
articleModels.Add(articleModel.ID, articleModel);
}
await Task.WhenAll();
rdr.Close();
return articleModels;
}
else
Console.WriteLine("Not connected to DB.");
return null;
}
const string typeguid = "System.Guid";
public async Task<List<Dictionary<string, object>>> SQLSelectComplex(string request, bool check = true)
{
Console.WriteLine($"SQLSelectComplex {request}");
bool connected = true;
if (check)
{
Console.WriteLine($"SQLSelectComplex check");
connected = await IsConnect();
}
if (connected)
{
Console.WriteLine($"SQLSelectComplex connected");
List<Dictionary<string, object>> retval = new List<Dictionary<string, object>>();
SQLcom = new(request, Connection);
Console.WriteLine($"SQLSelectComplex new SQLcom");
//try
//{
MySqlDataReader Reader = SQLcom.ExecuteReader();
Console.WriteLine($"SQLSelectComplex ExecuteReader");
while (Reader.Read())
{
Console.WriteLine($"SQLSelectComplex Reader.Read");
Dictionary<string, object> data = new Dictionary<string, object>();
for (int i = 0; i < Reader.FieldCount; i++)
{
if (!Reader.IsDBNull(i))
{
data.Add(Reader.GetName(i), Reader.GetValue(i));
}
else
{
//Console.WriteLine(Reader.GetName(i)+": GetDataTypeName " + Reader.GetDataTypeName(i) + " GetFieldType " + Reader.GetFieldType(i).ToString());
if (Reader.GetFieldType(i).ToString() == typeguid)
{
data.Add(Reader.GetName(i), "00000000-0000-0000-0000-000000000000");
}
else if (Reader.GetName(i) == "mac_address")
{
data.Add(Reader.GetName(i), "00-00-00-00-00-00");
}
else
{
data.Add(Reader.GetName(i), null);
}
}
}
retval.Add(data);
}
await Task.WhenAll();
Reader.Close();
//}
//catch (Exception e)
//{
// Console.WriteLine("SQL Exception 5.1 " + e.Message + " query " + request + "stack trace" + e.StackTrace);
//}
return retval;
}
return null;
}
public async Task<ArticleModel> SQLSelectArticle(string sql)
{
Console.WriteLine($"SQLSelectArticle");
ArticleModel articleModel = new();
bool connected = await 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.ID = rdr.GetInt32(0);
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);
Console.WriteLine($"ID: {articleModel.ID}, rating: {articleModel.Rating}");
}
await Task.WhenAll();
rdr.Close();
return articleModel;
}
else
Console.WriteLine("Not connected to DB.");
return null;
}
public async Task<string> SQLSelectUUID(string sql)
{
Console.WriteLine($"SQLSelectUUID");
bool connected = await IsConnect();
if (connected)
{
SQLcom = new(sql, Connection);
object obj = await SQLcom.ExecuteScalarAsync();
return obj.ToString();
}
else return "";
}
public async Task<int> SQLSelectCount(string sql)
{
Console.WriteLine($"SQLSelectCount");
bool connected = await IsConnect();
if (connected)
{
SQLcom = new(sql, Connection);
object obj = await SQLcom.ExecuteScalarAsync();
int count = int.Parse(obj.ToString());
return count;
}
else return 0;
}
public void Close()
{
Connection.Close();
}
}
}