Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Samples » Downloads
 
Add your own
alternative version

Generating menu from database according to user privilege

, 25 Sep 2012
Dynamic menu generation according to user privileges from database.
MenuWithCustomPrivs.zip
MenuWithCustomPrivs
MenuWithCustomPrivs
App_Code
App_Data
Images
Header.png
MenuWithCustomPrivs.suo
Style
--Table : [tblGroupPrivMst]
CREATE TABLE [dbo].[tblGroupPrivMst](
	[GroupCode] [char](3) NOT NULL,
	[GroupDesc] [varchar](50) NOT NULL,
	[PrivilegeID] [varchar](30) NOT NULL,
 CONSTRAINT [PK_tblGroupPrivMst] PRIMARY KEY CLUSTERED 
(
	[GroupCode] 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
/*Insert Data*/
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'AD ', N'Admin', N'1111111111111111111111111')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'NW ', N'New User', N'10001000000000000100000')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'PM ', N'Project Manager', N'1011001110110111001111100')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'SA ', N'System Admin', N'1111001110110101011011100')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'SE ', N'Software Engineer', N'1110011100010101100101000')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'SSE', N'Senior Software Engineer', N'1011001110110001001011100')
INSERT [dbo].[tblGroupPrivMst] ([GroupCode], [GroupDesc], [PrivilegeID]) VALUES (N'TL ', N'Software Engineer', N'1011001110110111001100100')

