当前位置： 文江博客话题详情

从数据库优化树视图人口

发布于 2024-11-05 09:17:33 字数 9429 浏览 1 评论 0原文

下面是我用来将数据从数据库加载到树视图中的代码！但我觉得检索时有点滞后……有什么方法可以优化它吗？

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Try
        If Not IsPostBack Then
            PopulateRootLevel("0", "ALL")
        End If
    Catch ex As Exception
        MSGMgr.errHandlerSys(ex.Message, lblMsg)
    End Try
End Sub

Private Sub PopulateRootLevel(ByVal LvlCD As String, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, GroupNo, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo and SubItemNo1='0' AND LevelCD='0' AND ItemNo <> '0') SubItemCnt FROM i_Menu_Access prt WHERE ItemNo = '0' AND LevelCD = '" & LvlCD & "' ORDER BY GroupNo", objConn)
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, GroupNo, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo and SubItemNo1='0' AND LevelCD='0' AND ItemNo <> '0') SubItemCnt FROM i_Menu_Access prt WHERE ItemNo = '0' AND LevelCD = '" & LvlCD & "' AND Level3 = @Scheme ORDER BY GroupNo", objConn)
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim GrpNo As String = dr("GroupNo").ToString().Trim()

        child.ToolTip = "Click to get Child"
        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        tvMenuTable.Nodes.Add(child)
        PopulateSubLevel(GrpNo, LvlCD, child, scheme)
    Next

End Sub

Private Sub PopulateSubLevel(ByVal parentid As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo AND ItemNo = prt.ItemNo AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND LevelCD = '0') SubItemCnt FROM i_Menu_Access prt WHERE LevelCD = @LvlCD AND GroupNo = @parentID AND ItemNo <> '0' AND SubItemNo1 = '0' ORDER BY ItemNo", objConn)
        objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo AND ItemNo = prt.ItemNo AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND LevelCD = '0') SubItemCnt FROM i_Menu_Access prt WHERE LevelCD = @LvlCD AND GroupNo = @parentID AND ItemNo <> '0' AND SubItemNo1 = '0' AND Level3 = @Scheme ORDER BY ItemNo", objConn)
        objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim ID() As String
        ID = dr("ID2").ToString().Trim().Split("|")

        child.ToolTip = "Click to get Child"
        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
        PopulateThirdLevel(ID(0), ID(1), LvlCD, child, scheme)
    Next

End Sub

Private Sub PopulateThirdLevel(ByVal lvlOneID As Integer, ByVal lvlTwoID As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo1, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND ItemNo <> '0' AND SubItemNo1 <> '0' AND SubItemNo2 = '0' ORDER BY SubItemNo1", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo1, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND ItemNo <> '0' AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND Level3 = @Scheme ORDER BY SubItemNo1", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim ID() As String
        ID = dr("ID2").ToString().Trim().Split("|")

        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
        Populate4thLevel(ID(0), ID(1), ID(2), LvlCD, child, scheme)
    Next
End Sub

Private Sub Populate4thLevel(ByVal lvlOneID As Integer, ByVal lvlTwoID As Integer, ByVal lvl3rdID As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND SubItemNo1 = @lvl3rdID AND SubItemNo2 <> '0' ORDER BY SubItemNo2", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@lvl3rdID", SqlDbType.Int).Value = lvl3rdID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND SubItemNo1 = @lvl3rdID AND SubItemNo2 <> '0' AND Level3 = @Scheme ORDER BY SubItemNo2", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@lvl3rdID", SqlDbType.Int).Value = lvl3rdID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
    Next
End Sub
原文

Below is the code that i used to load the data into treeview from database! But i feel like it's a bit laggy while retrieving...is thr any way to optimize it?

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Try
        If Not IsPostBack Then
            PopulateRootLevel("0", "ALL")
        End If
    Catch ex As Exception
        MSGMgr.errHandlerSys(ex.Message, lblMsg)
    End Try
End Sub

Private Sub PopulateRootLevel(ByVal LvlCD As String, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, GroupNo, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo and SubItemNo1='0' AND LevelCD='0' AND ItemNo <> '0') SubItemCnt FROM i_Menu_Access prt WHERE ItemNo = '0' AND LevelCD = '" & LvlCD & "' ORDER BY GroupNo", objConn)
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, GroupNo, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo and SubItemNo1='0' AND LevelCD='0' AND ItemNo <> '0') SubItemCnt FROM i_Menu_Access prt WHERE ItemNo = '0' AND LevelCD = '" & LvlCD & "' AND Level3 = @Scheme ORDER BY GroupNo", objConn)
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim GrpNo As String = dr("GroupNo").ToString().Trim()

        child.ToolTip = "Click to get Child"
        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        tvMenuTable.Nodes.Add(child)
        PopulateSubLevel(GrpNo, LvlCD, child, scheme)
    Next

