Generating Enums From Database Tables
I posted earlier about how to use enums in LINQ To SQL, and I spoke about why I think enums are useful. The only problem with this approach was that you had to create the enums by hand, even if you had the same data sitting in a lookup table. If you were at my recent Code Generation with T4 Chalk 'n Talk at Tech Ed South Africa, you would have seen me generate enums automatically from the database, which is what we're going to do below.
Note: If you're not familiar at all with T4, check out this post on T4 Templates.
<#@ template language="c#" #>
<#@ output extension=".cs" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
string tableName = "CustomerTypes";
string idColumnName = "CustomerTypeID";
string valueColumnName = "Value";
#>
using System;
namespace ConsoleApplication1
{
public enum <#= tableName #>
{
<#
SqlConnection sqlConn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=TaskList;Integrated Security=True");
sqlConn.Open();
string sql = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {0}", idColumnName, valueColumnName, tableName);
SqlCommand sqlComm = new SqlCommand(sql, sqlConn);
IDataReader reader = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);
System.Text.StringBuilder sb = new System.Text.StringBuilder();
while (reader.Read())
{
sb.Append("\t\t" + reader[valueColumnName] + " = " + reader[idColumnName] + "," + Environment.NewLine);
}
sb.Remove(sb.Length - 3, 3);
sqlComm.Dispose();
sqlConn.Dispose();
#>
<#= sb #>
}
}
The template above will generate an enum like the following:
using System;
namespace ConsoleApplication1
{
public enum CustomerTypes
{
Good = 0,
Bad = 1,
Ugly = 2
}
}
There are two drawbacks with the template above though. The first is that it uses the table name for the enum name, which means the enum might be a plural if you follow the table-names-as-plurals convention, whereas I only make an enum a plural if it is a flag (i.e. it can contain a combination of values). A lot of people don't follow the same conventions, so I'm not going to try solve this issue here.
The second, and more important, problem is that the template above means you need to have a copy of it for each lookup table in your database that you want to create an enum from. I've amended the template to cater for multiple, specific tables instead and to put these into one file. The new version appears below:
<#@ template language="c#" #>
<#@ output extension=".cs" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#
string[] tableNames = { "CustomerTypes", "TaskTypes" };
string[] idColumnNames = { "CustomerTypeID", "TaskTypeID" };
string[] valueColumnNames = { "Value", "Value" };
#>
using System;
namespace ConsoleApplication1
{
<#
SqlConnection sqlConn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=TaskList;Integrated Security=True");
sqlConn.Open();
for (int i = 0; i < tableNames.Length; i++)
{
string tableName = tableNames
;
string idColumnName = idColumnNames;
string valueColumnName = valueColumnNames;
#>
public enum <#= tableName #>
{
<#
string sql = string.Format("SELECT {0}, {1} FROM {2} ORDER BY {0}", idColumnName, valueColumnName, tableName);
SqlCommand sqlComm = new SqlCommand(sql, sqlConn);
IDataReader reader = sqlComm.ExecuteReader();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
while (reader.Read())
{
sb.Append("\t\t" + reader[valueColumnName] + " = " + reader[idColumnName] + "," + Environment.NewLine);
}
sb.Remove(sb.Length - 3, 3);
reader.Close();
sqlComm.Dispose();
#>
<#= sb #>
}
<#
}
sqlConn.Dispose(); #>
}
and it will generate a set of enums like this:
using System;
namespace ConsoleApplication1
{
public enum CustomerTypes
{
Good = 0,
Bad = 1,
Ugly = 2
}
public enum TaskTypes
{
Bug = 0,
Task = 1,
Enhancement = 2
}
}