--Table : [tblUserGrpMap]
CREATE TABLE [dbo].[tblUserGrpMap](
	[UserID] [char](4) NOT NULL,
	[GroupCode] [char](3) NOT NULL,
 CONSTRAINT [unique_key] UNIQUE NONCLUSTERED 
(
	[UserID] ASC,
	[GroupCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [your_unique_key] UNIQUE NONCLUSTERED 
(
	[UserID] ASC,
	[GroupCode] 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
/*Insert Data*/
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K001', N'PM ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K002', N'SE ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K003', N'TL ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K004', N'SE ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K004', N'TL ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K005', N'SSE')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K006', N'SA ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K010', N'AD ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K019', N'SA ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K032', N'SE ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K032', N'SSE')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K034', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K038', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K039', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K040', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K041', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K042', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K043', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K044', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K044', N'SSE')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K046', N'NW ')
INSERT [dbo].[tblUserGrpMap] ([UserID], [GroupCode]) VALUES (N'K047', N'NW ')


--Table : [tblDesgMst]
CREATE TABLE [dbo].[tblDesgMst](
	[DesigCode] [char](3) NOT NULL,
	[Description] [varchar](50) NOT NULL,
	[Active] [bit] NOT NULL,
 CONSTRAINT [PK__tblDesgM__66B4CA08719CDDE7] PRIMARY KEY CLUSTERED 
(
	[DesigCode] 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
/*Insert Data*/
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'AD ', N'System Administrator', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'PM ', N'Project Manager', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'SA ', N'System Analyst', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'SE ', N'Software Engineer', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'SSE', N'Senior Software Engineer', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'ST ', N'Software Tester', 1)
INSERT [dbo].[tblDesgMst] ([DesigCode], [Description], [Active]) VALUES (N'TL ', N'Team Leader', 1)


--Table : [tblUserMst]
CREATE TABLE [dbo].[tblUserMst](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [char](4) NOT NULL,
	[Name] [varchar](70) NOT NULL,
	[Password] [varchar](40) NOT NULL,
	[DOB] [datetime] NOT NULL,
	[DOJ] [datetime] NOT NULL,
	[DesigCode] [char](3) NOT NULL,
	[EmailId] [varchar](50) NOT NULL,
	[TelNo] [varchar](26) NULL,
	[HandPhone] [varchar](25) NULL,
	[Active] [bit] NOT NULL,
	[Question] [varchar](100) NOT NULL,
	[Answer] [varchar](100) NOT NULL,
 CONSTRAINT [PK__tblUserM__1788CCAC7B264821] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__tblUserM__737584F6762C88DA] UNIQUE NONCLUSTERED 
(
	[Name] 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].[tblUserMst]  WITH CHECK ADD  CONSTRAINT [FK__tblUserMs__Desig__7D0E9093] FOREIGN KEY([DesigCode])
REFERENCES [dbo].[tblDesgMst] ([DesigCode])
GO

ALTER TABLE [dbo].[tblUserMst] CHECK CONSTRAINT [FK__tblUserMs__Desig__7D0E9093]
GO
/*Insert Data*/
SET IDENTITY_INSERT [dbo].[tblUserMst] ON
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (2, N'K002', N'DineshKumar', N'c2RrMDYwOTg5', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'dinesh.kumar@kalsinfo.com', N'4286-233325378', N'91-9597919128', 1, N'What was your childhood nickname? ', N'SD')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (1, N'K003', N'Prashanth', N'OTE4OTE=', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'prashanth.m@kalsinfo.com', N'0800-220273679', N'91-9164275508', 1, N'Your Mother Name?', N'Parameshwari')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (4, N'K004', N'PradeepKumar', N'MTIzNDU=', CAST(0x00007CD700000000 AS DateTime), CAST(0x00009F5500000000 AS DateTime), N'PM ', N'pradeepkumar.s@abc.com', N'4286-233325232', N'91-9597919128', 1, N'What was your childhood nickname? ', N'PP')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (6, N'K006', N'Raju', N'MTIz', CAST(0x00007FF300000000 AS DateTime), CAST(0x00009FCB00000000 AS DateTime), N'ST ', N'raju@abc.com', N'', N'', 1, N'What was your childhood nickname? ', N'Rak')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (9, N'K009', N'Kumar', N'YXNk', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'AD ', N'dineskmr10@gmail.com', N'', N'', 1, N'What was your childhood nickname? ', N'sdf')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (18, N'K010', N'Admin', N'dGVzdA==', CAST(0x00007DCF00000000 AS DateTime), CAST(0x00009FAB00000000 AS DateTime), N'AD ', N'amit.kumar@kalsinfo.com', N'', N'91-8904270677', 1, N'In what city or town was your first job?', N'Bangalore')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (11, N'K011', N'bhaskar', N'MTIzNDU2', CAST(0x0000722300000000 AS DateTime), CAST(0x00009A1600000000 AS DateTime), N'SSE', N'bhaskar@kisltech.com', N'', N'', 1, N'In what city or town was your first job?', N'NBSC')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (14, N'K014', N'SD', N'ZGZm', CAST(0x00007FF300000000 AS DateTime), CAST(0x00009FCB00000000 AS DateTime), N'AD ', N'dinesh@abc.com', N'', N'', 0, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (16, N'K015', N'kumarSD', N'ZGFzZnNhZGY=', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A00700000000 AS DateTime), N'AD ', N'xyz@abc.com', N'1234-123123123', N'91-4234234234', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (17, N'K017', N'Amit', N'YW1pdA==', CAST(0x00007EFB00000000 AS DateTime), CAST(0x00009FCB00000000 AS DateTime), N'SE ', N'amit@kalsinfo.com', N'1234-121313411', N'12-1212121212', 1, N'What was your childhood nickname? ', N'Amit')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (20, N'K019', N'Gopi', N'dGVzdA==', CAST(0x000080A900000000 AS DateTime), CAST(0x00009FAB00000000 AS DateTime), N'SA ', N'gopi@gmail.com', N'', N'91-8904270677', 1, N'In what city or town was your first job?', N'Bangalore')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (21, N'K021', N'Solanki', N'dGVzdA==', CAST(0x00007E6D00000000 AS DateTime), CAST(0x00009FD600000000 AS DateTime), N'ST ', N'aaa@gmail.com', N'', N'91-5656565644', 1, N'What was your childhood nickname? ', N'aaaaa')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (24, N'K024', N'TestEmp', N'abc', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'TestEmp@kalsinfo.com', N'1234-12345678', N'12-1234567890', 1, N'What is your childhood Nickname?', N'SD')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (26, N'K026', N'TestEmp2', N'abc', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'TestEmp2@kalsinfo.com', N'1234-12345678', N'12-1234567890', 1, N'What is your childhood Nickname?', N'SP')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (29, N'K029', N'as', N'c2Q=', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'PM ', N'a.b@c.c', N'', N'', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (30, N'K030', N'DineshRao', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'AD ', N'din@kals.com', N'1234-121212121', N'91-1212121221', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (31, N'K031', N'tstuser', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A00900000000 AS DateTime), N'AD ', N'abc@xtz.com', N'', N'', 1, N'What was your childhood nickname? ', N'sdk')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (32, N'K032', N'amy', N'dGVzdA==', CAST(0x000080A900000000 AS DateTime), CAST(0x0000A02900000000 AS DateTime), N'SE ', N'amit.kumar@kalsinfo.com', N'', N'90-8904270677', 1, N'What was your childhood nickname? ', N'Amy')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (34, N'K034', N'tstusernm', N'dGVzdA==', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'din@kals.com', N'', N'', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (38, N'K038', N'bhaskartst', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'dis@din.com', N'', N'', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (39, N'K039', N'tstmp', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'PM ', N'din@sdk.com', N'', N'', 1, N'What was your childhood nickname? ', N'sdk')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (40, N'K040', N'user', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'PM ', N'din@gmail.com', N'', N'', 1, N'What was your childhood nickname? ', N'ss')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (41, N'K041', N'sgl', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'PM ', N'din@y.com', N'', N'', 1, N'In what city or town was your first job?', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (42, N'K042', N'sdgh', N'c2Rn', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SA ', N'din@gmail.com', N'', N'', 1, N'What was your childhood nickname? ', N'sdk')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (43, N'K043', N'KalsEmp', N'c2Rr', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'SE ', N'din@abc.com', N'', N'', 1, N'What was your childhood nickname? ', N'sdk')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (44, N'K044', N'empdin', N'c2Rr', CAST(0x00007F1B00000000 AS DateTime), CAST(0x0000A03000000000 AS DateTime), N'AD ', N'din@kalsinfo.com', N'', N'', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (46, N'K046', N'sfdf', N'dGVzdA==', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A00600000000 AS DateTime), N'AD ', N'aa@fg.fgg', N'', N'', 1, N'What was your childhood nickname? ', N'sd')
INSERT [dbo].[tblUserMst] ([ID], [UserID], [Name], [Password], [DOB], [DOJ], [DesigCode], [EmailId], [TelNo], [HandPhone], [Active], [Question], [Answer]) VALUES (47, N'K047', N'dd', N'ZGQ=', CAST(0x00007FF300000000 AS DateTime), CAST(0x0000A01B00000000 AS DateTime), N'PM ', N'diin@abc.com', N'', N'', 1, N'In what city or town was your first job?', N'ssd')
SET IDENTITY_INSERT [dbo].[tblUserMst] OFF

--Table : [tblMenuMst]
CREATE TABLE [dbo].[tblMenuMst](
	[MenuID] [int] NULL,
	[Text] [varchar](50) NULL,
	[Description] [varchar](200) NULL,
	[ParentID] [int] NULL,
	[NavigateUrl] [varchar](100) NULL
) ON [PRIMARY]

GO
/*Insert Data*/
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (1, N'HOME', N'HOME', NULL, N'frmHome.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (2, N'MASTERS', N'MASTERS', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (3, N'TRANSCATION', N'TRANSCATION', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (4, N'REPORT', N'REPORT', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (5, N'OTHERS', N'OTHERS', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (6, N'Price Type Master', N'Price Type Master', 2, N'Masters/frmPriceTypeMst.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (7, N'Seasonal Code Master', N'Seasonal Code Master', 2, N'Masters/frmSeasonCodeMst.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (8, N'SKU Screen', N'SKU Screen', 2, N'Masters/frmSKUScreen.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (9, N'Division Master Screen', N'Division Master Screen', 2, N'Masters/frmDivisionMst.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (10, N'Supplier Master  Screen', N'Supplier Master  Screen', 2, N'Masters/frmSupplierMst.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (11, N'Article Master  Screen', N'Article Master Screen', 2, N'Masters/frmArticleMaster.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (12, N'Area Master  Screen', N'Area Master  Screen', 2, N'Masters/frmAreaMaster.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (13, N'Region Master Screen', N'Region Master Screen', 2, N'Masters/frmRegionMaster.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (14, N'Outlet Master  Screen', N'Outlet Master Screen', 2, N'Masters/frmOutletMaster.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (15, N'Master Allocation  Screen', N'Master Allocation Screen', 3, N'Transactions/frmMADetails.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (16, N'MA Authorization and Search Screen', N'MA Authorization and Search Screen', 3, N'Transactions/frmMASearch.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (17, N'MA Reports', N'MA Reports', 4, N'Transactions/frmGlobalReport.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (18, N'Change Password', N' Change Password', 5, N'Masters/frmChangePassword.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (24, N'Group Privilege', N'Group Privilege', 2, N'Masters/frmGroupPrivilege.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (25, N'Group Mapping', N'Group Mapping', 2, N'Masters/frmGroupMap.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (19, N'ADMIN', N'Admin', NULL, N'#')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (20, N'Report HeaderMaster', N'ReportHeaderMaster', 19, N'Admin/frmReportHeaders.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (21, N'Report Details Master', N'Report Details Master', 19, N'Admin/frmReportDetails.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (22, N'Status Type Master', N'Status Type Master', 19, N'Admin/frmStatusTypeMst.aspx')
INSERT [dbo].[tblMenuMst] ([MenuID], [Text], [Description], [ParentID], [NavigateUrl]) VALUES (23, N'Report Status Screen', N'Report Status Screen', 19, N'Admin/frmReportStatusDetails.aspx')
GO

--Stored Procedure : 
CREATE PROCEDURE [dbo].[spMenuItem]
	@UserID [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN
	--DECLARE @GroupCode VARCHAR(5)
	--SET @GroupCode=(SELECT DISTINCT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID)
	CREATE TABLE #TMP(MenuID INT, Text VARCHAR(50), Description VARCHAR(50), ParentID INT, NavigateUrl VARCHAR(100))
	DECLARE @VAL VARCHAR(MAX), @Pos INT, @len INT
	SET @VAL=(SELECT REPLACE(REPLACE(CONVERT(VARCHAR(30), SUM(CAST(PrivilegeID AS NUMERIC(30, 0)))), '2', '1'), '3', '1') FROM tblGroupPrivMst WHERE GroupCode in (SELECT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID))
	SET @Pos=1
	SET @len=LEN(@VAL)
	WHILE(@len!=0)
	BEGIN
		DECLARE @Value CHAR(1)
		SET @Value=SUBSTRING(@VAL, @Pos, 1)
		IF @Value=1
		BEGIN
			PRINT @Value
			INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID=@Pos
		END
		SET @Pos=@Pos+1
		SET @len=@len-1
	END
	--For first Node (Inserting The Parent Node)
	INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT ParentID		FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
	--For second Node (Inserting The Parent Node)
	INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT ParentID		FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
	--For third Node (Inserting The Parent Node)
	INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT ParentID		FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
	SELECT * FROM #TMP ORDER BY MenuID ASC
	DROP TABLE #TMP
END

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

_Amy
Software Developer (Senior)
India India
I've been working with various Microsoft Technologies. I have earned my Microsoft Certified Technology Specialist (MCTS) certification. I'm a highly motivated self-starter with an attitude for learning new skills and utilizing that in my work.
 

--Amit Kumar

 
You can reach me at:
Facebook | Linkedin | Twitter | Google+
Follow on   Twitter   Google+

| Advertise | Privacy | Mobile
Web04 | 2.8.140814.1 | Last Updated 26 Sep 2012
Article Copyright 2012 by _Amy
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid