How to map enum as string in database

问题:

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 enum:

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 execute the following test, I get System.FormatException : Input string was not in a correct format...

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

What is the right way to map an enum to it's string representation in the database?

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

回答1:

You need to create a custom IUserType to convert an enum to its string representation and back. There's a good example in C# here and an example in VB.NET for working with enums here (scroll down to implementing IUserType).

回答2:

Well as far as I am aware NHibernate stores enums as string only in the db by default. I think I know what the problem here is. The way you are creating the table is incorrect.

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

We use enums extensively in our app and it makes sense for us to store it as strings in the db. The reasons are simple if I add a new value to an enum tom then if default values are not set then my code and my data are tightly coupled which I definitely wouldnt want.

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

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

After the update: Cant you guys do some kind of manipulation before you store it in the database? Thus when you want to store the new char enums write a function that generates an int value for you and store this in the propertry and now save it or if you want to make it simple the function can have a switch case.

So what you do is you dont have a get on this property that is retrieved from the db instead you add a new property in the class Status that basically has the logic of getting the appropriate enum.

Do you think thats a good idea?

Hope this helps.