ok.... so i couldn't find anything on converting an adjacency model to a nested set using vb.net... so i made something up... ok... i'm not the smartest of coders, and i learn as i go along. i want to make this as clean and simple as possible... it's big, it's clunky, introducing my 'vb adjacency nested convertor'...!!

any better developers out (not hard!)? some hints and tips on becoming a better coder and stream lining my code?... (i've tried to comment whats going on!)...




'MY category table currently looks like this
'BUILT using the adjacency model

'--------------------------------------
'ID ' PARENT ' NAME ' LFT ' RGT '
'--------------------------------------
'1 ' 0 ' shop ' ' '
'2 ' 1 ' mugs ' ' '
'3 ' 0 ' tea ' ' '
'4 ' 2 ' coffee ' ' '



'I need to build the following using the adjacency model to convert into the nested sets model.
'where L is LEFT and R is RIGHT, and the number is the category ID
'L1,L2,L4,R4,R2,R1,L3,R3
' 1, 2, 3, 4, 5, 6, 7, 8
'using the above i can convert it to an array and work through, using the L or R to insert a counter to the relevant ID





'create a string builder for nested set
Dim nestedSet As New StringBuilder()
'create a temporary string builder for nested set children before being added to nestedSet
Dim childTemp As New StringBuilder()
'create a temporary string for location of insert
Dim CatInsert As String
'create queue for adjacency model parent and id
Dim catparentQueue As New Queue()
Dim catidQueue As New Queue()

'loop through category DB and fill queues
If cat_reader.HasRows Then
Do While cat_reader.Read()

catparentQueue.Enqueue(cat_reader("cat_parent"))
catidQueue.Enqueue(cat_reader("cat_id"))

Loop
End If



cat_reader.Close()


'create temporary variables for Dequeued items, so we can insert back into queue if no parent is found
dim popparentQueue
dim popidQueue


Do While catparentQueue.Count > 0
'Dequeue parent and id
popparentQueue = catparentQueue.Dequeue()
popidQueue = catidQueue.Dequeue()


'TOP LEVEL - if parent ID is root, throw into string
if popparentQueue = 0 then


'LcatRcat'
nestedSet.Append("L")
nestedSet.Append(popidQueue)
nestedSet.Append("R")
nestedSet.Append(popidQueue)

'CHILD - if parent ID not root
else


'IF the categories parent exists, add
'We add the category by inserting into current string
'if we had L2,R2 already, we insert L4,R4 as child and now have L2,L4,R4,R2
if nestedSet.Tostring().IndexOf(popparentQueue) >= 0 then


childTemp.Append("L")
childTemp.Append(popidQueue)
childTemp.Append("R")
childTemp.Append(popidQueue)


catInsert = ("L" & popparentQueue)
nestedSet.replace(catInsert,catInsert & childTemp.ToString())
'clear the childBuild for next item
childTemp.remove(0,childTemp.Length)

else
'ELSE move to back of queue

catparentQueue.Enqueue(popparentQueue)
catidQueue.Enqueue(popidQueue)

end if



end if

Loop



'convert to comma seperated string
nestedSet.replace("R", ",R")
nestedSet.replace("L" , ",L")
'remove first comma
nestedSet.remove(0 , 1)


'WE NOW HAVE A STRING THAT LOOKS LIKE THIS
'WE CAN NOW CONVERT TO ARRAY AND WORK THROUGH ADDING TO DB
'L1,L2,L5,R5,R2,R1,L4,R4


'convert to array
Dim nestedArray(cat_records * 2)
nestedArray = Split(nestedSet.ToString(), ",", -1, 1)



'filter through array and update category columns
Dim nestedItem
Dim nestedCount As Integer = 1
Dim nestedCat As String
Dim nestedNode As String
For Each nestedItem in nestedArray



'remove the letter
nestedCat = nestedItem.remove(0,1)



dim sql_update
sql_update = "UPDATE category SET "
'IF L insert into LFT column
if nestedItem.IndexOf("L") >= 0 then
sql_update + = "lft=" & nestedCount & ""
'IF R insert into RGT column
elseif nestedItem.indexof("R") >= 0 then
sql_update + = "rgt=" & nestedCount & ""
end if
sql_update + = " WHERE id=" & nestedCat



Try
dbcomm=New SqlCommand(sql_update,oSQLConn)
dbread=dbcomm.ExecuteReader()
dbread.close()
catch Exp as exception
panel_error.visible = true
'error_message.Text = "An error occurred while converting adjacency model to nested sets ."
error_message.Text = error_message.Text & sql_update & "<br>"
End Try


nestedCount = nestedCount + 1
Next