What type of data do you use for transfers in SQL Server

Question

When we serialize an enumeration from C # to SQL Server, we use the NCHAR (3) data type with mnemonic values ​​for each enumeration value. Thus, we can easily read SELECT qry.

How to save enumeration in the database?

What type of data are you using?

Answer 1

It would be best to store as an int. This way you can deserialize / drop back from the database to the correct enum value.

If the listing is likely to be changed in the future, then use explicit values, for example.

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

Should storage practices as mnemonics cause collisions depending on your poison generation algorithm?

Answer 2

I use a separate reference table with an identifier and description for each listing. You can group them all into one table with an identifier, type and description, but I found that you often get the opportunity to extend these tables over time, and if you group all the reference data into one table, it can make life harder to work .

Answer 3

I always used lookup tables in MSSQL, where I would use other enumerations in databases that support them. If you are using some type of char, then you need to use a check constraint to ensure that the inserted or updated values ​​are members of the enumeration.

When the members of an enumeration change, I always felt that adding entries to the lookup table is easier than changing the control limit.

Answer 4

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

This is too primitive because you are talking about a lot of complex things.

Answer 5

nchar (3) for mnemonics?

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

You can write the stored procedure in the table value script as an enumeration of C # or as public consts of C #.

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

Answer 6

Here is my answer to another thread.

The best way, in my opinion, is to use a lookup table with int, as suggested above, but combine it with code generation to support the principle of DRY (Do not Repeat Yourself).

Here is a link for code generation example.

Answer 7

This is not true. There is the indefinite equivalent:

Genre VARCHAR (10) NOT NULL CHECK (Genre IN ("Man", "Woman", "Other"))