End Sub

Private Sub PopulateSubLevel(ByVal parentid As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo AND ItemNo = prt.ItemNo AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND LevelCD = '0') SubItemCnt FROM i_Menu_Access prt WHERE LevelCD = @LvlCD AND GroupNo = @parentID AND ItemNo <> '0' AND SubItemNo1 = '0' ORDER BY ItemNo", objConn)
        objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title, (SELECT DISTINCT COUNT(*) FROM i_Menu_Access WHERE GroupNo = prt.GroupNo AND ItemNo = prt.ItemNo AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND LevelCD = '0') SubItemCnt FROM i_Menu_Access prt WHERE LevelCD = @LvlCD AND GroupNo = @parentID AND ItemNo <> '0' AND SubItemNo1 = '0' AND Level3 = @Scheme ORDER BY ItemNo", objConn)
        objCommand.Parameters.Add("@parentID", SqlDbType.Int).Value = parentid
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim ID() As String
        ID = dr("ID2").ToString().Trim().Split("|")

        child.ToolTip = "Click to get Child"
        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
        PopulateThirdLevel(ID(0), ID(1), LvlCD, child, scheme)
    Next

End Sub

Private Sub PopulateThirdLevel(ByVal lvlOneID As Integer, ByVal lvlTwoID As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo1, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND ItemNo <> '0' AND SubItemNo1 <> '0' AND SubItemNo2 = '0' ORDER BY SubItemNo1", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo1, cast(groupno as varchar)+'|'+cast(itemno as varchar)+'|'+cast(subitemno1 as varchar) ID2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND ItemNo <> '0' AND SubItemNo1 <> '0' AND SubItemNo2 = '0' AND Level3 = @Scheme ORDER BY SubItemNo1", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        Dim ID() As String
        ID = dr("ID2").ToString().Trim().Split("|")

        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
        Populate4thLevel(ID(0), ID(1), ID(2), LvlCD, child, scheme)
    Next
End Sub

Private Sub Populate4thLevel(ByVal lvlOneID As Integer, ByVal lvlTwoID As Integer, ByVal lvl3rdID As Integer, ByVal LvlCD As String, ByVal parentNode As TreeNode, ByVal scheme As String)

    Dim objConn As New SqlConnection(DBMgr.asyncADOCnnStr("SQL"))

    Dim objCommand As SqlCommand
    If scheme = "ALL" Then
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND SubItemNo1 = @lvl3rdID AND SubItemNo2 <> '0' ORDER BY SubItemNo2", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@lvl3rdID", SqlDbType.Int).Value = lvl3rdID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
    Else
        objCommand = New SqlCommand("SELECT DISTINCT LevelCD, Level1 IsChecked, LevelCD+Level2+'|'+Level1+'|'+CASE WHEN Level3 IS NULL THEN '' ELSE Level3 END ID, Level3 Scheme, ItemNo, SubItemNo2, Descriptions Title FROM i_Menu_Access WHERE LevelCD = @LvlCD AND GroupNo = @lvlOneID AND ItemNo = @lvlTwoID AND SubItemNo1 = @lvl3rdID AND SubItemNo2 <> '0' AND Level3 = @Scheme ORDER BY SubItemNo2", objConn)
        objCommand.Parameters.Add("@lvlOneID", SqlDbType.Int).Value = lvlOneID
        objCommand.Parameters.Add("@lvlTwoID", SqlDbType.Int).Value = lvlTwoID
        objCommand.Parameters.Add("@lvl3rdID", SqlDbType.Int).Value = lvl3rdID
        objCommand.Parameters.Add("@LvlCD", SqlDbType.VarChar).Value = LvlCD
        objCommand.Parameters.Add("@Scheme", SqlDbType.VarChar).Value = scheme
    End If

    Dim da As New SqlDataAdapter(objCommand)
    Dim dt As New DataTable()
    da.Fill(dt)

    For Each dr As DataRow In dt.Rows
        Dim child As New TreeNode()
        child.Text = dr("Title").ToString().Trim()
        child.Value = dr("ID").ToString().Trim()

        child.SelectAction = TreeNodeSelectAction.SelectExpand
        child.CollapseAll()

        parentNode.ChildNodes.Add(child)
    Next
