.NET数据视图中的HierarchyID

Question

以前的代码可以工作，但在新的PC上安装时无法工作。可能遗漏了一个设置？使用Visual Studio 2013专业版。

SQL Server 2012具有带hierarchyID字段的数据库表，但其值为null值。这个表被查询到一个数据视图中。此字段的项无法识别为hierarchyID数据类型。代码继续使用地理数据类型。

Answer 1

我放弃了使用hierarchyID作为数据类型，因为: a) HierarchyID列的数据库查询没有在数据视图中创建类似的数据类型；b) hierarchyID很难处理空值。

相反，我实现了P O‘’Neil，E O‘’Neil，S Pal等人所描述的ORDPATH。2004年：http://www.cs.umb.edu/~poneil/ordpath.pdf。它使用一个DB表LiOi，其中包含文章中的表中的值，该表被呈现为dvORDPATH。我几年前就写过这段代码。在此代码中，您将使用Dewey表示法(例如/1/1/15/7/)创建一个ORDPATH数据类型。该数据类型有三个属性，1) ORDPATH本身，它是一个连接的位串(在本例中为0100101001100011101111)；2)它是Dewey；3)层次结构中的级别。

这种方法类似于hierarchyID。主要区别在于hierarchyID使用十六进制，而我的代码使用基数为2的连接位串。两者都按树层次结构排序。

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

结束类