2

I have just started working with hierarchyId in Sql Server. I am looking at different functionalities in SQL server. However, this sounds to me like a call to very thick data layer, which I want to avoid. Is there any API in C# for HierarchyId type which supports SQl Hierarchy Id?

Lost
  • 12,007
  • 32
  • 121
  • 193

1 Answers1

2

In short: yes. HierarchyID is a CLR data type. Every method in the list of methods has syntax both for calling the method from SQL Server and from C#.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • HierarchyID is not a CLR type. – Gert Arnold Apr 27 '22 at 07:38
  • It's not? When I run `select * from sys.assembly_types;` in my database, there it is. – Ben Thul Apr 27 '22 at 14:56
  • [CLR is .net.](https://learn.microsoft.com/en-us/dotnet/standard/clr). There is a NuGet package that brings HierarchyId into C# for EF support. – Gert Arnold Apr 27 '22 at 17:20
  • It sure is. But CLR has been integrated into SQL Server since the 2005 release. You can read all about it [here](https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/common-language-runtime-integration-overview?view=sql-server-ver15). And given that MS official documentation is comfortable calling such things CLR, so am I. – Ben Thul Apr 27 '22 at 21:53
  • No, you *can* integrate CLR modules in SQL Server. – Gert Arnold Apr 28 '22 at 06:39
  • I'm a) unsure what distinction you're trying to draw and b) if there is one (which I'm not convinced that there is) what difference it makes. That said, I still stand by my assertion that the presence of hierarchyid in sys.assembly_types means that it's a CLR datatype. It's one provided by MS but CLR nonetheless. – Ben Thul Apr 28 '22 at 14:46
  • Just try to create a HierarchyID object in c# code w/o any additional NuGet packages, i.e. only with .Net CLR types. You can't. I don't see how you can conclude that CLR integration in SQL Server would introduce SQL Server types in .Net. Your link is about the possibility to run .Net code in SQL Server if you want. It doesn't change anything in what's available in .Net. – Gert Arnold Apr 28 '22 at 15:00
  • Ah. Okay. We're talking about two different things. The system-provided SQLCLR data types (of which hierarchyid is one) have been implemented in such a way as to be able to run in the environment called CLR. To me, that it can run in that environment is sufficient to call it CLR. If you'd like to integrate that same functionality into an application of your own devising, you will have to obtain and reference the assembly. As you state, that is as easy as a nuget pull in this the year 2022. If the need to do that disqualifies it from your definition of CLR, so be it. – Ben Thul Apr 28 '22 at 16:23