namespace IT_A_stroy
{
    public partial class Form3 : Form
    {
        DataSet ds;
        MySqlDataAdapter adapter;
        MySqlDataAdapter adapter1;
        MySqlCommandBuilder commandBuilder;
        MySqlCommandBuilder commandBuilder1;
        //string sql = "select 'Name_works', 'Size_works', 'Unit_of_size'";
        readonly string connectionString = "server=localhost;user=root;database=a_stroy;password=q1w2e3r4t5y6u7i8o9p0;";
        string sql = "select Name_works, Size_works, Unit_of_size from a_stroy.words_object where 1=2";

        public Form3()
        {
            InitializeComponent();
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            dataGridView1.AllowUserToAddRows = false;
            
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                ds = new DataSet();
                adapter = new MySqlDataAdapter(sql, connection);
                adapter1 = new MySqlDataAdapter(sql, connection);
                adapter.Fill(ds);
                adapter1.Fill(ds);
                dataGridView1.DataSource = ds.Tables[0];
            }
        }
        public object SelectItem { get; set; }

        //public void comboBox1_Click(object sender, EventArgs e) 
        //{
        //    int selected_id = comboBox1.SelectedIndex;
        //    Object selectItem = comboBox1.SelectedItem;
        //}

        //public bool Visible { get; set; }
        private void InsertFromDataGridView_Load(object sender, EventArgs e)
        {
            dataGridView1.AutoGenerateColumns = false;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection connection = new MySqlConnection(connectionString);// объект для установления соединения с БД
            connection.Open();// открываем соединение
                              // запросы
                              // запрос вставки данных в Object
                              //string query = "INSERT INTO Object (id_customer, Name_object, Start_construction, End_construction, Number_contract, Type_of_object, Type_of_construction, Length, Width, Heigth, Name_general_work, Name_proect_organization) VALUES ('" + comboBox1.SelectedValue + "','" + textBox1.Text + "','" + dateTimePicker1.Value.ToShortDateString() + "','" + dateTimePicker2.Value.ToShortDateString() + "','" + textBox2.Text + "','" + comboBox2.SelectedItem + "','" + comboBox3.SelectedItem + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text + "','" + textBox6.Text + "','" + textBox7.Text + "')";// запрос вставки данных
                              //LAST_INSERT_ID()

            // INSERT INTO table_name(col1, col2, ...) VALUES('val1', 'val2'...);
            //MySqlCommand command = new MySqlCommand(query, connection);// объект для выполнения SQL-запроса
            //command.ExecuteNonQuery();// выполняем запрос
            adapter1 = new MySqlDataAdapter(sql, connection);
            commandBuilder1 = new MySqlCommandBuilder(adapter1);
            MySqlCommand mySqlCommand = new MySqlCommand("sp_createObject", connection);
            adapter1.InsertCommand = mySqlCommand;
            adapter1.InsertCommand.CommandType = CommandType.StoredProcedure;
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Name_object", MySqlDbType.VarChar, 255, "Name_object"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Start_construction", MySqlDbType.Date, 50, "Start_construction"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@End_construction", MySqlDbType.Date, 50, "End_construction"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Number_contract", MySqlDbType.VarChar, 255, "Number_contract"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Type_of_object", MySqlDbType.VarChar, 255, "Type_of_object"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Type_of_construction", MySqlDbType.VarChar, 255, "Type_of_construction"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Length", MySqlDbType.Float, 50, "Length"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Width", MySqlDbType.Float, 50, "Width"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Heigth", MySqlDbType.Float, 50, "Heigth"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Name_general_work", MySqlDbType.VarChar, 255, "Name_general_work"));
            adapter1.InsertCommand.Parameters.Add(new MySqlParameter("@Name_proect_organization", MySqlDbType.VarChar, 255, "Name_proect_organization"));
            MySqlParameter parameter2 = adapter1.InsertCommand.Parameters.Add("@id_Object", MySqlDbType.Int32, 0, "id_Object");
            MySqlParameter parameter3 = adapter1.InsertCommand.Parameters.Add("@id_customer", MySqlDbType.Int32, 0, "id_customer");
            parameter2.Direction = ParameterDirection.Output;
            parameter3.Direction = ParameterDirection.Output;
            //adapter1.Direction = ParameterDirection.ReturnValue;
            //adapter1.InsertCommand.ExecuteNonQuery();
            //var result = adapter1.Value;

            adapter = new MySqlDataAdapter(sql, connection);
            commandBuilder = new MySqlCommandBuilder(adapter);
            adapter.InsertCommand = new MySqlCommand("sp_CreateUser", connection);
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
            adapter.InsertCommand.Parameters.Add(new MySqlParameter("@Name_works", MySqlDbType.VarChar, 50, "Name_works"));
            adapter.InsertCommand.Parameters.Add(new MySqlParameter("@Size_works", MySqlDbType.Float, 50, "Size_works"));
            adapter.InsertCommand.Parameters.Add(new MySqlParameter("@Unit_of_size", MySqlDbType.VarChar, 50, "Unit_of_size"));
            MySqlParameter parameter = adapter.InsertCommand.Parameters.Add("@id_words", MySqlDbType.Int32, 0, "id_words");
            MySqlParameter parameter1 = adapter.InsertCommand.Parameters.Add("@id_Object", MySqlDbType.Int32, 0, "id_Object");
            parameter.Direction = ParameterDirection.Output;
            parameter1.Direction = ParameterDirection.Output;
            
            adapter1.Update(ds);
            adapter.Update(ds);
            
            connection.Close();
            Close();
        }

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_CreateUser`(in Name_works VarChar(255), 
in Size_works Float, in Unit_of_size VarChar(100), out id_words integer, out id_Object integer)
Begin
INSERT INTO Words_object (Name_works, Size_works, Unit_of_size)
VALUES (Name_works, Size_works, Unit_of_size);
SET id_words=LAST_INSERT_ID();
End

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_creteObject`(out Name_object  VarChar(255), in Start_construction Date, 
in End_construction Date, in Number_contract VarChar(255), in Type_of_object VarChar(255), in Type_of_construction VarChar(255), 
in Length Float, in Width Float, in Heigth Float, in Name_general_work VarChar(255), in Name_proect_organization VarChar(255), 
inout id_Object integer, inout id_customer integer)
Begin
INSERT INTO Object (id_customer, Name_object, Start_construction, End_construction, Number_contract, Type_of_object, Type_of_construction, Length, Width, Heigth, Name_general_work, Name_proect_organization)
VALUES (id_customer, Name_object, Start_construction, End_construction, Number_contract, Type_of_object, Type_of_construction, Length, Width, Heigth, Name_general_work, Name_proect_organization);
SET id_Object=LAST_INSERT_ID();
SET id_customer=LAST_INSERT_ID();
End

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `sp_createObject`(out Name_object  VarChar(255), in Start_construction Date, in End_construction Date, in Number_contract VarChar(255), in Type_of_object VarChar(255), in Type_of_construction VarChar(255), in Length Float, in Width Float, in Heigth Float, in Name_general_work VarChar(255), in Name_proect_organization VarChar(255), out id_Object integer, out id_customer integer)
Begin
INSERT INTO Object (id_customer, Name_object, Start_construction, End_construction, Number_contract, Type_of_object, Type_of_construction, Length, Width, Heigth, Name_general_work, Name_proect_organization)
VALUES (id_customer, Name_object, Start_construction, End_construction, Number_contract, Type_of_object, Type_of_construction, Length, Width, Heigth, Name_general_work, Name_proect_organization);
SET id_Object=LAST_INSERT_ID();
SET id_customer=LAST_INSERT_ID();
End