PEPTool/AV-ToolV3/SQL.cs

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity.Infrastructure;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

namespace PEP_Tool
{
    public static class SQL
    {
        private static string p = "TTk3M3ibIPeyTagYPbtY6jtR9qHbL8DdtzhEwbGlMEVimC0BJb1Q3UEjGoKVRC9L";
        private static string s = "00dv35EtwBftlIyfGhp9UA==";
        private static string u = "MXgxjJ/UrLOYrW5RLTUSFA==";
        private static string uSSL = "PK9zR2pVrqcp0rrEUcdoqg==";
        private static string d = "r2ryY4uVzlr/xjohFwdDZg==";

        static MySqlConnection conn = new MySqlConnection(GetConnstr());
        static MySqlDataAdapter adp = new MySqlDataAdapter();


        private static string GetIP()
        {
            return Crypto.Decrypt(Crypto.GetHashKey("32F34372C1FF605B409C22EF135ACEE1"), s);
        }

        internal static string GetConnstr()
        {
            return $"Server={GetIP()};" +
                    $"Uid={Crypto.Decrypt(Crypto.GetHashKey("93380A87635F99B4A25FFA6E62A2AB59"), uSSL)};" +
                    $"Pwd={Crypto.Decrypt(Crypto.GetHashKey("DD18BA4FD3CED369794C8E020E7180E7"), p)};" +
                    $"database={Crypto.Decrypt(Crypto.GetHashKey("35E41210D890F44186F62DA1C6BA030F"), d)};" +
                    $"SslMode=Required;";
            //return $"Server={GetIP()};" +
            //    $"Uid={Crypto.Decrypt(Crypto.GetHashKey("494094CF6E4DA4B0E4C8CF4AC418BFC0"), u)};" +
            //    $"Pwd={Crypto.Decrypt(Crypto.GetHashKey("DD18BA4FD3CED369794C8E020E7180E7"), p)};" +
            //    $"database={Crypto.Decrypt(Crypto.GetHashKey("35E41210D890F44186F62DA1C6BA030F"), d)}";
        }



        public static MySqlDataAdapter GetAdapter()
        {
            return adp;
        }

        public static MySqlConnection GetConnection()
        {
            if (CheckDeviceConnection())
            {
                return conn;
            }
            else
            {
                if (System.Windows.MessageBox.Show(MainWindow.main, $"Server ist nicht erreichbar\n" +
                    $"Netzwerkverbindung überprüfen\n" +
                    $"Sie können versuchen erneut zu Verbinden, andernfalls die die Anwendung beendet\n\n" +
                    $"Verbindung erneut versuchen?", "Fehler", System.Windows.MessageBoxButton.YesNo, System.Windows.MessageBoxImage.Error) == System.Windows.MessageBoxResult.Yes)
                    return GetConnection();
                else
                {
                    Application.Current.Shutdown();
                    return conn;
                }
            }
        }

        public static bool CheckDeviceConnection()
        {
            System.Net.NetworkInformation.Ping ping = new System.Net.NetworkInformation.Ping();
            //change the following ip variable into the ip adress you are looking for

            System.Net.IPAddress address = System.Net.IPAddress.Parse(GetIP());
            System.Net.NetworkInformation.PingReply pong = ping.Send(address);
            if (pong.Status == System.Net.NetworkInformation.IPStatus.Success)
            {
                return true;
            }
            else
            {                
                return false;
            }
        }

        public static async Task<DataTable> WriteSQL(string SQLQuery, DataTable list)
        {
            try
            {
                var filter = list.DefaultView.RowFilter;
                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                    conn.Open();
                    adp.UpdateCommand = new MySqlCommand(SQLQuery, conn);

                    adp.Update(list);

                    list.AcceptChanges();

                    //File.WriteAllText(@"\\mhb00swfs003v.fv-werke.db.de\GLW99\PEP\sql.lck", "erwischt!!");
                    if (!SQLQuery.Contains("track"))
                        WriteLastUpdate();
                    //if (SQLQuery.Contains("track"))
                    //    File.WriteAllText(@"\\mhb00swfs003v.fv-werke.db.de\GLW99\PEP\track.lck", "erwischt!!");

                    conn.Close();
                }

                list.DefaultView.RowFilter = filter;
            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }

