ITSagar ITSagar - 2 months ago 17
ASP.NET (C#) Question

Database driven menu is too slow

in ASP.Net using C#, I am populating an ASP.Net menu control from database dynamically. I am trying to make those items unusable which have some child items below them. This is to ensure that on mobile, where we dont have a mouse to hover, on clicking the parent menu item, user will surely get child items in stead of a postback. The code below is working fine but with a drawback, it takes too much time to load and in the mean time shows a bad result by displaying all the items (all parent + all child) vertically in a single view.

SqlCommand com = new SqlCommand("SELECT * FROM menutab order by sno", c.con);
if(c.con.State == ConnectionState.Closed)
c.con.Open();
da = new SqlDataAdapter(com);
table = new DataTable();
da.Fill(table);
parents = new int[table.Rows.Count];
for (int i = 0; i < table.Rows.Count; i++)
if (table.Rows[i].ItemArray.GetValue(2).ToString() != String.Empty)
parents[i] = Convert.ToInt32(table.Rows[i].ItemArray.GetValue(2).ToString());
DataRow[] drowpar = table.Select("menu_parent = " + 0);

view = new DataView(table);
view.RowFilter = "menu_parent is NULL";
foreach (DataRowView row in view)
{
MenuItem menuItem = new MenuItem(row["item"].ToString(), row["sno"].ToString());
//menuItem.NavigateUrl = row["menu_url"].ToString();
for (int i = 0; i < parents.Length; i++)
{
if (Convert.ToInt32(row["sno"].ToString()) == parents[i])
{
menuItem.Selectable = false;
}
}
NavigationMenu.Items.Add(menuItem);
AddChildItems(table, menuItem);
}
private void AddChildItems(DataTable table, MenuItem menuItem)
{
DataView viewItem = new DataView(table);
viewItem.RowFilter = "menu_parent=" + menuItem.Value;
foreach (DataRowView childView in viewItem)
{
MenuItem childItem = new MenuItem(childView["item"].ToString(), childView["sno"].ToString());
//childItem.NavigateUrl = childView["menu_url"].ToString();
for (int i = 0; i<parents.Length; i++)
{
if (Convert.ToInt32(childView["sno"].ToString()) == parents[i])
{
childItem.Selectable = false;
}
}
menuItem.ChildItems.Add(childItem);
AddChildItems(table, childItem);
}
}


Can this process be more faster and reliable with some other code implementation?

Answer

Database driven menu will surely show some lag in loading it for the first time and it depends on the speed of your web server too. However, the following code will help you in reducing the load time:

DataRow[] drowpar = table.Select("menu_parent = " + 0);
foreach (DataRow row in drowpar)
{
    MenuItem menuItem = new MenuItem(row["item"].ToString(), row["sno"].ToString());
    NavigationMenu.Items.Add(menuItem);
    AddChildItems(table, menuItem);
}

private void AddChildItems(DataTable table, MenuItem menuItem)
{
    DataView viewItem = new DataView(table);
    viewItem.RowFilter = "menu_parent=" + menuItem.Value;
    foreach (DataRowView childView in viewItem)
    {
        MenuItem childItem = new MenuItem(childView["item"].ToString(), childView["sno"].ToString());
        menuItem.Selectable = false;
        menuItem.ChildItems.Add(childItem);
        AddChildItems(table, childItem);
    }
}

If you are doing other tasks on page_load, make sure you write the menu code at beginning so that server starts the processing with menu and then works on other code.
Hope this helps.