您在SQL Server中使用什么数据类型进行枚举

When we serialize an enum from C# to SQL Server we use a NCHAR(3) datatype with mnemonic values for each value of the enum. That way we can easily read a SELECT qry.

当我们将枚举从C#序列化到SQL Server时,我们使用NCHAR(3)数据类型,其中包含枚举值的每个值的助记符值。这样我们就可以轻松阅读SELECT qry。

How do you save enum to your database?

如何将枚举保存到数据库中?

What datatype do you use?

你使用什么数据类型?

7 个解决方案

#1

A better way would be to store as an int. That way you can deserialise/cast from the DB right back to the correct enum value.

更好的方法是存储为int。这样你就可以从数据库反序列化/强制转换回正确的枚举值。

If the enum is likely to be changed in the future then use explicit values e.g.

如果将来可能更改枚举,则使用显式值,例如

public enum ActionType
{
  Insert = 1,
  Update = 2,
  Delete = 3
}

The practicalities of storing as a mnemonic must cause you have *es depending on your mnemonic generating algorithm?

存储作为助记符的实用性必然会导致您的助记符依赖于您的助记符生成算法?

#2

I use a separate reference data table with ID & Description for each enum. You can group them all into one table with ID, Type & Description, but I have found that you often end up wanting to extend these tables over time and if you group all the reference data into one table it can make life harder in the long run.

我为每个枚举使用带有ID和描述的单独参考数据表。您可以将它们全部分组到一个具有ID,类型和描述的表中,但我发现您经常最终想要扩展这些表,如果将所有参考数据分组到一个表中,它可能会使生命变得更难跑。

#3

I've always just used lookup tables in MSSQL where I would have otherwise used enums in databases that support them. If you use some kind of char type, then you have to use a check constraint to ensure that inserted or updated values are members of the enum.

我一直只是在MSSQL中使用查找表,否则我会在支持它们的数据库中使用枚举。如果使用某种char类型,则必须使用检查约束来确保插入或更新的值是枚举的成员。

When the members of the enum change, I've always felt it was easier to add entries to a lookup table than to alter a check constraint.

当枚举的成员发生变化时,我总是觉得将条目添加到查找表比更改检查约束更容易。

#4

I keep them as integers. its real cute casting them to each other (int)enum and (enum)int :) also my other favorite is dropdownlist.datasource = Enum.GetNames(typeof(EnumType));

我把它们保持为整数。它的真正可爱将它们互相投射(int)enum和(enum)int :)另外我最喜欢的是dropdownlist.datasource = Enum.GetNames(typeof(EnumType));

Is this too primitive, cuz you are talking about lots of complicated stuff

这太原始了,因为你在谈论很多复杂的东西

#5

nchar(3) for mnemonics?

nchar(3)用于助记符?

As Martin pointed out, use a lookup table with INT as PK, VARCHAR identifier as UK, and NVARCHAR description.

正如Martin指出的那样,使用一个查询表,其中INT为PK,VARCHAR标识符为UK,NVARCHAR描述。

You can write a stored procedure to script the table values as C# enum or as C# public consts.

您可以编写一个存储过程来将表值编写为C#enum或C#public consts。

Thus the values are documented both in the database and in the C# source code.

因此,值记录在数据库和C#源代码中。

#6

Here is my answer on a different thread.

这是我在另一个主题上的答案。

The best way in my opinion is to use a lookup table with int, as suggested above, but combining it with code generation to keep with the DRY principle (Don't Repeat Yourself).

在我看来,最好的方法是使用带有int的查找表,如上所述,但将其与代码生成相结合以保持DRY原则(不要重复自己)。

Here is the link for the code generation example.

这是代码生成示例的链接。

#7

It doesn't. There's a vague equivalent:

它没有。有一个模糊的等价物:

Genre VARCHAR(10) NOT NULL CHECK (Genre IN('Male', 'Female', 'Others'))

类型VARCHAR(10)NOT NULL CHECK(流派IN('男','女','其他'))

#1