asp/asp.net中遍历树型结构.

Posted on 2006-03-16 12:19 laifangsong 阅读(4628) 评论(22) 编辑收藏所属分类: 开发常用

数据库：SqlServer2000
表：tree
表结构：

测试数据：

算法：使用递归实现

======================================
asp代码：

<%@ Language = VBScript%>

<%Option Explicit%>

Dim oConn, sSql

Call DBConnBegin()

Dim sTableName

Dim nCurrent, nLen, aTree()

sTableName = "tree"

nCurrent = 1

nLen = GetRescordCount()

ReDim aTree(nLen)

Call GetPowerName(0)

'Dim i

'For i = 1 To nLen

'Response.Write aTree(i) & " "

'Next

Call DBConnEnd()

Function GetRescordCount()

GetRescordCount = oConn.Execute("select count(id) from " & sTableName)(0)

End Function

Sub GetPowerName(s_Id)

Dim n_ParentId, j, oRs

Set oRs = Server.CreateObject( "ADODB.Recordset" )

sSql = "select id,PowerName,Layer from " & sTableName & " where ParentId=" & s_Id

oRs.Open sSql, oConn, 0, 1

Do While Not oRs.Eof

For j = 1 To oRs("Layer")

response.write "    "

aTree(nCurrent) = oRs("PowerName")

response.write aTree(nCurrent) & " "

nCurrent = nCurrent + 1

n_ParentId = oRs("id")

Call GetPowerName(n_ParentId)

oRs.MoveNext

Loop

oRs.Close

Set oRs = Nothing

End Sub

Sub DBConnBegin()

' 如果数据库对象已打开，不要再打开

If IsObject(oConn) = True Then Exit Sub

' 你可以不需要打开数据库连接对象而直接打开记录集对象，但如果你需要打开多个记录集对象的话，效率是很低的。

' 如果你不创建一个数据库连接对象，ADO会在每个记录集打开时自动创建一个新的数据库连接对象，就算你用的是相同的SQL语句。

Set oConn = Server.CreateObject("ADODB.Connection")

On Error Resume Next

'Provider=SQLOLEDB.1;Server=(local);Initial Catalog =cx_soft;Integrated Security=SSPI;

'Provider=SQLOLEDB.1;Server=(local);Initial Catalog =cx_soft;Trusted_Connection=yes;

oConn.Open "Provider=sqloledb.1;Data Source=(local);Initial Catalog=AspNetTest;User Id=sa;Password=;"

If Err.Number > 0 Then

' 完全地退出正在运行的脚本

Response.End

End If

' 创建一个记录集

End Sub

Sub DBConnEnd()

On Error Resume Next

oRs.Close

Set oRs = Nothing

oConn.Close

Set oConn = Nothing

End Sub

asp.net代码：
(请注意：asp.net1.1中用同一个Connection，在前一个DataReader没有Close情况下再创建DataReader会出现错误提示：已有打开的与此连接相关联的 DataReader，必须首先将它关闭
我的做法是每次都创建一个Connection，很浪费资源，不知道有没其他好办法.
)

using System;
2

using System.Collections;
3

using System.ComponentModel;
4

using System.Data;
5

using System.Drawing;
6

using System.Web;
7

using System.Web.SessionState;
8

using System.Web.UI;
9

using System.Web.UI.WebControls;
10

using System.Web.UI.HtmlControls;
11

using System.Data.SqlClient;
12

namespace AspNetTest.Common
14

{
15

/// <summary>
16

/// tree 的摘要说明。
17

/// </summary>
18

public class tree : System.Web.UI.Page
20

{
21

private string tablename = "tree";
22

int current = 0;
23

int len = 0;
24

string[] arrPowerName;
25

string ConnectionString;
26

public tree()
27

{
28

ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
29

len = GetRecordCount();
30

arrPowerName = new string[len];
31

}
32

private void Page_Load(object sender, System.EventArgs e)
33

{
34

GetPowerName(0);
36

//CloseDbConnection();
37

//ResponseArray();
38

// 在此处放置用户代码以初始化页面
39

}
40

private void GetPowerName(int _ParentId)
41

{
42

int Id;
43

string PowerName;
44

string sql = "select Id,PowerName,Layer from " + tablename + " where ParentId=" + _ParentId;
45

try
46

{
47

SqlConnection conn = new SqlConnection(ConnectionString);
48

conn.Open();
49

SqlCommand cmd = new SqlCommand(sql, conn);
50

SqlDataReader dr = cmd.ExecuteReader();
51

while(dr.Read())
52

{
53

for(int j=0; j<int.Parse(dr["Layer"].ToString()); j++)
54

{
55

Response.Write("    ");
56

}
57

PowerName = dr["PowerName"].ToString();
58

Response.Write(PowerName + " ");
59

arrPowerName[current++] = PowerName;
60

Id = int.Parse(dr["Id"].ToString());
61

GetPowerName(Id);
62

}
63

dr.Close();
64

conn.Close();
65

}
66

catch(Exception ex)
67

{
68

Response.Write(ex.ToString());
69

}
70

}
71

private void ResponseArray()
72

{
73

int i;
74

for(i=0; i<len; i++)
75

{
76

Response.Write(arrPowerName[i] + " ");
77

}
78

}
79

private void CloseDbConnection()
80

{
81

//conn.Close();
82

}
83

private int GetRecordCount()
84

{
85

SqlConnection conn = new SqlConnection(ConnectionString);
86

string sql = "select count(id) from " + tablename;
87

conn.Open();
88

SqlCommand cmd = new SqlCommand(sql, conn);
89

int count = int.Parse(cmd.ExecuteScalar().ToString());
90

conn.Close();
91

return count;
92

}
93

Web 窗体设计器生成的代码
114

}
115

}
116

