Enums storage

Mar 6, 2015 at 2:54 PM
I just realized one detail about saving enums. Whenever the enum changes non-trivially (i.e. not just by adding a new value at the end), the database needs to be rebuilt.

This is not a problem for my use case (the DB is just an index of the real data which is stored in files), so I just increase the expected value for SQLite's user_version pragma in the code and the SQLite DB gets rebuilt for all clients.

But I wondered if enums could be saved as strings to the DB, so that the DB doesn't have to be rebuilt when enum values get reordered. So I tried decorating the enum property with
[Column( DbType = System.Data.DbType.String )]
but it had no effect, the column was still created as an integer.

I realize that when saving as strings we'd lose the ability to rename enum values without reordering them, so maybe integers are a better idea after all. One just has to remember that new values need to be added at the end.
Mar 6, 2015 at 7:17 PM
What would you expect? - Enums are just sets of named integer constants, just that. With automatic increment values, if you don't provide explicit values. So one way to handle it is to set values explicitly in enum declaration, so when you add new element in the middle, you can set it appropriately and old data is still valid.
As for switching to string using Column attribute. That did not work, because SQLite is 'type-less' database, and essentially everything is 'object' or 'variant', and can contain anything; the type associations are merely suggestions on most probable values. So DbType specs in this case are meaningless... Not sure would it work for other servers, but for SQLite - no.
Mar 6, 2015 at 11:40 PM
Edited Mar 6, 2015 at 11:40 PM
As for switching to string using Column attribute. That did not work, because SQLite is 'type-less' database, and essentially everything is 'object' or 'variant', and can contain anything; the type associations are merely suggestions on most probable values. So DbType specs in this case are meaningless... Not sure would it work for other servers, but for SQLite - no.
The only way an enum value could be stored as a string in any database is to use .NET's ToString() and Enum.Parse() methods to convert the value to and from string. The DbType.String type of the column could be used as an indicator that such conversion must be done. But I don't know how useful would this be. Of what I seen in my experience from my coworkers (I'm not a DB programmer myself), enums are handled with a reference to a separate table containing all of the enum values (and a long description of each value).
Mar 8, 2015 at 1:02 AM
Storing enums as rows in separate table - this is often done when enums are in fact 'state' names (like document status) that are shown to users in UI; so they need all kinds of extra info around them (short/long labels etc). But generally enums are simple named constants used mostly internally in c# code. In this case it makes sense to use them as-is, and store in db as integers.