When building applications with C# and SQL Server, it is often necessary to define codes in the database that correspond with enums in the application. However, it can be burdensome to maintain the enums manually, as codes are added, modified, and deleted in the database.
In this blog post, I’ll share a T4 template that I wrote which does this automatically. I looked online first, and did find a few different solutions for this, but none that worked for me as-is. So, I built this generic T4 template to do the job, and you can use it too.
Let’s say you’ve got a Color table and ErrorType table in the database, populated as follows:
Now you’d like enums in your C# application that correspond to these rows. The T4 template will generate them as follows:
Before showing the code that generates this, let’s point out a few things.
First, notice that the Color table has only an ID and a name column, while the ErrorType table has an ID, name, and description columns. Thus, the ErrorType enums include XML comments based on the descriptions in the database, which appear in IntelliSense when referencing the enums in Visual Studio.
Also notice that one enum was generated for the Color table, while two enums were generated for the ErrorType table. This is because we instructed the T4 template to create different enums for different value ranges (0 – 999 for SystemErrorType, and 1000 – 1999 for CustomerErrorType) in the same table.
Finally, the Color enum includes an additional member Undefined with a value of 0 that does not appear in the database, while the other enums don’t include the Undefined member. This is because, in the case of Color, we’d like an enum to represent Undefined, but we don’t want it in the database because we don’t want to allow 0 as a foreign key into the Color table.
This gives you an idea of the flexibility that this T4 template gives you for generating enums from the database. The enums above were generated with the following T4 template:
<#@include file="EnumsDb.ttinclude" #>
<#
var configFilePath = "app.config";
var enums = new []
{
new EnumEntry
("Supported colors", "DemoDatabase", "dbo", "Color", "ColorId", "ColorName", "ColorName")
{ GenerateUndefinedMember = true },
new EnumEntry
("System error types", "DemoDatabase", "dbo", "ErrorType", "ErrorTypeId", "ErrorTypeName", "Description")
{ EnumName = "SystemErrorType", ValueRange = new Tuple<long, long>(0, 999) },
new EnumEntry
("Customer error types", "DemoDatabase", "dbo", "ErrorType", "ErrorTypeId", "ErrorTypeName", "Description")
{ EnumName = "CustomerErrorType", ValueRange = new Tuple<long, long>(1000, 1999) },
};
var code = this.GenerateEnums(configFilePath, enums);
return code;
#>
You can see that there is very little code that you need to write in the template, and that’s because all the real work is contained inside the EnumsDb.ttinclude file (referenced at the top with @include), which contains the actual code to generate the enums, and can be shared throughout your application. A complete listing of the EnumsDb.ttinclude file appears at the end of this blog post.
All you need to do is call GenerateEnums with two parameters. The first is a path your application’s configuration file, which holds the database connection string(s). The second is an array of EnumEntry objects, which drives the creation of enums in C#. Specifically, in this case, there are three EnumEntry objects, which results in the three enums that were generated.
Each enum entry contains the following properties:
- Description (e.g., “Supported colors”). This description appears as XML comments for the enum.
- Database connection string key (e.g., “DemoDatabase”). It is expected that a connection string named by this key is defined in the application’s configuration file. Since this property exists for each enum entry, it is entirely possible to generate different enums from different databases.
- Schema name (e.g., “dbo”). This is the schema that the table is defined in.
- Table name (e.g., “Color”). This is the table that contains rows of data for each enum member.
- ID column name (e.g., “ColorId”). This is the column that contains the numeric value for each enum member. It must be an integer type, but it can be an integer of any size. The generator automatically maps tinyint to byte, int to int, smallint to short, and bigint to long.
- Member column name (e.g., “ColorName”). This is the column that contains the actual name of the enum member.
- Description column name. This should be the same as the Member column name (e.g., “ColorName”) if there is no description, or it can reference another column containing the description if one exists. If there is a description, then it is generated as XML comments above each enum member.
- Optional:
- EnumName. If not specified, the enum is named after the table. Otherwise, you can choose another name if you don’t want the enum named after the table name.
- GenerateUndefinedMember. If specified, then the template will generate an Undefined member with a value of 0.
- ValueRange. If specified, then enum members are generated only for rows in the database that fall within the specified range of values. The ErrorType table uses this technique to generate two enums from the table; one named SystemErrorType with values 0 – 999, and another named CustomerErrorType with values 1000 – 1999.
This design offers a great deal of flexibility, while constantly ensuring that the enums defined in your C# application are always in sync with the values defined in the database. And of course, you can modify the template as desired to suit any additional needs that are unique to your application.
The full template code is listed below. I hope you enjoy using it as much as I enjoyed writing it. Happy coding! 😊
<#@ template hostspecific="true" language="C#" debug="false" #>
<#@ assembly name="System.Configuration" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text" #>
<#+
private string GenerateEnums(string configFilePath, EnumEntry[] entries)
{
if (entries == null)
{
return string.Empty;
}
var ns = System.Runtime.Remoting.Messaging.CallContext.LogicalGetData("NamespaceHint");
var sb = new StringBuilder();
sb.AppendLine(this.GenerateHeaderComments());
sb.AppendLine($"namespace {ns}");
sb.AppendLine("{");
foreach(var entry in entries)
{
try
{
sb.Append(this.GenerateEnumMembers(configFilePath, entry));
}
catch (Exception ex)
{
sb.AppendLine($"#warning Error generating enums for {entry.EnumDescription}");
sb.AppendLine($" // Message: {ex.Message}");
}
}
sb.AppendLine();
sb.AppendLine("}");
return sb.ToString();
}
private string GenerateHeaderComments()
{
var comments = $@"
// ------------------------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a C# code generator
// Generated at {DateTime.Now}
//
// Warning: Do not make changes directly to this file; they will get overwritten on the next
// code generation.
// </auto-generated>
// ------------------------------------------------------------------------------------------------
";
return comments;
}
private string GenerateEnumMembers(string configFilePath, EnumEntry entry)
{
var code = new StringBuilder();
var connStr = this.GetConnectionString(configFilePath, entry.ConnectionStringKey);
var enumDataType = default(string);
using (var conn = new SqlConnection(connStr))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = @"
SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND COLUMN_NAME = @IdColumnName
";
cmd.Parameters.AddWithValue("@SchemaName", entry.SchemaName);
cmd.Parameters.AddWithValue("@TableName", entry.TableName);
cmd.Parameters.AddWithValue("@IdColumnName", entry.IdColumnName);
var sqlDataType = cmd.ExecuteScalar();
if(sqlDataType == null)
{
throw new Exception($"Could not discover ID column [{entry.IdColumnName}] data type for enum table [{entry.SchemaName}].[{entry.TableName}]");
}
enumDataType = this.GetEnumDataType(sqlDataType.ToString());
var whereClause = string.Empty;
if (entry.ValueRange != null)
{
whereClause = $"WHERE [{entry.IdColumnName}] BETWEEN {entry.ValueRange.Item1} AND {entry.ValueRange.Item2}";
}
cmd.CommandText = $@"
SELECT
Id = [{entry.IdColumnName}],
Name = [{entry.NameColumnName}],
Description = [{entry.DescriptionColumnName}]
FROM
[{entry.SchemaName}].[{entry.TableName}]
{whereClause}
ORDER BY
[{entry.IdColumnName}]
";
cmd.Parameters.Clear();
var innerCode = new StringBuilder();
var hasUndefinedMember = false;
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr["Name"].ToString() == "Undefined" || rdr["Id"].ToString() == "0")
{
hasUndefinedMember = true;
}
if (entry.NameColumnName != entry.DescriptionColumnName)
{
innerCode.AppendLine("\t\t/// <summary>");
innerCode.AppendLine($"\t\t/// {rdr["Description"]}");
innerCode.AppendLine("\t\t/// </summary>");
}
innerCode.AppendLine($"\t\t{rdr["Name"].ToString().Replace("-", "_")} = {rdr["Id"]},");
}
rdr.Close();
}
if ((entry.GenerateUndefinedMember) && (!hasUndefinedMember))
{
var undefined = new StringBuilder();
undefined.AppendLine("\t\t/// <summary>");
undefined.AppendLine("\t\t/// Undefined (not mapped in database)");
undefined.AppendLine("\t\t/// </summary>");
undefined.AppendLine("\t\tUndefined = 0,");
innerCode.Insert(0, undefined);
}
code.Append(innerCode.ToString());
}
conn.Close();
}
var final = $@"
/// <summary>
/// {entry.EnumDescription}
/// </summary>
public enum {entry.EnumName} : {enumDataType}
{{
// Database information:
// {entry.DbInfo}
// ConnectionString: {connStr}
{code}
}}
";
return final;
}
private string GetConnectionString(string configFilePath, string key)
{
var map = new ExeConfigurationFileMap();
map.ExeConfigFilename = this.Host.ResolvePath(configFilePath);
var config = ConfigurationManager.OpenMappedExeConfiguration(map, ConfigurationUserLevel.None);
var connectionString = config.ConnectionStrings.ConnectionStrings[key].ConnectionString;
return connectionString;
}
private string GetEnumDataType(string sqlDataType)
{
var enumDataType = default(string);
switch (sqlDataType.ToString())
{
case "tinyint" : return "byte";
case "smallint" : return "short";
case "int" : return "int";
case "bigint" : return "long";
default :
throw new Exception($"SQL data type {sqlDataType} is not valid as an enum ID column");
}
}
private class EnumEntry
{
public string EnumDescription { get; }
public string ConnectionStringKey { get; }
public string SchemaName { get; }
public string TableName { get; }
public string IdColumnName { get; }
public string NameColumnName { get; }
public string DescriptionColumnName { get; }
public string EnumName { get; set; }
public Tuple<long, long> ValueRange { get; set; }
public bool GenerateUndefinedMember { get; set; }
public string DbInfo
{
get
{
var info = $"ConnectionStringKey: {this.ConnectionStringKey}; Table: [{this.SchemaName}].[{this.TableName}]; IdColumn: [{this.IdColumnName}]; NameColumn: [{this.NameColumnName}]; DescriptionColumn: [{this.DescriptionColumnName}]";
if (this.ValueRange != null)
{
info = $"{info}; Range: {this.ValueRange.Item1} to {this.ValueRange.Item2}";
}
return info;
}
}
public EnumEntry(
string enumDescription,
string connectionStringKey,
string schemaName,
string tableName,
string idColumnName = null,
string nameColumnName = null,
string descriptionColumnName = null
)
{
this.EnumDescription = enumDescription;
this.ConnectionStringKey = connectionStringKey;
this.SchemaName = schemaName;
this.TableName = tableName;
this.IdColumnName = idColumnName ?? tableName + "Id";
this.NameColumnName = nameColumnName ?? "Name";
this.DescriptionColumnName = descriptionColumnName ?? "Description";
this.EnumName = tableName;
}
}
#>
