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

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

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.

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.