可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
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
回答1:
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
回答2:
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>
回答3:
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);
}
}
}
}
