How to match enum as a string in a database

Question

My table:

create table MyTable ( Id int identity(1,1) not null, MyStatus char(2) not null ) insert into MyTable(MyStatus) select 'A' 

Class and enumeration:

 public class MyTable { public virtual int Id { get; set; } public virtual MyTableStatus MyStatus { get; set; } } public enum MyTableStatus { A, B } 

Mapping:

 public MyTableMap() { Id(x => x.Id); Map(x => x.MyStatus); } 

When I perform the following test, I get a System.FormatException: the input line was not in the correct format ...

 [Test] public void Blah() { MyTable myTable = Session.Get<MyTable>(1); Assert.That(myTable.MyStatus, Is.EqualTo(MyTableStatus.A)); } 

What is the correct way to map an enumeration to its string representation in a database?

Change I am writing my application in an existing database, which I cannot easily modify, because it is also used by other applications. Therefore, some fields in the database (which I would like to represent as enumerations in my application) are of type int and some of types are char (2).

Answer 1

You need to create a custom IUserType to convert the enumeration to its string representation and vice versa. There is a good example in C # here and an example in VB.NET for working with enumerations here (scroll down to the IUserType implementation).

Answer 2

Well, as far as I know, NHibernate stores enums as a string only in db by default. I seem to know what the problem is. Wrong way to create a table.

if you use Nhibernate, use the build function to create tables instead of manually creating tables, and then you will see that your enum is stored as a string.

We make extensive use of enums in our application, and it is reasonable for us to store it as strings in db. The reasons are simple, if I add a new value to enum tom, then if the default values ​​are not set, my code and my data are closely related, which I definitely would not want.

 SimpleConfig.ExposeConfiguration(c => new SchemaExport(c).Create(false, true)).BuildConfiguration(); 

Also, instead of using char for your string, you can use varchar for this property.

After the update: Could you do some manipulations before storing them in the database? Thus, when you want to save new char enums, write a function that generates an int value for you and saves it in the correct form, and then saves it or if you want to make it simple, the function may have a switch case.

So, what you are doing, you do not have access to this property, which is extracted from db, instead you add a new property to the Status class, which is based on the logic for obtaining the corresponding enumeration.

Do you think this is a good idea?

Hope this helps.