Getting Menu Items from database in asp.net

Question

I'm trying to get the menu items from the SQL Server 2008 database.

Ive tried something like this as I Googled around and found these tutorials first and second:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace MenuDriven
{
    public partial class MenuDB : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            menuBar.MaximumDynamicDisplayLevels = 3;

            if (!IsPostBack)
            {

                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString);

                con.Open();
                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                string sql = "Select * from MenuItems";
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                da.Fill(ds);
                dt = ds.Tables[0];
                DataRow[] drowpar = dt.Select("ParentID=" + 0);

                foreach (DataRow dr in drowpar)
                {
                    menuBar.Items.Add(new MenuItem(dr["MenuName"].ToString(),
                            dr["MenuID"].ToString(), "",
                            dr["MenuLocation"].ToString()));
                }

                foreach (DataRow dr in dt.Select("ParentID >" + 0))
                {
                    MenuItem mnu = new MenuItem(dr["MenuName"].ToString(), dr["MenuID"].ToString(),
                    "", dr["MenuLocation"].ToString());

                    //Code for Multiple Menu Levels
                    string valuePath = getValuePath(Convert.ToInt32(dr["ParentID"].ToString()), dt);
                    //menuBar.FindItem(dr["ParentID"].ToString()).ChildItems.Add(mnu);
                    menuBar.FindItem(valuePath).ChildItems.Add(mnu);**NullReferenceException was handled by the code**
                    //End Code for Multiple Menu Levels
                }
                con.Close();
            }

        }
        private string getValuePath(Int32 Parent, DataTable dt)
        {
            int predecessor = Parent;
            StringBuilder valuePath = new StringBuilder();
            valuePath.Append(Parent.ToString());
            DataRow[] drPar;
            while (true)
            {
                drPar = dt.Select("MenuID=" + predecessor);
                if (drPar[0]["ParentID"].ToString().Equals("0"))**//Index out of range exception**
                    break;
                valuePath.Insert(0, '/');
                valuePath.Insert(0, drPar[0]["ParentID"].ToString());
                predecessor = Convert.ToInt32(drPar[0]["ParentID"].ToString());
            }
            return valuePath.ToString();
        }

    }
}

I've two errors from this code:

Index out of range exception
NullReferenceException was handled by the code

I've pointed out the code that generated the error.

This is my database along with headers.

MenuID  MenuName    MenuLocation    ParentID    Value
1        Parent1      NULL            0             p1
2        Parent2      NULL            0             p2
3        Parent3      NULL            0             p3
11       SubMenuItem1     NULL            1             s1
12       SubMenuItem2     NULL            1             s1
21       SubMenuItem3     NULL            2             s1
111      SubSubMenuItem4  NULL            1             ss1
211      SubSubMenuItem5  NULL            2             ss1

score 3 · Accepted Answer · edited Sep 11 '12 at 15:38

I've got solution for any number of levels in the Menu Items.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace MenuDriven
{
    public partial class AnotherMenuTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            populateMenuItem();
        }
        private void populateMenuItem()
        {

            DataTable menuData = GetMenuData();
            AddTopMenuItems(menuData);

        }

        private DataTable GetMenuData()
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerString"].ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT MenuID,MenuName,ParentID FROM MenuItems", con))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }

            }
        }

        /// Filter the data to get only the rows that have a
        /// null ParentID (This will come on the top-level menu items)

        private void AddTopMenuItems(DataTable menuData)
        {
            DataView view = new DataView(menuData);
            view.RowFilter = "ParentID = 0";
            foreach (DataRowView row in view)
            {
                MenuItem newMenuItem = new MenuItem(row["MenuName"].ToString(), row["MenuID"].ToString());
                menuBar.Items.Add(newMenuItem);
                AddChildMenuItems(menuData, newMenuItem);
            }

        }

        //This code is used to recursively add child menu items by filtering by ParentID

        private void AddChildMenuItems(DataTable menuData, MenuItem parentMenuItem)
        {
            DataView view = new DataView(menuData);
            view.RowFilter = "ParentID=" + parentMenuItem.Value;
            foreach (DataRowView row in view)
            {
                MenuItem newMenuItem = new MenuItem(row["MenuName"].ToString(), row["MenuID"].ToString());
                parentMenuItem.ChildItems.Add(newMenuItem);
                AddChildMenuItems(menuData, newMenuItem);
            }
        }
    }
}

