vpr-mitarbeiterverwaltung/Server/MySQL.cs

using System;
using System.Collections.Generic;
using System.Data;
using DX86.Modules;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;

namespace Server
{
    public class MySQL
    {
        private MySqlConnection _connection;
        private MessageSender ms;


        public MySQL(string username, string password, string server, string port, string database, MessageSender ms)
        {
            this.ms = ms;
            ms.Log("[MySQL] Initializing connection...");
            // include port in the connection string:
            string connectionString = $"Server={server};Port={port};Database={database};User ID={username};Password={password};";
            _connection = new MySqlConnection(connectionString);
            ms.Log("[MySQL] Connection to database...");

            try
            {
                _connection.Open();
                ms.Log("[MySQL] Connection established.");
            }
            catch (Exception ex)
            {
                ms.Error("[MySQL] Connection failed: " + ex.Message);
                ms.Error("[MySQL] Please check your connection settings.");
                ms.Error("[MySQL] Make sure the MySQL server is running and accessible.");
                ms.Error("[MySQL] A MySQL Connection is necessary to run this application.");
                ms.Error("[MySQL] Exiting...");
                Environment.Exit(1);
            }
        }

        private string JsonResponse(object data, bool error = false)
        {
            return JsonConvert.SerializeObject(new { error, data });
        }

        private string GetType(object value)
        {
            return value switch
            {
                int => "i",
                float or double => "d",
                string => "s",
                _ => "b"
            };
        }
        /// <summary>
        /// Executes a non‐query SQL statement (e.g. CREATE TABLE, ALTER, etc.).
        /// Returns null on success, or an error JSON if it fails.
        /// </summary>
        public string ExecuteNonQuery(string sql)
        {
            using var cmd = new MySqlCommand(sql, _connection);
            try
            {
                cmd.ExecuteNonQuery();
                return JsonResponse(new { success = true });
            }
            catch (Exception ex)
            {
                return JsonResponse($"Execute failed: {ex.Message}", true);
            }
        }
        
        public string Insert(string table, Dictionary<string, object> data)
        {
            string columns = string.Join(", ", data.Keys);
            string placeholders = string.Join(", ", new string[data.Count].Select(_ => "?"));

            string sql = $"INSERT INTO `{table}` ({columns}) VALUES ({placeholders})";
            using var cmd = new MySqlCommand(sql, _connection);

            string types = "";
            foreach (var value in data.Values)
            {
                types += GetType(value);
                cmd.Parameters.AddWithValue(null, value);
            }

            try
            {
                cmd.ExecuteNonQuery();
                long insertId = cmd.LastInsertedId;
                return JsonResponse(new { insert_id = insertId });
            }
            catch (Exception ex)
            {
                return JsonResponse($"Execute failed: {ex.Message}", true);
            }
        }

        public string Get(string table, Dictionary<string, object> parameters = null, int? limit = null, int? offset = null)
        {
            string sql = $"SELECT * FROM `{table}`";
            string types = "";
            var values = new List<object>();

            if (parameters != null && parameters.Count > 0)
            {
                var conditions = new List<string>();
                foreach (var param in parameters)
                {
                    conditions.Add($"`{param.Key}` = ?");
                    types += GetType(param.Value);
                    values.Add(param.Value);
                }
                sql += " WHERE " + string.Join(" AND ", conditions);
            }

            if (limit.HasValue)
            {
                sql += " LIMIT ?";
                types += "i";
                values.Add(limit.Value);
                if (offset.HasValue)
                {
                    sql += " OFFSET ?";
                    types += "i";
                    values.Add(offset.Value);
                }
            }

            using var cmd = new MySqlCommand(sql, _connection);
            for (int i = 0; i < values.Count; i++)
            {
                cmd.Parameters.AddWithValue(null, values[i]);
            }

            try
            {
                using var reader = cmd.ExecuteReader();
                var data = new List<Dictionary<string, object>>();
                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[reader.GetName(i)] = reader.GetValue(i);
                    }
                    data.Add(row);
                }
                return JsonResponse(data);
            }
            catch (Exception ex)
            {
                return JsonResponse($"Execute failed: {ex.Message}", true);
            }
        }

        public string Update(string table, Dictionary<string, object> data, Dictionary<string, object> where)
        {
            var setClauses = new List<string>();
            var whereClauses = new List<string>();
            string types = "";
            var values = new List<object>();

            foreach (var item in data)
            {
                setClauses.Add($"`{item.Key}` = ?");
                types += GetType(item.Value);
                values.Add(item.Value);
            }

            foreach (var condition in where)
            {
                whereClauses.Add($"`{condition.Key}` = ?");
                types += GetType(condition.Value);
                values.Add(condition.Value);
            }

            string sql = $"UPDATE `{table}` SET {string.Join(", ", setClauses)} WHERE {string.Join(" AND ", whereClauses)}";
            using var cmd = new MySqlCommand(sql, _connection);
            for (int i = 0; i < values.Count; i++)
            {
                cmd.Parameters.AddWithValue(null, values[i]);
            }

            try
            {
                int affectedRows = cmd.ExecuteNonQuery();
                return JsonResponse(new { affected_rows = affectedRows });
            }
            catch (Exception ex)
            {
                return JsonResponse($"Execute failed: {ex.Message}", true);
            }
        }

        public string Delete(string table, Dictionary<string, object> where)
        {
            var whereClauses = new List<string>();
            string types = "";
            var values = new List<object>();

            foreach (var condition in where)
            {
                whereClauses.Add($"`{condition.Key}` = ?");
                types += GetType(condition.Value);
                values.Add(condition.Value);
            }

            string sql = $"DELETE FROM `{table}` WHERE {string.Join(" AND ", whereClauses)}";
            using var cmd = new MySqlCommand(sql, _connection);
            for (int i = 0; i < values.Count; i++)
            {
                cmd.Parameters.AddWithValue(null, values[i]);
            }

            try
            {
                int affectedRows = cmd.ExecuteNonQuery();
                return JsonResponse(new { affected_rows = affectedRows });
            }
            catch (Exception ex)
            {
                return JsonResponse($"Execute failed: {ex.Message}", true);
            }
        }

        public string Query(string sql, string types = null, List<object> values = null)
        {
            using var cmd = new MySqlCommand(sql, _connection);
            if (types != null && values != null)
            {
                for (int i = 0; i < values.Count; i++)
                {
                    cmd.Parameters.AddWithValue(null, values[i]);
                }
            }

            try
            {
                using var reader = cmd.ExecuteReader();
                var data = new List<Dictionary<string, object>>();
                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[reader.GetName(i)] = reader.GetValue(i);
                    }
                    data.Add(row);
                }
                return JsonResponse(data);
            }
            catch (Exception ex)
            {
                return JsonResponse($"Query failed: {ex.Message}", true);
            }
        }
    }
}