
Introduction
When we write code, we often use enumerations; and enumeration is a set of named integer constants to replace ‘magic numbers’ with meaningful names. When we design relational databases, we use Look Up tables for the same purpose. A look up table is typically modeled with two fields such as ID and Code. Often, the same lookup information is needed in code as well. This results in duplication of this look up data as enumeration types in code. Though the look up information in the database is not as dynamic as other data, it does change from time to time. A prudent developer will update the enums whenever there is a change in the corresponding look up table. This is not a big deal if there are only a few rows, but it becomes cumbersome when there are lots of rows. This EnumGenerator tool eases that pain by auto-generating .NET code (both C# and VB.NET) from a look up table.
Connecting to Data Source
Microsoft packages a COM control called DataLinksClass
as part of the ‘MS Data Access Components’ library, to connect to a wide range of data sources. To use this control, you need to add a reference to the COM component ‘Microsoft OLE DB Service Component 1.0 Type Library’. Since this component returns the ADO (not ADO.NET) Connection
object on successful connection, you also need to add a reference to ‘Microsoft ActiveX Data Objects 2.8 Library’. The library version does not matter as long as the one you reference includes the ADO Connection
object. The following code shows how the connection is established. After successful connection, the connection string from the ADO object is used to create a new ASP.NET connection.
DataLinksClass dl = new DataLinksClass();
_Connection conn = dl.PromptNew() as _Connection;
Reading Database Schema
To generate the enum code, the table name and the column names need to be identified first. ASP.NET’s Connection
class provides a method ‘GetOleDbSchemaTable
’ for this purpose.
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, null, "TABLE" });
foreach (DataRow row in schemaTable.Rows)
{
tableList.Items.Add(row["TABLE_NAME"]);
}
To get all the columns for a table, set the first parameter of the method GetOleDbSchemaTable
as OleDbSchemaGuid.Columns
, and pass the table name as the last object in the second parameter’s object collection.
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Columns,
new object[] { null, null, tableName });
foreach (DataRow row in schemaTable.Rows)
{
fieldList.Items.Add(row["COLUMN_NAME"]);
}
Reading Data
The DataView
in the GUI displays look up data by executing a dynamic SQL on the table for the selected columns. There can be only two columns in the query, out of which one has to be of integer type. Often, this is the primary key. Since the enum
is based on two required fields (name and value), there is a validation in the code that checks for the selected fields to be two always. The GetDynamicSql
method combines these selected fields to build the SELECT
query.
private string GetDynamicSql()
{
StringBuilder sb = new StringBuilder();
sb.Append("Select ");
int i = 0;
foreach (string fieldName in fieldList.SelectedItems)
{
i++;
sb.Append("[");
sb.Append(fieldName);
sb.Append("]");
if (fieldList.SelectedItems.Count != i)
sb.Append(", ");
}
sb.Append(" from ");
sb.Append(tableName);
return sb.ToString();
}
Code Generation
Initially, I started with just C# code generation. Since there are only a few keyword differences between C# and VB.NET, I decided to add VB.NET code generation also later. The data view displays rows from the lookup table for review. If there is any data, code view displays the corresponding enum code. The following code shows how this is done. To use the generated enums in your code, just cut and paste the text from the code view into your C# or VB.NET project and make the necessary changes.
private void PopulateData(bool isVBDotNet)
{
try
{
if (fieldList.SelectedItems.Count == 2)
{
oleDbConn.Open();
string strSql = GetDynamicSql();
OleDbCommand command = new OleDbCommand(strSql, oleDbConn);
OleDbDataReader dataReader = command.ExecuteReader(
CommandBehavior.CloseConnection);
int nFields = dataReader.FieldCount;
if (nFields == 2)
{
dataView.Clear();
for (int i = 0; i < nFields; i++)
{
dataView.Columns.Add(dataReader.GetName(i), 100,
HorizontalAlignment.Left);
}
StringBuilder sb = new StringBuilder();
if (isVBDotNet)
{
sb.AppendLine("Public Enum " + tableName);
}
else
{
sb.AppendLine("public enum " + tableName);
sb.AppendLine("{");
}
while (dataReader.Read())
{
String[] subitems = new String[nFields];
int val;
if (Int32.TryParse(dataReader[0].ToString(), out val))
{
subitems[0] = dataReader[1].ToString();
subitems[1] = dataReader[0].ToString();
}
else if (Int32.TryParse(dataReader[1].ToString(), out val))
{
subitems[0] = dataReader[0].ToString();
subitems[1] = dataReader[1].ToString();
}
else
{
MessageBox.Show("There are no ID columns " +
"with integer values in the selection");
codeView.Text = "";
dataReader.Close();
return;
}
string eunumName = subitems[0];
if (eunumName.Length == 0)
eunumName = "None";
sb.Append("\t");
eunumName = eunumName.Replace(' ', '_');
eunumName = eunumName.Replace('/', '_');
eunumName = eunumName.Replace('.', '_');
sb.Append(eunumName + " = " + subitems[1]);
if (!isVBDotNet)
{
sb.Append(",");
}
sb.Append("\n");
ListViewItem item = new ListViewItem(subitems, -1);
dataView.Items.Add(item);
}
dataReader.Close();
if (isVBDotNet)
{
sb.AppendLine("End Enum");
}
else
{
sb.Remove(sb.Length - 2, 1);
sb.AppendLine("}");
}
codeView.Text = sb.ToString();
}
}
else
{
MessageBox.Show("Please select two(and only" +
" two fields) from the field list");
dataView.Clear();
codeView.Text = "";
}
}
finally
{
if ((oleDbConn != null) &&
(oleDbConn.State == ConnectionState.Open))
oleDbConn.Close();
}
}
Note
In some cases, the Name or Code column in the look up table may have spaces or other special characters as part of the text data. Since ‘enum
’ does not allow spaces or special characters, the code generator replaces those with an underscore.