0

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 :'(

Vadim Loboda
  • 2,431
  • 27
  • 44
Uranne
  • 25
  • 6
  • I think you're looking for [hierarchyid.ToString()](https://learn.microsoft.com/en-us/sql/t-sql/data-types/tostring-database-engine?view=sql-server-ver15). HierarchyID, geometry, geography and more are SQLCLR types and have methods you can call to convert them to strings, parse them, get levels, ancestors etc – Panagiotis Kanavos Nov 18 '19 at 11:58
  • 1
    ToString() give the "/-25/-5/" value, not the raw value :/ – Uranne Nov 18 '19 at 11:59
  • What raw value? You assume there is a *different* one. There isn't. In fact, how did you get negative IDs in there? – Panagiotis Kanavos Nov 18 '19 at 12:00
  • A `hierarchyid` is a path representation using *unsigned* integral values. `-25` may just be the way a very large unsigned IS is displayed. – Panagiotis Kanavos Nov 18 '19 at 12:04
  • @PanagiotisKanavos The [documentation](https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver15#remarks) does not agree with you. *Nodes inserted before 0 have the logical representation as a negative number. For example, a node that comes before /1/1/ can be represented as /1/-1/*. – GSerg Nov 18 '19 at 12:05
  • @GSerg what I meant is that those are the raw values, there's no other representation. We still don't know what the OP wants to do though - beautify the hierarchy perhaps? How would it be used though? – Panagiotis Kanavos Nov 18 '19 at 12:06
  • @Uranne what are you trying to do and why don't you want negative IDs? Its form shouldn't matter. How is this string going to be used? – Panagiotis Kanavos Nov 18 '19 at 12:08
  • @PanagiotisKanavos No, you said that `hierarchyid` [uses unsigned integers](https://stackoverflow.com/questions/58912442/hierarchyid-get-raw-representation-value#comment104090164_58912442). – GSerg Nov 18 '19 at 12:08
  • @Uranne btw `each line of my entity is transform via a ToCsv method:` this code will break if any field contains `;` and doesn't fix decimal separators. Use a library like CsvHelper to generate CSVs that take care of locales. – Panagiotis Kanavos Nov 18 '19 at 12:09
  • @Uranne If you really need this, can you not cast the field to `varbinary` before feeding it to EF? Or, depending on the EF version you are using, https://stackoverflow.com/q/14363332/11683 might be of help. – GSerg Nov 18 '19 at 12:17
  • @PanagiotisKanavos The "raw" value I want is the value ssms show me on a simple select, without using ToList(), the value is an Hex code starting with 0x. I'm simply extracting values from a table already existing, I don't know how negative value is possible. About the Csv, the formating is already done before, it won't break. GSerg : the line of code I show is a line that give me the string I want if was able to use directly sql, but I have to make it by c# ... Sorry it seems like I'm not really clear :/ – Uranne Nov 18 '19 at 12:19
  • @Uranne you mean `0x29F9DC`? Then modify your client-side code to format field directly instead of depending on the built-in `ToString()`. You haven't posted the relevant code though so people can't help. What's the type of `IdclassificationEc` on the *client-side* code? A string, a `byte[]`, some other type? I don't remember all that stuff - I google them too. You should update the question itself and explain what you want and what you did – Panagiotis Kanavos Nov 18 '19 at 12:23
  • @Uranne So what is stopping you from putting that SQL line into your query that you then feed to EF? – GSerg Nov 18 '19 at 12:27
  • @Uranne and right now I have to google quite a bit simply to understand what you're asking and why. EF 6.2 never got HierachyID support, so where do you get that value from? Are you using one of the forks or addins that tried adding support for this? Update the question and put all relevant information there – Panagiotis Kanavos Nov 18 '19 at 12:27
  • On my project we use a custom version of EF (intergrating HierarchyId, I gave you all the code I made, what code would be the best to show you ? @PanagiotisKanavos Yes, I want the 0x29F9DC like value. The code on client side is not editable, I don't have the hand on the component HierarchyId – Uranne Nov 18 '19 at 13:06
  • I would be curious why you want the binary representation of the hierarchyid. That is, the value that you're getting (which others have correctly said is likely coming from calling `ToString()` on the value in the db) is sufficient to be able to reconstruct the value. So, if you're trying get some way to serialize the value, what you're already getting back is sufficient. – Ben Thul Nov 18 '19 at 17:29
  • my work is to automatise a previously export of the base made by bcp in a .bat file, the client want the same extract ... – Uranne Nov 20 '19 at 13:50

1 Answers1

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().

Vadim Loboda
  • 2,431
  • 27
  • 44