Simple SQL proc to generate an enum class for a lookup table

by Heathesh 8. February 2012 01:40

Here's the proc I created and below is the explanation of how I went about doing this:

I'm sure every c# developer has come across a situation where they have a lookup table that they'd like to convert into an enum in code. I've actually had to do it a few times myself and each time I use some sort of cursor and t-sql to create the enum class code. So I figured why not create a procedure to do this that I could re-use?

To begin with, there's a few parameters I needed to pass into the procedure. These were:

  1. The table name
  2. The namespace I wanted of the code
  3. The DataContract namespace. Because most of the stuff I do now is WCF this was a necessity but you can obviously remove this if you want.
  4. The id field on the lookup table.
  5. The value field on the lookup table.

I've attached the proc for you to copy ( The basics of how it works is that I insert the id and value fields into a temporary table using sp_executesql, then loop through the values using a cursor and simply use print statements to output the relevant code.

To use the proc I created a simple table called Title with an Id and a Name column like so:

CREATE TABLE [dbo].[Title](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Id] ASC

I then populated the table with values like so:

INSERT INTO [Title] ([Name]) VALUES ('Mr')
INSERT INTO [Title] ([Name]) VALUES ('Mrs')
INSERT INTO [Title] ([Name]) VALUES ('Miss')
INSERT INTO [Title] ([Name]) VALUES ('Dr')
INSERT INTO [Title] ([Name]) VALUES ('Prof')
INSERT INTO [Title] ([Name]) VALUES ('Sir')
INSERT INTO [Title] ([Name]) VALUES ('Madam')
INSERT INTO [Title] ([Name]) VALUES ('Mr And Mrs')

You'll notice one of the values has spaces in it. Since enum names cannot have spaces in it when you run the proc you'll notice in the output that it adds the Description attribute with the full name of the row, this is so you can still get the value of the string. If you need help with this check out a previous post of mine on reading the description attribute: Reading the DescriptionAttribute of enumerators ...

Then I simply ran my proc like so:

'Title', -- this is the name of the lookup table
'Heathesh.Com.Entity', -- this is the namespace I wanted to appear in the code
'', -- this is the data contract namespace I wanted to appear in the code
'Id', -- this is the id field of the table
'Name' -- this is the name field of the table

And this produced my required output like so:

using System;
using System.Runtime.Serialization;
using System.ComponentModel;
namespace Heathesh.Com.Entity
/// <summary>
/// Title enum
/// </summary>
[DataContract(Name = "Title", Namespace = "")]
public enum Title
Mr = 1,
Mrs = 2,
Miss = 3,
Dr = 4,
Prof = 5,
Sir = 6,
Madam = 7,
[Description("Mr And Mrs")]
MrAndMrs = 8,

I simply then added a "Title.cs" class to my project, replaced all the code in the class with what was generated and that was it. An enum class generated using sql from a lookup table.

Please note: Don't forget to add a reference to "System.Runtime.Serialization" in your project for the WCF stuff (DataContract, DataMember, EnumMember etc.)

Happy generating!



Tags: , , ,

Development | .Net | SQL Server 2008

Reading the DescriptionAttribute of enumerators and returning the corresponding enum based on the description

by Heathesh 24. May 2010 01:36

There are two things I wanted to achieve using the DescriptionAttribute for my enumerators. The first was to retrieve the description of an enumerator using an extension method, and the second was to return the relevant enumerator based on the description.

To begin with I created a static class I called EnumExtensionManager. I wanted to add my two methods as extension methods so hence the need for the static class. The very first thing I did was apply the description attribute to my enum definitions as follows:

    /// <summary>
    /// Enum of the regions
    /// </summary>
    public enum Region
        AllRegions = 0,
        Gauteng = 1,
        [Description("Free State")]
        FreeState = 2,
        [Description("Eastern Cape")]
        EasternCape = 3,
        [Description("Western Cape")]
        WesternCape = 4,
        Mpumalanga = 5,
        [Description("Northern Cape")]
        NorthernCape = 6,
        [Description("North West")]
        NorthWest = 7,
        KwaZuluNatal = 8,
        Limpopo = 9

Now, in order to retrieve the description of the enumerator I created the following static method in my EnumExtensionManager class:

        /// <summary>
        /// Gets the description of an enumerator
        /// </summary>
        /// <param name="enumerator"></param>
        /// <returns></returns>
        public static string GetDescription(this Enum enumerator)
            //get the enumerator type
            Type type = enumerator.GetType();

            //get the member info
            MemberInfo[] memberInfo = type.GetMember(enumerator.ToString());

            //if there is member information
            if (memberInfo != null && memberInfo.Length > 0)
                //we default to the first member info, as it's for the specific enum value
                object[] attributes = memberInfo[0].GetCustomAttributes(typeof(DescriptionAttribute), false);

                //return the description if it's found
                if (attributes != null && attributes.Length > 0)
                    return ((DescriptionAttribute)attributes[0]).Description;

            //if there's no description, return the string value of the enum
            return enumerator.ToString();

The method will return the description text for an enumerator or return the enumerator name as a string if it can't find the description. Since I created it as an extension method, it was easy enough to call it in code simply by invoking .GetDescription() on any enum value.

            Region regionEnumerator = Region.KwaZuluNatal;
            string regionDescription = regionEnumerator.GetDescription();

Next I wanted to be able to use the description of an enum and return the corresponding enum from it. I achieved this by adding the following extension method to me EnumExtensionManager class:

        /// <summary>
        /// Gets the enumerator from the description passed in
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="description"></param>
        /// <returns></returns>
        public static T GetEnumFromDescription<T>(this string description)
            //get the member info of the enum
            MemberInfo[] memberInfos = typeof(T).GetMembers();

            if (memberInfos != null && memberInfos.Length > 0)
                //loop through the member info classes
                foreach (MemberInfo memberInfo in memberInfos)
                    //get the custom attributes of the member info
                    object[] attributes = memberInfo.GetCustomAttributes(typeof(DescriptionAttribute), false);

                    //if there are attributes
                    if (attributes != null && attributes.Length > 0)
                        //if the description attribute is equal to the description, return the enum
                        if (((DescriptionAttribute)attributes[0]).Description == description)
                            return (T)Enum.Parse(typeof(T), memberInfo.Name);

            //this means the enum was not found from the description, so return the default
            return default(T);

Calling the method was easily done and it could be invoked on any string value like so:

            string regionDescription = "Northern Cape";
            Region regionEnumerator = regionDescription.GetEnumFromDescription<Region>();

That was it. Happy enumerating!


Tags: , , , , , ,

Development | .Net

Powered by BlogEngine.NET (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)


Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions


Tag cloud


<<  September 2017  >>

View posts in large calendar