EnumExtension for Visual Studio

Table of contents

Last updated 3 May 2016.

EnumExtension - a Visual Studio extension for creating enums from database data

In more than one project and in several different jobs I, and at least some of my coworkers, have found it to be a good idea to define certain enums based on data from various database tables. Typically, the data in question are static or semi-static and represent integer values that a program needs to know about. Examples include tables containing language codes, currency codes, status codes - typically the sort of table whose name ends in Type or Category.

It is fairly easy to create database scripts that can produce the .Net code for defining an enum based on the data in the table. Nevertheless, I have done this enough times over and over, and so decided to go for a solution that is integrated in Visual Studio, in other words, a solution where the developer can create or update the enum(s) in question without leaving Visual Studio. This calls for a Visual Studio plugin - or, as it is called nowadays - an extension. An extension comes as a *.vsix installer file, which installs the extension dll globally to Visual Studio. Once installed, extensions can be managed in the Visual Studio extensions page, which is shown when you invoke the Tools/Extensions and Updates... menu command.

The extension that I came up with is developed and tested in Visual Studio 2015, and supports getting data from a SQL Server database to produce enums in C#.

In order to generate an enum from the data in a database table, a rather long list of things must be considered:

How to connect to the database - essentially a connection string is required.
The name of the table containing the data.
The name of the column containing the numeric value of the enum members.
The name of the column containing the names for the enum members.
The type of enum - byte, short, int etc.
Documentation of enum members in the form of XML comments.
Which table rows should be part of the enum generation?
Support for a special enum member to function as a logical null value.
Visibility of the generated enum type - public or internal.
How to handle invalid data.

It goes without saying that the table column for the enum member names should be textual, so should be type char, varchar or the like. Likewise, the column for the numeric values should be of an integer type, such as int or bigint. In order to allow the developer to specify values for all these variables, I opted to define an Attribute type to hold these parameters. This attribute is called DbEnumAttribute and is defined in a separate assembly. The idea is then, that the developer can indicate that an enum should be generated from database data by adorning her enum type with this attribute.

The DbEnumAttribute type comes with these properties, the first three of which are mandatory and must be specified in a constructor call:

Property Name	Type	Description
Table	String	Name of database table holding the data
NumericField	String	Name of database column with the numeric values
StringField	String	Name of database column with enum member names
DocumentationField	String	Name of database column containing enum member documentation (for XML comments)
NullName	String	Name for the special enum member which is not generated from the database data
NullValue	Long	Value for the above
MinValue	Long	Minimum value for the numeric enum values - used for selecting only a subset of table rows
UseMinValue	Bool	Flag that indicates whether to use the above MinValue
MaxValue	Long	Maximum value for the numeric enum values - used for selecting only a subset of table rows
UseMaxValue	Bool	Flag that indicates whether to use the above MaxValue
DbHostName	String	Name of database server
DbName	String	Name of database

More often than not, the solution to host the generated enum will already contain information on how to connect to a database, in the form of a connection string inside a configuration file. So, I implemented the extension to pick a connection string from a configuration file in the same or another project and then use that for connecting to the database. This means that, in the typical case, there will be no need to use the above-mentioned DbHostName and DbName properties. However, they can be used if no or the wrong database connection string is selected by the extension or for whatever other reason. If you do use these properties, the extension will connect to a database using the supplied information and SQL Server integrated security.

An example

Consider this table and data:

Now I can go ahead and define a corresponding enum type:

EnumTest.cs

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:

namespace EnumExtensionTest
{
    /// <summary>
    /// This is my documentation for
    /// the SomeTest enum type
    /// </summary>
    [EnumAttributes.DbEnum("EnumTest", 
        "Id", 
        "Name", 
        DocumentationField = "Description", 
        NullName = "None", 
        NullValue = 0, 
        DbHostName = "localhost", 
        DbName = "Market", 
        UseMaxValue = true, 
        MaxValue = 100)]
    public enum SomeTest
    {
    }
}

