Generating Enums from the database tables using T4 templates in Visual Studio

October 31, 2012 | | Tags : SQL Tools Scripts T4


Many if not all database projects use lookup tables. This enforces database normalization, saves space, allows application-side caching, and many other nice and useful things. But now you have to teach the application which value of this lookup has which meaning. Also you need to make sure that after the lookup table has been updated, or records have been added or deleted from it, the application knows about these changes. But if we use just numeric values in the application, this makes the task to keep app in sync with the database more difficult if not impossible if the project is too large.

So somewhere heard about using Enums. This makes the task much simpler, as I only need to keep the enum definition in sync with the database lookup table. Great, but is there a way to keep the lookups automatically in sync with the database?

I came across this great article: Generate enum from a database lookup table using T4

I wanted to automate the enum creation as much as possible. With Microsoft SQL Server I can use extended properties to provide instructions to the application how to generate the enums.

###UPDATE: I’ve converted all of this into a visual studio gallery template T4 based generator of enums from the database

Here is the script that creates all necessary database objects:

IF (NOT EXISTS (SELECT * FROM sys.[schemas] WHERE [schema_id] = SCHEMA_ID(N'System')))
 EXEC sys.[sp_executesql] @stmt = N'CREATE SCHEMA [System]'
GO
IF (OBJECT_ID(N'[System].ListEnums') IS NOT NULL)
 DROP FUNCTION [System].[ListEnums];
GO
CREATE FUNCTION [System].ListEnums ()
RETURNS TABLE 
AS
RETURN (
 SELECT [object_id] AS TableObjectId,
--   QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + N'.' + QUOTENAME(OBJECT_NAME([object_id])) AS [object_name],
   [System.Attributes.Enums.EnumValue] AS ValueColumnName,
   [System.Attributes.Enums.EnumKey] AS KeyColumnName,
   [System.Attributes.Enums.EnumName] AS EnumName
 FROM (
  SELECT [_p].[major_id] AS [object_id], [_p].[name], [_c].[name] AS value 
  FROM [sys].[extended_properties] _p
   INNER JOIN [sys].[columns] _c
    ON [_c].[object_id] = [_p].[major_id]
     AND [_c].[column_id] = [_p].[minor_id]
  WHERE 
   [_p].[class] = 1 /* Object or Column */ 
   AND [_p].[name] IN (N'System.Attributes.Enums.EnumValue', N'System.Attributes.Enums.EnumKey')
  UNION
   SELECT [_p].[major_id] AS [object_id], [_p].[name], CONVERT(sysname, [_p].[value]) AS value 
  FROM [sys].[extended_properties] _p
  WHERE 
   [_p].[class] = 1 /* Object or Column */ 
   AND [_p].[name] IN (N'System.Attributes.Enums.Enum', N'System.Attributes.Enums.EnumName')
       
 ) s
  PIVOT (MIN([value]) FOR [s].[name] IN ([System.Attributes.Enums.Enum], [System.Attributes.Enums.EnumValue], [System.Attributes.Enums.EnumKey], [System.Attributes.Enums.EnumName])) p
);
GO
IF (OBJECT_ID(N'[System].UndefineEnum') IS NOT NULL)
 DROP PROC [System].UndefineEnum
GO
CREATE PROCEDURE [System].UndefineEnum
 @TableName sysname
AS
SET NOCOUNT ON;

DECLARE 
 @TableId int = OBJECT_ID(@TableName),
 @TableSchemaName_normalized sysname = OBJECT_SCHEMA_NAME(OBJECT_ID(@TableName)),
 @TableName_normalized sysname = OBJECT_NAME(OBJECT_ID(@TableName)),
 @True bit = 1,
 @False bit = 0,
 @Value_ColumnName sysname,
 @Key_ColumnName sysname;

SELECT 
 @Value_ColumnName = [ValueColumnName],
 @Key_ColumnName = [KeyColumnName]
FROM [System].[ListEnums]() 
WHERE
 [TableObjectId] = @TableId


BEGIN TRAN;
BEGIN TRY;
 IF (@Value_ColumnName IS NOT NULL)
 BEGIN;
  EXEC sys.sp_dropextendedproperty 
   @name = N'System.Attributes.Enums.EnumValue',
   @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
   @level1type = N'Table',  @level1name = @TableName_normalized,
   @level2type = N'Column',  @level2name = @Value_ColumnName;
 END;

  IF (@Key_ColumnName IS NOT NULL)
 BEGIN;
  EXEC sys.sp_dropextendedproperty 
   @name = N'System.Attributes.Enums.EnumKey',
   @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
   @level1type = N'Table',  @level1name = @TableName_normalized,
   @level2type = N'Column',  @level2name = @Key_ColumnName;
 END;
 
 IF (EXISTS (SELECT * FROM sys.[fn_listextendedproperty] (N'System.Attributes.Enums.Enum', N'Schema', @TableSchemaName_normalized, N'Table', @TableName_normalized, DEFAULT, DEFAULT)))
 BEGIN;
  EXEC sys.sp_dropextendedproperty 
   @name = N'System.Attributes.Enums.Enum',
   @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
   @level1type = N'Table',  @level1name = @TableName_normalized;  
 END;
 IF (EXISTS (SELECT * FROM sys.[fn_listextendedproperty] (N'System.Attributes.Enums.EnumName', N'Schema', @TableSchemaName_normalized, N'Table', @TableName_normalized, DEFAULT, DEFAULT)))
 BEGIN;
  EXEC sys.sp_dropextendedproperty 
   @name = N'System.Attributes.Enums.EnumName',
   @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
   @level1type = N'Table',  @level1name = @TableName_normalized;
 END;


  COMMIT TRAN;
