C# 根据数据库查找表中的值自动创建枚举？

Automatically create an Enum based on values in a database lookup table?

提问by billfredtom

How do I automatically create an enum and subsequently use its values in C# based on values in a database lookup table (using enterprise library data layer)?

如何根据数据库查找表中的值（使用企业库数据层）自动创建枚举并随后在 C# 中使用其值？

For example, If I add a new lookup value in the database, I don't want to have to manually add the extra static enum value declaration in code - I'd like to keep the enum in sync with the database.

例如，如果我在数据库中添加一个新的查找值，我不想在代码中手动添加额外的静态枚举值声明 - 我想让枚举与数据库保持同步。

Is there such a thing as this?

有这样的事情吗？

---

---

I don't want to create a code generated static enum (as per The Code Projectarticle Enum Code Generator - Generating enum code automatically from database look up tables) and would prefer it to be completely automatic.

我不想创建代码生成的静态枚举（根据代码项目文章枚举代​​码生成器 - 从数据库查找表自动生成枚举代码），并且希望它是完全自动的。

回答by Autodidact

Does it have to be an actual enum? How about using a Dictionary<string,int>instead?

它必须是一个实际的枚举吗？用 aDictionary<string,int>代替怎么样？

for example

例如

Dictionary<string, int> MyEnum = new Dictionary(){{"One", 1}, {"Two", 2}};
Console.WriteLine(MyEnum["One"]);

回答by Marcus L

Enums must be specified at compile time, you can't dynamically add enums during run-time - and why would you, there would be no use/reference to them in the code?

枚举必须在编译时指定，您不能在运行时动态添加枚举 - 为什么在代码中没有使用/引用它们？

From Professional C# 2008:

来自专业 C# 2008：

The real power of enums in C# is that behind the scenes they are instantiated as structs derived from the base class, System.Enum . This means it is possible to call methods against them to perform some useful tasks. Note that because of the way the .NET Framework is implemented there is no performance loss associated with treating the enums syntactically as structs. In practice, once your code is compiled, enums will exist as primitive types, just like int and float .

C# 中枚举的真正威力在于，它们在幕后被实例化为派生自基类 System.Enum 的结构。这意味着可以针对它们调用方法来执行一些有用的任务。请注意，由于 .NET Framework 的实现方式，在语法上将枚举视为结构不会造成性能损失。实际上，一旦您的代码被编译，枚举将作为原始类型存在，就像 int 和 float 一样。

So, I'm not sure you can use Enums the way you want to.

因此，我不确定您是否可以按照自己的方式使用 Enum。

回答by Binary Worrier

You want System.Web.Compilation.BuildProvider

你想要 System.Web.Compilation.BuildProvider

I also doubt the wisdom of doing this, but then there maybe a good use case that I can't think of.

我也怀疑这样做是否明智，但是可能有一个我想不到的好用例。

What you're looking for are Build Providersi.e. System.Web.Compilation.BuildProvider

您正在寻找的是构建提供程序，即 System.Web.Compilation.BuildProvider

They're used veryeffectively by SubSonic, you can download the source and see how they use them, you won't need anything half as intricate as what they're doing.

它们被SubSonic非常有效地使用，您可以下载源代码并查看它们是如何使用它们的，您不需要任何像他们正在做的事情那么复杂的东西。

Hope this helps.

希望这可以帮助。

回答by Sani Singh Huttunen

Let's say you have the following in your DB:

假设您的数据库中有以下内容：

table enums
-----------------
| id | name     |
-----------------
| 0  | MyEnum   |
| 1  | YourEnum |
-----------------

table enum_values
----------------------------------
| id | enums_id | value | key    |
----------------------------------
| 0  | 0        | 0     | Apple  |
| 1  | 0        | 1     | Banana |
| 2  | 0        | 2     | Pear   |
| 3  | 0        | 3     | Cherry |
| 4  | 1        | 0     | Red    |
| 5  | 1        | 1     | Green  |
| 6  | 1        | 2     | Yellow |
----------------------------------

Construct a select to get the values you need:

