Introduction
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.
Background
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.
C#:
enum TransactionType
{
Credit = 0
,
Debit = 1
}
VB:
Enum TransactionType
Credit = 0
Debit = 1
End Enum
These names can then be used in other statements:
C#:
if ( trans.Type == TransactionType.Credit ) ...
switch ( trans.Type )
{
case TransactionType.Credit : ...
case TransactionType.Debit : ...
}
VB:
If trans.Type = TransactionType.Credit ...
Select trans.Type
Case TransactionType.Credit ...
Case TransactionType.Debit ...
End Select
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:
Table: TransactionType
Code Meaning
---- -------
0 Credit
1 Debit
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...
Design Requirements
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
- name
- namespace
- 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):
1 ="text/xsl"="GenOmatic.enum.xsl#cs"
2 <GenOmatic>
3 <Datasource>
4 <Connector>PIEBALD.Data.OleDbDatabaseConnector.dll</Connector>
5 <ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Demo.mdb
</ConnectionString>
6 <Query>SELECT Meaning AS Name, Code AS [Value], Summary,
Description FROM EnumSample ORDER BY Code</Query>
7 <Format Column="Value" Format="'0x'X4" />
8 </Datasource>
9 <Result>
10 <Namespace>PIEBALD</Namespace>
11 <Class>EnumSampleTest</Class>
12 <Summary>An example of a C# enum produced by GenOmatic</Summary>
13 <Attribute>System.FlagsAttribute()</Attribute>
14 <AccessModifier>public</AccessModifier>
15 <Name>EnumSample</Name>
16 <Type>int</Type>
17 </Result>
18 <File>EnumSample.cs</File>
19 </GenOmatic>
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 GenOmatic.
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.)
-
<Format Column="Value">'0x'X4</Format>
(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 Columns and 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:
1 ="text/xsl"="GenOmatic.enum.xsl#vb"
7 <Format Column="Value" Format="'&H'X4" />
16 <Type>Integer</Type>
18 <File>EnumSample.vb</File>
The Stylesheets
GenOmatic.enum.xsl
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 Columns and Rows. The supplied stylesheets require Name and Value fields, and also support Summary and 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.
Implementation
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.
Main
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
The 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.
top, mid, and bot are variables to hold XmlElements; 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"> ...
att ---------------^
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 null. A 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" ;
}
else
{
att.Value = "false" ;
if
(
( 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 ] ) ;
}
else
{
bot.InnerText = dr [ col ].ToString() ;
}
}
}
}
Transform
The 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." ) ;
}
else
{
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 ) ;
}
else
{
System.IO.FileInfo fi = new System.IO.FileInfo ( temp.InnerText ) ;
string oldver = null ;
if ( fi.Exists )
{
using
(
System.IO.StreamReader sr
=
new System.IO.StreamReader ( fi.FullName )
)
{
oldver = sr.ReadToEnd() ;
sr.Close() ;
}
if ( ( oldver.Length == newver.Length ) && ( oldver == newver ) )
{
System.Console.WriteLine ( "The file is unchanged." ) ;
}
else
{
oldver = null ;
}
}
if ( oldver == null )
{
using
(
System.IO.TextWriter sw
=
new System.IO.StreamWriter ( fi.FullName )
)
{
sw.Write ( newver ) ;
sw.Close() ;
}
}
}
}
Bonus Features
The above code uses several library routines; here, I'll document the more interesting ones.
LoadXmlDocument
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
LoadXmlDocument
(
string Source
)
{
using
(
System.Xml.XmlReader reader =
System.Xml.XmlReader.Create
(
System.Environment.ExpandEnvironmentVariables ( Source )
,
XmlReaderSettings.Settings
)
)
{
System.Xml.XmlDocument result = new System.Xml.XmlDocument() ;
result.Load ( reader ) ;
reader.Close() ;
return ( result ) ;
}
}
private static class XmlReaderSettings
{
public static readonly System.Xml.XmlReaderSettings Settings ;
static XmlReaderSettings
(
)
{
Settings = new System.Xml.XmlReaderSettings() ;
System.Xml.XmlUrlResolver resolver = new System.Xml.XmlUrlResolver() ;
resolver.Credentials =
System.Net.CredentialCache.DefaultNetworkCredentials ;
Settings.XmlResolver = resolver ;
Settings.ValidationType = System.Xml.ValidationType.Schema ;
Settings.ProhibitDtd = false ;
return ;
}
}
GetStylesheet
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 null.
private static readonly System.Text.RegularExpressions.Regex HrefReg =
new System.Text.RegularExpressions.Regex
(
"href\\s*=\\s*(('(?'uri'[^'#]*)?(#(?'id'[^']*))?')" +
"|(\"(?'uri'[^\"#]*)?(#(?'id'[^\"]*))?\"))"
) ;
public static System.Xml.XmlElement GetStylesheet
(
System.Xml.XmlDocument Doc
)
{
System.Xml.XmlElement result = null ;
if ( Doc == null )
{
throw ( new System.ArgumentNullException
(
"Doc"
,
"You must provide a document"
) ) ;
}
if ( Doc.DocumentElement == null )
{
throw ( new System.ArgumentNullException
(
"Doc.DocumentElement"
,
"You must provide a document"
) ) ;
}
int nod = 0 ;
while ( nod < Doc.ChildNodes.Count )
{
if
(
( 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 ;
}
else
{
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 )
{
if
(
( sheets [ ele ].Attributes [ "id" ] != null )
&&
( sheets [ ele ].Attributes [ "id" ].Value ==
mat [ 0 ].Groups [ "id" ].Value )
)
{
result = (System.Xml.XmlElement) sheets [ ele ] ;
break ;
}
ele++ ;
}
}
else
{
if ( sheet.DocumentElement.Name == "xsl:stylesheet" )
{
result = sheet.DocumentElement ;
}
}
}
break ;
}
nod++ ;
}
return ( result ) ;
}
Transform
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
Transform
(
System.Xml.XmlDocument Doc
,
System.Xml.XmlElement Stylesheet
)
{
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() ;
xslt.Transform
(
Doc
,
null
,
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
del EnumSample.cs
del EnumSample.vb
del CsvSample.csv
GenOmatic EnumSample.cs.xml EnumSample.vb.xml CsvSample.xml
type EnumSample.cs
type EnumSample.vb
type CsvSample.csv
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:
GenOmatic ..\..\MyEnum.xml
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:
..\..\MyEnum.GenOmatic
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.
="text/xsl"="C:\Program files\Utilities\GenOmatic.enum.xsl#cs"
<Connector>C:\Program files\Utilities\PIEBALD.Data.OleDbDatabaseConnector.dll
</Connector>
<File>..\..\EnumSample.cs</File>
(Or wherever you want them.)
You can also use environment variables if that makes things easier:
="text/xsl"="%Programfiles%\Utilities\GenOmatic.enum.xsl#cs"
<Connector>%Programfiles%\Utilities\PIEBALD.Data.OleDbDatabaseConnector.dll
</Connector>
History
- 2009-02-15 - First submitted
- 2009-03-06 - Added new features of
ApplyFormat
