private void Register()
{
database database = new database();
database.openConnection();
MySqlCommand objCmdEndereco = new MySqlCommand("insert into endereco (id, logradouro, numero, cep, bairro, cidade, estado) value(null, ?, ?, ?, ?, ?, ?)", database.getConnection());
objCmdEndereco.Parameters.Add("@logradouro", MySqlDbType.VarChar, 256).Value = input_address.Text;
objCmdEndereco.Parameters.Add("@numero", MySqlDbType.Int32).Value = input_address_number.Text;
objCmdEndereco.Parameters.Add("@cep", MySqlDbType.Int32).Value = input_zip.Text;
objCmdEndereco.Parameters.Add("@bairro", MySqlDbType.VarChar, 50).Value = input_neighborhood.Text;
objCmdEndereco.Parameters.Add("@cidade", MySqlDbType.VarChar, 30).Value = input_city.Text;
objCmdEndereco.Parameters.Add("@estado", MySqlDbType.VarChar, 20).Value = input_state.Text;
objCmdEndereco.ExecuteNonQuery();
long idEndereco = objCmdEndereco.LastInsertedId;
MySqlCommand objCmdPessoa = new MySqlCommand("insert into pessoa (id, nome, cpf, email, senha, endereco) value(null, ?, ?, ?, ?, ?)", database.getConnection());
objCmdPessoa.Parameters.Add("@nome", MySqlDbType.VarChar, 256).Value = input_name.Text;
objCmdPessoa.Parameters.Add("@cpf", MySqlDbType.Int64).Value = input_cpf.Text;
objCmdPessoa.Parameters.Add("@email", MySqlDbType.VarChar, 256).Value = input_email.Text;
objCmdPessoa.Parameters.Add("@senha", MySqlDbType.VarChar, 10).Value = input_password.Text;
objCmdPessoa.Parameters.Add("@endereco", MySqlDbType.Int32).Value = idEndereco;
objCmdPessoa.ExecuteNonQuery();
long idPessoa = objCmdPessoa.LastInsertedId;
MySqlCommand objCmdTelefoneTipo = new MySqlCommand("insert into telefone_tipo (id, tipo) value(null, ?)", database.getConnection());
objCmdTelefoneTipo.Parameters.Add("@tipo", MySqlDbType.VarChar, 10).Value = input_phone_type.Text;
objCmdTelefoneTipo.ExecuteNonQuery();
long idTelefoneTipo = objCmdTelefoneTipo.LastInsertedId;
MySqlCommand objCmdTelefone = new MySqlCommand("insert into telefone (id, numero, ddd, tipo) value(null, ?, ?, ?)", database.getConnection());
objCmdTelefone.Parameters.Add("@numero", MySqlDbType.Int32).Value = input_phone.Text;
objCmdTelefone.Parameters.Add("@ddd", MySqlDbType.Int32).Value = input_ddd.Text;
objCmdTelefone.Parameters.Add("@tipo", MySqlDbType.Int32).Value = idTelefoneTipo;
objCmdTelefone.ExecuteNonQuery();
long idTelefone = objCmdTelefone.LastInsertedId;
MySqlCommand objCmdPessoaTelefone = new MySqlCommand("insert into pessoa_telefone (id_pessoa, id_telefone) value(?, ?)", database.getConnection());
objCmdPessoaTelefone.Parameters.Add("@id_pessoa", MySqlDbType.Int32).Value = idPessoa;
objCmdPessoaTelefone.Parameters.Add("@id_telefone", MySqlDbType.Int32).Value = idTelefone;
objCmdPessoaTelefone.ExecuteNonQuery();
database.closeConnection();
}
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
database database = new database();
database.openConnection();
MySqlCommand cmd = new MySqlCommand("select pt.ID_PESSOA, pt.ID_TELEFONE, p.ENDERECO, t.TIPO, p.id, p.nome, p.cpf, t.DDD, t.NUMERO, tp.TIPO, e.CEP, e.LOGRADOURO, e.NUMERO, e.BAIRRO, e.CIDADE, e.ESTADO, p.email, p.senha from cadastro.pessoa p join cadastro.endereco e on p.ID = e.ID join cadastro.telefone t on t.ID = e.ID join cadastro.telefone_tipo tp on tp.ID = e.ID join cadastro.pessoa_telefone pt on pt.ID_PESSOA = p.ID", database.getConnection());
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dt);
i = GridView1.SelectedIndex;
id = dt.Rows[i][0].ToString();
id = dt.Rows[i][1].ToString();
id = dt.Rows[i][2].ToString();
id = dt.Rows[i][3].ToString();
id = dt.Rows[i][4].ToString();
input_name.Text = dt.Rows[i][5].ToString();
input_cpf.Text = dt.Rows[i][6].ToString();
input_ddd.Text = dt.Rows[i][7].ToString();
input_phone.Text = dt.Rows[i][8].ToString();
input_phone_type.Text = dt.Rows[i][9].ToString();
input_zip.Text = dt.Rows[i][10].ToString();
input_address.Text = dt.Rows[i][11].ToString();
input_address_number.Text = dt.Rows[i][12].ToString();
input_neighborhood.Text = dt.Rows[i][13].ToString();
input_city.Text = dt.Rows[i][14].ToString();
input_state.Text = dt.Rows[i][15].ToString();
input_email.Text = dt.Rows[i][16].ToString();
input_password.Text = dt.Rows[i][17].ToString();
bt_edit.Enabled = true;
if (bt_edit.Enabled)
{
bt_edit.CssClass = "style-bt-edit";
}
database.closeConnection();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "AVISO !", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
protected void GridViewLogic()
{
database database = new database();
database.openConnection();
MySqlCommand cmd = new MySqlCommand("select p.ID, p.nome, p.cpf, t.DDD, t.NUMERO, tp.TIPO, p.email, p.senha, e.CEP, e.LOGRADOURO, e.NUMERO, e.BAIRRO, e.CIDADE, e.ESTADO from cadastro.pessoa p join cadastro.endereco e on p.ID = e.ID join cadastro.telefone t on t.ID = e.ID join cadastro.telefone_tipo tp on tp.ID = e.ID", database.getConnection());
MySqlDataReader dt = cmd.ExecuteReader();
if (dt.HasRows == true)
{
GridView1.DataSource = dt;
GridView1.DataBind();
}
GridView1.Columns[0].ItemStyle.Width = 20;
GridView1.Columns[1].ItemStyle.Width = 300;
GridView1.Columns[3].ItemStyle.Width = 110;
}
protected void bt_enter_Click(object sender, EventArgs e)
{
try
{
database database = new database();
database.openConnection();
MySqlCommand cmd = new MySqlCommand("select * from pessoa where email=@email and senha=@password", database.getConnection());
cmd.Parameters.AddWithValue("@email", input_login.Text);
cmd.Parameters.AddWithValue("@password", input_password.Text);
var v = cmd.ExecuteScalar();
if (v != null)
{
Response.Redirect("panel-user.aspx", false);
}
else
{
if (String.IsNullOrEmpty(input_login.Text))
{
input_login.Focus();
error.Text = "Email em branco";
}
else if (String.IsNullOrEmpty(input_password.Text))
{
input_password.Focus();
error.Text = "Senha em branco";
}
else
{
error.Text = "Email / Senha não encontrada";
}
}
database.closeConnection();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "AVISO !", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
protected void bt_delete_Click(object sender, EventArgs e)
{
DeleteLogic();
try
{
database database = new database();
database.openConnection();
MySqlCommand cmdEndereco = new MySqlCommand("delete from cadastro.endereco where ID=@id", database.getConnection());
cmdEndereco.Parameters.AddWithValue("@id", id);
cmdEndereco.ExecuteNonQuery();
MySqlCommand cmdPessoa = new MySqlCommand("delete from cadastro.pessoa where ID=@id and ENDERECO=@id", database.getConnection());
cmdPessoa.Parameters.AddWithValue("@id", id);
cmdPessoa.ExecuteNonQuery();
MySqlCommand cmdTelefoneTipo = new MySqlCommand("delete from cadastro.telefone_tipo where ID=@id", database.getConnection());
cmdTelefoneTipo.Parameters.AddWithValue("@id", id);
cmdTelefoneTipo.ExecuteNonQuery();
MySqlCommand cmdTelefone = new MySqlCommand("delete from cadastro.telefone where ID=@id and TIPO=@id", database.getConnection());
cmdTelefone.Parameters.AddWithValue("@id", id);
cmdTelefone.ExecuteNonQuery();
MySqlCommand cmdPessoaTelefone = new MySqlCommand("delete from cadastro.pessoa_telefone where ID_PESSOA=@id and ID_TELEFONE=@id", database.getConnection());
cmdPessoaTelefone.Parameters.AddWithValue("@id", id);
cmdPessoaTelefone.ExecuteNonQuery();
MySqlCommand cmd = new MySqlCommand("select p.ID, p.nome, p.cpf, t.DDD, t.NUMERO, tp.TIPO, p.email, p.senha, e.CEP, e.LOGRADOURO, e.NUMERO, e.BAIRRO, e.CIDADE, e.ESTADO from cadastro.pessoa p join cadastro.endereco e on p.ID = e.ID join cadastro.telefone t on t.ID = e.ID join cadastro.telefone_tipo tp on tp.ID = e.ID", database.getConnection());
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
error.ForeColor = System.Drawing.Color.Red;
error.Text = "Cadastro excluido com sucesso !";
database.closeConnection();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "AVISO !", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
input_name.Text = "";
input_cpf.Text = "";
input_ddd.Text = "";
input_phone.Text = "";
input_phone_type.Text = "";
input_zip.Text = "";
input_address.Text = "";
input_address_number.Text = "";
input_neighborhood.Text = "";
input_city.Text = "";
input_state.Text = "";
input_email.Text = "";
input_email_confirm.Text = "";
input_password.Text = "";
input_email_confirm.Text = "";
id = "";
}
}
protected void bt_save_Click(object sender, EventArgs e)
{
SaveLogic();
try
{
database database = new database();
database.openConnection();
MySqlCommand cmd = new MySqlCommand("update cadastro.pessoa p join cadastro.endereco e on p.ID = e.ID join cadastro.telefone t on t.ID = e.ID join cadastro.telefone_tipo tp on tp.ID = e.ID set p.nome=@name, p.cpf=@cpf, t.DDD=@ddd, t.NUMERO=@phoneNumber, tp.TIPO=@type, e.CEP=@cep, e.LOGRADOURO=@address, e.NUMERO=@address_n, e.BAIRRO=@neighborhood, e.CIDADE=@city, e.ESTADO=@state, p.email=@email, p.senha=@password where p.ID=@id", database.getConnection());
cmd.Parameters.AddWithValue("@name", input_name.Text);
cmd.Parameters.AddWithValue("@cpf", input_cpf.Text);
cmd.Parameters.AddWithValue("@ddd", input_ddd.Text);
cmd.Parameters.AddWithValue("@phoneNumber", input_phone.Text);
cmd.Parameters.AddWithValue("@type", input_phone_type.Text);
cmd.Parameters.AddWithValue("@cep", input_zip.Text);
cmd.Parameters.AddWithValue("@address", input_address.Text);
cmd.Parameters.AddWithValue("@address_n", input_address_number.Text);
cmd.Parameters.AddWithValue("@neighborhood", input_neighborhood.Text);
cmd.Parameters.AddWithValue("@city", input_city.Text);
cmd.Parameters.AddWithValue("@state", input_state.Text);
cmd.Parameters.AddWithValue("@email", input_email.Text);
cmd.Parameters.AddWithValue("@password", input_password.Text);
cmd.Parameters.AddWithValue("@id", id);
int t = cmd.ExecuteNonQuery();
if (t > 0)
{
GridView1.EditIndex = -1;
GridViewLogic();
}
database.closeConnection();
error.ForeColor = System.Drawing.Color.Green;
error.Text = "Cadastro atualizado com sucesso !";
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message, "AVISO !", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
input_name.Text = "";
input_cpf.Text = "";
input_ddd.Text = "";
input_phone.Text = "";
input_phone_type.Text = "";
input_zip.Text = "";
input_address.Text = "";
input_address_number.Text = "";
input_neighborhood.Text = "";
input_city.Text = "";
input_state.Text = "";
input_email.Text = "";
input_email_confirm.Text = "";
input_password.Text = "";
input_password_confirm.Text = "";
id = "";
}
}