构造一个选择以获取您需要的值：

select * from enums e inner join enum_values ev on ev.enums_id=e.id where e.id=0

Construct the source code for the enum and you'll get something like:

构建枚举的源代码，你会得到类似的东西：

String enumSourceCode = "enum " + enumName + "{" + enumKey1 + "=" enumValue1 + "," + enumKey2 + ... + "}";

(obviously this is constructed in a loop of some kind.)

（显然这是在某种循环中构建的。）

Then comes the fun part, Compiling your enum and using it:

然后是有趣的部分，编译您的枚举并使用它：

CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
CompilerParameters cs = new CompilerParameters();
cp.GenerateInMemory = True;

CompilerResult result = provider.CompileAssemblyFromSource(cp, enumSourceCode);

Type enumType = result.CompiledAssembly.GetType(enumName);

Now you have the type compiled and ready for use.
To get a enum value stored in the DB you can use:

现在您已经编译好类型并可以使用了。
要获取存储在数据库中的枚举值，您可以使用：

[Enum].Parse(enumType, value);

where value can be either the integer value (0, 1, etc.) or the enum text/key (Apple, Banana, etc.)

其中 value 可以是整数值（0、1 等）或枚举文本/键（Apple、Banana 等）

回答by Pandincus

I'm doing this exact thing, but you needto do some kind of code generation for this to work.

我正在做这件事，但你需要做一些代码生成才能让它工作。

In my solution, I added a project "EnumeratedTypes". This is a console application which gets all of the values from the database and constructs the enums from them. Then it saves all of the enums to an assembly.

在我的解决方案中，我添加了一个项目“EnumeratedTypes”。这是一个控制台应用程序，它从数据库中获取所有值并从中构造枚举。然后它将所有枚举保存到一个程序集中。

The enum generation code is like this:

枚举生成代码是这样的：

// Get the current application domain for the current thread
AppDomain currentDomain = AppDomain.CurrentDomain;

// Create a dynamic assembly in the current application domain,
// and allow it to be executed and saved to disk.
AssemblyName name = new AssemblyName("MyEnums");
AssemblyBuilder assemblyBuilder = currentDomain.DefineDynamicAssembly(name,
                                      AssemblyBuilderAccess.RunAndSave);

// Define a dynamic module in "MyEnums" assembly.
// For a single-module assembly, the module has the same name as the assembly.
ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule(name.Name,
                                  name.Name + ".dll");

// Define a public enumeration with the name "MyEnum" and an underlying type of Integer.
EnumBuilder myEnum = moduleBuilder.DefineEnum("EnumeratedTypes.MyEnum",
                         TypeAttributes.Public, typeof(int));

// Get data from database
MyDataAdapter someAdapter = new MyDataAdapter();
MyDataSet.MyDataTable myData = myDataAdapter.GetMyData();

foreach (MyDataSet.MyDataRow row in myData.Rows)
{
    myEnum.DefineLiteral(row.Name, row.Key);
}

// Create the enum
myEnum.CreateType();

// Finally, save the assembly
assemblyBuilder.Save(name.Name + ".dll");

My other projects in the solution reference this generated assembly. As a result, I can then use the dynamic enums in code, complete with intellisense.

我在解决方案中的其他项目引用了这个生成的程序集。因此，我可以在代码中使用动态枚举，完成智能感知。

Then, I added a post-build event so that after this "EnumeratedTypes" project is built, it runs itself and generates the "MyEnums.dll" file.

然后，我添加了一个构建后事件，以便在构建这个“EnumeratedTypes”项目后，它会自行运行并生成“MyEnums.dll”文件。

By the way, it helps to change the build orderof your project so that "EnumeratedTypes" is built first. Otherwise, once you start using your dynamically generated .dll, you won't be able to do a build if the .dll ever gets deleted. (Chicken and egg kind of problem -- your other projects in the solution need this .dll to build properly, and you can't create the .dll until you build your solution...)

