参考资料:http://msdn.microsoft.com/zh-cn/library/ms191268(v=sql.90).aspx
绑定树形图(TreeView),看到网上很多方法都是用循环遍历进行绑定。应用SqlServer 中自带的XML功能,通过存储过程或sql语句,直接生产xml格式的数据源,
能够方便且高效的完成对树形图的绑定。
1、数据表结构
2、存储过程代码及查询结果
SELECT A.title,discription,url,
(select B.title,discription,url,
(select C.title,discription,url from Table_3 C where parentID = B.ID FOR XML RAW('TreeNode'), TYPE)
from Table_3 B where parentID = A.ID FOR XML RAW('TreeNode'), TYPE)
FROM Table_3 A where parentID =0 FOR XML RAW('TreeNode'), TYPE
3、asp.net前台与后台
3.1前台
<form id="form1" runat="server">
<asp:TreeView ID="MyTreeView" runat="server" DataSourceID="MyXmlDataSource">
<DataBindings>
<asp:TreeNodeBinding DataMember="TreeNode" TextField="title" NavigateUrlField="url" />
</DataBindings>
</asp:TreeView>
<asp:XmlDataSource ID="MyXmlDataSource" runat="server"></asp:XmlDataSource>
</form>
3.2 后台
protected void Page_Load(object sender, EventArgs e) { string connString = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ToString(); string commandtext = "SELECT A.title,discription,url," + "(select B.title,discription,url," + "(select C.title,discription,url from Table_3 C where parentID = B.ID FOR XML RAW('TreeNode'), TYPE) from Table_3 B where parentID = A.ID FOR XML RAW('TreeNode'), TYPE)" + " FROM Table_3 A where parentID =0 FOR XML RAW('TreeNode'), TYPE"; SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(commandtext, conn); conn.Open(); string xmlstring = cmd.ExecuteScalar().ToString(); MyXmlDataSource.Data = xmlstring; if (conn.State != ConnectionState.Open) { conn.Close(); } }
4、效果