這是改寫MSDN的Treeview in SQL範例

原本它是搭配NorthWind資料庫來取出資料，並且有兩層節點。簡單的說有點類似迴圈，從DB撈出產品類別+專屬代號以後，當成第一層的樹根，文字為類別名稱，值是代號。再用代號去建立新的SQL指令然後去尋找相同代號(類別)的產品，建立第二層的根。

原先的程式已經被我改成可以直接點選就作加入類別(產品)/刪除的功能了，但是尋找的功能一直沒空研究。最近把專案拉回Access的懷抱，為了測試概念所以重寫了這份範例，而且也只有一層樹根。另外有一個地方的宣告名稱寫很爛，因為測試用的就隨便寫了，別鞭我！

 

資料表結構 [com] , [No]

資料表名稱 udb_com

 

命名空間

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>

SCRIPT

Protected Sub TreeView1_TreeNodePopulate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.TreeNodeEventArgs)

        If e.Node.ChildNodes.Count = 0 Then
            Select Case e.Node.Depth
                Case 0
                    '欄位名稱
                    PopulateCategories(e.Node)
                Case 1
                    '標題下面的分類
                    'PopulateProducts(e.Node)
            End Select
        End If
    End Sub


    Sub PopulateCategories(ByVal node As TreeNode)
        Dim sqlQuery As New OleDbCommand
        sqlQuery.CommandText = "SELECT [No], [com] FROM [udb_com]"
        
        Dim ResultSet As DataSet
        ResultSet = RunQuery(sqlQuery)
        
        Response.Write(ResultSet.Tables(0).Rows.Count)
        

        If ResultSet.Tables.Count > 0 Then
            Dim row As DataRow
            For Each row In ResultSet.Tables(0).Rows
                Dim NewNode As TreeNode = New TreeNode(row("com").ToString(), row("no").ToString())
                NewNode.PopulateOnDemand = True
                NewNode.SelectAction = TreeNodeSelectAction.SelectExpand
                NewNode.Selected = True
                node.ChildNodes.Add(NewNode)
            Next
        Else
            Response.Write("no")
        End If

    End Sub
    
    


    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    
    End Sub


    
    Function RunQuery(ByVal sqlQuery As OleDbCommand) As DataSet
        Dim x As String
        x = ConfigurationManager.ConnectionStrings("ConnectionString2").ConnectionString
        
        Dim xx As New OleDbConnection
        xx.ConnectionString = x
        
        Dim xxx As New OleDbDataAdapter
        xxx.SelectCommand = sqlQuery
        sqlQuery.Connection = xx
        
        Dim xxxx As DataSet = New DataSet
        
        Try
            xxx.Fill(xxxx)
        Catch ex As Exception
            Response.Write("Unable to connect to SQL Server.")
        End Try
        
        Return xxxx
    End Function

 

版面佈署

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>"
        ProviderName="<%$ ConnectionStrings:ConnectionString2.ProviderName %>"
        SelectCommand="SELECT [No], [com] FROM [udb_com]"></asp:SqlDataSource>

    <asp:TreeView ID="TreeView1" runat="server"
        OnTreeNodePopulate="TreeView1_TreeNodePopulate" MaxDataBindDepth="2"
        ExpandDepth="1" Font-Bold="False" Font-Overline="False" Font-Size="Large"
                    ImageSet="Contacts" NodeIndent="10" OnSelectedNodeChanged="TreeView1_SelectedNodeChanged">
        <ParentNodeStyle Font-Bold="True" ForeColor="#5555DD" />
        <HoverNodeStyle Font-Underline="False" BackColor="#CCFFCC" Font-Bold="True" />
        <SelectedNodeStyle Font-Underline="True" HorizontalPadding="0px"
            VerticalPadding="0px" BackColor="#FFCCFF" Font-Bold="True" />
        <Nodes>
            <asp:TreeNode PopulateOnDemand="True" Text="客戶" Value="main"></asp:TreeNode>
        </Nodes>
        <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black"
            HorizontalPadding="5px" NodeSpacing="0px" VerticalPadding="0px" />
    </asp:TreeView>