END TRY
BEGIN CATCH;
 DECLARE 
  @Error_Severity int,
  @Error_State int,
  @ExceptionMessage nvarchar(max);

  SELECT 
  @Error_State = ERROR_STATE (),
  @Error_Severity = ERROR_SEVERITY ();
 
 SET @ExceptionMessage = 'Failed to Undefine Enum';

  ROLLBACK TRAN; 
 RAISERROR (@ExceptionMessage, @Error_Severity, @Error_State);
 
 RETURN;
END CATCH;
GO
IF (OBJECT_ID(N'[System].DefineEnum') IS NOT NULL)
 DROP PROC [System].DefineEnum
GO
CREATE PROCEDURE [System].DefineEnum
 @TableName sysname,
 @EnumName sysname,
 @Value_ColumnName sysname,
 @Key_ColumnName sysname
AS
SET NOCOUNT ON;

DECLARE 
 @TableId int = OBJECT_ID(@TableName),
 @TableSchemaName_normalized sysname = OBJECT_SCHEMA_NAME(OBJECT_ID(@TableName)),
 @TableName_normalized sysname = OBJECT_NAME(OBJECT_ID(@TableName)),
 @True bit = 1,
 @False bit = 0,
 @ValidationErrorMessage nvarchar(1000),
 @CurrentStep tinyint;

IF (ISNULL(@EnumName, N'') IS NULL)
BEGIN
 SET @ValidationErrorMessage = N'Parameter @EnumName must be not NULL or Empty string.'; 
 RAISERROR (@ValidationErrorMessage, 16, 1);
 RETURN;
END

BEGIN TRAN;
BEGIN TRY;
 IF (EXISTS (SELECT * FROM [System].[ListEnums]() WHERE [TableObjectId] = @TableId))
  EXEC [System].[UndefineEnum] @TableName = @TableName;

  SET @CurrentStep = 1;
 EXEC sys.sp_addextendedproperty 
  @name = N'System.Attributes.Enums.EnumName',
  @value = @EnumName,
  @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
  @level1type = N'Table',  @level1name = @TableName_normalized;

  EXEC sys.sp_addextendedproperty 
  @name = N'System.Attributes.Enums.Enum',
  @value = @True,
  @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
  @level1type = N'Table',  @level1name = @TableName_normalized;

  SET @CurrentStep = 2;
 EXEC sys.sp_addextendedproperty 
  @name = N'System.Attributes.Enums.EnumValue',
  @value = @True,
  @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
  @level1type = N'Table',  @level1name = @TableName_normalized,
  @level2type = N'Column',  @level2name = @Value_ColumnName;

  SET @CurrentStep = 3;
 EXEC sys.sp_addextendedproperty 
  @name = N'System.Attributes.Enums.EnumKey',
  @value = @True,
  @level0type = N'Schema', @level0name = @TableSchemaName_normalized,
  @level1type = N'Table',  @level1name = @TableName_normalized,
  @level2type = N'Column',  @level2name = @Key_ColumnName;

  /* 
  Now when I know that all column and table names are correct, 
  make sure that the EnumValue and EnumKey columns each has a unique constraint or index 
 */

  IF (2 != (SELECT COUNT(*) 
 FROM sys.[indexes] i
  INNER JOIN sys.[index_columns] ic
   ON ic.[object_id] = i.[object_id]
    AND ic.[index_id] = i.[index_id]
  INNER JOIN (
   /* Limit to one-column indexes where this single column is not a covering column (however there should be none of these) */
   SELECT [object_id], [index_id]
   FROM sys.[index_columns] 
   WHERE 
    [is_included_column] = 0
   GROUP BY [object_id], [index_id]
   HAVING COUNT(*) = 1
  ) icx
   ON icx.[object_id] = ic.[object_id]
    AND icx.[index_id] = ic.[index_id]
  INNER JOIN sys.[columns] c
   ON c.[object_id] = ic.[object_id]
    AND c.[column_id] = ic.[column_id]
 WHERE 
  [is_unique] | [is_unique_constraint] = 1
  AND i.[object_id] = @TableId
  AND c.[name] IN (@Value_ColumnName, @Key_ColumnName)
 ))
 RAISERROR ('Both columns - key ([%s]) and value ([%s]) must be enforced by unique indexes', 16, 1, @Key_ColumnName, @Value_ColumnName);

  COMMIT TRAN;
