Enum Code Generator - Generating enum code automatically from database look up tables

• Download demo - 36.1 Kb
• Download source - 12.4 Kb

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.

//Get table names
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(
                        OleDbSchemaGuid.Tables, 
                        new object[] { null, null, null, "TABLE" });

// Display the table name from each row in the schema
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.

//Get column names
DataTable schemaTable = oleDbConn.GetOleDbSchemaTable(
                        OleDbSchemaGuid.Columns, 
                        new object[] { null, null, tableName });

// Display the table name from each row in the schema
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()
{
    // create dynamic sql
    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();
            //Get table names
            OleDbCommand command = new OleDbCommand(strSql, oleDbConn);
            OleDbDataReader dataReader = command.ExecuteReader(
                            CommandBehavior.CloseConnection);

            int nFields = dataReader.FieldCount;

            if (nFields == 2)
            {
                // Setup the columns in the listview
                // using the fields in the table
                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("{");
                }

                // Fill the rows in the listview
                // using the data in the rows
                while (dataReader.Read())
                {
                    // Create an array of subitems for quick insertion
                    // The subitems will be all fields in the row except for 
                    // the first field
                    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");

                    // Insert a new item into the listview,
                    // and add the subitems at 
                    // the same time. The item will
                    // be the first field in the row
                    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.