// Used stackoverflow forums & youtube videos for help on this part
// Will use requests class to perform queries, display databases, and verify admin/owner login information
// Must be static because this class will NOT be instantiated
// First time using try, catch, and finally (classmate Lou presented this). So far it works
// https://stackoverflow.com/questions/651619/what-is-the-advantage-of-using-try-catch-versus-if-else
// https://stackoverflow.com/questions/3042474/when-is-it-worth-using-a-bindingsource
//

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CSC330_CSharp_Midterm
{
    public static class Requests
    {
        public static BindingSource Select(string command)
        {
            using (SqlConnection connection = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BD.mdf;Integrated Security=True")) // the connection string to the database
            {
                SqlCommand com = new SqlCommand(command, connection);
                connection.Open();
                com.ExecuteNonQuery(); // perform a query in the database
                DataTable tab = new DataTable();
                tab.Load(com.ExecuteReader()); // the query result into a table in the database
                BindingSource bs = new BindingSource();
                bs.DataSource = tab.DefaultView; // assign the table to the data source, which is then assigned to the table on the form               
                connection.Close();
                return bs;
            }
        }        

        public static string Request(string request)
        {
            string errorMessage = "";
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BD.mdf;Integrated Security=True");

            try
            {                
                con.Open();
                SqlCommand com = new SqlCommand(request, con);
                com.ExecuteNonQuery(); // perform a query in the database                
            }
            catch (Exception ex)
            {
                errorMessage = ex.ToString();
            }
            finally
            {
                con.Close();
            }

            return errorMessage;
        }

        public static bool CheckAdmin(string request, string field1) 
        {
            bool flag = true;
            string field2 = "";
            SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\BD.mdf;Integrated Security=True");

            try
            {
                con.Open();
                SqlCommand com = new SqlCommand(request, con);
                field2 = com.ExecuteScalar().ToString(); // the username of the administrator is found by the entered password                
            }
            catch
            {
                flag = false;
            }
            finally
            {
                con.Close();
            }

            if (field1 == field2) // admin login found by the entered name and password in the program are equal
            {
                flag = true;
            }
            else // admin login found by the entered name and password in the program are not equal
            {
                flag = false;
            }

            return flag;
        }        
    }
}