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();
}
}
}