            return list;
        }

        public static async Task WriteSQL(string SQLQuery)
        {
            try
            {
                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand(SQLQuery, conn);
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    if (!SQLQuery.Contains("track"))
                        WriteLastUpdate();

                    //if (SQLQuery.Contains("track"))
                    //    File.WriteAllText(@"\\mhb00swfs003v.fv-werke.db.de\GLW99\PEP\track.lck", "erwischt!!");
                }
            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }
        }


        private static void WriteLastUpdate()
        {
            try
            {
                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                    conn.Open();

                    MySqlCommand cmd = new MySqlCommand($"UPDATE pep_tool.misc SET `Value`='{DateTime.Now.ToString("dd.MM.  HH:mm:ss")}' WHERE `idmisc`='LastUpdate'", conn);
                    cmd.ExecuteNonQuery();
                    conn.Close();

                    //if (SQLQuery.Contains("track"))
                    //    File.WriteAllText(@"\\mhb00swfs003v.fv-werke.db.de\GLW99\PEP\track.lck", "erwischt!!");
                }
            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }
        }


        public static async Task<DataTable> ReadSQL(string SQLCommand, DataTable list)
        {
            try
            {
                var filter = list.DefaultView.RowFilter;

                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                    conn.Open();
                    adp.SelectCommand = new MySqlCommand(SQLCommand, conn);

                    list.Clear();
                    adp.Fill(list);
                    conn.Close();
                }

                list.DefaultView.RowFilter = filter;
            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }

            return list;
        }

        public static async Task<DataTable> ReadSQL(string SQLCommand)
        {
            DataTable list = new DataTable();

            try
            {

            using (GetConnection())
            {
                if (conn.State == ConnectionState.Open) conn.Close();

                conn.Open();
                adp.SelectCommand = new MySqlCommand(SQLCommand, conn);

                list.Clear();
                adp.Fill(list);
                conn.Close();
            }

            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }

            return list;
        }



        public static async Task<DataTable> ReadAllData(DataTable list)
        {
            var filter = list.DefaultView.RowFilter;
            try
            {
                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                    conn.Open();
                    adp.SelectCommand = new MySqlCommand("select PersNr,Name,Vorname,Abteilung,o.Datum,Beginn,Gehen,Tagescode,Arbeitsmuster,Zuweisung,Bemerkung,Telefon,AnzahlLizenzen,ZugNameB from pep_tool.mitarbeiter_static p left join pep_tool.mitarbeiter_change o ON o.mitarbeiter_static_PersNr = p.PersNr ORDER BY Name", conn);
                    list.Clear();
                    adp.Fill(list);
                    conn.Close();
                }

                list.DefaultView.RowFilter = filter;
            }
            catch (Exception ex)
            {
                LogFile.WriteLine(ex.ToString());
            }

            return list;
        }

        public static async Task<DataTable> ReadAllData(string filter = "")
        {
            try
            {
                DataTable list = new DataTable("UserList");

                using (GetConnection())
                {
                    if (conn.State == ConnectionState.Open) conn.Close();

                    conn.Open();
                    adp.SelectCommand = new MySqlCommand("select PersNr,Name,Vorname,Abteilung,o.Datum,Beginn,Gehen,Tagescode,Arbeitsmuster,Zuweisung,Bemerkung,Telefon,AnzahlLizenzen,ZugNameB from pep_tool.mitarbeiter_static p left join pep_tool.mitarbeiter_change o ON o.mitarbeiter_static_PersNr = p.PersNr ORDER BY Name", conn);
                    list.Columns.Clear();
                    adp.Fill(list);
                    conn.Close();
                }

                list.DefaultView.RowFilter = filter;
                return list;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex);
                return await ReadAllData();
            }
        }
    }
}