Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Page History: Menu System based on User Roles

Compare Page Revisions



« Older Revision - Back to Page History - Newer Revision »


Page Revision: Thu, Jul 12, 2012, 12:27 PM


Overview

This article outlines how to build a role-based menu for an ASP.NET website. Menu items are stored in a database table, and roles (from the ASP.NET Membership API) are granted visibility to the menu items.

Details

Database Tables

In addition to the ASP.NET Membership tables, the following two tables are needed.

{copytext|DatabaseTables}
CREATE TABLE [dbo].[MenuItem](
	[MenuItemId] [int] IDENTITY(1,1) NOT NULL,
	[DisplayText] [varchar](50) NOT NULL,
	[Url] [varchar](300) NULL,
	[ParentId] [int] NULL,
	[SortOrder] [int] NOT NULL,
	[ActiveFlag] [bit] NOT NULL,
 CONSTRAINT [PK_MenuItem] PRIMARY KEY CLUSTERED 
(
	[MenuItemId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[MenuItem] WITH CHECK ADD CONSTRAINT [FK_MenuItem_MenuItem] 
FOREIGN KEY([ParentId])
REFERENCES [dbo].[MenuItem] ([MenuItemId])
GO

ALTER TABLE [dbo].[MenuItem] ADD CONSTRAINT [DF_MenuItem_SortOrder] DEFAULT ((1000)) FOR [SortOrder]
GO

ALTER TABLE [dbo].[MenuItem] ADD CONSTRAINT [DF_MenuItem_ActiveFlag] DEFAULT ((1)) FOR [ActiveFlag]
GO
/*-----------------------------------------------------------------------------------------------*/
CREATE TABLE [dbo].[MenuItemRole](
	[MenuItemRoleId] [int] IDENTITY(1,1) NOT NULL,
	[MenuItemId] [int] NOT NULL,
	[RoleId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_MenuItemRole] PRIMARY KEY CLUSTERED 
(
	[MenuItemRoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MenuItemRole]  WITH CHECK ADD  CONSTRAINT [FK_MenuItemRole_aspnet_Roles] 
FOREIGN KEY([RoleId]) REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO

ALTER TABLE [dbo].[MenuItemRole] CHECK CONSTRAINT [FK_MenuItemRole_aspnet_Roles]
GO

ALTER TABLE [dbo].[MenuItemRole]  WITH CHECK ADD  CONSTRAINT [FK_MenuItemRole_MenuItem] 
FOREIGN KEY([MenuItemId]) REFERENCES [dbo].[MenuItem] ([MenuItemId])
GO

ALTER TABLE [dbo].[MenuItemRole] CHECK CONSTRAINT [FK_MenuItemRole_MenuItem]
GO

Stored Procedures

This stored procedure requires the PadLeft function found here.

{copytext|GetMenuItems}
CREATE procedure [dbo].[GetMenuItems]
    (
    @UserName nvarchar(256)
    ) as
/*-----------------------------------------------------------------------------------------------*/
/*
declare
     @UserName nvarchar(256) = 'pjasinski'
*/
/*-----------------------------------------------------------------------------------------------*/
;
with Items as (
    select distinct top 100 percent 
        i.*
    from    
        dbo.MenuItem i
        
        inner join dbo.MenuItemRole mir
            on i.MenuItemId = mir.MenuItemId
            
        inner join dbo.aspnet_UsersInRoles ur
            on mir.RoleId = ur.RoleId
            
        inner join dbo.aspnet_Users u
            on ur.UserId = u.UserId
            
    where 1=1
        and i.ActiveFlag = 1
        and u.UserName = @UserName
    )
select
     DisplayText
    ,Url
    ,SortOrder      = dbo.PadLeft(SortOrder, 10, '0') + '.' + dbo.PadLeft(MenuItemId, 10, '0')
    ,MenuItemId
    ,HasChildren    = case (select count(1) from Items b where b.ParentId = a.MenuItemId)
                        when 0 then 'false' else 'true' end
    ,MenuItems      = (   
                        select 
                             DisplayText
                            ,Url
                            ,SortOrder      = dbo.PadLeft(SortOrder, 10, '0') + '.' 
                                                + dbo.PadLeft(MenuItemId, 10, '0')
                            ,HasChildren    = case (select count(1) from Items c 
                                                    where c.ParentId = b.MenuItemId)
                                                when 0 then 'false' else 'true' end
                            ,MenuItems      = (   
                                                select 
                                                     DisplayText
                                                    ,Url
                                                    ,SortOrder  = dbo.PadLeft(SortOrder, 10, '0') 
                                                                    + '.' 
                                                                    + dbo.PadLeft(MenuItemId, 10, '0')
                                                from 
                                                    Items c
                                                where 1=1
                                                    and c.ParentId = b.MenuItemId
                                                for 
                                                    xml path('MenuItem'), type
                                                )
                        from 
                            Items b
                        where 1=1
                            and b.ParentId = a.MenuItemId
                        for 
                            xml path('MenuItem'), type
                        )
from 
    Items a
where 
    ParentId is null
for xml path('MenuItem')                   

GO

The above stored procedure can be enhanced using the following SQL as an example. The above stored procedure requires that a role be granted access to every level up the menu hierarchy in order to see a specific menu item. (If the parent isn't visible, then the child won't be either.) This SQL demonstrates how to include the entire menu "ancestry", thus permitting access to be granted only to the menu leafs (items without children).

/*

Given a leaf item ID (i.e., a menu item with no children), this SQL demonstrates how get one 
record for each menu item up the menu hierarchy.  This will be useful when we need to query
all the menu items for a given user.  Whereas we used to have to assign ALL the menu items
to a given role, now we can assign only the leaf items and the rest of the hierarchy will
come with it, ensure that the menu item is visible.

*/
;with a as (
    select 
         MenuItemId
        ,DisplayText
        ,ParentId
        ,LeafItemID = MenuItemID
    from
        dbo.MenuItem
    where 1=1
        and ParentId is not null
        and MenuItemId not in (select distinct ParentId from dbo.MenuItem where ParentId is not null)
        
    union all select
         m.MenuItemId
        ,m.DisplayText
        ,m.ParentId
        ,a.LeafItemID
    from 
        dbo.MenuItem m
        inner join a on 
            m.MenuItemId = a.ParentId
    )
select * from a where LeafItemID = 3

Importing the Stored Procedure

In order for the following code to work, you must add to your Entity Model what is called a "Function Import" for the above stored procedure. See here for how to add a Function Import to your Entity Model, and in the Add Function Import dialog specify that the stored procedure returns a collection of strings.

Add Function Import Dialog

Add Function Import Dialog


Code-Behind

This code goes in your DataProvider class, and depends on the TransformXml method, found here.

{copytext|codeBehind}
public static string GetMenuItemsHtml()
{
    string result;
    string username = HttpContext.Current.User.Identity.Name;
    DataProvider dp = new DataProvider(Common.RequestContext);
    string xml = "<MenuItems>" + dp.GetMenuItemsXml(username) + "</MenuItems>";
    result = Common.TransformXml(xml, "~/Content/Menu.xsl");
    return result;
}
. . .
public string GetMenuItemsXml(string username)
{
    // Database is simply an instance of your Entity Model
    ObjectResult<string> chunks = Database.GetMenuItems(username);
    StringBuilder sb = new StringBuilder();
    foreach (string chunk in chunks)
        sb.Append(chunk);
    return sb.ToString();
}

XSL Transformation

This XSL is designed for use with the supersubs/superfish jQuery menu, but can be adapted to any menu system.

{copytext|xsl}
<?xml version="1.0" encoding="iso-8859-1"?>

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxml="urn:schemas-microsoft-com:xslt"
  version="1.0">
    <!--=================================================================================-->
    <xsl:template match="/MenuItems">
        <ul id="top-navigation" class="sf-menu">
            <li class="current">
                <xsl:for-each select="*">
                    <xsl:sort select="SortOrder" data-type="text" order="ascending"/>
                    <xsl:call-template name="MenuItem"/>
                </xsl:for-each>
            </li>
        </ul>
    </xsl:template>
    <!--=================================================================================-->
    <xsl:template name="MenuItem">
        <li>

            <xsl:element name="a">
                <xsl:choose>
                    <xsl:when test="string-length(Url)=0">
                        <xsl:attribute name="onclick">return false;</xsl:attribute>
                        <xsl:attribute name="style">cursor:pointer;</xsl:attribute>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:attribute name="href">
                            <xsl:value-of select="Url"/>
                        </xsl:attribute>
                    </xsl:otherwise>
                </xsl:choose>

                <xsl:value-of select="DisplayText" />
                <xsl:choose>
                    <xsl:when test="HasChildren='true'">
                        <span class="sf-sub-indicator"> &#187;</span>
                    </xsl:when>
                </xsl:choose>
            </xsl:element>

            <xsl:choose>
                <xsl:when test="HasChildren='true'">
                    <ul>
                        <xsl:for-each select="MenuItems/MenuItem">
                            <xsl:sort select="SortOrder" data-type="text" order="ascending"/>
                            <xsl:call-template name="MenuItem" />
                        </xsl:for-each>
                    </ul>
                </xsl:when>
            </xsl:choose>
        </li>
    </xsl:template>
</xsl:stylesheet>

ASPX Code

<%if (Request.IsAuthenticated)
{
    // . . .

    Response.Write(AAA.Web.Common.Security.GetMenuItemsHtml());

    // . . .

    %>

Deployment

Comparison

The following code will compare the menu system in two databases: SourceDb and TargetDb.

{copytext|compareMenus}
/*== Roles ======================================================================================*/
select
    b.*
from
    SourceDb.dbo.aspnet_Roles b
    left join TargetDb.dbo.aspnet_Roles a
        on a.RoleId = b.RoleId
where
    a.RoleId is null
    or a.ApplicationId <> b.ApplicationId
    or a.LoweredRoleName <> b.LoweredRoleName
    or a.RoleName <> b.RoleName        
    
/*===============================================================================================*/
/* Menu Items */
select 
     b.*
    ,Diff = case when a.MenuItemId is null then 'Not deployed'
            else 'Field diffs: ' 
                + case when b.ActiveFlag <> a.ActiveFlag then 'ActiveFlag; ' else '' end
                + case when b.DisplayText <> a.DisplayText then 'DisplayText; ' else '' end
                + case when b.ParentId <> a.ParentId then 'ParentId; ' else '' end
                + case when b.SortOrder <> a.SortOrder then 'SortOrder; ' else '' end
                + case when b.Url <> a.Url then 'Url; ' else '' end
            end
            
from 
    TargetDb.dbo.MenuItem a
    right join SourceDb.dbo.MenuItem b
        on a.MenuItemId = b.MenuItemId
where 
    a.MenuItemId is null
    or
    b.ActiveFlag <> a.ActiveFlag
    or
    b.DisplayText <> a.DisplayText
    or
    b.ParentId <> a.ParentId
    or
    b.SortOrder <> a.SortOrder
    or
    b.Url <> a.Url
/*===============================================================================================*/
/* Menu Item Role */
select
     b.*  
    ,mi.DisplayText 
    ,r.RoleName
    ,Diff = case when a.MenuItemRoleId is null then 'Not deployed'
            else 'Field diffs: ' 
                + case when b.MenuItemId <> a.MenuItemId then 'MenuItemId; ' else '' end
                + case when b.RoleId <> a.RoleId then 'RoleId; ' else '' end
            end
from
    SourceDb.dbo.MenuItemRole b
    
    left join TargetDb.dbo.MenuItemRole a
        on a.MenuItemRoleId = b.MenuItemRoleId
        
    inner join SourceDb.dbo.MenuItem mi
        on b.MenuItemId = mi.MenuItemId
        
    inner join SourceDb.dbo.aspnet_Roles r
        on b.RoleId = r.RoleId
        
where
    a.MenuItemRoleId is null
    or b.MenuItemId <> a.MenuItemId
    or b.RoleId <> a.RoleId

Synchronization

The following code will update the menu configuration from the SourceDb database to the TargetDb database.

{copytext|syncMenu}
use TargetDb
/*=== Roles =====================================================================================*/
insert into TargetDb.dbo.aspnet_Roles(ApplicationId, RoleId, RoleName, LoweredRoleName, Description)
select ApplicationId, RoleId, RoleName, LoweredRoleName, Description
from SourceDb.dbo.aspnet_Roles 
where RoleId not in (select RoleId from TargetDb.dbo.aspnet_Roles)

/*=== Menu Items ================================================================================*/
delete from TargetDb.dbo.MenuItemRole 
where MenuItemId not in (select MenuItemId from SourceDb.dbo.MenuItem)

delete from TargetDb.dbo.MenuItem 
where MenuItemId not in (select MenuItemId from SourceDb.dbo.MenuItem)

update 
    TargetDb.dbo.MenuItem
set
     ActiveFlag = b.ActiveFlag
    ,DisplayText = b.DisplayText
    ,ParentId = b.ParentId
    ,SortOrder = b.SortOrder
    ,Url = b.Url

from 
    TargetDb.dbo.MenuItem a
    inner join SourceDb.dbo.MenuItem b
        on a.MenuItemId = b.MenuItemId

set identity_insert TargetDb.dbo.MenuItem on

insert into dbo.MenuItem (MenuItemId, DisplayText, Url, ParentId, SortOrder, ActiveFlag)
select MenuItemId, DisplayText, Url, ParentId, SortOrder, ActiveFlag 
from SourceDb.dbo.MenuItem
where MenuItemId not in (select MenuItemId from TargetDb.dbo.MenuItem)

set identity_insert TargetDb.dbo.MenuItem off

/*=== Menu Item Roles ===========================================================================*/
delete from TargetDb.dbo.MenuItemRole 
where MenuItemRoleId not in (select MenuItemRoleId from SourceDb.dbo.MenuItemRole)

update 
    TargetDb.dbo.MenuItemRole
set
     RoleId = b.RoleId
    ,MenuItemId = b.MenuItemId
from
    TargetDb.dbo.MenuItemRole a
    inner join SourceDb.dbo.MenuItemRole b
        on a.MenuItemRoleId = b.MenuItemRoleId

set identity_insert TargetDb.dbo.MenuItemRole on

insert into dbo.MenuItemRole (MenuItemRoleId, MenuItemId, RoleId)
select MenuItemRoleId, MenuItemId, RoleId
from SourceDb.dbo.MenuItemRole
where MenuItemRoleId not in (select MenuItemRoleId from TargetDb.dbo.MenuItemRole)

set identity_insert TargetDb.dbo.MenuItemRole off

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.