After saving my changes, I can right-click on the file in Solution Explorer and select Update enum. This command rewrites the entire code file, updating all enums that are adorned with a DbEnumAttribute.

Using the Update Enum command — Using the *Update Enum* command

This produces the below result:

EnumTest.cs

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

namespace EnumExtensionTest
{
    /// <summary>
    /// This is my documentation for
    /// the SomeTest enum type
    /// </summary>
    [EnumAttributes.DbEnum("EnumTest", "Id", "Name", DocumentationField = "Description", NullName = "None", 
        NullValue = 0, DbHostName = "localhost", DbName = "Market", UseMaxValue = true, MaxValue = 100)]
    public enum SomeTest : long
    {
        None = 0,
        /// <summary>Tiny scandinavian country</summary>
        Denmark = 2,
        /// <summary>Somewhere east</summary>
        Kurdistan = 4,
        /// <summary>Southern Europe</summary>
        Italy = 5,
        /// <summary>Far away</summary>
        Australia = 10,
        Thailand = 11,
    }
}

Notice that

The public qualifier for the enum type has been retained, and so has the xml comment.
The base type for the enum is long, in accordance with the bigint type for the corresponding column.
The enum contains the special member None with a value of 0.
The last row of the table is not included in the enum, because its id is higher than the upper limit of 100, as specified with the MaxValue property of the attribute.

Implementation

As outlined above, the extension is implemented using the Visual Studio Extensibility VSIX Project template:

The new project itself is quite empty and useless, so I added a new Custom Command item to the project:

Visual Studio Extensibility Custom Command item

This item template adds a number of files to the project, and contains code that causes a menu item to be added to the Visual Studio top-level menu at runtime. I changed that so that the menu item is added to the Solution Explorer context menu instead. This is accomplished through this configuration in the generated EnumCommandPackage.vsct xml file:

EnumCommandPackage.vsct (partial)

    <Groups>
      <Group guid="guidEnumCommandPackageCmdSet" id="MyMenuGroup" priority="0x0600">
        <Parent guid="guidSHLMainMenu" id="IDM_VS_CTXT_ITEMNODE"/>
      </Group>
    </Groups>

    <!--Buttons section. -->
    <!--This section defines the elements the user can interact with, like a menu command or a button
        or combo box in a toolbar. -->
    <Buttons>
      <Button guid="guidEnumCommandPackageCmdSet" id="EnumCommandId" priority="0x0100" type="Button">
        <Parent guid="guidEnumCommandPackageCmdSet" id="MyMenuGroup" />
        <Icon guid="guidImages" id="bmpPic1" />
        <CommandFlag>DefaultInvisible</CommandFlag>
        <CommandFlag>DynamicVisibility</CommandFlag>
        <Strings>
          <ButtonText>Update Enum</ButtonText>
        </Strings>
      </Button>
    </Buttons>

I specified the constant IDM_VS_CTXT_ITEMNODE to make my menu item appear on the Solution Explorer context menu in Visual Studio and also added the DefaultInvisible and DynamicVisibility so that the visibility of the menu item is determined at runtime.

When the developer opens a project in Visual Studio, the extension component is called, registering the command (i.e. the menu item) with this constructor code:

EnumCommand.cs (partial)

    private EnumCommand(Package package)
    {
        if (package == null) 
            throw new ArgumentNullException("package");

        this.package = package;

        OleMenuCommandService commandService = this.ServiceProvider.GetService(typeof(IMenuCommandService)) as OleMenuCommandService;
        if (commandService != null) {
            var menuCommandID = new CommandID(CommandSet, CommandId);
            var menuItem = new OleMenuCommand(MenuItemCallback, menuCommandID);
            menuItem.BeforeQueryStatus += menuCommand_BeforeQueryStatus;
            commandService.AddCommand(menuItem);
        }
    }

The above code registers the command, specifying a handler for when the menu is selected and also specifying a callback that will be called by Visual Studio to query whether the menu command should be shown - the menuCommand_BeforeQueryStatus method.

The remainder of the code can be inspected by downloading the source code:

Source code

Installer

2016 by Niels Hede Pedersen