public List <Perfil_Usuario> ListarTudo()
{
SqlCommand command = new SqlCommand();
SqlDataReader reader;
List <Perfil_Usuario> listaPerfilUsuario = new List <Perfil_Usuario>();
StringBuilder vsql = new StringBuilder();
Int32 x = 0;
try
{
command.Connection = SigletonCnxSQL.getConexao();
command.Connection.Open();
vsql.Append("SELECT ID, NOME FROM TB_PERFIL_USUARIO ");
vsql.Append("ORDER BY NOME ");
command.CommandText = vsql.ToString();
reader = command.ExecuteReader();
while (reader.Read())
{
listaPerfilUsuario.Add(new Perfil_Usuario());
listaPerfilUsuario[x].ID = Convert.ToInt32(reader["ID"]);
listaPerfilUsuario[x].Nome = reader["NOME"].ToString();
x++;
}
return(listaPerfilUsuario);
}
catch (Exception e)
{
throw new Exception("Erro ao montar a lista de perfil de usuário. " + e.Message);
}
finally
{
command.Connection.Close();
}
}
public override bool excluir(ClasseBase obj)
{
SqlCommand command = new SqlCommand();
StringBuilder vsql = new StringBuilder();
bool erro = true;
try
{
vsql.Append("DELETE FROM USUARIO WHERE ID=" + ((Usuario)obj).ID);
command.Connection = SigletonCnxSQL.getConexao();
command.Connection.Open();
command.CommandText = vsql.ToString();
command.ExecuteNonQuery();
}
catch (Exception e)
{
erro = false;
throw new Exception("Erro ao excluir registro do usuário. " + e.Message);
}
finally
{
command.Connection.Close();
}
return(erro);
}
public override bool alterar(ClasseBase obj)
{
SqlCommand command = new SqlCommand();
StringBuilder vsql = new StringBuilder();
bool erro = true;
try
{
vsql.Append("UPDATE USUARIO SET LOGIN='******', NOME='" + ((Usuario)obj).Nome + "', SENHA='" + ((Usuario)obj).Senha +
"', ATIVO='" + ((Usuario)obj).Ativo + "', ID_PERFIL=" + ((Usuario)obj).perfil.ID.ToString() + " " +
"WHERE ID=" + ((Usuario)obj).ID);
command.Connection = SigletonCnxSQL.getConexao();
command.Connection.Open();
command.CommandText = vsql.ToString();
command.ExecuteNonQuery();
}
catch (Exception e)
{
erro = false;
throw new Exception("Erro ao alterar registro do usuário. " + e.Message);
}
finally
{
command.Connection.Close();
}
return(erro);
}
public override bool inserir(ClasseBase obj)
{
SqlCommand command = new SqlCommand();
StringBuilder vsql = new StringBuilder();
bool erro = true;
try
{
vsql.Append("INSERT INTO USUARIO ");
vsql.Append("(LOGIN,NOME,SENHA,ATIVO,ID_PERFIL) ");
vsql.Append("VALUES ");
vsql.Append("('" + ((Usuario)obj).Login + "','" + ((Usuario)obj).Nome +
"','" + ((Usuario)obj).Senha + "','" + ((Usuario)obj).Ativo + "'," +
((Usuario)obj).perfil.ID.ToString() + ")");
command.Connection = SigletonCnxSQL.getConexao();
command.Connection.Open();
command.CommandText = vsql.ToString();
command.ExecuteNonQuery();
}
catch (Exception e)
{
erro = false;
throw new Exception("Erro ao inserir registro do usuário. " + e.Message);
}
finally
{
command.Connection.Close();
}
return(erro);
}
public bool buscarLogin(ClasseBase obj)
{
SqlCommand command = new SqlCommand();
SqlDataReader reader;
bool erro = true;
try
{
command.Connection = SigletonCnxSQL.getConexao();
command.Connection.Open();
command.CommandText = "SELECT ID,LOGIN,NOME,SENHA,ID_PERFIL,ATIVO FROM USUARIO " +
"WHERE LOGIN='******'";
reader = command.ExecuteReader();
if (reader.Read())
{
((Usuario)obj).ID = Convert.ToInt32(reader["ID"]);
((Usuario)obj).Nome = reader["NOME"].ToString();
((Usuario)obj).Senha = reader["SENHA"].ToString();
((Usuario)obj).perfil.ID = Convert.ToInt32(reader["ID_PERFIL"]);
((Usuario)obj).Ativo = reader["ATIVO"].ToString();
((Usuario)obj).Estado = Stateobj.stLimpo;
}
else
{
erro = false;
}
}
finally
{
command.Connection.Close();
}
return(erro);
}
public DataSet ListaMenuPerfil(Usuario obj)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
StringBuilder vsql = new StringBuilder();
try
{
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = SigletonCnxSQL.getConexao();
da.SelectCommand.Connection.Open();
vsql.Append("SELECT A.OM_CP_OPCAO_MENU, A.OM_CP_PARENT, A.OM_NOME, A.OM_TOOL_TIP, ");
vsql.Append("A.OM_URL, A.OM_SEPARATOR FROM OPCOES_MENU A ");
vsql.Append("Inner Join OPCOES_MENU_PERFIS B ");
vsql.Append("On (B.OM_CP_OPCAO_MENU=A.OM_CP_OPCAO_MENU) ");
vsql.Append("Where B.PU_CP_PERFIL_USUARIO=" + obj.perfil.ID);
vsql.Append("Order By A.OM_CP_OPCAO_MENU");
da.SelectCommand.CommandText = vsql.ToString();
da.Fill(ds, "OPCOES_MENU");
return(ds);
}
catch (Exception e)
{
throw new Exception("Erro ao inserir registro do usuário. " + e.Message);
}
finally
{
da.SelectCommand.Connection.Close();
}
}
