"Tree View" with a SQL Server Database

[imani_technology_spam@yahoo.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?

[Robin Tucker]

Re: "Tree View" with a SQL Server Database

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.

<imani_technolo gy_spam@yahoo.c om> wrote in message
news:8be6e8.031 2030710.3b40bc8 9@posting.googl e.com...[color=blue]
> 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?[/color]

[imani_technology_spam@yahoo.com]

Re: &quot;Tree View&quot; with a SQL Server Database

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

"Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in message news:<bqn6dt$o7 l$1$830fa7b3@ne ws.demon.co.uk> ...[color=blue]
> 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.
>
> <imani_technolo gy_spam@yahoo.c om> wrote in message
> news:8be6e8.031 2030710.3b40bc8 9@posting.googl e.com...[color=green]
> > 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?[/color][/color]

[John Bell]

Re: &quot;Tree View&quot; with a SQL Server Database

Hi

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

John

<imani_technolo gy_spam@yahoo.c om> wrote in message
news:8be6e8.031 2050907.644b5ab e@posting.googl e.com...[color=blue]
> This helps a lot. Thanks. I wonder if there is a practical graphical
> solution in addition to the text-based solution?
>
> "Robin Tucker" <idontwanttobes pammedanymore@r eallyidont.com> wrote in[/color]
message news:<bqn6dt$o7 l$1$830fa7b3@ne ws.demon.co.uk> ...[color=blue][color=green]
> > Simplist is an adjacency list. Store the parent ID along with each[/color][/color]
record,[color=blue][color=green]
> > 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[/color][/color]
the[color=blue][color=green]
> > 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[/color][/color]
you[color=blue][color=green]
> > 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' ) +[/color][/color]
@Value[color=blue][color=green]
> > 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,[/color][/color]
building[color=blue][color=green]
> > 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[/color][/color]
done[color=blue][color=green]
> > easily like this:
> >
> > SELECT dbo.Adjacency.I D, (a unique ID)
> > dbo.Adjacency.I D_Parent, (the unique ID of the parent[/color][/color]
of[color=blue][color=green]
> > this node)
> > dbo.Adjacency.I D_Index, (the sibling number of this[/color][/color]
child)[color=blue][color=green]
> > LEN(dbo.Adjacen cy.Path) / 9 AS Depth, (the depth of[/color][/color]
the[color=blue][color=green]
> > node, useful for building your tree structure afterwards)
> > FROM dbo.Adjacency
> > ORDER BY dbo.Adjacency.P ath
> >
> > Hope this helps some.
> >
> > <imani_technolo gy_spam@yahoo.c om> wrote in message
> > news:8be6e8.031 2030710.3b40bc8 9@posting.googl e.com...[color=darkred]
> > > 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?[/color][/color][/color]