By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

"Tree View" with a SQL Server Database

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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_Padded_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.ID, (a unique ID)
dbo.Adjacency.ID_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.ID_Index, (the sibling number of this child)
LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.Path

Hope this helps some.

<im*******************@yahoo.com> wrote in message
news:8b************************@posting.google.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

P: n/a
This helps a lot. Thanks. I wonder if there is a practical graphical
solution in addition to the text-based solution?

"Robin Tucker" <id*************************@reallyidont.com> wrote in message news:<bq*******************@news.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_Padded_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.ID, (a unique ID)
dbo.Adjacency.ID_Parent, (the unique ID of the parent of
this node)
dbo.Adjacency.ID_Index, (the sibling number of this child)
LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the
node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.Path

Hope this helps some.

<im*******************@yahoo.com> wrote in message
news:8b************************@posting.google.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

P: n/a
Hi

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

John

<im*******************@yahoo.com> wrote in message
news:8b************************@posting.google.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*************************@reallyidont.com> wrote in

message news:<bq*******************@news.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_Padded_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.ID, (a unique ID)
dbo.Adjacency.ID_Parent, (the unique ID of the parent of this node)
dbo.Adjacency.ID_Index, (the sibling number of this child) LEN(dbo.Adjacency.Path) / 9 AS Depth, (the depth of the node, useful for building your tree structure afterwards)
FROM dbo.Adjacency
ORDER BY dbo.Adjacency.Path

Hope this helps some.

<im*******************@yahoo.com> wrote in message
news:8b************************@posting.google.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 discussion thread is closed

Replies have been disabled for this discussion.