Hello,

here is a question I was researching for quite a bit and need some
help with (a lot :)

I need to store in MS SQL database a hierarchical categories structure that allows
for unlimited (or limited but to 100 levels) level depth.

Ideally someone can provide me with:

- The example table structure
- Insert SQL statement where the parent ID is specified of indicates a top level
- Select statement to draw the entire tree
- Select statement to draw immediate child
- Delete statement that deletes given tree branch (anything below given ID)

This should work on MS SQL and can use stored procs. Thanks.

Well, the table structure should be pretty simple...

Categories
----------
ID (INT)
ParentID (INT)
Name (VARCHAR(255))


Drawing the whole tree would be quite simple..

You'd start with a query like the following to pull all records
back... you would then need to render them in the proper order in the
GUI.

SELECT * FROM Cateogories ORDER BY ParentID, ID


To select the immediate child, you would use something like..
SELECT * FROM Categories WHERE ParentID = x --- where x is the Parent
ID you are looking for children of.


Insert SQL statement where the parent ID is specified 

INSERT INTO Categories (ParentID, Name) VALUES(x, 'My Name'); ---
again where x is the parent id.

Insert SQL statement where the parent ID is top level.
INSERT INTO Categories (ParentID, Name) VALUES(0, 'My Name'); --- I
use 0 to determine my root level categories.


The delete should be pretty simple, but im not a MS SQL guru, so there
may be a better way of doing this...

------------
CREATE PROCEDURE spDeleteCategory
	(
	@ID		int
	)
AS


	DECLARE @tmpID int	

DECLARE CHILDREN CURSOR LOCAL FOR 
SELECT ID FROM Categories WHERE ParentID = @ID

OPEN CHILDREN

	FETCH NEXT FROM CHILDREN INTO @tmpID

	WHILE @@FETCH_STATUS = 0
		BEGIN
			
			EXEC spDeleteCategory @tmpID
			
			FETCH NEXT FROM CHILDREN INTO @tmpID
		END
	
	DELETE FROM Categories WHERE ID=@ID
GO

------------



Let me know how it goes!

Thanks for you input ! A few more questions:

The query: SELECT * FROM Categories ORDER BY ParentID, ID

will select the categories in correct order but not in hierarchical
structure but rather a flat structure, what I was after was to display
a structure like this:

Main Cate
 Sub Cate
  Sub Sub Cate
 Another Sub Cate
Another Main Cate
 Sub Cate
  Sub Sub Cate
    Sub Sub Sub Cate

It doesn't need to be dynamic etc. but needs to be a hierarchical structure view.

I think this is the main question, the rest would be simply adding
child nodes to the parent and refreshing the view.

Any ideas ? Thanks !

I'm not an MS SQL guru by any means, but it seems to me you will be
needing something recursive, both in the child direction (for routines
designed to display or delete entire branches) and back up to root
(possibly to determine depth of the current node and use that info to
control appropriate indentation in displays).

czech_man_canada-ga,
can you try this solution and tell me if this works. copy the
following code as is into query analyzer and run one statement. just
follow the comments.

-- Create table to hold Heirarchical structure
Create Table Categories( 
	CategoryID int identity(1,1) Primary key, 
	CategoryName varchar(100), 
	ParentID int References Categories(CategoryID) 
)

