473,561 Members | 3,730 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

"Tree View" with a SQL Server Database

We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?
Jul 20 '05 #1
3 10805
Simplist is an adjacency list. Store the parent ID along with each record,
also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the
tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you
want to order your nodes in an arbitrary way.

ALTER FUNCTION dbo.func_Get_Pa dded_Number8
(
@number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @Value VARCHAR(8)
DECLARE @Length INTEGER

/*
Convert the number and get its string length
*/

SET @Value = CONVERT ( VARCHAR ( 8 ), @number )
SET @Length = LEN ( @Value )

/*
If the length is less than 8, pad it
*/
IF LEN ( @Value ) < 8
BEGIN
SET @Value = REPLACE ( SPACE ( 8 - @Length ), ' ', '0' ) + @Value
END

RETURN @Value
END

Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building
the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done
easily like this:

SELECT dbo.Adjacency.I D, (a unique ID)
dbo.Adjacency.I D_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.I D_Index, (the sibling number of this child)
LEN(dbo.Adjacen cy.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.P ath

Hope this helps some.

<im************ *******@yahoo.c om> wrote in message
news:8b******** *************** *@posting.googl e.com...
We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?

Jul 20 '05 #2
This helps a lot. Thanks. I wonder if there is a practical graphical
solution in addition to the text-based solution?

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in message news:<bq******* ************@ne ws.demon.co.uk> ...
Simplist is an adjacency list. Store the parent ID along with each record,
also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the
tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you
want to order your nodes in an arbitrary way.

ALTER FUNCTION dbo.func_Get_Pa dded_Number8
(
@number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @Value VARCHAR(8)
DECLARE @Length INTEGER

/*
Convert the number and get its string length
*/

SET @Value = CONVERT ( VARCHAR ( 8 ), @number )
SET @Length = LEN ( @Value )

/*
If the length is less than 8, pad it
*/
IF LEN ( @Value ) < 8
BEGIN
SET @Value = REPLACE ( SPACE ( 8 - @Length ), ' ', '0' ) + @Value
END

RETURN @Value
END

Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building
the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done
easily like this:

SELECT dbo.Adjacency.I D, (a unique ID)
dbo.Adjacency.I D_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.I D_Index, (the sibling number of this child)
LEN(dbo.Adjacen cy.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.P ath

Hope this helps some.

<im************ *******@yahoo.c om> wrote in message
news:8b******** *************** *@posting.googl e.com...
We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?

Jul 20 '05 #3
Hi

I posted this a short time ago!
http://tinyurl.com/xw5s

John

<im************ *******@yahoo.c om> wrote in message
news:8b******** *************** *@posting.googl e.com...
This helps a lot. Thanks. I wonder if there is a practical graphical
solution in addition to the text-based solution?

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in

message news:<bq******* ************@ne ws.demon.co.uk> ...
Simplist is an adjacency list. Store the parent ID along with each record, also store the "full path" in a text string, such as 0001/0004/0007/0002
(the second node of the seventh node of the 4th node of the 1st node of the tree). I use a function to generate a number for the string (below)
because each "path node" has to be the same length in order to perform a
correct "select" of the tree order. You also need a "sibling number" if you want to order your nodes in an arbitrary way.

ALTER FUNCTION dbo.func_Get_Pa dded_Number8
(
@number INTEGER
)
RETURNS VARCHAR(8)
AS
BEGIN
DECLARE @Value VARCHAR(8)
DECLARE @Length INTEGER

/*
Convert the number and get its string length
*/

SET @Value = CONVERT ( VARCHAR ( 8 ), @number )
SET @Length = LEN ( @Value )

/*
If the length is less than 8, pad it
*/
IF LEN ( @Value ) < 8
BEGIN
SET @Value = REPLACE ( SPACE ( 8 - @Length ), ' ', '0' ) + @Value END

RETURN @Value
END

Adding a new node is simple given the parent, you just find the highest
sibling number of the parent and increment it then insert a node, building the path string by taking the parent and adding "/00n" (where n is the
sibling number). Listing all of the nodes in the tree structure can be done easily like this:

SELECT dbo.Adjacency.I D, (a unique ID)
dbo.Adjacency.I D_Parent, (the unique ID of the parent of this node)
dbo.Adjacency.I D_Index, (the sibling number of this child) LEN(dbo.Adjacen cy.Path) / 9 AS Depth, (the depth of the node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.P ath

Hope this helps some.

<im************ *******@yahoo.c om> wrote in message
news:8b******** *************** *@posting.googl e.com...
We need to present hierarchical data on a web page, the same way the
tree view shows files in Windows Explorer. Here's the catch: that
tree view needs to be bound to a SQL Server database. How can this be
done?

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2153
by: Jonathan Wilson | last post by:
I am looking for a C++ class library that can store data in a tree. The class library needs to be: 1.Available under a licence like GPL, LGPL or BSD so I can use it in my GPL program and 2.Usable on multiple compilers (specificly, Visual C++ on windows and GCC 3.x MingW on windows. If it works on GCC on Linux, thats even better but not...
4
1974
by: Robin Tucker | last post by:
Hi, I'm currently implementing a database with a tree structure in a table. The nodes in the tree are stored as records with a column called "Parent". The root of the tree has a "NULL" parent. The path to each node is stored in the column "Path" and is of the form "\000001\000002\000003\" etc. The latter enabling me to fetch subtrees...
18
2531
by: Lorem Ipsum | last post by:
interesting! I just found a page in which Explorer's View Source does nothing! How did they do that?
2
3184
by: Vinod I | last post by:
Hi Team, When I tryed following code, I am getting the Runtime Error as "The View State is invalid for this page and might be corrupted." Exception Details: System.Web.HttpException: The View State is invalid for this page and might be corrupted. Stack Trace: System.Web.UI.Page.LoadPageStateFromPersistenceMedium() +150...
0
3731
by: Tom Bower | last post by:
In the Windows Task Manager if I select a Process and right-click, I can choose to "End Process" or "End Process Tree." Is there a VB equivalent for "End Process Tree" if you have a handle to a process? I know about process.Kill and process.CloseMainWindow, but how would I do the equivalent of "End Process Tree" where any child or...
1
1420
by: Amil Hanish | last post by:
I use the VS 2005 option "View in browser" to often view my web site. If I have a link in there like: <img src="/graphics/myimage.jpg"> This doesn't work even though the image is really there. I'm sure this has to do with the fact that the page is being served out of the virtual server/port that VS fires up to serve the page. This...
0
1300
by: Amil Hanish | last post by:
Earlier I had inquired as to why a normal <a hreftag didn't work when using the VS hosted site using a random local port. The more I think about it, this seems very wrong. Using the random port hosted site, if I have <a href="/mypage.aspx">, this link doesn't seem to work? I've verified this by creating a simple .htm page with a link like...
10
1743
by: Wildemar Wildenburger | last post by:
Hi there :) I don't know how else to call what I'm currently implementing: An object that behaves like a list but doesn't store it's own items but rather pulls them from a larger list (if they match a certain criterion). Changes to the filter are instantly reflected in the underlying list. Clear enough? Ok, so I figured that this is...
6
4362
by: jluo | last post by:
I found a problem with my application when using "innerhtml" to replace the content from the server after making an ajax call. The display is perfectly correct, but when I use "view source" on the browser, I found the "source" is still the old content the same as before I replaced them with the "innerhtml". This causes some problem with my...
0
7647
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7618
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6210
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5472
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5187
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3617
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3600
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2068
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
896
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.