| |
 How to generate Menu from the database?
by: Itfunda
Product Type: Tips and Tricks (Books)
Technologies: ASP.NET Menu
To automatically generate horizontal or vertical menu from the database, we can follow this
approach.
ASPX Page
<asp:Menu ID="Menu1" runat="server" Orientation="Horizontal" />
DATABASE STRUCTURE
Code Behind
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetMenuData();
}
}
private void GetMenuData()
{
DataTable table = new DataTable();
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
{
// write the sql statement to execute
string sql = "SELECT MenuId, MenuName, MenuDescription, ParentMenuId, MenuUrl FROM Menus";
// instantiate the command object to fire
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
// get the adapter object and attach the command object to it
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
// fire Fill method to fetch the data and fill into DataTable
ad.Fill(table);
}
}
}
// populate the parent node first
DataView view = new DataView(table);
view.RowFilter = "ParentMenuId is NULL";
foreach (DataRowView row in view)
{
MenuItem menuItem = new MenuItem(row["MenuName"].ToString(),
row["MenuId"].ToString());
menuItem.NavigateUrl = row["MenuUrl"].ToString();
Menu1.Items.Add(menuItem);
AddChildItems(table, menuItem);
}
}
private static void AddChildItems(DataTable table, MenuItem menuItem)
{
DataView viewItem = new DataView(table);
viewItem.RowFilter = "ParentMenuId = " + menuItem.Value;
foreach (DataRowView childView in viewItem)
{
MenuItem childItem = new MenuItem(childView["MenuName"].ToString(),
childView["MenuId"].ToString());
childItem.NavigateUrl = childView["MenuUrl"].ToString();
menuItem.ChildItems.Add(childItem);
AddChildItems(table, childItem);
}
}
In the above code snippet, we have an asp:Menu control on the .aspx page with Orientation as “Horizontal”.
In the code behind, first we have retrieved the menu items from the database (look at my database table structure above). The record with ParentMenuId as “NULL” will be the parent item of the menu and rest will be the child (It is very important that you organize the records in such a way that it forms a hierarchical menu items). Once we have the records into the DataTable, we can filter it based on the ParentMenuID to “NULL” to get the parent node and add the menu Item to the Menu then we have called AddChildItems method to fetch the child items from the DataTable and added into the Menu (AddChildITems is the recursive function to make sure that it goes deep into several herarchy of the items and sub items).
OUTPUT
|