-- Insert sample data. Structured as 
Insert Into Categories(CategoryName, ParentID) Values ('Root', Null)
Insert Into Categories(CategoryName, ParentID) Values ('Cat1', 1)
Insert Into Categories(CategoryName, ParentID) Values ('Cat2', 1)
Insert Into Categories(CategoryName, ParentID) Values ('Cat3', 1)
Insert Into Categories(CategoryName, ParentID) Values ('Cat4', 1)
Insert Into Categories(CategoryName, ParentID) Values ('Cat1-1', 2)
Insert Into Categories(CategoryName, ParentID) Values ('Cat2-1', 3)
Insert Into Categories(CategoryName, ParentID) Values ('Cat1-2', 2)
Insert Into Categories(CategoryName, ParentID) Values ('Cat3-1', 4)
Insert Into Categories(CategoryName, ParentID) Values ('Cat2-2', 3)
Insert Into Categories(CategoryName, ParentID) Values ('Cat3-2', 4)
Insert Into Categories(CategoryName, ParentID) Values ('Cat1-1-1', 6)
Insert Into Categories(CategoryName, ParentID) Values ('Cat2-2-1', 10)
Insert Into Categories(CategoryName, ParentID) Values ('Cat3-1-1', 9)
Insert Into Categories(CategoryName, ParentID) Values ('Cat3-2-1', 11)

-- select to verify
Select * from Categories

-- Create a recursive function to build tree
Create function BuildCategoryTree( @CategoryID int, @level int )
Returns varchar(8000)
AS
Begin
	Declare @categoryTree varchar(8000),
		@childCount int,
		@tab varchar(200)
	Set @categoryTree = ''
	Select @categoryTree = CategoryName + Char(13) from Categories Where
CategoryID = @CategoryID
	Select @childCount = count(1) from Categories Where ParentID = @CategoryID
	If( @childCount > 0 )
	Begin
		Select @categoryTree = @categoryTree + REPLICATE(Char(9), @level) +
dbo.BuildCategoryTree( CategoryID, @level + 1 )  from Categories Where
ParentID = @CategoryID
	End
	return @categoryTree
End

-- execute the function by passing Root category ID and root level (1
most of the cases)
Select dbo.BuildCategoryTree(1,1)

--Output 
Root
	Cat1
		Cat1-1
			Cat1-1-1
		Cat1-2
	Cat2
		Cat2-1
		Cat2-2
			Cat2-2-1
	Cat3
		Cat3-1
			Cat3-1-1
		Cat3-2
			Cat3-2-1
	Cat4

When you run the function, you need to view the results in text mode
(not in the grid mode) to view line breaks and tabs. Press Ctrl + T
before you run the function.

Above SQL statements are for Select only. Here is the procedure you
can use for deleting any branch given the category ID.

-- Create Procedure to delete the category.
Create Procedure DeleteBranchOf( @CategoryID int )
As
Declare @childCount int,
	@childCategoryID int
Begin
	Select @childCount = count(1) from Categories Where ParentID = @CategoryID
	If( @childCount > 0 )
	Begin
		While( 1 = 1 )
		Begin
			Select top 1 @childCategoryID = CategoryID from Categories Where
ParentID = @CategoryID
			Order by CategoryID
			exec DeleteBranchOf @childCategoryID
			Select @childCount = count(1) from Categories Where ParentID = @CategoryID
			If( @childCount = 0 )
			Begin
				Break;
			End
		End
	End
	Delete from Categories Where CategoryID = @CategoryID
End

-- Test the procedure
Select dbo.BuildCategoryTree(1,1)

--output 
Root
	Cat1
		Cat1-1
			Cat1-1-1
		Cat1-2
	Cat2
		Cat2-1
		Cat2-2
			Cat2-2-1
	Cat3
		Cat3-1
			Cat3-1-1
		Cat3-2
			Cat3-2-1
	Cat4

--Delete Cat1 branch.
exec DeleteBranchOf 2

--check the tree again
Select dbo.BuildCategoryTree(1,1)

--output : check that Cat1 is completely deleted.
Root
	Cat2
		Cat2-1
		Cat2-2
			Cat2-2-1
	Cat3
		Cat3-1
			Cat3-1-1
		Cat3-2
			Cat3-2-1
	Cat4

And if you have more than one Root in Categories table, you need to
run this statement to get the structure needed.

Select dbo.BuildCategoryTree( CategoryID ,1 ) from Categories Where
ParentID is Null

Let me know if this answers you questions.

Hi avnrao-ga, thanks for your solution ! I won't have time to test it
out till after the 24th so I'll let you know then. Thanks again.