END TRY
BEGIN CATCH;
 DECLARE 
  @Error_Severity int,
  @Error_State int,
  @ExceptionMessage nvarchar(max);

  SELECT 
  @Error_State = ERROR_STATE (),
  @Error_Severity = ERROR_SEVERITY ();

  IF (@CurrentStep = 1)
  SET @ValidationErrorMessage = N'Invalid object "' + ISNULL(@TableName, N'') + '".';

  IF (@CurrentStep = 2)
  SET @ValidationErrorMessage = N'Invalid Enum Value column "' + ISNULL(@Value_ColumnName, N'') + '".';

  IF (@CurrentStep = 3)
  SET @ValidationErrorMessage = N'Invalid Enum Title column "' + ISNULL(@Key_ColumnName, N'') + '".';
 
 ROLLBACK TRAN; 
 RAISERROR (@ValidationErrorMessage, @Error_Severity, @Error_State);
 
 RETURN;
END CATCH;
GO

Here is the code of the T4 template file. In the Visual studio right click on the project tree, add new item of a “Text Template” type to the project:

Then open the file and paste the following code into it.

<#@ template debug="true" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ output extension=".cs" #>
<# 
/****************************************************************************/
/*                              Variables            */
/****************************************************************************/

 string _namespace = "...";
 string _connectionString = "...";

/****************************************************************************/

Stack<EnumTable> enumTables = new Stack<EnumTable>();

using (SqlConnection _conn = new SqlConnection(_connectionString))
{
    _conn.Open();

    // First I will need to populate the list of the lookup tables
    using (SqlCommand _cmd = _conn.CreateCommand())
    {
        _cmd.CommandType = CommandType.StoredProcedure;
        _cmd.CommandText = "sp_executesql";

  _cmd.Parameters.Add(new SqlParameter("@stmt", "SELECT QUOTENAME(OBJECT_SCHEMA_NAME([TableObjectId])) + N'.' + QUOTENAME(OBJECT_NAME([TableObjectId])) AS [TableName], [EnumName], [ValueColumnName], [KeyColumnName] FROM [System].[ListEnums]()"));

        using (SqlDataReader _reader = _cmd.ExecuteReader())
        {
            while (_reader.Read())
            {
                enumTables.Push(new EnumTable((String)_reader["TableName"], (String)_reader["EnumName"], (String)_reader["ValueColumnName"], (String)_reader["KeyColumnName"]));
            }
        }
    }
#>
namespace <#= _namespace #> 
{
<#
    while (enumTables.Count > 0)
    {
        EnumTable _item = enumTables.Pop();
        using (SqlCommand _cmd = _conn.CreateCommand())
        {
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.CommandText = "sp_executesql";

            _cmd.Parameters.Add(new SqlParameter("@stmt", String.Format("SELECT {0}, {1} FROM {2} ORDER BY {0}",_item.ValueColumnName, _item.KeyColumnName,  _item.TableName)));

   using (SqlDataReader _reader = _cmd.ExecuteReader())
   {
    System.Text.StringBuilder _sb = new System.Text.StringBuilder();
    bool _firstLine = true;
    //_sb.Append("\t\t");
    while (_reader.Read())
    {
     if (_firstLine)
     {
      _firstLine = false;
     }
     else
     {
      _sb.Append("," + Environment.NewLine + "\t\t");
     }
     _sb.Append(String.Format("{0} = {1}", CleanString(_reader[_item.KeyColumnName].ToString()), _reader[_item.ValueColumnName]));
    }
#>
 public enum <#= _item.EnumName #>
 {
  <#= _sb.ToString() #>
 }
<#   }
        }
    }
}
#>
}
<#+
 class EnumTable 
 {
  private string tableName;
  public string TableName
  {
   get
   {
    return tableName;
   }
  }

  private string enumName;
  public string EnumName 
  {
   get 
   {
    return enumName;
   }
  }

  private string valueColumnName;
  public string ValueColumnName 
  {
   get 
   {
    return valueColumnName;
   }
  }

  private string keyColumnName;
  public string KeyColumnName 
  {
   get 
   {
    return keyColumnName;
   }
  }

  public EnumTable (string tableName, string enumName, string valueColumnName, string keyColumnName)
  {
   this.tableName = tableName;
   this.enumName = enumName;
   this.valueColumnName = valueColumnName;
   this.keyColumnName = keyColumnName;
  }
 }
#>
<#+
 
    public static string CleanString(string name)
    {
  Regex rgx = new Regex("[^a-zA-Z0-9_]");
  return rgx.Replace(name, "_");
    }

#>

Replace “…” with your own values, and then save the file and right click on it in the project tree and select “Run Custom Tool”

Now to define the lookup table I just need to make sure that both Key and Value columns of the table have unique constraint or index and run the stored procedure [System].[DefineEnum] providing the table name, Enum name, Key and Value column names, and then refreshing the generated class. Any changes will be reflected immediately.

Comments