private void button5_Click(object sender, EventArgs e) { string uno = "", use = "", rprice = "", sprice = "", sq = "", usq = "", rooms = "", housenum = "", dis = "", str = ""; if (_inn == 0) MessageBox.Show("Сначала выберите владельца!"); else { try { AddFacility w = new AddFacility(); w.ShowDialog(); if (w.DialogResult == DialogResult.OK) { string s = ""; bool samestr = false, samedis = false, samenum = false; w.get_data(out uno, out use, out rprice, out sprice, out sq, out usq, out rooms, out housenum, out dis, out str); mconnect.Open(); cmd[1] = new MySqlCommand(); cmd[1].Connection = mconnect; cmd[0] = new MySqlCommand(); cmd[0].Connection = mconnect; cmd[0].Prepare(); cmd[0].CommandText = @"SELECT Street FROM Streets"; rdr = cmd[0].ExecuteReader(); while (rdr.Read() && !samestr) { s = rdr.GetString(0); if (s == str) samestr = true; } rdr.Close(); if (samestr == false) { cmd[0].CommandText = @"INSERT INTO Streets(Street) VALUES( @str)"; cmd[0].Parameters.AddWithValue("@str", str); cmd[0].Prepare(); cmd[0].ExecuteNonQuery(); } cmd[2] = new MySqlCommand(); cmd[2].Connection = mconnect; cmd[2].Prepare(); cmd[2].CommandText = @"SELECT District FROM Districts"; rdr = cmd[2].ExecuteReader(); while (rdr.Read() && !samedis) { s = rdr.GetString(0); if (s == dis) samedis = true; } rdr.Close(); if (samedis == false) { cmd[2].CommandText = @"INSERT INTO Districts(District) VALUES( @dis)"; cmd[2].Parameters.AddWithValue("@dis", dis); cmd[2].Prepare(); cmd[2].ExecuteNonQuery(); } if (samestr && samedis) { cmd[2].Prepare(); cmd[2].CommandText = @"SELECT * FROM HouseNumber"; rdr = cmd[2].ExecuteReader(); while (rdr.Read() && !samenum) { s = rdr.GetString(3); if (s == housenum) samenum = true; } rdr.Close(); } if (!samenum) { _idhouse = KEY_GEN(@"SELECT idHouseNumber FROM HouseNumber"); cmd[3] = new MySqlCommand(); cmd[3].Connection = mconnect; cmd[3].CommandText = @"INSERT INTO HouseNumber(idHouseNumber, District_h, Street_h, Number) VALUES( @idHN, @dis, @str, @housenum)"; cmd[3].Prepare(); cmd[3].Parameters.AddWithValue("@idHN", _idhouse); cmd[3].Parameters.AddWithValue("@dis", dis); cmd[3].Parameters.AddWithValue("@str", str); cmd[3].Parameters.AddWithValue("@housenum", housenum); cmd[3].ExecuteNonQuery(); } if (samenum) { cmd[3] = new MySqlCommand(); cmd[3].Connection = mconnect; cmd[3].Prepare(); cmd[3].CommandText = @"SELECT idHouseNumber FROM HouseNumber WHERE HouseNumber.District_h = @dis AND HouseNumber.Street_h = @str AND HouseNumber.Number = @housenum"; cmd[3].Parameters.AddWithValue("@dis", dis); cmd[3].Parameters.AddWithValue("@str", str); cmd[3].Parameters.AddWithValue("@housenum", housenum); rdr = cmd[3].ExecuteReader(); while (rdr.Read()) _idhouse = rdr.GetInt32(0); rdr.Close(); } cmd[1].CommandText = @"INSERT INTO facility(numb, usag, RentPrice, SalePrice, Square, UsefulSquare, NumOfRooms, household, house, idfacility) VALUES( @uno, @use, @rprice, @sprice, @sq, @usq, @rooms, @inn, @idHN, @k)"; cmd[1].Prepare(); cmd[1].Parameters.AddWithValue("@uno", uno); cmd[1].Parameters.AddWithValue("@use", use); cmd[1].Parameters.AddWithValue("@rprice", rprice); cmd[1].Parameters.AddWithValue("@sprice", sprice); cmd[1].Parameters.AddWithValue("@sq", sq); cmd[1].Parameters.AddWithValue("@usq", usq); cmd[1].Parameters.AddWithValue("@rooms", rooms); cmd[1].Parameters.AddWithValue("@inn", _inn); cmd[1].Parameters.AddWithValue("@idHN", _idhouse); cmd[1].Parameters.AddWithValue("@k", KEY_GEN(@"SELECT idfacility FROM facility")); cmd[1].ExecuteNonQuery(); stm = "SELECT * FROM facility WHERE facility.household = @inn"; cmd[0] = new MySqlCommand(stm); cmd[0].Connection = mconnect; cmd[0].Prepare(); cmd[0].Parameters.AddWithValue("@inn", _inn); da[1] = new MySqlDataAdapter(cmd[0]); ds[1].Clear(); da[1].Fill(ds[1], "facility"); dataGridView2.DataSource = ds[1].Tables["facility"]; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } if (mconnect != null) mconnect.Close(); } }
private void button5_Click(object sender, EventArgs e) { string uno = "", use = "", rprice = "", sprice = "", sq = "", usq = "", rooms = "", housenum = "", dis = "", str = ""; if (_inn == 0) { MessageBox.Show("Сначала выберите владельца!"); } else { try { AddFacility w = new AddFacility(); w.ShowDialog(); if (w.DialogResult == DialogResult.OK) { string s = ""; bool samestr = false, samedis = false, samenum = false; w.get_data(out uno, out use, out rprice, out sprice, out sq, out usq, out rooms, out housenum, out dis, out str); mconnect.Open(); cmd[1] = new MySqlCommand(); cmd[1].Connection = mconnect; cmd[0] = new MySqlCommand(); cmd[0].Connection = mconnect; cmd[0].Prepare(); cmd[0].CommandText = @"SELECT Street FROM Streets"; rdr = cmd[0].ExecuteReader(); while (rdr.Read() && !samestr) { s = rdr.GetString(0); if (s == str) { samestr = true; } } rdr.Close(); if (samestr == false) { cmd[0].CommandText = @"INSERT INTO Streets(Street) VALUES( @str)"; cmd[0].Parameters.AddWithValue("@str", str); cmd[0].Prepare(); cmd[0].ExecuteNonQuery(); } cmd[2] = new MySqlCommand(); cmd[2].Connection = mconnect; cmd[2].Prepare(); cmd[2].CommandText = @"SELECT District FROM Districts"; rdr = cmd[2].ExecuteReader(); while (rdr.Read() && !samedis) { s = rdr.GetString(0); if (s == dis) { samedis = true; } } rdr.Close(); if (samedis == false) { cmd[2].CommandText = @"INSERT INTO Districts(District) VALUES( @dis)"; cmd[2].Parameters.AddWithValue("@dis", dis); cmd[2].Prepare(); cmd[2].ExecuteNonQuery(); } if (samestr && samedis) { cmd[2].Prepare(); cmd[2].CommandText = @"SELECT * FROM HouseNumber"; rdr = cmd[2].ExecuteReader(); while (rdr.Read() && !samenum) { s = rdr.GetString(3); if (s == housenum) { samenum = true; } } rdr.Close(); } if (!samenum) { _idhouse = KEY_GEN(@"SELECT idHouseNumber FROM HouseNumber"); cmd[3] = new MySqlCommand(); cmd[3].Connection = mconnect; cmd[3].CommandText = @"INSERT INTO HouseNumber(idHouseNumber, District_h, Street_h, Number) VALUES( @idHN, @dis, @str, @housenum)"; cmd[3].Prepare(); cmd[3].Parameters.AddWithValue("@idHN", _idhouse); cmd[3].Parameters.AddWithValue("@dis", dis); cmd[3].Parameters.AddWithValue("@str", str); cmd[3].Parameters.AddWithValue("@housenum", housenum); cmd[3].ExecuteNonQuery(); } if (samenum) { cmd[3] = new MySqlCommand(); cmd[3].Connection = mconnect; cmd[3].Prepare(); cmd[3].CommandText = @"SELECT idHouseNumber FROM HouseNumber WHERE HouseNumber.District_h = @dis AND HouseNumber.Street_h = @str AND HouseNumber.Number = @housenum"; cmd[3].Parameters.AddWithValue("@dis", dis); cmd[3].Parameters.AddWithValue("@str", str); cmd[3].Parameters.AddWithValue("@housenum", housenum); rdr = cmd[3].ExecuteReader(); while (rdr.Read()) { _idhouse = rdr.GetInt32(0); } rdr.Close(); } cmd[1].CommandText = @"INSERT INTO facility(numb, usag, RentPrice, SalePrice, Square, UsefulSquare, NumOfRooms, household, house, idfacility) VALUES( @uno, @use, @rprice, @sprice, @sq, @usq, @rooms, @inn, @idHN, @k)"; cmd[1].Prepare(); cmd[1].Parameters.AddWithValue("@uno", uno); cmd[1].Parameters.AddWithValue("@use", use); cmd[1].Parameters.AddWithValue("@rprice", rprice); cmd[1].Parameters.AddWithValue("@sprice", sprice); cmd[1].Parameters.AddWithValue("@sq", sq); cmd[1].Parameters.AddWithValue("@usq", usq); cmd[1].Parameters.AddWithValue("@rooms", rooms); cmd[1].Parameters.AddWithValue("@inn", _inn); cmd[1].Parameters.AddWithValue("@idHN", _idhouse); cmd[1].Parameters.AddWithValue("@k", KEY_GEN(@"SELECT idfacility FROM facility")); cmd[1].ExecuteNonQuery(); stm = "SELECT * FROM facility WHERE facility.household = @inn"; cmd[0] = new MySqlCommand(stm); cmd[0].Connection = mconnect; cmd[0].Prepare(); cmd[0].Parameters.AddWithValue("@inn", _inn); da[1] = new MySqlDataAdapter(cmd[0]); ds[1].Clear(); da[1].Fill(ds[1], "facility"); dataGridView2.DataSource = ds[1].Tables["facility"]; } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } if (mconnect != null) { mconnect.Close(); } } }