顺便说一句，它有助于更​​改项目的构建顺序，以便首先构建“EnumeratedTypes”。否则，一旦您开始使用动态生成的 .dll，如果 .dll 被删除，您将无法进行构建。（鸡和蛋的问题 - 解决方案中的其他项目需要此 .dll 才能正确构建，并且在构建解决方案之前您无法创建 .dll...）

I got most of the above code from this msdn article.

我从这篇 msdn 文章中得到了上面的大部分代码。

Hope this helps!

希望这可以帮助！

回答by Yordan Georgiev

Just showing the answerof Pandincus with "of the shelf" code and some explanation: You need two solutions for this example ( I know it could be done via one also ; ), let the advanced students present it ...

只是用“架子上的”代码和一些解释来展示Pandincus的 答案：对于这个例子，你需要两个解决方案（我知道也可以通过一个解决方案来完成；），让高级学生展示它......

So here is the DDL SQL for the table :

所以这是表的 DDL SQL：

USE [ocms_dev]
    GO

CREATE TABLE [dbo].[Role](
    [RoleId] [int] IDENTITY(1,1) NOT NULL,
    [RoleName] [varchar](50) NULL
) ON [PRIMARY]

So here is the console program producing the dll:

所以这里是生成 dll 的控制台程序：

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Reflection.Emit;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace DynamicEnums
{
    class EnumCreator
    {
        // after running for first time rename this method to Main1
        static void Main ()
        {
            string strAssemblyName = "MyEnums";
            bool flagFileExists = System.IO.File.Exists (
                   AppDomain.CurrentDomain.SetupInformation.ApplicationBase + 
                   strAssemblyName + ".dll"
            );

            // Get the current application domain for the current thread
            AppDomain currentDomain = AppDomain.CurrentDomain;

            // Create a dynamic assembly in the current application domain,
            // and allow it to be executed and saved to disk.
            AssemblyName name = new AssemblyName ( strAssemblyName );
            AssemblyBuilder assemblyBuilder = 
                    currentDomain.DefineDynamicAssembly ( name,
                            AssemblyBuilderAccess.RunAndSave );

            // Define a dynamic module in "MyEnums" assembly.
            // For a single-module assembly, the module has the same name as
            // the assembly.
            ModuleBuilder moduleBuilder = assemblyBuilder.DefineDynamicModule (
                    name.Name, name.Name + ".dll" );

            // Define a public enumeration with the name "MyEnum" and
            // an underlying type of Integer.
            EnumBuilder myEnum = moduleBuilder.DefineEnum (
                    "EnumeratedTypes.MyEnum",
                    TypeAttributes.Public,
                    typeof ( int )
            );

            #region GetTheDataFromTheDatabase
            DataTable tableData = new DataTable ( "enumSourceDataTable" );

            string connectionString = "Integrated Security=SSPI;Persist " +
                    "Security Info=False;Initial Catalog=ocms_dev;Data " +
                    "Source=ysg";

            using (SqlConnection connection = 
                    new SqlConnection ( connectionString ))
            {

                SqlCommand command = connection.CreateCommand ();
                command.CommandText = string.Format ( "SELECT [RoleId], " + 
                        "[RoleName] FROM [ocms_dev].[dbo].[Role]" );

                Console.WriteLine ( "command.CommandText is " + 
                        command.CommandText );

                connection.Open ();
                tableData.Load ( command.ExecuteReader ( 
                        CommandBehavior.CloseConnection
                ) );
            } //eof using

            foreach (DataRow dr in tableData.Rows)
            {
                myEnum.DefineLiteral ( dr[1].ToString (),
                        Convert.ToInt32 ( dr[0].ToString () ) );
            }
            #endregion GetTheDataFromTheDatabase

            // Create the enum
            myEnum.CreateType ();

            // Finally, save the assembly
            assemblyBuilder.Save ( name.Name + ".dll" );
        } //eof Main 
    } //eof Program
} //eof namespace 

Here is the Console programming printing the output ( remember that it has to reference the dll ). Let the advance students present the solution for combining everything in one solution with dynamic loading and checking if there is already build dll.

这是打印输出的控制台编程（记住它必须引用 dll ）。让高级学员展示将所有内容与动态加载合并为一个解决方案的解决方案，并检查是否已经构建了 dll。

