Convert HierarchyId to Hex string in C#, get raw ("representation") value for CSV export

Question

So... for a CSV export from a SQL Server database, using C# and Entity Framework, I need to get the raw value of HierarchyId.

The raw value looks like that in SSMS: 0x29F9DC and the output in my CSV is like that: /-25/-5/.

The CSV is generated using a StringBuilder, each line of my entity is transform via a ToCsv method:

return IdClassificationEc + ";" + Libelle + ";" + Complement;

where IdclassificationEc is a hierarchyId.

With or without a ToString, I get the readable value and there is no known way to get the raw, hex, value.

Using T-SQL, I was able to get the good value on a string using this:

CONVERT(VARCHAR, CAST(evc.ID_CLASSIFICATION_EC AS VARBINARY), 1)

Please, help me with this frustrating issue :'(

Answer 1

On SQL Server side:

declare @hid hierarchyid = '/1/1/1/';

select 
    v1 = convert(varchar(1000), cast(@hid as varbinary(892)), 1), -- = 0x5AD6
    v2 = convert(varchar(1000), cast(@hid as varbinary(892)), 2); -- = 5AD6

In C#:

using System;
using Microsoft.SqlServer.Types;
using System.IO;

public class Program
{
    public static void Main()
    {
        Console.WriteLine("Conversion of SqlHierarchyId to Hex string:");

        var hid = SqlHierarchyId.Parse("/1/-1.12/-2.2.39/");    

        Console.WriteLine(HierarchyIdToHexString(hid)); // = 0x5A2C9F9D93E0 
    }

    private static String HierarchyIdToHexString(SqlHierarchyId hid) 
    {
        using (var ms = new MemoryStream())
        using (var binWriter = new BinaryWriter(ms))
        {
            hid.Write(binWriter);   
            var byteString = BitConverter.ToString(ms.ToArray()).Replace("-","");
            return String.Format("0x{0}", byteString);          
        }
    }   
}

Here is the working example on dotnetfiddle.net

@Uranne, use HierarchyIdToHexString() method in your ToCsv().