Menu System based on User Roles

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.

if OBJECT_ID('dbo.GetMenuItems') is not null drop procedure dbo.GetMenuItems
go
create procedure dbo.GetMenuItems
    (
    @UserName nvarchar(256)
    ) as
/*-----------------------------------------------------------------------------------------------*/
/*
declare
     @UserName nvarchar(256) = 'pjasinski'
*/
/*-----------------------------------------------------------------------------------------------*/
;
with ExplicitMenu 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
    )
/*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/
,WholeMenu as (
    select
         MenuItemID
        ,DisplayText
        ,ParentId
        ,SortOrder
        ,Url
        ,LeafItemId = MenuItemId
    from
        ExplicitMenu
    where 1=1
        and ParentId is not null
        and MenuItemId not in (select distinct ParentId from dbo.MenuItem where ParentId is not null and ActiveFlag = 1)
        
    union all select
         m.MenuItemID
        ,m.DisplayText
        ,m.ParentId
        ,m.SortOrder
        ,m.Url
        ,a.LeafItemId
    from
        dbo.MenuItem m
        inner join WholeMenu a on
            m.MenuItemId = a.ParentId
)
/*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/
,Items as (
    select distinct
         MenuItemId
        ,DisplayText
        ,ParentId
        ,SortOrder
        ,Url
    from
        WholeMenu
    )
/*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*/
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')

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

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