This article describes a simple console application that can be used to generate the code for an enumeration from a database query. I've been meaning to write something like this for some time. As I worked on it, I realized that I could make it flexible enough to be used for other things as well, but the primary goal is the generation of small code files. I do not intend for this to be an introduction to enumerations, I will cover only as much as I feel is necessary to explain the motivation for this utility.
Also, I do not use VB, so please alert me to any errors in the VB statements.
By now, you should know what an enumeration is; in essence, it's a type-safe way of associating a name with a numeric value.
Credit = 0
Debit = 1
Credit = 0
Debit = 1
These names can then be used in other statements:
if ( trans.Type == TransactionType.Credit ) ...
switch ( trans.Type )
case TransactionType.Credit : ...
case TransactionType.Debit : ...
If trans.Type = TransactionType.Credit ...
Case TransactionType.Credit ...
Case TransactionType.Debit ...
The use of enumerations makes the meaning of the code clearer (and easier to maintain) than simply using the numeric values.
This applies to databases too. Many databases have been set up with single-character fields, perhaps with values 'C' to indicate credit and 'D' to indicate debit; others user numbers. Such codes can be cryptic. Generally, a database will have a table that provides referential integrity. Such a table can also provide translations of these codes.
I generally follow the pattern:
Having an enumeration that matches such a database table is quite common; however, the two disparate sets of information require synchronization. On the other hand, this data doesn't change very frequently, and the application would likely need to be updated to add handling of any added values, so the synchronization only needs to happen when the application is compiled.
What I have pondered for some time, particularly when someone has posted a question seeking an automated synchronization solution to this situation, is a small console application that will query a database and generate the code for the enumeration. Here I present what I have devised...
I decided that the utility requires the following features:
- The utility must get its instructions from a file that the user creates (rather than a bunch of parameters on the command line)
- Use SQL to get the data; allow the user to provide the query
- Not be tied to any particular database system; allow the user to specify
- Produce C# or VB.NET code as requested; support other languages, if possible
- Support attributes and XML documentation comments on the enumeration and its members
- Preferably allow the user the ability to specify everything about the enumeration
- access modifier
- enclosing class
- underlying type
- number format
As I worked on this, I decided I needed a system of plug-ins for database access; this article is the result. I also found I needed a way to allow the user to specify a more complex format for the values; this article is the result. The code from both those articles is included in the zip for this one.
I chose to use XML as the basis for the system. This article will also not explain XML; here's a good resource.
I considered having a system of plug-ins to provide the code generation as well, but quickly realized that XSLT would provide all that and more. And, if you think I'm going to explain XSLT, you're crazy; try here instead.
The Definition File
Everything about the file to produce (other than the data itself) is contained in an XML document. (The XSLT may be in the definition file, but will usually be in a separate file, as it is in the example.)
The zip file includes the following EnumSample.cs.xml file (line numbers added to aid the discussion that follows):
5 <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Demo.mdb
6 <Query>SELECT Meaning AS Name, Code AS [Value], Summary,
Description FROM EnumSample ORDER BY Code</Query>
7 <Format Column="Value" Format="'0x'X4" />
12 <Summary>An example of a C# enum produced by GenOmatic</Summary>
Line 1: The file should specify the stylesheet (XSLT) to use to produce the output. The GenOmatic.enum.xsl file is included in the zip file, it contains stylesheets to produce enumerations in C# and VB. If you don't specify a stylesheet, or if it can't be found, then no transform will be performed.
Lines 2 through 19: The root (document) element must be named
Lines 3 through 8: The file must contain one
Datasource element with the following children (in any order):
Line 4: The
Connector element specifies the database connector plug-in to use.
Line 5: The
ConnectionString element specifies the connection string to use. If you are concerned about plain text passwords, then I suggest you specify a user with read-only access to read only what is needed. Plus, this should only be on development systems, not deployed to customer sites.
Line 6: The
Query element contains, obviously, the query to execute to get the data for the enumeration. If you've read my Database Connector article, you know that you can actually specify a semi-colon-delimited list of statements. However, GenOmatic will only use the results of the first statement.
Line 7: You may specify formats to use for the retrieved data; this would generally be for the enumeration value. If you wish to do so, add a
Format element that specifies the column name and the format to use; the data in the column must be
IFormattable and non-null. See my ApplyFormat article for more information.
Update: Due to enhancements to the
ApplyFormat method, the
Format element may now specify the format in any of the following ways:
<Format Column="Value" Format="'0x'X4" />
(I now consider this deprecated.)
(I should have done it this way to begin with.)
<Format Column="Value"> <Text>0x</Text> <Formatter>X4</Formatter> </Format>
Lines 9 through 17: One
Result element may be present;
GenOmatic will create one if there is not,
GenOmatic doesn't use the contents directly.
GenOmatic will create a
Columns element and a
Rows element in the
Result element and populate them with the results of the query. (Any pre-existing
Rows elements will be replaced. Any other content is left unchanged.) When
GenOmatic completes, you may review the results to be sure you got the desired data. The contents of the
Result element are intended to be processed by the XSLT; I'll discuss that in a moment.
Line 18: You may specify the name of one file to receive the generated code. If you don't specify a file, then the generated code will be sent to the console; you may redirect or pipe it to somewhere else. If you do specify a file, and the file exists, the contents of the file and the newly generated code will be compared, and the file will only be written if the new code is different; this is an attempt to reduce needless rebuilds when the data has not changed. If the file doesn't exist, the newly generated code will be written to the file.
Note: The compare is just a simple
string compare; it doesn't seem terribly elegant, but I think hashing or CRCing them would be even less efficient. Please remember this if you consider producing very large files.
The zip also includes EnumSample.vb.xml, which produces the same enumeration, but in VB.NET syntax. It differs only in the following lines:
7 <Format Column="Value" Format="'&H'X4" />
The stylesheets supplied in GenOmatic.enum.xsl will transform the contents of the
Result element into C# or VB.NET enumerations.
Line 10: You must specify a namespace for the enumeration.
Line 11: You may specify a class to hold the enumeration. If you do, it will be marked
partial so it can be part of a class that exists in other files.
Line 12: You may specify a summary which will become an XML documentation comment for the enumeration.
Line 13: You may specify any number of attributes to attach to the enumeration.
Line 14: You may specify an access modifier for the enumeration; usually, you wouldn't unless you want it to be private to a class.
Line 15: You must provide a name for the enumeration.
Line 16: You may provide an underlying type for the enumeration.
The information for the enumeration members comes from the query results; the
Rows. The supplied stylesheets require
Value fields, and also support
Description fields, if supplied; the query will need to take these names into account.
I suggest you build
GenOmatic and run it on the supplied XML files to see the results for yourself.
You may alter the XSLT to support other things you may need for your enumeration; if you think others may want to know about it, please post a message.
GenOmatic.csv.xsl and CsvSample.xml
I have included these files in the zip as an example of other uses for the
GenOmatic. If all you need to do is perform a simple query and produce a small CSV file, this will do it. However, if the query returns a lot of data, you may want to seek another technique.
GenOmatic is a rather simple console application that reads the definition files described above, connects to the specified database, executes the query, transforms the results with the specified stylesheet, and updates the definition file.
The heart of the
Main method in GenOmatic.cs is:
foreach ( string filename in args )
System.Xml.XmlDocument doc = PIEBALD.Lib.LibXml.LoadXmlDocument ( filename ) ;
Process ( doc ) ;
PIEBALD.Lib.LibXml.WriteXmlDocument ( doc , filename ) ;
Transform ( doc ) ;
Process method does all the work of connecting to the database, executing the query, and updating the document with the results. I won't show the whole method, but here is the
foreach loop that populates the
Rows element with the data.
bot are variables to hold
att is a variable to hold an
XmlAttribute; perhaps this diagram will help you visualize how the variables relate to the XML structure.
top --> <Rows>
mid ----> <Row>
bot ------> <Value Null="false"> ...
What this code is doing is appending a new
Row element to hold the current row of data, then iterating the columns of the
DataTable, appending an element for the value of each field. An attribute named
Null is used to indicate whether or not the value is
Dictionary will have been populated with the contents of any
Format elements, so if the value of a field is
IFormattable and its column has a
Format, that format will be applied to the value.
foreach ( System.Data.DataRow dr in dt [ 0 ].Rows )
top.AppendChild ( mid = Doc.CreateElement ( "Row" ) ) ;
for ( int col = 0 ; col < dt [ 0 ].Columns.Count ; col++ )
mid.AppendChild ( bot = Doc.CreateElement (
dt [ 0 ].Columns [ col ].ColumnName ) ) ;
bot.Attributes.Append ( att = Doc.CreateAttribute ( "Null" ) ) ;
if ( dr [ col ] == System.DBNull.Value )
att.Value = "true" ;
att.Value = "false" ;
( dr [ col ] is System.IFormattable )
formats.ContainsKey ( dt [ 0 ].Columns [ col ].ColumnName )
bot.InnerText = ((System.IFormattable) dr [ col ]).ApplyFormat
( formats [ dt [ 0 ].Columns [ col ].ColumnName ] ) ;
bot.InnerText = dr [ col ].ToString() ;
Transform method retrieves the stylesheet (if any) and performs the transform. The result of the transform is stored in a
string rather than directly to a file. After that, a determination of whether or not to save to a file is made.
System.Xml.XmlElement stylesheet = PIEBALD.Lib.LibXsl.GetStylesheet ( Doc ) ;
if ( stylesheet == null )
System.Console.WriteLine ( "No stylesheet specified." ) ;
string newver =
PIEBALD.Lib.LibXsl.Transform ( Doc , stylesheet ).ToString() ;
System.Xml.XmlElement temp =
(System.Xml.XmlElement) Doc.SelectSingleNode ( "GenOmatic/File" ) ;
if ( ( temp == null ) || ( temp.InnerText.Length == 0 ) )
System.Console.Write ( newver ) ;
System.IO.FileInfo fi = new System.IO.FileInfo ( temp.InnerText ) ;
string oldver = null ;
if ( fi.Exists )
new System.IO.StreamReader ( fi.FullName )
oldver = sr.ReadToEnd() ;
if ( ( oldver.Length == newver.Length ) && ( oldver == newver ) )
System.Console.WriteLine ( "The file is unchanged." ) ;
oldver = null ;
if ( oldver == null )
new System.IO.StreamWriter ( fi.FullName )
sw.Write ( newver ) ;
The above code uses several library routines; here, I'll document the more interesting ones.
This method is actually quite simple; it creates an
XmlReader with my preferred settings, instantiates an
XmlDocument, and loads the document. There are overloads that take other sources.
public static System.Xml.XmlDocument
System.Xml.XmlReader reader =
System.Environment.ExpandEnvironmentVariables ( Source )
System.Xml.XmlDocument result = new System.Xml.XmlDocument() ;
result.Load ( reader ) ;
return ( result ) ;
private static class XmlReaderSettings
public static readonly System.Xml.XmlReaderSettings Settings ;
Settings = new System.Xml.XmlReaderSettings() ;
System.Xml.XmlUrlResolver resolver = new System.Xml.XmlUrlResolver() ;
Settings.XmlResolver = resolver ;
Settings.ValidationType = System.Xml.ValidationType.Schema ;
Settings.ProhibitDtd = false ;
This method is a little more complex; it first tries to find a stylesheet
ProcessingInstruction in the provided
XmlDocument; once it finds one, it tries to parse its value with the Regular Expression. If the stylesheet is in another file, an
XmlDocument will be loaded from that file; otherwise, the stylesheet is assumed to be in the current
XmlDocument. Then, if a stylesheet with a particular ID is requested, the
XmlDocument will be searched for a stylesheet with that ID. If no stylesheet is requested or the requested stylesheet is not found, this method will return
private static readonly System.Text.RegularExpressions.Regex HrefReg =
public static System.Xml.XmlElement GetStylesheet
System.Xml.XmlElement result = null ;
if ( Doc == null )
throw ( new System.ArgumentNullException
"You must provide a document"
) ) ;
if ( Doc.DocumentElement == null )
throw ( new System.ArgumentNullException
"You must provide a document"
) ) ;
int nod = 0 ;
while ( nod < Doc.ChildNodes.Count )
( Doc.ChildNodes [ nod ] is System.Xml.XmlProcessingInstruction )
( Doc.ChildNodes [ nod ].Name == "xml-stylesheet" )
System.Text.RegularExpressions.MatchCollection mat ;
if ( ( mat = HrefReg.Matches ( Doc.ChildNodes [ nod ].Value ) ).Count == 1 )
System.Xml.XmlDocument sheet ;
if ( mat [ 0 ].Groups [ "uri" ].Value == "" )
sheet = Doc ;
sheet = PIEBALD.Lib.LibXml.LoadXmlDocument
mat [ 0 ].Groups [ "uri" ].Value
if ( mat [ 0 ].Groups [ "id" ].Value != "" )
System.Xml.XmlNodeList sheets =
sheet.GetElementsByTagName ( "xsl:stylesheet" ) ;
int ele = 0 ;
while ( ele < sheets.Count )
( sheets [ ele ].Attributes [ "id" ] != null )
( sheets [ ele ].Attributes [ "id" ].Value ==
mat [ 0 ].Groups [ "id" ].Value )
result = (System.Xml.XmlElement) sheets [ ele ] ;
if ( sheet.DocumentElement.Name == "xsl:stylesheet" )
result = sheet.DocumentElement ;
return ( result ) ;
This method uses the provided stylesheet to perform an XSLT transform on the provided
XmlDocument. The stylesheet must be passed in as an
XmlElement because it may be just one element in a larger
XmlDocument, perhaps even the
XmlDocument that is being passed in to be transformed. There are overloads that take other sources.
public static System.Text.StringBuilder
if ( Doc == null )
throw ( new System.ArgumentNullException ( "Doc" ,
"You must provide a document" ) ) ;
if ( Stylesheet == null )
throw ( new System.ArgumentNullException ( "Stylesheet" ,
"You must provide a stylesheet" ) ) ;
System.Xml.Xsl.XslCompiledTransform xslt =
new System.Xml.Xsl.XslCompiledTransform() ;
xslt.Load ( Stylesheet ) ;
System.Text.StringBuilder result = new System.Text.StringBuilder() ;
new System.IO.StringWriter ( result )
return ( result ) ;
Using the Code
As with the other articles I mentioned, I am using a build.bat to build and test this code. I have added the following lines:
@rem Build GenOmatic
csc GenOmatic.cs Lib*.cs /r:DatabaseConnector.dll
@rem Test the GenOmatic
GenOmatic EnumSample.cs.xml EnumSample.vb.xml CsvSample.xml
Using the Utility from Visual Studio
I designed this utility with the idea that it would be used in a pre-build event for a Visual Studio project. Open the project's property page, select the Build Events tab, there is a box for pre-build command lines. One of the shortcomings of that is that (by default) pre-build events run with the project's release\bin or debug\bin subdirectory as the working directory; you'll need to take that into account. The following command is an example of how a GenOmatic file in the project's directory may be referenced in a pre-build event:
This assumes that GenOmatic is accessible via the Path (the environment variable), but I may be the last person using that in Windows. If you don't place GenOmatic in a directory listed in the Path, you can fully specify where it is:
"C:\Program files\Utilities\GenOmatic" ..\..\MyEnum.xml
(Or wherever you have it.)
A somewhat smarter and more Windows-centric way is to register a file type. You will need to pick an extension (perhaps "GenOmatic", it just has to be unique) to use for the XML files. In Windows Explorer | Tools | Folder Options | File Types: click New, enter the extension you chose, click OK, click Change..., click "Select the program from a list", click OK, click Browse..., locate and select GenOmatic.exe, click OK, click Close.
Now, the pre-build event can be simplified as:
But wait! That's not all! That only tells the system where GenOmatic and the XML file are. The examples presented above assume that all the files are in one directory, which won't likely be true in the actual use. You need to tell GenOmatic where to find the database accessor plug-ins and stylesheets and where to put the output file. That's all handled in the XML file.
(Or wherever you want them.)
You can also use environment variables if that makes things easier:
- 2009-02-15 - First submitted
- 2009-03-06 - Added new features of