public static Usuario BuscarPorId(int id)
{
Usuario usuario;
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("SELECT Id, Nome, Email FROM Usuario WHERE Id = @id", conn))
{
cmd.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read() == true)
{
string nome = reader.GetString(1);
string email = reader.GetString(2);
usuario = new Usuario();
usuario.Id = id;
usuario.Nome = nome;
usuario.Email = email;
}
else
{
usuario = null;
}
}
}
}
return(usuario);
}
public static void Excluir(int id)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM Usuario WHERE Id = @id", conn))
{
cmd.Parameters.AddWithValue("@id", id);
cmd.ExecuteNonQuery();
}
}
}
public void CriarProva([FromBody] Prova prova)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tbProva (Nome, DataAplicacao) VALUES (@nome, @data)", conn))
{
cmd.Parameters.AddWithValue("@nome", prova.Nome);
cmd.Parameters.AddWithValue("@data", prova.DataAplicacao);
cmd.ExecuteNonQuery();
}
}
}
//Remover via Header
public bool RemoverQuestao(int id)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("DELETE FROM tbQuestao WHERE IdQuestao = @id", conn))
{
cmd.Parameters.AddWithValue("@id", id);
return(cmd.ExecuteNonQuery() == 1);
}
}
}
public void CriarQuestao([FromBody] Questao questao)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tbQuestao (Nome, Enunciado) VALUES (@Nome, @Enunciado)", conn))
{
cmd.Parameters.AddWithValue("@Nome", questao.Nome);
cmd.Parameters.AddWithValue("@Enunciado", questao.Enunciado);
cmd.ExecuteNonQuery();
}
}
}
public bool AlterarQuestao([FromBody] Questao questao)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("UPDATE tbQuestao SET Nome = @nome, Enunciado = @enunciado WHERE IdQuestao = @id", conn))
{
cmd.Parameters.AddWithValue("@nome", questao.Nome);
cmd.Parameters.AddWithValue("@enunciado", questao.Enunciado);
cmd.Parameters.AddWithValue("@id", questao.IdQuestao);
return(cmd.ExecuteNonQuery() == 1);
}
}
}
public void CriarAluno([FromBody] Aluno aluno)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tbAluno (Nome, Email, Ra) VALUES (@nome, @email, @ra)", conn))
{
cmd.Parameters.AddWithValue("@nome", aluno.Nome);
cmd.Parameters.AddWithValue("@email", aluno.Email);
cmd.Parameters.AddWithValue("@ra", aluno.Ra);
cmd.ExecuteNonQuery();
}
}
}
public void AssociarQuestaoProva([FromBody] ProvaQuestao provaQuestao)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tbProvaQuestao (Valor, IdProva, IdQuestao) VALUES (@Valor, @IdProva, @IdQuestao)", conn))
{
cmd.Parameters.AddWithValue("@Valor", provaQuestao.Valor);
cmd.Parameters.AddWithValue("@IdProva", provaQuestao.IdProva);
cmd.Parameters.AddWithValue("@IdQuestao", provaQuestao.IdQuestao);
cmd.ExecuteNonQuery();
}
}
}
public void AtribuirRespostaProva([FromBody] AlunoProvaQuestao alunoProva)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO tbAlunoProvaQuestao (Resposta, Nota, IdProvaQuestao, IdAluno) VALUES (@Resp, @Nota, @IdProvaQuestao, @IdAluno)", conn))
{
cmd.Parameters.AddWithValue("@Resp", alunoProva.Resposta);
cmd.Parameters.AddWithValue("@Nota", 0);
cmd.Parameters.AddWithValue("@IdProvaQuestao", alunoProva.IdProvaQuestao);
cmd.Parameters.AddWithValue("@IdAluno", alunoProva.IdAluno);
cmd.ExecuteNonQuery();
}
}
}
public static void InserirUsuario(Usuario user)
{
if (string.IsNullOrWhiteSpace(user.Nome) || string.IsNullOrWhiteSpace(user.Email))
{
return;
}
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO USUARIO (Nome, Email) OUTPUT INSERTED.Id VALUES (@nome, @email)", conn))
{
cmd.Parameters.AddWithValue("@nome", user.Nome);
cmd.Parameters.AddWithValue("@email", user.Email);
user.Id = (int)cmd.ExecuteScalar();
}
}
}
public bool EditarProva([FromBody] Prova prova)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand(@"
UPDATE tbProva SET
Nome=@nome,
DataAplicacao=@data
WHERE IdProva=@id", conn))
{
cmd.Parameters.AddWithValue("@nome", prova.Nome);
cmd.Parameters.AddWithValue("@data", prova.DataAplicacao);
cmd.Parameters.AddWithValue("@id", prova.IdProva);
return(cmd.ExecuteNonQuery() == 1);
}
}
}
public static void Editar(Usuario user)
{
if (string.IsNullOrWhiteSpace(user.Nome) || string.IsNullOrWhiteSpace(user.Email))
{
return;
}
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("UPDATE Usuario SET Nome=@nome, Email=@email WHERE Id=@id", conn))
{
cmd.Parameters.AddWithValue("@nome", user.Nome);
cmd.Parameters.AddWithValue("@email", user.Email);
cmd.Parameters.AddWithValue("@id", user.Id);
cmd.ExecuteNonQuery();
}
}
}
public bool EditarAluno([FromBody] Aluno aluno)
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand(@"
UPDATE tbAluno SET
Nome=@nome,
Email=@email,
Ra=@ra
WHERE IdAluno=@id", conn))
{
cmd.Parameters.AddWithValue("@nome", aluno.Nome);
cmd.Parameters.AddWithValue("@email", aluno.Email);
cmd.Parameters.AddWithValue("@ra", aluno.Ra);
cmd.Parameters.AddWithValue("@id", aluno.IdAluno);
return(cmd.ExecuteNonQuery() == 1);
}
}
}
public static List <Usuario> Listar()
{
using (SqlConnection conn = SqlConn.Abrir())
{
using (SqlCommand cmd = new SqlCommand("select id,nome, email from usuario", conn))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
List <Usuario> lista = new List <Usuario>();
while (reader.Read() == true)
{
lista.Add(new Usuario()
{
Id = reader.GetInt32(0),
Nome = reader.GetString(1),
Email = reader.GetString(2)
});
}
return(lista);
}
}
}
}