Hi  avnrao-ga,

regarding your tree select function. It does almost exactly what I was
after though I'll ask you one more thing.

The result does get formatted in a hierarchical manner, though in
order for it to be useful I'll need to make it formatted in this
manner while adding certain formatting such as <td><tr> tags etc. or
pull the category ID out of it as well.

As it's now it doesn't allow me to do, so it's great I can see it, but
I need to make a TreeView out of it,

Hi,
got back now from holidays. if you want to make a TreeView control out
of the database, i suggest you to write code in middle layer, instead
of sql server. Though the function allows you to generate HTML needed,
its not good implementation.

If you still want to do it in SQL, tell me exactly what kind of Output
you need out of sql server. i will try to get that.

Hi  avnrao-ga,

hope you had a good time, happy holidays BTW ! :)

In a nutshell what I need to develop is a troubleshooter type of
application that requires me to create the tree on the admin part and
a "browser" on the public side that would allow me to say: "Show me
the immediate child nodes for this node."

Your code would work wonderfully for the public side, though now for
the admin I need to create the tree (fully opened ideally) where I can
add some HTML such as icons to each node. I'd like to keep it in SQL
where I would run some Store Proc and it'd return the tree, or where I
can bind it to some TreeView control, though if the StoreProc was to
return the hard coded HTML I'd not mind either :)
As you can see I'm open to anything that would work, doesn't have to be pretty.

One option would be to generate Xml string out of function i created
and bind it to TreeView control. That way, I think, TreeView will
display all nodes. You have to specify what icons to show using
TreeView properties. It is easy to convert the function i wrote to
return XML string. Check if you can get the job done with Xml, then i
can convert the function to return Xml and Post it here.
what say?

I'm having all kinds of trouble converting that function to XML. 
When I output it as XML and bind it to the TreeView control I get:

"HierarchicalDataBoundControl only accepts data sources that implement
IHierarchicalDataSource or IHierarchicalEnumerable."

Which I guess makes sense since it returns a flat data representation.
Any ideas ?

I have modified the function to return XML structure. Load it in
XmlDocument (or XmlDataSource control) and try to databind it to tree
view control. hope it works now.

Alter function BuildCategoryTree( @CategoryID int, @level int )
Returns varchar(8000)
AS
Begin
	Declare @categoryTree varchar(8000),
		@childCount int,
		@tab varchar(200),
		@categoryName varchar(100)
	Set @categoryTree = ''
	Set @categoryName = ''
	Select @categoryName = CategoryName from Categories Where CategoryID = @CategoryID
	Select @childCount = count(1) from Categories Where ParentID = @CategoryID
	If( @childCount > 0 )
	Begin
		Select @categoryTree = '<' + @categoryName + '>'
		Select @categoryTree = @categoryTree + dbo.BuildCategoryTree(
CategoryID, @level + 1 )  from Categories Where ParentID = @CategoryID
		Select @categoryTree = @categoryTree + '</' + @categoryName + '>'
	End
	Else
	Begin
		Select @categoryTree = '<' + @categoryName + '/>'
	End
	return @categoryTree
End

Thanks for all the trouble. It still doesn't seem to recognize it as a
valid source. The code used:


        Dim XML_STRUCTURE As String
        Try
            Call OPEN_DB()
            Dim SQL As String = "Select dbo.BuildCategoryTree(1,1)"
            Dim MyCMD As New Data.OleDb.OleDbCommand(SQL, MyConn)
            XML_STRUCTURE = CType(MyCMD.ExecuteScalar(), String)
            MyCMD.Dispose()
        Catch ex As Exception
            Response.Write(ex.Message)
        Finally
            MyConn.Dispose()
        End Try

        Try
            XmlDataSource1.Data = XML_STRUCTURE
        Catch ex As Exception
            Response.Write(ex.Message)
        End Try

        TreeView1.DataSource = XmlDataSource1