using System;
using System.Collections.Generic;
using System.Data;
using System.Net;
using Microsoft.Data.SqlClient;

namespace SQL
{
    class program
    {
           class sqlhelper
           {
            internal static String getConnectionString()
            {
                string sqlConnection = "server=FE005-H033";
                sqlConnection += "; Trusted_Connnection=no;database=AZUBI_Test";
                sqlConnection += "; connection Timeout=10; MultipleActiveResultSets=True; TrustServerCertificate=True";

                return sqlConnection;
            }
            internal static SqlCredential GetSqlCredential()
            {
                System.Security.SecureString loginpassword = new NetworkCredential("", "Cufingmbh2").SecurePassword;
                loginpassword.MakeReadOnly();
                SqlCredential Logindata = new SqlCredential("AYUBI_Test", loginpassword);
                return Logindata;
            }
            public static DataSet getDataFromTable(string table)
            {
                string sQuery = $"SELECT * FROM {table}";
                DataSet Datensatz = excecuteQuery(sQuery);
                return Datensatz;
            }
            public static DataSet getDataFromTable()
            {
                string sQuery = "SELECT * FROM Sleman_Credentials";
                DataSet Datensatz = excecuteQuery(sQuery);
                return Datensatz;
            }
            public static DataSet getDataFromTableColumns(string table, string lst_columns)
            {
                string sQuery = $"SELECT {lst_columns} FROM {table}";
                return excecuteQuery(sQuery);

            }
            public static DataSet deleteDataFromTale(int iDel)
            {
                String sQuery = "DELETE FROM Sleman_Credentials Where ID=" + iDel;
                DataSet Datensatz = excecuteQuery(sQuery);
                return Datensatz;

            }
            public static DataSet excecuteQuery(string abfrage)
            {
                DataSet AbfrargeErgebnis = new DataSet();

                using (SqlConnection sqlConn = new SqlConnection(sqlhelper.getConnectionString(), sqlhelper.GetSqlCredential()))
                {
                    using (SqlCommand sqlCommand = new SqlCommand(abfrage, sqlConn))
                    {
                        using (SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCommand))
                        {
                            sqlDataAdap.Fill(AbfrargeErgebnis);
                        }
                    }
                }
                return AbfrargeErgebnis;
            }
            public static string insertCredentials(object[,] liste)
            {
                int loops = liste.GetLength(0);
                string antwort;
                for (int i = 0; i < loops; i++)
                {
                    int iStringCounter = i + 1;
                    string Eingabeabfrage = "INSERT INTO Sleman_Credentials(ID, Vorname, Nachname, Geschlecht, PLZ) " + "VALUES((SELECT Max(ID) + " + iStringCounter + " From Sleman_Credentials), '" + liste[i, 0].ToString()
                        + "', '" + liste[i, 1].ToString() + "', '" + liste[i, 2].ToString() + "', '" + liste[i, 3].ToString() + ")";
                    SQLBefehlAusfuehren(Eingabeabfrage);






                }
                antwort = "Es Wurde " + loops.ToString() + "Datensätze gespeichert";
                Console.WriteLine(antwort);
                return antwort;
            }
            public static bool SQLBefehlAusfuehren(string sQuery)
            {
                bool bResult = true;
                Int32 iAffected;

                using (SqlConnection sqlConn = new SqlConnection(sqlhelper.getConnectionString(),
                sqlhelper.GetSqlCredential()))
                {
                    sqlConn.Open();
                    using (SqlTransaction SQLTrans = sqlConn.BeginTransaction())
                    {
                        try
                        {
                            using (SqlCommand SQLBefehl = new SqlCommand(sQuery, sqlConn, SQLTrans))
                            {
                                iAffected = SQLBefehl.ExecuteNonQuery();
                            }
                            if (iAffected == 0)
                                bResult = false;
                            if (bResult)
                                SQLTrans.Commit();
                            else
                                SQLTrans.Rollback();
                        }
                        catch (Exception Fehlermeldung)
                        {
                            Console.WriteLine("Es ist ein Datenbankfehler aufgetreten.\nError: " + Fehlermeldung.Message);

                            if (SQLTrans.Connection != null)
                                SQLTrans.Rollback();
                        }
                    }

                    return bResult;
                }
            }
           }
        
    }
}