ISQL.cs

#region Header
// San Diego Gas And Electric Parser >San Diego Gas And Electric Parser >ISql.cs\n Copyright (C) Adonis Deliannis, 2019\nCreated 26 08, 2019
#endregion

using System.Collections.Generic;
using System.Threading.Tasks;

namespace San_Diego_Gas_And_Electric_Parser {
    public interface ISql {
        /// <summary>
        /// Inserts data into the specified table.
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="data"><see cref="KeyValuePair{TKey,TValue}"/> to insert into <see cref="table"/></param>
        /// <remarks>The <see cref="data"/> parameter must match the table's structure else it will throw an error.</remarks>
        Task InsertInto(string table, Dictionary<string, dynamic> data);

        /// <summary>
        /// Updates given data to the specified <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="newData"><see cref="KeyValuePair{TKey,TValue}"/> to update in the <see cref="table"/></param>
        /// <param name="queryColumn">The query to use for WHERE</param>
        /// <param name="queryData">The data to use for WHERE</param>
        /// <remarks>The <see cref="newData"/> parameter must match whatever you are updating else it will throw an error.
        /// <see cref="queryColumn"/> and <see cref="queryData"/> refer to the WHERE clause such as 'WHERE <see cref="queryColumn"/>=<see cref="queryData"/>'
        /// </remarks>
        Task Update(string table, Dictionary< string, dynamic > newData, string queryColumn, dynamic queryData);

        /// <summary>
        /// Removes the element by <see cref="index"/> from the <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="index">The index of the element you want to remove</param>
        Task Delete(string table, int index);

        /// <summary>
        /// Removes the element by <see cref="query"/> from the <see cref="table"/>
        /// </summary>
        /// <param name="table">The table to modify</param>
        /// <param name="query">The query specification to select the element you want to remove</param>
        Task Delete(string table, string query);

        /// <summary>
        /// Opens a connection
        /// </summary>
        Task Connect();

        /// <summary>
        /// Closes the connection
        /// </summary>
        Task Disconnect();

    }
}

SQL.cs

// #define DRYRUN
#region Header
// San Diego Gas And Electric Parser >San Diego Gas And Electric Parser >Sql.cs\n Copyright (C) Adonis Deliannis, 2019\nCreated 26 08, 2019
#endregion

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using log4net;
using MySql.Data.MySqlClient;

// ReSharper disable CSharpWarnings::CS8625

namespace San_Diego_Gas_And_Electric_Parser {
    internal class Sql : ISql {
        private readonly SqlConfig _config;
        private readonly MySqlConnection _con;
        private readonly ILog _log = LogManager.GetLogger("SQL");

        public Sql(SqlConfig config) {
            _config = config;
            _con =
                new /*Server=string;Port=int;Database=string;Uid=string;Pwd=string*/
                    MySqlConnection($"Server={_config.Server};Port={_config.Port};Database={_config.Database};Uid={_config.Username};Pwd={_config.Password};");
        }

        private async Task< int > Execute(string query) {
        #if !DRYRUN
            _log.Info(query);
            var cmd = new MySqlCommand(query, _con);
            return await cmd.ExecuteNonQueryAsync();
        #else
            _log.Info($"DRY: {query}");
            await Task.CompletedTask;
            return 0;
        #endif
        }

        /// <summary>
        /// Creates a new reader of the data specified by the <see cref="query"/>
        /// </summary>
        /// <param name="query">The MYSQL Query to send to the server</param>
        /// <returns>A new <see cref="DbDataReader"/></returns>
        private async Task< DbDataReader > ExecuteReader(string query) {
            var cmd = new MySqlCommand(query, _con);
            return await cmd.ExecuteReaderAsync();
        }

        #region Implementation of ISql

        /// <inheritdoc />
        public async Task InsertInto(string table, Dictionary< string, dynamic > data) {
            // data.Values.Aggregate((x, y) => $"{x}, {y}");
            var sb = new StringBuilder();
            
            foreach (KeyValuePair<string,dynamic> k in data) {

                sb.Append(long.TryParse(Convert.ToString(k.Value), out long v)
                              ? $"`{k.Key}`={v},"
                              : $"`{k.Key}`='{k.Value}',");
            }

            await
                Execute($"INSERT INTO {table.EscapeString()} ({data.Keys.Aggregate((x, y) => $"{x.EscapeString()}, {y.EscapeString()}")}) VALUES ({data.Values.Aggregate((x, y) => $"{x}, {y}")})");
        }

        /// <inheritdoc />
        public async Task Update(string table, Dictionary< string, dynamic > newData, string queryColumn, dynamic queryValue) {
            var sb = new StringBuilder();
            foreach (KeyValuePair<string,dynamic> k in newData) {

                sb.Append(long.TryParse(Convert.ToString(k.Value), out long v)
                              ? $"`{k.Key}`={v},"
                              : $"`{k.Key}`='{k.Value}',");
            }
            string query =
                $"UPDATE {table.EscapeString()} set {sb.ToString().TrimEnd(",".ToCharArray())} WHERE `{queryColumn}`='{queryValue}'";
            _log.Info(query);
            await Execute(query);
        }

        /// <inheritdoc />
        public async Task Delete(string table, int index) {
            await Delete(table, $"INDEX=`{MySqlHelper.EscapeString(index.ToString())}`");
        }

        /// <inheritdoc />
        public async Task Delete(string table, string query) {

            await Execute($"DELETE FROM `{table}` WHERE `{query}`;");
        }

        public async Task< bool > Exists(string table, string column, dynamic entry) {
            bool ret = false;
            long te = Global.ToUnixTime(entry);
            DbDataReader reader = await ExecuteReader($"SELECT * FROM `{table}` WHERE `{column}`='{te}'");
            while (await reader.ReadAsync()) {
                dynamic ts = reader.GetFieldValueAsync< dynamic >(0);
                if (await ts != te) continue;
                ret = true;
                break;
            }
            
            reader.Close();
            return ret;
        }

        /// <inheritdoc />
        public async Task Connect() {
            await _con.OpenAsync();
        }

        /// <inheritdoc />
        public async Task Disconnect() {
            await _con.CloseAsync();
        }

        public async Task< int > GetUsername() {
            DbDataReader db = await ExecuteReader($"SELECT `userid` FROM `userdb` WHERE `username`='{_config.Username}'");
            await db.ReadAsync();
            int ret = (int) db[ 0 ];
            db.Close();
            return ret;
        }

        #endregion

        // ReSharper disable CS8625
        public async Task< List< object > > GetEntry(string table, string column, string queryColumn = "",
                                                       dynamic queryValue = default) {
            string query = queryColumn.IsEmpty()
                ? $"SELECT {column} from `{table}`"
                : $"SELECT {column} from `{table}` where `{queryColumn}`='{queryValue}'";
            
            _log.Info(query);
            DbDataReader reader = await ExecuteReader(query);

            List< object > lst = new List< object >();
            await reader.ReadAsync();
            object[] values = new object[reader.FieldCount];
            reader.GetValues(values);
            lst.AddRange(values);
            // while (await reader.ReadAsync()) {
            //     object[] values = new object[reader.FieldCount];
            //     reader.GetValues(values);
            //     lst.Add(values);
            // }
            reader.Close();
            return lst;
        }
    }
}