How to get the Menus Based on Role in XML Format and Bind them to the Menu Control in ASP.NET





0/5 (0 vote)
This article describes how to get the Menus Based on Role in XML Format and Bind them to the Menu Control in ASP.NET
Introduction
Following are the steps of how to get the Menus based on Role in XML format and bind them to the Menu control in ASP.NET :
- Create the
GroupMaster
where we can have define the Roles or Group. This table contains the following columns,pkGroupId
as Primary Key AndGroupName
andDescription
. - Create another
Table
where we can have theMenu
table where we can have columns likepkMenuId
as Primary Key which defines the menu,ParentID
(defines the submenu under which menu the submenu should appear),Title
,URL
,description
,Roles
. - Create the third table,
GroupMenuDetails
where we can place relation of the above two tables primary key i.e.,pkGroupId
andpkMenuId
. This table has the flagIsDisplay
: - Now create stored procedure which accepts the Role as input parameter and produces as output the XML String:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO Create PROCEDURE [dbo].[GetXMLMenus] ( @Role as Varchar(100) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. -- SET NOCOUNT ON; -- SET @Role = 1; SELECT -- Map columns to XML attributes/elements with XPath selectors. MainMenus.Title AS '@Text', Url AS '@Url', ( -- Use a sub query for child elements. SELECT SubMenus.Title AS '@Text', SubMenus.Url AS '@Url' FROM ( SELECT MD.pkMenuId AS pkMenuId, MD.ParentId AS ParentID, MD.Title, MD.Description, MD.Url, GMD.pkGroupMenuId, GMD.IsDisplay FROM GroupMenuDetails AS GMD INNER JOIN Menu AS MD ON GMD.pkMenuId = MD.pkMenuId AND GMD.pkGroupId = @Role ) AS SubMenus WHERE SubMenus.ParentID = MainMenus.pkMenuID AND SubMenus.ParentID IS NOT Null ORDER BY SubMenus.pkMenuID FOR XML PATH('SubMenu'),--The element name for each row. TYPE -- Column is typed so it nests as XML, not text. ) --AS 'products' -- The root element name for this child collection. FROM ( SELECT MD.pkMenuId AS pkMenuId, MD.ParentId AS ParentId, MD.Title, MD.Url, MD.Description, GMD.pkGroupMenuId, GMD.IsDisplay FROM GroupMenuDetails AS GMD INNER JOIN Menu AS MD ON GMD.pkMenuId = MD.pkMenuId AND GMD.pkGroupId = @Role AND MD.ParentId IS Null ) AS MainMenus FOR XML PATH('Menu'), --The element name for each row. ROOT('Menus') --The root element name for this result set. END
- Now create the aspx page and place the Menu control and
xmlDataSource
on it. - Now write the code on Page load to call the stored procedure by passing the parameter as role.
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { populatemenu(); } } protected void populatemenu() { --Create the sql connection . SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["SqlConn1"]); --Role Parameter int Role =1; SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; --Call to Stored Procedure cmd.CommandText = "GetXMLMenus"; if ((Role !=0)) { cmd.Parameters.AddWithValue("@Role", Role); cmd.Connection = conn; SqlDataAdapter da_1 = new SqlDataAdapter(cmd); DataSet ds_1 = new DataSet(); string result = ""; Xml XmlData = new Xml(); XmlDocument XmlDocument = new XmlDocument(); try { conn.Open(); --get the xml string result =Convert.ToString(cmd.ExecuteScalar()); da_1.Fill(ds_1); ds_1.DataSetName = "Menus1"; ds_1.Tables[0].TableName = "Menu1"; --Bind the get the xml string to xmldatasourse XmlDataSource1.Data = result; XmlDataSource1.XPath = "Menus/Menu"; --Bind the datasource to the menu control Menu1.DataSourceID= "XmlDataSource1"; Menu1.DataBind(); } catch (Exception ex) { Response.Write(ex.Message); } finally { conn.Dispose(); cmd.Dispose(); da_1.Dispose(); ds_1.Dispose(); } } }
Advantage / Benefits
We can use the same code for TreeView
control or for Menu control without any change in Stored Procedure or code. We will only have to place the TreeView
instead of the Menu control.
History
- 29th January, 2012: Initial version