End Sub

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

野の 2024-11-12 09:17:33

好吧，如果您正确设计数据库，那么您将只需要一个函数来获取和获取数据。填充任意级别的节点。例如，假设有一个示例数据库表“Items”，例如

ItemId int NOT NULL PK
ItemText varchar(200)
ParentId int NULL FK

请注意，我们已将 FK 放在具有相同 Items 表的 ParentId 上，并且可以为 NULL 表示根级别项目。现在，您可以使用查询获取任何级别的项目，例如

SELECT ItemId, ItemText, ParentId FROM Items WHERE ParentId = @ParentId

当您想要检索根级别项目时，将 @ParentId 传递为 NULL - 获取展开任何节点并获取子节点，传递 ItemId< /code> 将该节点作为上面查询的 ParentId 。

Well, if you design your data-base correctly then you will need only one function to get & fill nodes at any level. For example, let's say consider a sample database table 'Items' such as

ItemId int NOT NULL PK
ItemText varchar(200)
ParentId int NULL FK

Note that we have put FK on ParentId with the same Items table and that can be NULL indicating root level items. Now, you can get items at any level using query such as

SELECT ItemId, ItemText, ParentId FROM Items WHERE ParentId = @ParentId

When you want to retrieve root level items, pass @ParentId as NULL - get expand any node and get childs, pass the ItemId for that node as ParentId to above query.

回复 原文
满天都是小星星 2024-11-12 09:17:33

您可以将数据表添加到数据集中，并在数据集中设置父子关系。

这是完整、干净且优化的代码。我在必要的地方写了评论

DataTable dtbl1=new DataTable();//parent datatable
DataTable dtbl2=new DataTable();//child datatable

DataSet ds = new DataSet();
ds.Tables.Add(dtbl1);
ds.Tables.Add(dtbl2);
ds.Relations.Add("Children", dtbl1.Columns["dtb1ID"], dtbl2.Columns["dtbl2ID"]);//define parent child relation in dataset

if (ds.Tables[0].Rows.Count > 0)
{
    trv.Nodes.Clear();
    Int32 count = 0;

    foreach(DataRow masterRow in  ds.Tables[0].Rows)
    {
        TreeNode masterNode = new TreeNode((String)masterRow["dtbl1ColumnYouWantToDisplay"], Convert.ToString(masterRow["dtbl1ID"]));
        trv.Nodes.Add(masterNode);

        foreach (DataRow childRow in masterRow.GetChildRows("Children"))
        {
            TreeNode childNode = new TreeNode((String)childRow["dtbl2ColumnYouWantToDisplay"], Convert.ToString(childRow["dtb2ID"]));
            masterNode.ChildNodes.Add(childNode);
            count++;
        }
    }
    trv.ExpandAll();
}

You can add your datatables in a Dataset and setup the parent and child relationship in the Dataset.

Here is the complete, clean and optimized code. I wrote comments where necessary

DataTable dtbl1=new DataTable();//parent datatable
DataTable dtbl2=new DataTable();//child datatable

DataSet ds = new DataSet();
ds.Tables.Add(dtbl1);
ds.Tables.Add(dtbl2);
ds.Relations.Add("Children", dtbl1.Columns["dtb1ID"], dtbl2.Columns["dtbl2ID"]);//define parent child relation in dataset

if (ds.Tables[0].Rows.Count > 0)
{
    trv.Nodes.Clear();
    Int32 count = 0;

    foreach(DataRow masterRow in  ds.Tables[0].Rows)
    {
        TreeNode masterNode = new TreeNode((String)masterRow["dtbl1ColumnYouWantToDisplay"], Convert.ToString(masterRow["dtbl1ID"]));
        trv.Nodes.Add(masterNode);

        foreach (DataRow childRow in masterRow.GetChildRows("Children"))
        {
            TreeNode childNode = new TreeNode((String)childRow["dtbl2ColumnYouWantToDisplay"], Convert.ToString(childRow["dtb2ID"]));
            masterNode.ChildNodes.Add(childNode);
            count++;
        }
    }
    trv.ExpandAll();
}
回复 原文
~没有更多了~

关于作者

想你的星星会说话

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

lorenzathorton8

文章 0 评论 0

Zero

文章 0 评论 0

萧瑟寒风

文章 0 评论 0

mylayout

文章 0 评论 0

tkewei

文章 0 评论 0

17818769742

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文