public static List <dtoMenu> GetAll() { List <dtoMenu> menus = new List <dtoMenu>(); using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { SqlCommand cmdMenu = new SqlCommand("SELECT * FROM tbMenu ORDER BY idMenuPai, idMenu, Ordem", connection); try { connection.Open(); SqlDataReader drMenu = cmdMenu.ExecuteReader(); while (drMenu.Read()) { Dto.dtoMenu menu = new Dto.dtoMenu(); PreencheCampos(drMenu, ref menu); menus.Add(menu); } } catch { throw new ApplicationException("Erro ao capturar todos os registros"); } finally { connection.Close(); } } return(menus); }
public static List <dtoMenu> GetAllChildrensByUsuario(int idMenuPai, int idUsuario) { List <dtoMenu> menus = new List <dtoMenu>(); string stringSQL = @"SELECT * FROM tbMenu WHERE idMenuPai = @idMenuPai AND idMenu IN (SELECT idMenu FROM tbUsuarioPermissao WHERE idUsuario = @idUsuario AND Exibir = 1) ORDER BY idMenu, Ordem"; using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { SqlCommand cmdMenu = new SqlCommand(stringSQL, connection); cmdMenu.Parameters.AddWithValue("idMenuPai", idMenuPai); cmdMenu.Parameters.AddWithValue("idUsuario", idUsuario); try { connection.Open(); SqlDataReader drMenu = cmdMenu.ExecuteReader(); while (drMenu.Read()) { Dto.dtoMenu menu = new Dto.dtoMenu(); PreencheCampos(drMenu, ref menu); menus.Add(menu); } } catch { throw new ApplicationException("Erro ao capturar todos os registros"); } finally { connection.Close(); } } return(menus); }
public static List <dtoMenu> GetOnlyChildrens() { List <dtoMenu> menus = new List <dtoMenu>(); using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string stringSQL = @"SELECT * FROM tbMenu WHERE NOT EXISTS (SELECT 1 FROM tbMenu AS tbMenu1 WHERE tbMenu1.idMenuPai = tbMenu.idMenu) ORDER By tbMenu.idMenuPai, tbMenu.idMenu"; SqlCommand cmdMenu = new SqlCommand(stringSQL, connection); try { connection.Open(); SqlDataReader drMenu = cmdMenu.ExecuteReader(); while (drMenu.Read()) { Dto.dtoMenu menu = new Dto.dtoMenu(); PreencheCampos(drMenu, ref menu); menus.Add(menu); } } catch { throw new ApplicationException("Erro ao capturar todos os registros"); } finally { connection.Close(); } } return(menus); }
public static List <dtoMenu> GetAllParentsWithChildrens(int idUsuario) { List <dtoMenu> menus = new List <dtoMenu>(); using (SqlConnection connection = new SqlConnection(DataAccess.Configuracao.getConnectionString())) { string condicaoSQL = String.Empty; dtoUsuario usuario = bllUsuario.Get(idUsuario); if (!usuario.Administrador) { condicaoSQL = @"AND EXISTS ( SELECT 1 FROM tbMenu AS tbMenu2 WHERE tbMenu2.idMenuPai = tbMenu.idMenu AND tbMenu2.idMenu IN ( SELECT idMenu FROM tbUsuarioPermissao LEFT JOIN tbUsuario ON tbUsuarioPermissao.idUsuario = tbUsuario.idUsuario WHERE tbUsuarioPermissao.idUsuario = @idUsuario AND tbUsuarioPermissao.Exibir = 1 ) )" ; } string stringSQL = String.Format(@"SELECT * FROM tbMenu WHERE idMenuPai = 0 {0} ORDER BY Ordem", condicaoSQL); SqlCommand cmdMenu = new SqlCommand(stringSQL, connection); cmdMenu.Parameters.AddWithValue("idUsuario", idUsuario); try { connection.Open(); SqlDataReader drMenu = cmdMenu.ExecuteReader(); while (drMenu.Read()) { Dto.dtoMenu menu = new Dto.dtoMenu(); PreencheCampos(drMenu, ref menu); menus.Add(menu); } } catch (Exception Ex) { //throw new ApplicationException("Erro ao capturar todos os registros"); throw new ApplicationException(Ex.Message); } finally { connection.Close(); } } return(menus); }