public List <Usuario> GetAll()
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = "Select * From UsuarioTB usu INNER JOIN PermissaoTB per on usu.idPermissao = per.id";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
List <Usuario> lstUsuario = new List <Usuario>();
Usuario usuario;
foreach (DataRow dr in dt.Rows)
{
usuario = new Usuario();
usuario.Id = dr["id"].ToInteger();
usuario.Nome = dr["nome"].ToString();
usuario.Email = dr["email"].ToString();
usuario.IdPermissao = dr["idPermissao"].ToInteger();
usuario.Permissao = new Permissao {
Id = dr["idPermissao"].ToInteger(), Tipo = dr["tipo"].ToString()
};
lstUsuario.Add(usuario);
}
return(lstUsuario);
}
public List <Tarefa> FindByTitulo(String titulo)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@titulo", "%" + titulo + "%"));
comando.CommandText = "Select * From TarefaTB where titulo like @titulo";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
List <Tarefa> lstTarefa = new List <Tarefa>();
Tarefa tarefa;
foreach (DataRow dr in dt.Rows)
{
tarefa = new Tarefa();
tarefa.Id = dr["id"].ToInteger();
tarefa.Titulo = dr["titulo"].ToString();
lstTarefa.Add(tarefa);
}
return(lstTarefa);
}
public void Insert(Tarefa tarefa)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@titulo", tarefa.Titulo));
comando.CommandText = "Insert Into TarefaTB (titulo) Values (@titulo)";
comando.ExecuteNonQuery();
}
public void Delete(int id)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", id));
comando.CommandText = "Delete From TarefaTB Where id = @id";
comando.ExecuteNonQuery();
}
public void Update(Tarefa tarefa)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", tarefa.Id));
comando.Parameters.Add(new SqlParameter("@titulo", tarefa.Titulo));
comando.CommandText = "Update TarefaTB set titulo = @titulo Where id = @id";
comando.ExecuteNonQuery();
}
public void Insert(Usuario usuario)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@nome", usuario.Nome));
comando.Parameters.Add(new SqlParameter("@email", usuario.Email));
comando.Parameters.Add(new SqlParameter("@senha", usuario.Senha));
comando.Parameters.Add(new SqlParameter("@idPermissao", usuario.IdPermissao));
comando.CommandText = "Insert Into UsuarioTB (nome, email, senha, idPermissao) Values (@nome, @email, @senha, @idPermissao)";
comando.ExecuteNonQuery();
}
public void Update(Usuario usuario)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", usuario.Id));
comando.Parameters.Add(new SqlParameter("@nome", usuario.Nome));
comando.Parameters.Add(new SqlParameter("@email", usuario.Email));
comando.Parameters.Add(new SqlParameter("@senha", usuario.Senha));
comando.Parameters.Add(new SqlParameter("@idPermissao", usuario.IdPermissao));
comando.CommandText = "Update UsuarioTB set nome=@nome, email=@email, senha=@senha, idPermissao=@idPermissao Where id = @id";
comando.ExecuteNonQuery();
}
public Tarefa GetById(int id)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", id));
comando.CommandText = "Select * From TarefaTB where id = @id";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
Tarefa tarefa = new Tarefa {
Id = dt.Rows[0]["id"].ToInteger(),
Titulo = dt.Rows[0]["titulo"].ToString()
};
return(tarefa);
}
public Permissao GetById(int id)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", id));
comando.CommandText = "Select * From PermissaoTB Where id = @id";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
Permissao permissao = new Permissao
{
Id = dt.Rows[0]["id"].ToInteger(),
Tipo = dt.Rows[0]["tipo"].ToString()
};
return(permissao);
}
public Usuario GetByEmailSenha(Usuario usuario)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@email", usuario.Email));
comando.Parameters.Add(new SqlParameter("@senha", usuario.Senha));
comando.CommandText = "Select * From UsuarioTB Where email = @email and senha = senha";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
usuario.Id = dt.Rows[0]["id"].ToInteger();
usuario.Nome = dt.Rows[0]["nome"].ToString();
usuario.Email = dt.Rows[0]["email"].ToString();
usuario.Permissao = new PermissaoDAO().GetById(dt.Rows[0]["idPermissao"].ToString().ToInteger());
return(usuario);
}
public Boolean IsLoginValido(Usuario usuario)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@email", usuario.Email));
comando.Parameters.Add(new SqlParameter("@senha", usuario.Senha));
comando.CommandText = "Select * From UsuarioTB Where email = @email and senha = senha";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
if ((dt.Rows.Count != 0) && (usuario.Email.Equals(dt.Rows[0]["email"].ToString())) && (usuario.Senha.Equals(dt.Rows[0]["senha"].ToString())))
{
return(true);
}
return(false);
}
public Usuario GetById(int id)
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.Parameters.Add(new SqlParameter("@id", id));
comando.CommandText = "Select id, nome, email, idPermissao, senha From UsuarioTB Where id = @id";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
Usuario usuario = new Usuario
{
Id = dt.Rows[0]["id"].ToInteger(),
Nome = dt.Rows[0]["nome"].ToString(),
Email = dt.Rows[0]["email"].ToString(),
IdPermissao = dt.Rows[0]["idPermissao"].ToInteger(),
Senha = dt.Rows[0]["senha"].ToString()
};
return(usuario);
}
public List <Permissao> GetAll()
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = "Select * From PermissaoTB";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
List <Permissao> lstPermissao = new List <Permissao>();
Permissao permissao;
foreach (DataRow dr in dt.Rows)
{
permissao = new Permissao();
permissao.Id = dr["id"].ToInteger();
permissao.Tipo = dr["tipo"].ToString();
lstPermissao.Add(permissao);
}
return(lstPermissao);
}
public List <Tarefa> GetAll()
{
IDbCommand comando = DB.GetCommand();
comando.CommandType = CommandType.Text;
comando.CommandText = "Select * From TarefaTB";
DataTable dt = new DataTable();
dt.Load(comando.ExecuteReader());
List <Tarefa> lstTarefa = new List <Tarefa>();
Tarefa tarefa;
foreach (DataRow dr in dt.Rows)
{
tarefa = new Tarefa();
tarefa.Id = dr["id"].ToInteger();
tarefa.Titulo = dr["titulo"].ToString();
lstTarefa.Add(tarefa);
}
return(lstTarefa);
}
