2

I'm creating an tool called dbfiddle, and I'm having trouble with displaying a few SQL datatypes as readable text. One in particular is hierarchyid.

I'm using a JSON API to the database backends, and SQL Server 2014/2016 use System.Web.Script.Serialization.JavaScriptSerializer to convert the results to JSON.

For the decimal this works:

result.data(i).Add(If(reader.IsDBNull(i),Nothing,reader.GetSqlDecimal(i).ToString()))

For varbinary, this is fine:

result.data(i).Add(If(reader.IsDBNull(i),Nothing,"0x" & (New System.Runtime.Remoting.Metadata.W3cXsd2001.SoapHexBinary(reader(i))).ToString()))

For most other data types, a simple ToString is fine:

result.data(i).Add(If(reader.IsDBNull(i),Nothing,reader(i).ToString()))

but I can't figure out what the equivalent code for hierarchyid should be. How do I convert to a string? I can't alter the query in any way as dbfiddle does not parse the input SQL.

Here is a simplified version of the relevant portion of my code:

Dim sr = New StreamReader(Request.InputStream)
Dim ser = New System.Web.Script.Serialization.JavaScriptSerializer()
ser.MaxJsonLength = Int32.MaxValue
Dim queries = ser.Deserialize(Of List(Of String))(sr.ReadToEnd())
sr.Close()
Dim query = queries(0)

Dim connection As SqlConnection
Dim command As SqlCommand

…

command = connection.CreateCommand
command.CommandText = query

Dim reader = command.ExecuteReader()
Dim result = New List(Of List(Of Object))

For i As Integer = 0 To reader.FieldCount-1
    result.Add(New List(Of Object))
Next

While reader.Read()
    For i As Integer = 0 To reader.FieldCount-1
        result(i).Add(reader(i).ToString())
    Next
End While

reader.Close()
connection.Close()
…
Response.Write(ser.Serialize(result))
  • Have you looked into something like using a `Dictionary` of `System.Type` objects? – David Apr 19 '17 at 15:33
  • What about `ToString()` doesn't work? `reader(i)` should be returning a [`SqlHierarchyId`](https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.types.sqlhierarchyid.aspx) structure, which overrides `ToString` to provide values like `/`, `/1/` etc. I didn't understand the part about using a "JSON API", so perhaps I am missing something. – Mark Apr 19 '17 at 18:31
  • @Mark reader.GetDataTypeName(i) returns something like `username.sys.hierarchyid`. Whatever this translates to in .NET, it causes an error when the JavaScriptSerializer tries to serialize it. –  Apr 19 '17 at 20:36
  • and `TypeName(reader(i))` also returns `username.sys.hierarchyid`, as does `TypeName(reader(i).ToString())` which I don't get at all. –  Apr 19 '17 at 20:44
  • What about `reader(i).GetType().Name`? That should give `SqlHierarchyId`. If I run a `SqlHierarchyId` through the `JavaScriptSerializer` I get `{"IsNull":false}`, which obviously isn't what you would want, so I see why you would want to `ToString` it. I just don't know what issue you are seeing. – Mark Apr 19 '17 at 20:51
  • @Mark I get nothing from the aspx page and no error visible in the only log I know of (I run `WEVTUtil.exe clear-log Application` before getting the page, then `wevtutil qe Application` afterwards) –  Apr 19 '17 at 20:59
  • "What about reader(i).GetType().Name? That should give SqlHierarchyId" even executing that code causes the same error. I think [this documentation page](https://msdn.microsoft.com/en-us/library/dn236441(v=sql.120).aspx) is relevant and I don't think it is returning type `SqlHierarchyId` –  Apr 19 '17 at 20:59
  • Found the answer here: http://stackoverflow.com/a/18881556/6854914 –  Apr 19 '17 at 21:38

0 Answers0