// add the reference to the newly generated dll
use MyEnums ; 

class Program
{
    static void Main ()
    {
        Array values = Enum.GetValues ( typeof ( EnumeratedTypes.MyEnum ) );

        foreach (EnumeratedTypes.MyEnum val in values)
        {
            Console.WriteLine ( String.Format ( "{0}: {1}",
                    Enum.GetName ( typeof ( EnumeratedTypes.MyEnum ), val ),
                    val ) );
        }

        Console.WriteLine ( "Hit enter to exit " );
        Console.ReadLine ();
    } //eof Main 
} //eof Program

回答by Yordan Georgiev

You could use CodeSmith to generate something like this:

您可以使用 CodeSmith 生成如下内容：

http://www.csharping.com/PermaLink,guid,cef1b637-7d37-4691-8e49-138cbf1d51e9.aspx

http://www.csharping.com/PermaLink,guid,cef1b637-7d37-4691-8e49-138cbf1d51e9.aspx

回答by Runeborg

I don't think there is a good way of doing what you want. And if you think about it I don't think this is what you really want.

我认为没有什么好的方法可以做你想做的事。如果你仔细想想，我认为这不是你真正想要的。

If you would have a dynamic enum, it also means you have to feed it with a dynamic value when you reference it. Maybe with a lot of magic you could achieve some sort of IntelliSensethat would take care of this and generate an enum for you in a DLL file. But consider the amount of work it would take, how uneffective it would be to access the database to fetch IntelliSense information as well as the nightmare of version controlling the generated DLL file.

如果您有一个动态枚举，这也意味着您必须在引用它时为其提供动态值。也许用很多魔法，你可以实现某种智能感知，它可以解决这个问题，并在 DLL 文件中为你生成一个枚举。但考虑到它需要的工作量、访问数据库以获取 IntelliSense 信息的效率有多低，以及控制生成的 DLL 文件的版本的噩梦。

If you really don't want to manually add the enum values (you'll have to add them to the database anyway) use a code generation tool instead, for example T4templates. Right click+run and you got your enum statically defined in code and you get all the benefits of using enums.

如果您真的不想手动添加枚举值（无论如何您都必须将它们添加到数据库中），请改用代码生成工具，例如T4模板。右键单击+运行，您将在代码中静态定义枚举，并获得使用枚举的所有好处。

回答by Martin

Using dynamic enums is bad no matter which way. You will have to go through the trouble of "duplicating" the data to ensure clear and easy code easy to maintain in the future.

无论哪种方式，使用动态枚举都是不好的。您将不得不经历“复制”数据的麻烦，以确保将来易于维护的清晰简单的代码。

If you start introducing automatic generated libraries, you are for sure causing more confusion to future developers having to upgrade your code than simply making your enum coded within the appropriate class object.

如果你开始引入自动生成的库，你肯定会给未来的开发人员带来更多的困惑，他们不得不升级你的代码，而不是简单地在适当的类对象中编码你的枚举。

The other examples given sound nice and exciting, but think about the overhead on code maintenance versus what you get from it. Also, are those values going to change that frequently?

给出的其他示例听起来不错且令人兴奋，但请考虑代码维护的开销与从中获得的收益。此外，这些值是否会频繁更改？

回答by CodingWithSpike

I've done this with a T4template. It is fairly trivial to drop a .tt file into your project, and set up Visual Studio to run the T4 template as a pre-build step.

我已经用T4模板做到了这一点。将 .tt 文件放入项目中并设置 Visual Studio 以运行 T4 模板作为预构建步骤相当简单。

The T4 generates a .cs file, which means you can have it just query the database and build an enum in a .cs file from the result. Wired up as a pre-build task, it would re-create your enum on every build, or you can run the T4 manually as needed instead.

T4 生成一个 .cs 文件，这意味着您可以让它查询数据库并根据结果在 .cs 文件中构建枚举。作为预构建任务进行连接，它会在每次构建时重新创建您的枚举，或者您可以根据需要手动运行 T4。