Coding to access Class data of an enum in a CLR-UDT.

Category: sql server dotnet


Jörg Debus on Thu, 09 Jun 2016 14:45:45

Hi all,

I have implemented a bunch of enums using CLR-UDTs and this is working fine so far. Only one aspect bothers me: The Enum class in .Net contain static properties for each member name defined. When called, the properties return the member value. Intellysense in VS knows these properties and so coding enum is quite convenient. In C# static properties are called using the class name, e.g. WeatherCode.Wind sends you back the member value of Wind.

SQL CLR has the ability to call static data from an object by using "::" operator. E.g. hierarchyid::GetRoot( ) calls a static method from MS hierarchyid CLR UDT. Somewhere deep in the documentation I have found that "::" can be used to "access static methods, properties and fields".

I have implemented the CLR enums in two cs files which are both referenced: The Enum definition and the CLR-UDT code using this definition. Both are in the same Namespace. So the skeleton of the CLR-UDt looks like this:

[Serializable] [SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, Name = "pamEEGEnergy", MaxByteSize = 1, IsFixedLength = true)] public struct pamEEGEnergy : INullable, IBinarySerialize { //*----* Private fields private EEGEnergy enumEnergy; private bool bolNull; // --- the rest is standard CLR-UDT implementation }

// --- This is a snippet of the enum which is also straight forward

public enum EEGEnergy : byte {
       Laufwasser = 0,
       Biomasse = 1


So the name of the struct used to implement is pamEEGEnergy and the .Net Enum used is named EEGEnergy. This enum contains the default static properties. They can be used freely in the all CS code as long as the namespace is correctly referenced.

When trying to use this in T-SQL e.G. "DECLARE @TestEnum pamEEGEnergy = EEGEnergy.Wind" (CONVERT?) I get the message "Type EEGEnergy is not a defined system type.". Obviously, the SQL Engine does not know the EEGEnergy enum/class. It is no problem to implement static methods named after the member names pamEEGEnergy::Wind bit this is error prone and not easy to maintain.

So I need some advice from the CLR experts.

Thanks in advance.

Regards Jörg


Riaon on Fri, 10 Jun 2016 06:43:01

I am not the CLR expert but I also interested in this system defined type. Good Luck.

Jörg Debus on Fri, 17 Jun 2016 16:07:58

Hello dear moderator,

could you pls. check whether I am in the correct forum or not. I can't believe that I don't get an answer to this quite straightforward question.

Bob Beauchemin on Fri, 17 Jun 2016 16:46:38

AFAIK, enums have never been directly supported in SQLCLR. Doing a quick search of the forum posts confirms this: 

There's something close to the workaround they suggest here (from same forum search): And you suggest a similar but different workaround for your specific needs.

Jörg Debus on Fri, 17 Jun 2016 17:32:55

Hi Bob,

I know both links. The first one is purely administrative but the second contains two coding examples and stopped reading after having seen all the arguments contra UDTs instead of answers. So after receiving your answer, I opened that threat again. And, what a surprise, I have found a little change in the second code: The name of the UDT is identical to the name of the enum struct. Maybe that this would move the Enum class properties to the UDT code and in turn allow to use access these using the class operator.

I have to check this and will come back with the result.

The implementation of Enums using tables performs here worse compared to an UDT. Enum labels are normally only short lists. Accessing a value by name is completely handled by .Net with a little lookup in a compiled table residing in memory close to the code. Larger lists would not be as efficient as the short once. Here the table would outperform Enum by far.

Thanks for your assistance.

Regards Jörg