遍历结果：

代码下载：

asp代码：/Files/jiny-z/tree(asp).rar
asp.net代码:/Files/jiny-z/tree(asp.net).rar

*********************************************************
根据chill ,flower.b的提示，修改了代码。
原来：asp/asp.net代码中都是在数据集中循环，效率较低.。
现在：asp代码改为在数组中循环，asp.net代码改为在DataTable中循环。
*********************************************************

asp代码：

<%@ Language = VBScript%>

<%Option Explicit%>

Dim oConn, oRs, sSql

Dim aTree

Call FillArrayFromRs()

Call blTreeArray(0)

Sub FillArrayFromRs()

Dim s_TableName

s_TableName = "tree"

Call DBConnBegin()

Set oRs = Server.CreateObject( "ADODB.Recordset" )

sSql = "select Id,PowerName,Layer,ParentId from " & s_TableName

oRs.Open sSql, oConn, 0, 1

aTree = oRs.GetRows()

'response.write ubound(aTree, 2)

oRs.Close

Call DBConnEnd()

End Sub

Sub blTreeArray(n_ParentId)

Dim row, rows

Dim n_NextParentId

Dim n_Space

rows = UBound(aTree, 2)

for row = 0 To rows

If aTree(3, row) = n_ParentId Then

for n_Space = 1 To aTree(2, row)

Response.Write "    "

Response.Write aTree(1, row) & " "

n_NextParentId = aTree(0, row)

blTreeArray(n_NextParentId)

End If

End Sub

Sub DBConnBegin()

' 如果数据库对象已打开，不要再打开

If IsObject(oConn) = True Then Exit Sub

' 你可以不需要打开数据库连接对象而直接打开记录集对象，但如果你需要打开多个记录集对象的话，效率是很低的。

' 如果你不创建一个数据库连接对象，ADO会在每个记录集打开时自动创建一个新的数据库连接对象，就算你用的是相同的SQL语句。

Set oConn = Server.CreateObject("ADODB.Connection")

On Error Resume Next

'Provider=SQLOLEDB.1;Server=(local);Initial Catalog =cx_soft;Integrated Security=SSPI;

'Provider=SQLOLEDB.1;Server=(local);Initial Catalog =cx_soft;Trusted_Connection=yes;

oConn.Open "Provider=sqloledb.1;Data Source=(local);Initial Catalog=AspNetTest;User Id=sa;Password=;"

If Err.Number > 0 Then

' 完全地退出正在运行的脚本

Response.End

End If

' 创建一个记录集

End Sub

Sub DBConnEnd()

On Error Resume Next

oRs.Close

Set oRs = Nothing

oConn.Close

Set oConn = Nothing

End Sub

asp.net代码：

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace AspNetTest.Common

{

/// <summary>

/// tree_DataTable 的摘要说明。

/// </summary>

public class tree_DataTable : System.Web.UI.Page

{

private DataTable dtPowerTree = new DataTable();

const string tablename = "tree";

private void Page_Load(object sender, System.EventArgs e)

{

FillTreeDataTable();

blTreeDataTable(0);

dtPowerTree.Clear();

// 在此处放置用户代码以初始化页面

}

private void FillTreeDataTable()

{

string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];

SqlConnection conn = new SqlConnection(ConnectionString);

SqlDataAdapter da = new SqlDataAdapter("select Id,PowerName,Layer,ParentId from " + tablename, conn);

da.Fill(dtPowerTree);

}

private void blTreeDataTable(int _ParentId)

{

string filter = "ParentId=" + _ParentId;

string sort = "Id ASC";

DataRow[] drs = dtPowerTree.Select(filter, sort);

for(int i=0; i<drs.Length; i++)

{

if(Convert.ToInt32(drs[i][3]) == _ParentId)

{

int Id = Convert.ToInt32(drs[i][0]);

string PowerName = drs[i][1].ToString();

int Layer = Convert.ToInt32(drs[i][2]);

for(int space=1; space<=Layer; space++)

{

Page.Response.Write("    ");

}

Page.Response.Write(PowerName + " ");

blTreeDataTable(Id);

}