public static Alimento Encontrar(string id)
{
Alimento alimento;
SqlCommand cmd = new SqlCommand
{
CommandText = "SELECT * FROM alimento WHERE id = @id"
};
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = ConexaoFactory.GetConexao();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
alimento = new Alimento((int)reader[0], (string)reader[1], (int)reader[2], (int)reader[3], (string)reader[4]);
}
else
{
alimento = null;
}
reader.Close();
ConexaoFactory.Desconectar();
return(alimento);
}
public static Usuario Login(string cpf, string senha)
{
SqlCommand cmd = new SqlCommand
{
CommandText = "SELECT * FROM usuario where cpf = @cpf and senha = @senha"
};
cmd.Parameters.AddWithValue("@cpf", cpf);
cmd.Parameters.AddWithValue("@senha", Encriptador.Sha256(senha));
cmd.Connection = ConexaoFactory.GetConexao();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
Usuario usuario = new Usuario((int)reader["id"], (string)reader["nome"], (string)reader["cpf"], (string)reader["rg"], (string)reader["senha"], (int)reader["status"], (int)reader["perfil"]);
reader.Close();
ConexaoFactory.Desconectar();
return(usuario);
}
else
{
ConexaoFactory.Desconectar();
throw new Exception("Usuário ou senha incorreta");
}
}
public static void Remover(int id)
{
SqlCommand cmd = new SqlCommand
{
CommandText = "DELETE FROM alimento where id = @id"
};
cmd.Parameters.AddWithValue("@id", id);
cmd.Connection = ConexaoFactory.GetConexao();
cmd.ExecuteScalar();
ConexaoFactory.Desconectar();
}
public static void Editar(Alimento alimento)
{
SqlCommand cmd = new SqlCommand
{
CommandText = "UPDATE alimento set nome = @n, calorias = @c , quantidade = @q , medida = @m where id = @id"
};
cmd.Parameters.AddWithValue("@n", alimento.Nome);
cmd.Parameters.AddWithValue("@c", alimento.Calorias);
cmd.Parameters.AddWithValue("@q", alimento.Quantidade);
cmd.Parameters.AddWithValue("@m", alimento.Medida);
cmd.Parameters.AddWithValue("@id", alimento.Id);
cmd.Connection = ConexaoFactory.GetConexao();
cmd.ExecuteScalar();
ConexaoFactory.Desconectar();
}
public static void Atualizar(Usuario usuario, bool hashSenha)
{
string senhaSalva = (hashSenha) ? Encriptador.Sha256(usuario.Senha) : usuario.Senha;
using (SqlCommand cmd = new SqlCommand {
CommandText = "UPDATE LOGIN SET nome = @nome , rg = @rg, senha = @senha, status = @status, perfil = @perfil WHERE cpf = @cpf"
})
{
cmd.Parameters.AddWithValue("@cpf", usuario.Cpf);
cmd.Parameters.AddWithValue("@nome", usuario.Nome);
cmd.Parameters.AddWithValue("@rg", usuario.Rg);
cmd.Parameters.AddWithValue("@senha", senhaSalva);
cmd.Parameters.AddWithValue("@status", usuario.Status);
cmd.Parameters.AddWithValue("@perfil", usuario.Nome);
cmd.Connection = ConexaoFactory.GetConexao();
ConexaoFactory.Desconectar();
}
}
public static HashSet <Usuario> EncontrarUsuarios()
{
HashSet <Usuario> usuarios = new HashSet <Usuario>();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM usuario";
cmd.Connection = ConexaoFactory.GetConexao();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Usuario usuario = new Usuario((int)reader["id"], (string)reader["nome"], (string)reader["cpf"], (string)reader["rg"], (string)reader["senha"], (int)reader["status"], (int)reader["perfil"]);
usuarios.Add(usuario);
}
ConexaoFactory.Desconectar();
return(usuarios);
}
public static Alimento Inserir(string nome, int calorias, int quantitade, string medida)
{
SqlCommand cmd = new SqlCommand
{
CommandText = "INSERT INTO alimento (nome, calorias, quantidade, medida) values (@n,@c,@q,@p);SELECT SCOPE_IDENTITY()"
};
cmd.Parameters.AddWithValue("@n", nome);
cmd.Parameters.AddWithValue("@c", calorias);
cmd.Parameters.AddWithValue("@q", quantitade);
cmd.Parameters.AddWithValue("@p", medida);
cmd.Connection = ConexaoFactory.GetConexao();
int id = decimal.ToInt32((decimal)cmd.ExecuteScalar());
ConexaoFactory.Desconectar();
return(new Alimento(id, nome, calorias, quantitade, medida));
}
public static Usuario EncontrarUsuarioRG(string rg)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM usuario WHERE rg = @rg";
cmd.Parameters.AddWithValue("@rg", rg);
cmd.Connection = ConexaoFactory.GetConexao();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
Usuario usuario = new Usuario((int)reader["id"], (string)reader["nome"], (string)reader["cpf"], (string)reader["rg"], (string)reader["senha"], (int)reader["status"], (int)reader["perfil"]);
ConexaoFactory.Desconectar();
return(usuario);
}
else
{
ConexaoFactory.Desconectar();
return(null);
}
}
public static HashSet <Alimento> Encontrar()
{
HashSet <Alimento> alimentos = new HashSet <Alimento>();
SqlCommand cmd = new SqlCommand
{
CommandText = "SELECT * FROM alimento"
};
cmd.Connection = ConexaoFactory.GetConexao();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
alimentos.Add(new Alimento((int)reader[0], (string)reader[1], (int)reader[2], (int)reader[3], (string)reader[4]));
}
reader.Close();
ConexaoFactory.Desconectar();
return(alimentos);
}
public static Usuario Inserir(string nome, string cpf, string rg, string senha, int status, int perfil)
{
if (CpfValido(cpf))
{
if (EncontrarUsuarioRG(rg) != null)
{
throw new Exception("RG já cadastrado");
}
if (EncontrarUsuarioCPF(cpf) != null)
{
throw new Exception("CPF já cadastrado");
}
SqlCommand cmd = new SqlCommand
{
CommandText = "INSERT INTO usuario (cpf, nome, rg, senha, status, perfil) values(@c,@n,@r,@s,@st,@p);"
};
string hashSenha = Encriptador.Sha256(senha);
cmd.Parameters.AddWithValue("@c", cpf);
cmd.Parameters.AddWithValue("@n", nome);
cmd.Parameters.AddWithValue("@r", rg);
cmd.Parameters.AddWithValue("@s", hashSenha);
cmd.Parameters.AddWithValue("@st", status);
cmd.Parameters.AddWithValue("@p", perfil);
cmd.Connection = ConexaoFactory.GetConexao();
cmd.ExecuteNonQuery();
ConexaoFactory.Desconectar();
return(EncontrarUsuarioCPF(cpf));
}
else
{
throw new Exception("CPF inválido");
}
}