Generate Enum from Values present in a table using ADO.NET Entity framework

Question

My requirement is to create an Enum based on values present in a table from DB. I am using ADO.NET Entity Framework model (.edmx file), Can any one of you help me out.

Answer 1

It is probably a lot easier to use T4 templates. Here is a really good article on getting started

My example below uses a direct SQL Connection, but as you can see you can include any code and generate whatever output you like into a cs file that is compiled into your project. You could replace the ADO syntax below with an enumeration over a collection of objects retrieved via your Entituy Framework model and output accordingly.

Create a file with the extension .tt in the directory where you would like the enumeration file to be generated. If you name the file XXXXX.tt then a file called XXXXX.cs will be generated so, name the tt file appropriately.

Try something along these lines. You might need to experiment a little with the syntax and the output, but I'm not going to write it all for you or you won't learn anything :)

Just be aware, that this database call will be made every time you edit the tt file.

<#@ template language="C#" hostspecific="True" debug="True" #>
<#@ output extension="cs" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
    SqlConnection sqlConn = new SqlConnection(@"Data Source=XXXX;Initial Catalog=XXXX; Integrated Security=True");
    sqlConn.Open();
#>
namespace AppropriateNamespace
{
public enum YourEnumName
{
    <#
    string sql = string.Format("SELECT Id, Name FROM YourTable ORDER BY Id");
    SqlCommand sqlComm = new SqlCommand(sql, sqlConn);
    IDataReader reader = sqlComm.ExecuteReader();

    System.Text.StringBuilder sb = new System.Text.StringBuilder();
    while (reader.Read())
    {
        sb.Append(FixName(reader["Name"].ToString()) + " = " + reader["Id"] + "," + Environment.NewLine + "\t\t");
    }
    reader.Close();
    sqlComm.Dispose();
    #>
<#= sb.ToString() #>
    }
}

Try improving on this. Rather than writing to a StringBuilder, output the results of each reader.Read() directly to the output. Also, I have included a FixName method that doesn't exist yet, but you might need that to take out spaces or illegal characters.

Answer 2

Daniel's template is nice and all, but I've put some more effort into such a template so it does all these:

• generates enumeration values with explicit integer values;
• uses Visual Studio's namespace naming convention so generated enumerations have project's default namespace with any subfolders appended (just like any code file in Visual Studio);
• adds complete enumeration XML documentation by using additional description table column values; if you don't have these never mind;
• correctly names the generated file and adds an additional attribute in the code so the generated enum doesn't get scrutinised by code analysis;
• multi-word lookup table values are correctly concatenated to pascal-cased equivalents (ie. Multi word value becomes a MultiWordValue);
• enumeration values always start with a letter;
• all enumeration values consist of only letters and numbers, everything else gets cut out;

Anyway. Everything is very well documented in this blog post.