using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace lab9
{
    public partial class Form1 : Form
    {
        public string ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\Database.accdb";
        public OleDbConnection connection;
        public string table1_name = "Товары";
        public string table2_name = "Поставщики";
        public string table3_name = "Категории";
        public Form1()
        {
            InitializeComponent();
            OleDbDataAdapter dA1 = new OleDbDataAdapter($"SELECT * FROM {table1_name}", connection);
            OleDbDataAdapter dA2 = new OleDbDataAdapter($"SELECT * FROM {table2_name}", connection);
            OleDbDataAdapter dA3 = new OleDbDataAdapter($"SELECT * FROM {table3_name}", connection);
            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            DataSet ds3 = new DataSet();
            dA1.Fill(ds1);
            dA2.Fill(ds2);
            dA3.Fill(ds3);
            table1.DataSource = ds1.Tables[0].DefaultView;
            table2.DataSource = ds2.Tables[0].DefaultView;
            table3.DataSource = ds3.Tables[0].DefaultView;
        }
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            string cmd1 = $"DELETE * FROM {table1_name}";
            OleDbCommand command1 = new OleDbCommand(cmd1, connection);
            connection.Open();
            command1.ExecuteNonQuery();
            connection.Close();
            cmd1 = $"DELETE * FROM {table2_name}";
            command1 = new OleDbCommand(cmd1, connection);
            connection.Open();
            command1.ExecuteNonQuery();
            connection.Close();
            foreach (DataGridViewRow row in table1.Rows)
            {
                List<string> columns = new List<string>();
                List<string> params_ = new List<string>();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    columns.Add(cell.OwningColumn.Name);
                    params_.Add(cell.Value == null ? "" : cell.Value.ToString());
                }
                bool s = false;
                foreach (string str in params_)
                    s = str.Length == 0;
                if (s) continue;
                string cls = "(";
                string vals = "(";
                for (int i = 0; i < columns.Count; i++)
                {
                    cls += $"`{columns[i]}`,";
                    vals += $"@value{i},";
                }
                cls = cls.Substring(0, cls.Length - 1) + ")";
                vals = vals.Substring(0, vals.Length - 1) + ")";
                string cmd = $"INSERT INTO `{table1_name}` {cls} VALUES {vals}";
                OleDbCommand command = new OleDbCommand(cmd, connection);
                for (int i = 0; i < columns.Count; i++)
                    command.Parameters.Add($"@value{i}", OleDbType.VarChar).Value = params_[i];
                connection.Open();
                try { command.ExecuteNonQuery(); } catch { }
                connection.Close();
            }
            foreach (DataGridViewRow row in table2.Rows)
            {
                List<string> columns = new List<string>();
                List<string> params_ = new List<string>();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    columns.Add(cell.OwningColumn.Name);
                    params_.Add(cell.Value == null ? "" : cell.Value.ToString());
                }
                bool s = false;
                foreach (string str in params_)
                    s = str.Length == 0;
                if (s) continue;
                string cls = "(";
                string vals = "(";
                for (int i = 0; i < columns.Count; i++)
                {
                    cls += $"`{columns[i]}`,";
                    vals += $"?,";
                }
                cls = cls.Substring(0, cls.Length - 1) + ")";
                vals = vals.Substring(0, vals.Length - 1) + ")";
                string cmd = $"INSERT INTO `{table2_name}` {cls} VALUES {vals}";
                OleDbCommand command = new OleDbCommand(cmd, connection);
                for (int i = 0; i < columns.Count; i++)
                    command.Parameters.AddWithValue($"@{columns[i]}", params_[i]);
                connection.Open();
                try { command.ExecuteNonQuery(); } catch { }
                connection.Close();
            }
            foreach (DataGridViewRow row in table3.Rows)
            {
                List<string> columns = new List<string>();
                List<string> params_ = new List<string>();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    columns.Add(cell.OwningColumn.Name);
                    params_.Add(cell.Value == null ? "" : cell.Value.ToString());
                }
                bool s = false;
                foreach (string str in params_)
                    s = str.Length == 0;
                if (s) continue;
                string cls = "(";
                string vals = "(";
                for (int i = 0; i < columns.Count; i++)
                {
                    cls += $"`{columns[i]}`,";
                    vals += $"?,";
                }
                cls = cls.Substring(0, cls.Length - 1) + ")";
                vals = vals.Substring(0, vals.Length - 1) + ")";
                string cmd = $"INSERT INTO `{table3_name}` {cls} VALUES {vals}";
                OleDbCommand command = new OleDbCommand(cmd, connection);
                for (int i = 0; i < columns.Count; i++)
                    command.Parameters.AddWithValue($"@{columns[i]}", params_[i]);
                connection.Open();
                try { command.ExecuteNonQuery(); } catch { }
                connection.Close();
            }
        }

    }
}