Thanks for KnowledgeSeeker and Antonio for your replies.

and the Database is like this.

This is my database along with headers.

MenuID  MenuName    MenuLocation    ParentID    
1          Parent1             NULL           0             
2          Parent2             NULL           0             
3          Parent3             NULL           0             
11         SubMenuItem1    NULL           1             
12         SubMenuItem2    NULL           1             
21         SubMenuItem3    NULL           2             
111        SubSubMenuItem4     NULL           1             
211        SubSubMenuItem5     NULL           2

The Solution is : Set the parent nodes within the database itself.

Ex: insert into dbo.MenuItems values(333,'SubSubMenuItem8',NULL,21)

This will set the SubSubMenuItem8 as a child to Parent Node SubMenuItem3

Learning · Answer 2 · 2012-09-10T10:49:55.787

Modify You code as per below example. I am using following menu. It is working You may need to modify your code as per your need

            String sqlQuery = "SELECT * FROM pMenuItems";

            dsMenu = DataProvider.Connect_Select(sqlQuery);

            DataTable tableMenu = dsMenu.Tables[0];
            DataView dvMenu = new DataView(tableMenu);
            //dvMenu.RowFilter = "PageInheritance is NULL";
            dvMenu.RowFilter = "PageInheritance = 0";
            int dsMenuRowCount = dsMenu.Tables[0].Rows.Count;
            //Create Top Menu
            //StringBuilder sb = new StringBuilder();
            StringBuilder sbMenuFooter = new StringBuilder();
            if (dsMenu != null && dsMenu.Tables.Count > 0 && dsMenu.Tables[0].Rows.Count > 0)
            {
                foreach (DataRowView row in dvMenu)
                {
                   // MenuItem menuItem = new MenuItem(row["MenuName"].ToString().ToUpper(), row["MenuID"].ToString());
                    MenuItem menuItem = new MenuItem(row["PageName"].ToString(), row["PageId"].ToString());
                    bool PageInternalLink = bool.Parse(row["PageInternalLink"].ToString());
                    if (PageInternalLink == true)
                    {
                        menuItem.NavigateUrl = row["PageURL"].ToString() + "?PageId=" + row["PageId"];
                    }
                    else
                    {
                        menuItem.NavigateUrl = row["PageURL"].ToString();
                        menuItem.Target = row["PageWindow"].ToString();
                    }
                    //menuItem.Target = 
                    Menu1.Items.Add(menuItem);
                    AddChildItems(tableMenu, menuItem, PageInternalLink );
                }

private static void AddChildItems(DataTable table, MenuItem menuItem, bool PageInternalLink)
{
    DataView viewItem = new DataView(table);
    viewItem.RowFilter = "PageInheritance = " + menuItem.Value;
    //foreach (DataRowView row in dvMenu)
    foreach (DataRowView childView in viewItem)
    {
        MenuItem childItem = new MenuItem(childView["MenuName"].ToString(), childView["MenuID"].ToString());
        childItem.NavigateUrl = childView["PageURL"].ToString() + "?PageId=" + childView["PageId"];
        if (PageInternalLink == true)
        {
            childItem.NavigateUrl = childView["PageURL"].ToString() + "?PageId=" + childView["PageId"];
        }
        else
        {
            childItem.NavigateUrl = childView["PageURL"].ToString();
            childItem.Target = childView["PageWindow"].ToString();
        }
        menuItem.ChildItems.Add(childItem);
        AddChildItems(table, childItem, PageInternalLink);
    }


}

HTML

 <script type="text/javascript">
    $(document).ready(function () {
        $('#<%=Menu1.ClientID %> ul', '#<%=Menu1.ClientID %> li').removeClass();
    });
    $(document).ready(function () {
        $("#dMenuContainer").delay(500).fadeIn(500);
        $('#smoothmenu1 ul, #smoothmenu1 li').removeClass();
    });
    ddsmoothmenu.init({
        mainmenuid: "<%=Menu1.ClientID %>", //menu DIV id
        orientation: 'h', //Horizontal or vertical menu: Set to "h" or "v"
        classname: 'ddsmoothmenu', //class added to menu's outer DIV
        customtheme: ["", ""], //78AC1B
        contentsource: "markup" //"markup" or ["container_id", "path_to_menu_file"]
    })
</script>
...
    <table align="center" cellpadding="0" cellspacing="0" align="center" >
        <tr>
            <td align="center" valign="middle"><div id="smoothmenu1" class="ddsmoothmenu">
                 <asp:Menu ID="Menu1"  runat="server"  Orientation="Horizontal" CssClass="ddsmoothmenu" IncludeStyleBlock="False" DisappearAfter="500" >

                 </asp:Menu> 
                 </div>
            </td>
        </tr>
    </table>

Knowledge Seeker, I need to get the values for ASP MENU not for li ui elements as i've seen the menu that you have used. — Karthik Chintala, Sep 10 '12 at 10:44
you only need to modify your code as per you need & download the Jquery file & JS file plus css from the link which i have mentioned. Trust me i tried the same thing as you are trying but i got in all sort of trouble. this menu script is flexible & can be nested to n Level.. — Learning, Sep 10 '12 at 10:48
if you want to test then only use code below for default css ` ` — Learning, Sep 10 '12 at 10:51

score 0 · Answer 3 · edited May 23 '17 at 10:27

0

You have to sort your data by ParentID and then loop trough table and for each item find parent item and add it to it's ChildItems, if you don't find parent add item to root.

get data :

string sql = "Select * from MenuItems Order By ParentID";
DataTable data = GetTable(sql);

and then fill menu :

  foreach (DataRow rw in data.Rows)
  {
    IEnumerable<MenuItem> menuItems = Extensions.GetItems<MenuItem>(menuBar.Items, item => item.ChildItems);

    MenuItem parent = menuItems.FirstOrDefault(mi => mi.Value == rw.Field<int>("ParentID").ToString());
    MenuItem newItem = new MenuItem(rw.Field<string>("MenuName"), rw.Field<int>("MenuID").ToString());
    if (parent == null)
      menuBar.Items.Add(newItem);
    else
      parent.ChildItems.Add(newItem);
  }

and here is the GetItems method that will return all tree nodes, taken from here : https://stackoverflow.com/a/1815600/351383

 public static class Extensions
  {
    public static IEnumerable<T> GetItems<T>(this IEnumerable collection, Func<T, IEnumerable> selector)
    {
      Stack<IEnumerable<T>> stack = new Stack<IEnumerable<T>>();
      stack.Push(collection.OfType<T>());

      while (stack.Count > 0)
      {
        IEnumerable<T> items = stack.Pop();
        foreach (var item in items)
        {
          yield return item;

          IEnumerable<T> children = selector(item).OfType<T>();
          stack.Push(children);
        }
      }
    }
  }

edited May 23 '17 at 10:27

Community

1
1

answered Sep 10 '12 at 10:29

Antonio Bakula

20,445
6
75
102

Antonio i could see the output for second level elements but i could not see for the third level menu items. It's throwing error stating 'Null Reference Exception' – Karthik Chintala Sep 10 '12 at 10:37
your getValuePath return null, try to debug it to see why. But try this solution it's much simpler and will work for unlimited number of levels – Antonio Bakula Sep 10 '12 at 10:39
I dont find for what it is giving error. Also what is `mainMenu` in your code – Karthik Chintala Sep 10 '12 at 11:36
It threw and error at this line `MenuItem newItem = new MenuItem(rw.Field("MenuName"), rw.Field("MenuID").ToString());` stating that `Specified cast is not valid.` – Karthik Chintala Sep 10 '12 at 12:25
what type is MenuID field in your database ? If is string then put rw.Field("MenuID") or maybe you have null in that field ? – Antonio Bakula Sep 10 '12 at 12:33
Its nChar and notnull for MenuID – Karthik Chintala Sep 10 '12 at 12:35
Hello Antonio, Thank you for your patience for replying to my comments. I've got another code which is working fine for me for any number of levels in the Menu Items – Karthik Chintala Sep 10 '12 at 13:04
I'll post answer tomorrow as I dont have previlages to post it now itself – Karthik Chintala Sep 10 '12 at 13:14

Getting Menu Items from database in asp.net

3 Answers3

Linked