Code previously worked, but installing on new PC where it does not. Probably missing a setting? Using Visual Studio 2013 Professional.
SQL Server 2012 has db table with hierarchyID field, but it has NULL values. This table is queried into a dataview. The item for this field is not recognized as a hierarchyID datatype. The code continues to work with geography datatypes.
I abandoned the use of HierarchyID as a datatype because: a) a database query of a hierarchyID column did not create a similar datatype in the dataview and b) there is difficulty handling NULL values for hierarchyID.
Instead, I implemented ORDPATH as described by P O'Neil, E O'Neil, S Pal et al. 2004: http://www.cs.umb.edu/~poneil/ordpath.pdf. It uses a DB table LiOi with values from the table in the article which is rendered into dvORDPATH. I had written this code years before. With this code you create an ORDPATH datatype using the Dewey notation (e.g. /1/1/15/7/). This datatype has three properties, 1) the ORDPATH per se which is a concatenated bitstring (in this case 0100101001100011101111); 2) it's Dewey; and 3) the level in the hierarchy.
This methodology is similar to hierarchyID. The principle difference is that hierarchyID uses a hex and my code a base-2 concatenated bitstring. Both sort by the tree hierarchy.
Public Class ORDPATH
'user defined datatype
'based on P O'Neil, E O'Neil, S Pal et al. 2004
'http://www.cs.umb.edu/~poneil/ordpath.pdf
'
'Code by David A Stumpf, MD, PhD; Evanston, IL
'Dec 2009
Public Shared DeweyRef As String
Public Dewey As String 'human readable hierarchy
Public HID As String 'hierarchyID
Public Level As Integer 'level in hierarchy
Public Shared dvORDPATH As DataView
Public Shared Multiplier As Long = 1
Public Shared Sub InitializeORDPATH()
dvORDPATH = DataLib.QryToData("Select * from dbo.LiOi", EnumLib.DBList.Gen, EnumLib.DataObj.DataView)
dvORDPATH.Sort = "Oi_MIN"
End Sub
Public Shared Function CreateUsingDewey(ByVal DeweyStr As String, Optional ByVal Delimiter As String = "/", Optional ByVal BeginEndDelimiter As Boolean = True) As ORDPATH
'creates new ORDPATH object using submitted Dewey
DeweyRef = IIf(Delimiter <> "/", Replace(DeweyStr, Delimiter, "/"), DeweyStr)
Dim O As ORDPATH = CreateORDPATH(Delimiter, BeginEndDelimiter)
CreateUsingDewey = O
End Function
Public Shared Property CreateORDPATH(Optional ByVal Delimiter As String = "/", Optional ByVal BeginEndDelimiter As Boolean = True) As ORDPATH
'instantiates ORDPATH object and its properties: HID, Dewey, and Level
Get
'SetUpDV()
Dim O As ORDPATH = New ORDPATH
O.Dewey = IIf(BeginEndDelimiter, "", "/") & IIf(Delimiter <> "/", Replace(DeweyRef, Delimiter, "/"), DeweyRef) & IIf(BeginEndDelimiter, "", "/")
Dim DeweySplit() = O.Dewey.Split("/")
Dim OP As String = ""
For i As Integer = 1 To DeweySplit.Length - 2
OP = OP & GetLiOifromDV(Val(DeweySplit(i))) '& GenLib.OrdpathDelimiter
Next
O.HID = OP
O.Level = DeweySplit.Length - 2
Return O
End Get
Set(ByVal value As ORDPATH)
End Set
End Property
Public Shared Function GetLiOifromDV(ByVal DeweyItem As Double) As String
Dim DI As Double = DeweyItem * Multiplier
Dim Rtn As String = ""
For i As Integer = 0 To dvORDPATH.Count - 1
With dvORDPATH(i)
If DI >= .Item("oi_min") And DI <= .Item("oi_max") Then
Dim X As String = Trim(NumberToBase2(DI - .Item("oi_min")))
'Dim X As String = Trim(NumberToHex(DI - .Item("oi_min")))
Rtn = Trim(.Item("BITSTRING_ROOT")) & GenLib.LPad(X, .Item("li"), "0")
End If
End With
Next
GetLiOifromDV = Rtn
End Function
Public Shared Function NumberToHex(ByVal N As Object) As String
'not used
NumberToHex = Hex(N)
End Function
Public Shared Function StrToByte(ByVal S As String) As Byte()
'ms-help://MS.VSCC.v90/MS.MSDNQTR.v90.en/fxref_mscorlib/html/c590603b-8901-253c-78bf-171950a57438.htm
StrToByte = System.Text.Encoding.ASCII.GetBytes(S)
End Function
Public Shared Function NumberToBase2(ByVal N As Double) As String
'http://www.helpwithpcs.com/courses/binary-numbers.htm
'simple logic: iterative division by two, retaining remainder as digit in converted number
'this might be useful in future:
' http://dpatrickcaldwell.blogspot.com/2009/05/converting-hexadecimal-or-binary-to.html
'fractions not yet done, but see: http://www.mathpath.org/concepts/Num/frac.htm
Dim B2 As String = ""
Dim V As Long = Int(N)
Dim VN As Long = V
Dim X As Long 'remainder
Do While VN > 0
VN = Math.DivRem(VN, 2, X) 'integer of result of division
B2 = Trim(Str(X)) & B2
Loop
NumberToBase2 = B2
End Function
Public Overloads Shared Function ToString(ByVal HID As String) As String
'Li and Oi are components
'no Li is repeated in any other HID component.
'Thus you can run thru the Li options until found
' This tells you the length of the Oi component
'SetUpDV()
Dim OP As String = Trim(Replace(HID.ToString, Stegenography.OrdpathDelimiter, ""))
Dim D As Integer = 1
Dim S As String = "/"
For i As Integer = 0 To 100
For j As Integer = 0 To dvORDPATH.Count - 1
Dim L As Integer = Len(Trim(dvORDPATH(j).Item("bitstring_root")))
If Mid(OP, D, L) = Trim(dvORDPATH(j).Item("bitstring_root")) Then
S = S & Trim(Str((IM.Base2ToNumber(Mid(OP, D + L, dvORDPATH(j).Item("li"))) + dvORDPATH(j).Item("Oi_Min")) / Multiplier)) & "/"
D = D + L + dvORDPATH(j).Item("li")
Exit For
End If
Next
If D >= Len(OP) Then Exit For
Next
ToString = S
End Function
Public Shared Function GetParent(ByVal HID_Dewey As String) As String
Dim P As String = HID_Dewey.ToString
If InStr(P, "/") > 0 Then
GetParent = Mid(Trim(P), 1, InStrRev(Trim(P), "/", Len(Trim(P)) - 1))
If GetParent = "/" Then GetParent = Nothing
Else
GetParent = Nothing
End If
End Function
Public Shared Function GetAncestors(ByVal HID_Dewey As String, ByVal GenerationsBack As Integer) As String
Dim A As String = HID_Dewey.ToString
For i As Integer = 1 To GenerationsBack
If A Is Nothing Then Exit For
A = GetParent(A)
Next
GetAncestors = A
End Function
Public Shared Function GetRoot(ByVal HID_Dewey As String) As String
GetRoot = Mid(HID_Dewey, 1, InStr(Mid(HID_Dewey, 2), "/") + 1)
End Function
Public Shared Sub ORDPATHTest()
Dim HIDStr As String = "/1/5/3/1/11/;/100/10000/;/1/5/2/1/5/6/7/;/100/10001/;/1/5/3/-9/11/;/1/5/3/2/3/;/1/5/4/;/1/5/3/2/4/;/1/5/3/2/3/6/;/1/5/3/;/1/5/3/2/;/1/5/;/1/;/2/;/2/1/;/2/0.5/;/2/0.4/;/2/0.43/"
Dim T1 As Long = My.Computer.Clock.TickCount
Dim i As Integer
Dim ds As DataSet = New DataSet
Dim T As DataTable = ds.Tables.Add
Dim C1 As DataColumn = T.Columns.Add("HID")
Dim C2 As DataColumn = T.Columns.Add("Dewey")
Dim C0 As DataColumn = T.Columns.Add("i")
Dim C3 As DataColumn = T.Columns.Add("Lvl")
Dim dv As DataView = ds.DefaultViewManager.CreateDataView(ds.Tables(0))
Dim H() As String = HIDStr.Split(";")
DataLib.QryToData("delete from dbo.ORDPATH_Tests", EnumLib.DBList.Gen, EnumLib.DataObj.DataSet)
For i = 0 To H.Length - 1
Dim O1 As ORDPATH = ORDPATH.CreateUsingDewey(H(i))
'Dim HSql As Microsoft.SqlServer.Types.SqlHierarchyId
Dim rw1 As DataRow = dv.Table.Rows.Add
rw1.Item("i") = i
rw1.Item("HID") = O1.HID
rw1.Item("Dewey") = O1.Dewey
rw1.Item("Lvl") = O1.Level
' Dim B As Byte() = StrToByte(O1.HID)
Dim CB As Byte() = Compression.Compress(StrToByte(O1.HID))
'Dim SR As System.IO.Stream
'SR.Read(B, 0, B.Length)
' Dim gz As System.IO.Compression.GZipStream = New System.IO.Compression.GZipStream(SR, IO.Compression.CompressionMode.Compress)
'Dim B As Byte() = New Byte[(O1.HID) / 2]
DataLib.QryToData("insert into ordpath_tests (i,dewey,ordpath,ORDPATH_BIT,hid ) values(" & i & ",'" & O1.Dewey & "','" & O1.HID & "'," & System.Text.Encoding.ASCII.GetString(StrToByte(O1.HID)) & ",'" & O1.Dewey & "' )", EnumLib.DBList.Gen, EnumLib.DataObj.DataSet)
'DataLib.QryToData("insert into ordpath_tests (i,dewey,ordpath,hid ) values(" & i & ",'" & O1.Dewey & "','" & O1.HID & "','" & O1.Dewey & "' )", EnumLib.DBList.Gen, EnumLib.DataObj.DataSet)
'"0x" & O1.HID
Next
Dim S As String = ""
For i = 0 To dv.Count - 1
With dv(i)
S = S & .Item("i") & vbTab & .Item("Dewey") & vbTab & .Item("Lvl") & vbTab & "'" & .Item("HID") & vbLf
End With
Next
S = S & vbLf & vbLf
dv.Sort = "HID"
For i = 0 To dv.Count - 1
With dv(i)
S = S & .Item("i") & vbTab & .Item("Dewey") & vbTab & vbTab & .Item("Lvl") & vbTab & "'" & .Item("HID") & vbLf
End With
Next
Dim t2 As Long = My.Computer.Clock.TickCount
S = S & (Str(t2 - T1)) & " milliseconds"
Clipboard.SetText(S)
Dim P As String = ""
Dim Tm As Long = My.Computer.Clock.TickCount
For i = 0 To dv.Count - 1
P = P & Str(i) & vbTab & dv(i).Item("i") & vbTab & ORDPATH.ToString(dv(i).Item("hid")) & vbTab & GetParent(dv(i).Item("dewey")) & vbTab & vbLf
' GetRoot(dv(i).Item("dewey"))
Next
MsgBox(P & vbLf & vbLf & My.Computer.Clock.TickCount - Tm & " msec")
End Sub
End Class