Monday, September 21, 2015

Generating Enums out of Database Lookup Tables

With the help of this great article, I succeeded in generating c# enumerations automatically, out of lookup key/values from a database.

 Code: (for a template include)
<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>

<#+

    public static Dictionary<string, Dictionary<int, string>> GetSettings()
    {
        Dictionary<string, Dictionary<int, string>> settings = new Dictionary<string, Dictionary<int, string>>();

        try   
        {
            using (SqlConnection sqlServerConnection = new SqlConnection("my Connection String"))
            {
                sqlServerConnection.Open();

                SqlCommand sqlCommand = new SqlCommand("SELECT Group, Code, Description FROM Lookups WHERE Code = -1", sqlServerConnection);

                SqlDataReader sqlReader = sqlCommand.ExecuteReader();

                while(sqlReader.Read())
                {
                    settings.Add((sqlReader.GetString(2) ?? string.Empty).Trim(), new Dictionary<int, string>());
                }

                sqlCommand = new SqlCommand("SELECT Group, Code, Description FROM Lookups WHERE Code != -1", sqlServerConnection);

                sqlReader = sqlCommand.ExecuteReader();

                while(sqlReader.Read())
                {
                    foreach(var setting in settings)
                    {
                        setting.Value.Add(sqlReader.GetInt32(1), (sqlReader.GetString(2) ?? string.Empty).Trim());
                    }
                }

                sqlServerConnection.Close();
            }
        }

        catch (Exception)
        {
        }

        return settings;
    }
 #>



Code for the tt template:

<#@ include file="DbHelper.ttinclude" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>

namespace Lab.Generate_Enums_Dynamically

{
     <#   
        var settings = GetSettings();

        foreach (var settingGroup in settings)
        {

     #>
        public enum <#= settingGroup.Key.Replace(" ", "_") #>
        {
            <#
                List<string> enumEntries = new List<string>();
                foreach(var setting in settingGroup.Value)
                {
                    enumEntries.Add(string.Format("{0} = {1}", setting.Value.ToString().Replace(" ", "_"), setting.Key));

                }

            #>
            <#= string.Join(",\n", enumEntries) #>
        }
    <# } #>
}

No comments:

Post a Comment