public BLL.Alunos Preencher(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"SELECT ALUNOS.ID,
ALUNOS.NOME,
ALUNOS.DATA_NASC,
ALUNOS.EMAIL,
ALUNOS.ID_CURSOS
FROM
ALUNOS
WHERE
ID = @id";
cmd.Parameters.AddWithValue("@id", a.Id);
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
a.Id = Convert.ToInt32(dr["ID"]);
a.Nome = dr["NOME"].ToString();
a.Email = dr["EMAIL"].ToString();
a.Data_nasc = Convert.ToDateTime(dr["DATA_NASC"]);
a.Id_cursos = Convert.ToInt32(dr["ID_CURSOS"]);
dr.Close();
}
else
{
a.Id = 0;
}
con.Desconectar();
return(a);
}
protected void gvAluno_RowCommand(object sender, GridViewCommandEventArgs e)
{
alunos.Id = Convert.ToInt32(e.CommandArgument);
if (e.CommandName == "cmdExcluir")
{
alunosDAL.Excluir(alunos);
Response.Write("<script>alert('Aluno excluído')</script>");
btnFiltrar_Click(null, null);
}
else if (e.CommandName == "cmdAtualizar")
{
alunos = alunosDAL.Preencher(alunos);
btnAdicionar.Enabled = false;
btnEditar.Visible = true;
btnCancelar.Visible = true;
if (alunos.Id != 0)
{
lblAluno.Text = alunos.Id.ToString();
txtNome.Text = alunos.Nome;
txtEmail.Text = alunos.Email;
txtDataNasc.Text = alunos.Data_nasc.ToString();
cmbCurso.SelectedValue = alunos.Id_cursos.ToString();
}
else
{
Response.Write("<script>alert('Aluno não encontrado')</script>");
btnEditar.Visible = false;
btnAdicionar.Enabled = true;
btnCancelar.Visible = false;
}
}
}
public DataTable Consultar(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"SELECT ALUNOS.ID,
ALUNOS.NOME,
DATEDIFF(YEAR, ALUNOS.DATA_NASC, GETDATE()) AS IDADE,
ALUNOS.EMAIL,
CURSOS.NOME_CURSO AS 'NOME DO CURSO'
FROM
ALUNOS
INNER JOIN
CURSOS
ON
ALUNOS.ID_CURSOS = CURSOS.ID
WHERE
ALUNOS.NOME LIKE @nome";
cmd.Parameters.AddWithValue("@nome", "%" + a.Nome + "%");
cmd.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataTable dt = new DataTable();
da.Fill(dt);
con.Desconectar();
return(dt);
}
public bool VerificaIDeEmail(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"SELECT
ALUNOS.ID,
ALUNOS.EMAIL
FROM
ALUNOS
WHERE
ALUNOS.ID = @id
AND
ALUNOS.EMAIL = @email";
cmd.Parameters.AddWithValue("@id", a.Id);
cmd.Parameters.AddWithValue("@email", a.Email);
cmd.ExecuteNonQuery();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
con.Desconectar();
return(true);
}
else
{
con.Desconectar();
return(false);
}
}
public void Excluir(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"DELETE FROM ALUNOS
WHERE ID = @id";
cmd.Parameters.AddWithValue("@id", a.Id);
cmd.ExecuteNonQuery();
con.Desconectar();
}
public void Cadastrar(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"INSERT INTO ALUNOS
(NOME, DATA_NASC, EMAIL, ID_CURSOS)
VALUES
(@nome, @datanasc, @email, @curso)";
cmd.Parameters.AddWithValue("@nome", a.Nome);
cmd.Parameters.AddWithValue("@datanasc", a.Data_nasc);
cmd.Parameters.AddWithValue("@email", a.Email);
cmd.Parameters.AddWithValue("@curso", a.Id_cursos);
cmd.ExecuteNonQuery();
con.Desconectar();
}
public void Atualizar(BLL.Alunos a)
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con.Conectar();
cmd.CommandText = @"UPDATE
ALUNOS
SET
NOME = @nome,
EMAIL = @email,
DATA_NASC = @data,
ID_CURSOS = @idcursos
WHERE
ID = @id";
cmd.Parameters.AddWithValue("@id", a.Id);
cmd.Parameters.AddWithValue("@nome", a.Nome);
cmd.Parameters.AddWithValue("@email", a.Email);
cmd.Parameters.AddWithValue("@data", a.Data_nasc);
cmd.Parameters.AddWithValue("@idcursos", a.Id_cursos);
cmd.ExecuteNonQuery();
con.Desconectar();
}
