public void Inserir(Aluno aluno)
{
Conectar();
try
{
var sql =
@"INSERT INTO Aluno
(Nome, Cpf, Telefone, Sexo, Avatar)
VALUES
(@nome, @cpf, @telefone, @sexo, @avatar);
SELECT CAST(SCOPE_IDENTITY() as INT);";
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters
.Add(CriarParametro("@nome", SqlDbType.VarChar, aluno.Nome));
cmd.Parameters.AddWithValue("@cpf", aluno.Cpf).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@telefone", aluno.Telefone).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@sexo", aluno.Sexo).SqlDbType = SqlDbType.TinyInt;
cmd.Parameters.AddWithValue(
"@avatar", Traduz(aluno.Avatar)).SqlDbType = SqlDbType.Image;
aluno.ID = (int)cmd.ExecuteScalar();
}
finally
{
Desconectar();
}
}
public void Atualizar(Aluno aluno)
{
Conectar();
try
{
var sql =
@"UPDATE Aluno
SET Nome = @nome,
Cpf = @cpf,
Telefone = @telefone,
Sexo = @sexo,
Avatar = @avatar
WHERE ID = @id;
";
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters
.Add(CriarParametro("@nome", SqlDbType.VarChar, aluno.Nome));
cmd.Parameters.AddWithValue("@cpf", aluno.Cpf).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@telefone", aluno.Telefone).SqlDbType = SqlDbType.VarChar;
cmd.Parameters.AddWithValue("@sexo", aluno.Sexo).SqlDbType = SqlDbType.TinyInt;
cmd.Parameters.AddWithValue(
"@avatar", Traduz(aluno.Avatar)).SqlDbType = SqlDbType.Image;
cmd.Parameters.Add(CriarParametro("@id", SqlDbType.Int, aluno.ID));
cmd.ExecuteNonQuery();
}
finally
{
Desconectar();
}
}
private void btnSalvar_Click(object sender, EventArgs e)
{
if(Atual == null)
{
Atual = new Aluno();
}
Atual.Nome = txbNome.Text;
Atual.Cpf = txbCpf.Text;
Atual.Telefone = txbTelefone.Text;
Atual.Sexo = 1;
DialogResult = DialogResult.OK;
}
public void Excluir(Aluno aluno)
{
Conectar();
try
{
var sql =
@"DELETE FROM Aluno
WHERE ID = @id;
";
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.Add(CriarParametro("@id", SqlDbType.Int, aluno.ID));
cmd.ExecuteNonQuery();
}
finally
{
Desconectar();
}
}
public BindingList<Aluno> RecuperaTodos()
{
Conectar();
try
{
var sql =
@"SELECT ID, Nome, CPF, Telefone, Sexo FROM Aluno";
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
BindingList<Aluno> alunos = new BindingList<Aluno>();
SqlDataReader leitor = cmd.ExecuteReader();
while(leitor.Read())
{
Aluno novo = new Aluno();
novo.ID = leitor.GetInt32(0);
novo.Nome = leitor.GetString(1);
novo.Cpf = leitor.GetString(2);
novo.Telefone = leitor.GetString(3);
novo.Sexo = leitor.GetByte(4);
//novo.Avatar = leitor.Get(1);
alunos.Add(novo);
}
return alunos;
}
finally
{
Desconectar();
}
}