SQL

namespace UnixEvents
{
    using System;
    using System.Data.SqlClient;
    using System.Data;
    using System.Threading;
    using System.Windows.Forms;
    using System.Diagnostics;
    using System.Collections.Generic;

    class SQL
    {
        private static SqlConnection sqlconn;



        public static bool ExecuteQuery(string Query, SqlConnection con)
        {

            using (con)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(Query, con);
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    return true;
                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> Function ExecuteQuery(" + Query + "), exception catched: " + ex.Message);
                   // MessageBox.Show(ex.Message);
                    return false;
                }
            }

        }
        public static uint ReadUint(string Query, SqlConnection con)
        {
            uint Result = 0;
            using (con)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(Query, con);
                    con.Open();
                    cmd.CommandType = CommandType.Text;
                    Result = Convert.ToUInt32(cmd.ExecuteScalar());


                    con.Close();

                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> Function ReadIntValue(" + Query + "), exception catched: " + ex.Message);


                }
            }
            return Result;
        }

        public static int ReadInt(string Query, SqlConnection con)
        {
            int Result = 0;
            using (con)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(Query, con);
                    con.Open();
                    cmd.CommandType = CommandType.Text;
                    Result = Convert.ToInt32(cmd.ExecuteScalar());


                    con.Close();

                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> Function ReadIntValue(" + Query + "), exception catched: " + ex.Message);


                }
            }
            return Result;
        }
        public static ulong ReadUlong(string Query, SqlConnection con)
        {
            ulong Result = 0;
            using (con)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(Query, con);
                    con.Open();
                    cmd.CommandType = CommandType.Text;
                    Result = Convert.ToUInt64(cmd.ExecuteScalar());


                    con.Close();

                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> Function ReadUlongValue(" + Query + "), exception catched: " + ex.Message);


                }
            }
            return Result;
        }
        public static string ReadString(string Query, SqlConnection con)
        {
            string Result = "";
            using (con)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand(Query, con);
                    con.Open();
                    cmd.CommandType = CommandType.Text;
                    Result = (string)cmd.ExecuteScalar();


                    con.Close();

                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> Function ReadString(" + Query + "), exception catched: " + ex.Message);


                }
            }
            return Result;
        }

        public static string[] getSingleArrayData(string Query, SqlConnection con)
        {
            try
            {
                SqlDataAdapter SqlAD = new SqlDataAdapter();
                SqlAD.SelectCommand = new SqlCommand(Query, con);
                con.Open();
                DataSet ds = new DataSet();
                SqlAD.Fill(ds);

                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count != 0)
                {
                    string[] arr = new string[dt.Rows[0].ItemArray.Length];

                    arrayInit.InitStringArray(ref arr);


                    DataRow row = dt.Rows[0]; //first array

                    for (int i = 0; i < dt.Rows[0].ItemArray.Length; i++)
                    {
                        arr[i] = row[i].ToString();

                    }
                    return arr;

                }
                con.Close();
            }

            catch (Exception ex)
            {

                Log.UpdateLogs(
                          LoggerLevel.LogLevel.Error, "MSSQL Error -> Function getStringArray(" + Query + "), exception catched: " + ex.Message);

            }
            return null;
        }

        public static string[] getColumnRowsData(string Query, SqlConnection con)
        {
            try
            {
                SqlDataAdapter SqlAD = new SqlDataAdapter();
                SqlAD.SelectCommand = new SqlCommand(Query, con);

                DataSet ds = new DataSet();
                SqlAD.Fill(ds);

                DataTable dt = ds.Tables[0];
                string[] arr = new string[dt.Rows.Count];

                int i = 0;
                foreach (DataRow row in dt.Rows)
                {
                    //MessageBox.Show(row[0].ToString());
                    arr[i] = row[0].ToString();
                    i++;
                }

                return arr;

            }
            catch (Exception ex)
            {
                Log.UpdateLogs(
                          LoggerLevel.LogLevel.Error, "MSSQL Error -> Function getStringArray(" + Query + "), exception catched:" + ex.Message);


            }
            return null;
        }

        public static IEnumerable<uint> getIDs(string query, string colname, SqlConnection con)
        {


            using (con)
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            yield return Convert.ToUInt32(reader.GetInt32(reader.GetOrdinal(colname)));
                        }
                    }
                }
            }



        }


        public static SqlConnection CurrentConnection(string Database)
        {
            string ConString = "Data Source=" + SQLSettings.SqlServername + ";initial catalog=" + Database + ";user id=" + SQLSettings.SqlUsername + ";password=" + SQLSettings.SqlPassword;
            sqlconn = new SqlConnection(ConString);
            return sqlconn;
        }
        public static string GetConnectionString(string Database)
        {
            string ConString = "Data Source=" + SQLSettings.SqlServername + ";initial catalog=" + Database + ";user id=" + SQLSettings.SqlUsername + ";password=" + SQLSettings.SqlPassword;
            sqlconn = new SqlConnection(ConString);
            return ConString;
        }

        public static bool HaveConnection(SqlConnection con)
        {

            using (con)
            {
                try
                {
                    //Thread.Sleep(1000);
                    con.Open();

                    //Log.UpdateLogs(
                    //               LoggerLevel.LogLevel.Notify, $"{con.Database.ToString()} has been initialized successufly");

                    MessageBox.Show($"{con.Database.ToString()} has been initialized successufly");
                    con.Close();
                    return true;
                }
                catch (Exception ex)
                {
                    Log.UpdateLogs(
                        LoggerLevel.LogLevel.Error, "MSSQL Error -> exception catched: " + ex.Message);
                    return false;


                }


            }


        }
        public static bool IsConnected()
        {
            try
            {
                if (sqlconn.State == ConnectionState.Open)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {

                Log.UpdateLogs(
                    LoggerLevel.LogLevel.Error, ex.Message);
                return false;
            }
            return false;
        }
    }
    class arrayInit
    {
        public static void InitStringArray(ref string[] arr)
        {
            for (int i = 0; i < arr.Length; i++)
            {
                arr[i] = "";
            }
        }

        public static void InitIntArray(ref int[] arr)
        {
            for (int i = 0; i < arr.Length; i++)
            {
                arr[i] = 0;
            }
        }

        public static void InitBoolArray(ref bool[] arr)
        {
            for (int i = 0; i < arr.Length; i++)
            {
                arr[i] = false;
            }
        }
    }
}