public List <MenusVM> GetMenus(RoleMenusVM vm)
{
try
{
// StringBuilder selectDataCommand = new StringBuilder(@"
// SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark]
// FROM [RABC].[dbo].[Menus] m
// where not exists
// (
// select * from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID
// )
//");
string sql = @"
select * into #NoParents from (
SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status]
FROM [RABC].[dbo].[Menus] m
where not exists
(
select rm.ID from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID
) and m.[Status]=1
) t
select * into #parents from
(
select [ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status] from Menus where ID in
(
--查找所有有孩子的父节点
select ParentID from #NoParents
where ParentID<>0
group by ParentID
) and [Status]=1
)t1
select * from #NoParents
union
select * from #parents
drop table #NoParents
drop table #parents
";
SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID);
// selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <MenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramRoleID)));
}
catch (Exception ex)
{
return(null);
}
}
// GET: Roles/Create
public async Task <ActionResult> SetRoleMenus(int id)
{
var categories = await _context.MenuCategories.OrderByDescending(d => d.Importance)
.ProjectTo <MenuCategoryVM>(_mapper.ConfigurationProvider).ToListAsync();
var role = await _context.Roles.FindAsync(id);
var roledto = _mapper.Map <RoleVM>(role);
var setRoleMenuVM = new RoleMenusVM
{
Categories = categories,
Role = roledto
};
return(PartialView("_SetRoleMenus", setRoleMenuVM));
}
public List <RoleMenusVM> GetRoleMenus(RoleMenusVM vm)
{
try
{
StringBuilder selectDataCommand = new StringBuilder(@"
select rm.[ID],rm.[RoleID], rm.[MenuID],r.Name RoleName,m.TabHeaderText MenuName ,m.ParentID from [dbo].[RoleMenus] rm
join [dbo].[Roles] r on rm.RoleID=r.ID
join [dbo].[Menus] m on rm.MenuID =m.ID
");
StringBuilder where = new StringBuilder(" where 1=1 and m.[Status]=1 ");
List <SqlParameter> paramList = new List <SqlParameter>();
if (!string.IsNullOrEmpty(vm.RoleName))
{
where.Append(" and r.Name like @RoleName");
SqlParameter paramRoleName = new SqlParameter("@RoleName", string.Format("%{0}%", vm.RoleName));
paramList.Add(paramRoleName);
}
if (!string.IsNullOrEmpty(vm.MenuName))
{
where.Append(" and m.TabHeaderText like @MenuName");
SqlParameter paramMenuName = new SqlParameter("@MenuName", string.Format("%{0}%", vm.MenuName));
paramList.Add(paramMenuName);
}
if (vm.RoleID != 0)
{
where.Append(" and r.ID = @RoleID");
SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID);
paramList.Add(paramRoleID);
}
selectDataCommand.Append(where.ToString());
return(DataTableToList.DataSetToList <RoleMenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramList.ToArray())));
}
catch (Exception ex)
{
return(null);
}
}
private void ribeDeleteRoleMenu_Click(object sender, EventArgs e)
{
RoleMenusVM roleMenusVM = this.gvRoleMenus.GetRow(this.gvRoleMenus.FocusedRowHandle) as RoleMenusVM;//获取选中行的实体
if (roleMenusVM.RoleName.ToLower() == "admin")
{
XtraMessageBox.Show("can not delete role admin!", "提示", MessageBoxButtons.OK);
return;
}
int resDeleteUserRoles = _bll.DeleteRoleMenus(roleMenusVM);
if (resDeleteUserRoles > 0)
{
XtraMessageBox.Show("删除成功!", "提示", MessageBoxButtons.OK);
RequestRoleMenusData();
this.slueRoles.EditValue = null;
}
else
{
XtraMessageBox.Show("删除失败!", "提示", MessageBoxButtons.OK);
}
}
public int DeleteRoleMenus(RoleMenusVM vm)
{
StringBuilder sb = new StringBuilder();
try
{
string sql = @"
begin try
begin tran tr
declare @parentID int =0;
select @parentID=ParentID from [dbo].[Menus] where id=@MenuID
if(@parentID!=0)--子节点
begin
delete from [dbo].[RoleMenus] where MenuID=@MenuID and RoleID=@RoleID
end
else--要删除的是父节点
begin
--删除所有子节点和该节点
delete from [dbo].[RoleMenus] where MenuID in (select ID from [dbo].[Menus] where ParentID=@MenuID or ID=@MenuID )and RoleID=@RoleID;
end
commit tran tr
end try
begin catch
ROLLBACK TRAN tr
end catch
";
SqlParameter paramMenuID = new SqlParameter("@MenuID", vm.MenuID);
SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID);
return(SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, new SqlParameter[] { paramMenuID, paramRoleID }));
}
catch (Exception ex)
{
return(0);
}
}
private void tllueAddMenu_EditValueChanged(object sender, EventArgs e)
{
if (this.tllueAddMenu.EditValue == null)
{
this.lueAuthority.Properties.DataSource = null;
return;
}
else
{
//判断点击的节点有没有子节点
RoleMenusVM menu = this.tllueAddMenu.EditValue as RoleMenusVM;
if (menu.ParentID == 0)
{
List <RoleMenusVM> sourceList = this.tllueAddMenu.Properties.DataSource as List <RoleMenusVM>;
if (sourceList.Any(p => p.ParentID == menu.MenuID))
{
XtraMessageBox.Show("please select child node!", "提示", MessageBoxButtons.OK);
this.tllueAddMenu.EditValue = null;
return;
}
}
}
RequestAuthoritiesData();
}
public int DeleteRoleMenus(RoleMenusVM vm)
{
return(_dal.DeleteRoleMenus(vm));
}
public List <RoleMenusVM> GetRoleMenus(RoleMenusVM vm)
{
return(_dal.GetRoleMenus(vm));
}