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);
}
