using Oracle.ManagedDataAccess.Client;

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


namespace Stipendia
{

    public partial class Form1 : Form
    {
        string user = "Akivk0";
        string pwd = "system";
        string db = "localhost/";

        string conStringUser;


        public Form1()
        {
            InitializeComponent();
            conStringUser = $"User Id={user};Password={pwd};Data Source={db}/;";
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            PrintMainTable();
        }

        public void PrintMainTable()
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                try
                {
                    connection.Open();
                    using (OracleCommand cmd = new OracleCommand("SELECT * FROM AKSENOVSTREET", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            // Привязываем данные к DataGridView
                            mainDataBaseGrid.DataSource = dataTable;
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"Сообщение: {ex.Message}", "Ошибка!");
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                connection.Open();

                string query =
                @"UPDATE AKSENOVSTREET
                SET ДОМ = ДОМ - 50
                WHERE УЛИЦА = 'ул. Энгельса' AND ДОМ >= 51
                ";

                OracleCommand cmd = new OracleCommand(query, connection);

                string query1 =
               @"UPDATE AKSENOVSTREET
                SET 
                    ДОМ = 22,
                    КВАРТИРА = 0
                WHERE УЛИЦА = 'ул. Энгельса'
                AND (ДОМ IS NULL OR ДОМ = 0)
                ";

                OracleCommand cmd1 = new OracleCommand(query1, connection);

                string query2 =
              @"UPDATE AKSENOVSTREET
                SET 
                    УЛИЦА = 'ул. Милютина'
                WHERE УЛИЦА = 'ул. Энгельса'
                ";

                OracleCommand cmd2 = new OracleCommand(query2, connection);

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd1.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();

                }
                catch (Exception ex)
                {
                    MessageBox.Show($"{ex.Message}", $"Ошибка! Запись не удалена!");
                }

                connection.Close();

                PrintMainTable();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            using (OracleConnection connection = new OracleConnection(conStringUser))
            {
                connection.Open();

                string query =
                @"DELETE FROM AKSENOVSTREET";

                OracleCommand cmd = new OracleCommand(query, connection);


                string query2 =
              @"INSERT INTO AKSENOVSTREET SELECT * FROM AKSENOVSTREET_COPY";

                OracleCommand cmd2 = new OracleCommand(query2, connection);

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd2.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show($"{ex.Message}", $"Ошибка! Запись не удалена!");
                }
                connection.Close();

                PrintMainTable();
            }
        }
    }
}