private void GetPessoasByCasa(string morada)
{
data.connectToDB();
List <ProprietarioModel> pessoas = new List <ProprietarioModel>();
String sql = "SELECT * FROM (proj_pessoa join proj_tem_casa on proj_tem_casa.nif=proj_pessoa.nif) where morada='" + morada + "'";
SqlCommand com = new SqlCommand(sql, data.connection());
SqlDataReader reader;
reader = com.ExecuteReader();
while (reader.Read())
{
ProprietarioModel tmp = new ProprietarioModel();
tmp.fname = reader.GetString(0);
tmp.lname = reader.GetString(1);
tmp.telefone = reader.GetDecimal(2);
tmp.id = reader.GetDecimal(3);
tmp.nif = reader.GetDecimal(4);
tmp.morada = reader.GetString(6);
tmp.ini = reader.GetDateTime(7);
tmp.fim = reader.GetDateTime(8);
pessoas.Add(tmp);
}
data.close();
//return pessoas;
fillDataGrid(pessoas);
}
public void printProprietarios()
{
data.connectToDB();
List <ProprietarioModel> props = new List <ProprietarioModel>();
String sql = "SELECT * FROM (proj_pessoa join proj_proprietario on proj_pessoa.nif=proj_proprietario.nif)";
SqlCommand com = new SqlCommand(sql, data.connection());
SqlDataReader reader;
reader = com.ExecuteReader();
while (reader.Read())
{
ProprietarioModel tmp = new ProprietarioModel();
tmp.fname = reader.GetString(0);
tmp.lname = reader.GetString(1);
tmp.telefone = reader.GetDecimal(2);
tmp.id = reader.GetDecimal(3);
tmp.nif = reader.GetDecimal(4);
props.Add(tmp);
}
data.close();
fillDataGrid(props);
}
private void submit_bt_Click(object sender, EventArgs e)
{
ProprietarioModel inq = new ProprietarioModel();
try
{
inq.fname = fname_textbox.Text.ToString();
inq.lname = lname_textbox.Text.ToString();
inq.telefone = Decimal.Parse(tel_textbox.Text.ToString());
inq.id = Decimal.Parse(id_textBox.Text.ToString());
inq.nif = Decimal.Parse(nif_textBox.Text.ToString());
inq.morada = morada_text.Text.ToString();
inq.ini = DateTime.Parse(dateTimePicker1.Text.ToString());
inq.fim = DateTime.Parse(dateTimePicker2.Text.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
saveInq(inq);
Proprietario parent = (Proprietario)Owner;
parent.GetPessoasByCasa(inq.morada);
this.Close();
}
private void saveInq(ProprietarioModel inq)
{
data.connectToDB();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "exec inserirProp @fname, @lname, @telefone, @id, @nif, @morada, @data_ini, @data_fim";// "INSERT proj_pessoa (fname, lname, telefone, id, nif) values(@fname, @lname, @telefone, @id, @nif)";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@lname", inq.lname);
cmd.Parameters.AddWithValue("@fname", inq.fname);
cmd.Parameters.AddWithValue("@telefone", inq.telefone);
cmd.Parameters.AddWithValue("@id", inq.id);
cmd.Parameters.AddWithValue("@nif", inq.nif);
cmd.Parameters.AddWithValue("@morada", inq.morada);
cmd.Parameters.AddWithValue("@data_ini", inq.ini);
cmd.Parameters.AddWithValue("@data_fim", inq.fim);
cmd.Connection = data.connection();
try
{
cmd.ExecuteNonQuery();
//cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
//throw new Exception("Failed to insert in database. \n ERROR MESSAGE: \n" + ex.Message);
MessageBox.Show(ex.Message);
}
finally
{
data.close();
}
}
public void GetPessoasByCasa(string morada)
{
data.connectToDB();
List <ProprietarioModel> pessoas = new List <ProprietarioModel>();
//String sql = "SELECT * FROM (proj_pessoa join proj_tem_casa on proj_tem_casa.nif=proj_pessoa.nif) where morada='" + morada+"'";
SqlCommand com = new SqlCommand("getPropsByCasa", data.connection());
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@morada", morada);
SqlDataReader reader;
reader = com.ExecuteReader();
while (reader.Read())
{
ProprietarioModel tmp = new ProprietarioModel();
tmp.fname = reader["fname"].ToString();
tmp.lname = reader["lname"].ToString();
tmp.telefone = Decimal.Parse(reader["telefone"].ToString());
tmp.id = Decimal.Parse(reader["id"].ToString());
tmp.nif = Decimal.Parse(reader["nif"].ToString());
tmp.morada = morada;
tmp.ini = DateTime.Parse(reader["data_ini"].ToString());
tmp.fim = DateTime.Parse(reader["data_fim"].ToString());
pessoas.Add(tmp);
}
data.close();
//return pessoas;
fillDataGrid(pessoas);
}
