Wednesday, October 17, 2012

SubSonic 3 Automated Enum Generation

As I've said before, I love the SubSonic Project for generating data access classes from my database.

Probably 12 months ago, I wrote an automated enum generator for it, since that was one feature that was missing,
It's a drop-in - it doesn't affect any other functionality or require modifications to existing code.
I thought that it had been incorporated into the SubSonic 3 Templates trunk, but whoops - it hasn't !
So the two template files ( and Enums.ttinclude) can be downloaded at my GitHub branch.

I don't think there's any point rewriting the comments at the start of regarding usage, so here they are:

Enum Generator Features
 - auto generates enum values from the row data in the tables
 - will generate regular enums for integer values or an 'enum-like' struct for string values
 - a single setting will generate enums for all lookup tables with a standard prefix, with default enum
   name based on the table name
 - the enum name, and the value and description columns used to create the enum can be customised per-table
 - multiple enums can be generated from the same table 
 - a MULTI mode allows automated enum generation from a MUCK (Massively Unified Code-Key) general purpose lookup table
   (BTW MUCK tables are NOT a good idea, but in the tradition of SubSonic, we let you make the choice)

Typical 'integer valued' table:

  CategoryID  CategoryName   
  int         nvarchar(50)   
  ----------- ---------------
  1           Beverages       
  2           Condiments      
  3           Confections     
  4           Dairy Products  
  5           Grains/Cereals  

Typical 'string valued' table:

  State_Str     State
  nvarchar(10)  nvarchar(50)
  ------------  ----------------------------
  ACT           Australian Capital Territory
  NSW           New South Wales
  NT            Northern Territory
  QLD           Queensland
  SA            South Australia
  TAS           Tasmania
  VIC           Victoria
  WA            Western Australia

Typical 'MUCK' table:

  LookupKey                                          LookupVal    LookupDescLong
  nvarchar(50)                                       nvarchar(20) nvarchar(100)
  -------------------------------------------------- ----------   --------------------------
  AssignStatusStr                                    F            Fully
  AssignStatusStr                                    P            Partly
  AssignStatusStr                                    U            Not
  AssignStatusStr                                    X            n/a
  BatchAutoGenModeStr                                E            Assign to existing batch
  BatchAutoGenModeStr                                N            Make new batch
  BatchAutoGenModeStr                                X            Do not assign to batch
  BatchPackStatusStr                                 C            Cancelled
  BatchPackStatusStr                                 L            Locked
  BatchPackStatusStr                                 P            Packing
  BatchPackStatusStr                                 T            Complete

EnumSettings contains a list of enum generation settings.
NOTE: enum Generation uses CleanUp() from Settings.ttinclude to sanitize names so make sure it's up to scratch

FORMAT:   [table name regexp]:[enum name]:[id column name]:[descr column name]:[sql where clause]

 - all params are optional except the first. if omitting an earlier parameter but using a later parameter then 
   still include the ':' as a placeholder

  [table name regexp] = regular expression matching the table name.  Can be just the table name but is advisable 
      to use the end and/or start RegEx markers.

  [enum name] = the name to use for the enum (default=table name + 'Enum')
   - if the enum name is in the format MULTI=[KeyColName] then the key column values will be used to name 
     the enum and to match the blocks of row values to be used for each enum

  [id column name] = the name of the column to use for the enum value (default=PK col)

  [descr column name] = the name of the column to use for the enum description (default=first text col)

  [sql where clause] = where clause to use when retrieving the enum values (default=empty)

string[] EnumSettings = new string[]{
 - generates enums from all table in the database starting with 'lk_' using default names and columns

 "tblLookupVals:AssignStatusEnumStr:LookupVal:LookupDescLong:where [LookupKey]='AssignStatusStr'",
 - generates the named enum from the designated table, using the designated columns and WHERE

 - generates multiple enums from the 'tblLookupVals' MUCK table; one enum for each block of values in column 'LookupKey'

 - generates an enum of 'short' state values only

 - generates an enum of 'long' state values only

Samples of generated enums are shown below. Note that the tool can generate string 'enums', which are a struct of a type I put together after browsing the many proposals on StackOverflow.
namespace MyNamespace { 

 // string enum derived from database rows: libm_ColType.DotNetSystemTypeName, libm_ColType.DotNetSystemTypeName
 public struct DataTypeEnum {
  public const string Int64 = "Int64 ";
  public const string Boolean = "Boolean ";
  public const string Byte = "Byte ";
  public const string Decimal = "Decimal ";
  public const string DateTime = "DateTime ";
  public const string Double = "Double ";

  public string Value { get; set; }
  public override string ToString() { return Value; }

 // enum derived from database rows: libm_DataView.ViewDescr, libm_DataView.DataViewID
 public enum DataViewEnum {
  Table_Default = -81,
  Default = -18,
  None = -16
The string 'enum' can be used in two ways. You can declare your variable as string, and simply use the enum const values:
string s = DataTypeEnum.Decimal;
Or you can declare your variable as as DataTypeEnum, and use the .Value method to get and set values. This doesn't actually restrict the values used to those in the enum unless you write some more code (you can add it to the template so it autogenerates), but it conforms to the style of enums, and if you don't ever assign anything but the matching enum constant values, it's eqivalent to an enum.
DataTypeEnum x;
x.Value = DataTypeEnum.Decimal;
string x1 = x.Value;
string x2 = x.ToString();
Up to you if it's important enough to add that feature. NOTE: I've also added automatic enum generation to SubSonic 2. I'm an admin of that GitHub project, so the enum generation IS part of the main code branch.

No comments:

Post a Comment