I'm using DataSet to connect my C# program with SQL database. I want one of the columns to be an enumeration and I want it to act as an enum in my code. How can I do this?
A:
I don't think you can. SQL Server doesn't have a concept of enums.
John Saunders
2009-06-23 23:34:55
I don't care how it will be represented in the databse. It can be int or string there.
svick
2009-06-23 23:37:48
You have to care. One thing that you cannot represent in the database is an enum.
John Saunders
2009-06-23 23:53:56
Hate to disagree, but an enum can easily be represented by a lookup table that has an ID, and a Name. This is done a lot, especially if you know that the lookup table will only change when the enum in code changes.
Josh
2009-06-24 04:07:15
As a value, you cannot represent the .NET concept of an enum as a column in a database.
John Saunders
2009-06-24 05:28:34
A:
I would suggest using an ApplicationService layer that sits atop your repository layer. Then in your ApplicationService class (think of appropriate name for this class) you can transform the data that is returned from the repository layer to the appropriate enum value in your POCO object.
Andrew Siemer
2009-06-23 23:35:51
You could use this construct to alter/add one of the columns data types to the type of your custom enum. as long as the numeric db values that fill it are in the scope of the enum, i believe it could work. This would require adding a partial class or modifying the generated one.
James
2009-06-23 23:44:05
Even assuming this works, it's a maintenance problem, as you now have multiple definitions of what's valid in that column. Also, I'll be surprised to find out that ADO.NET will implicitly perform a cast you couldn't do in code: MyEnum e = 1; will not compile.
John Saunders
2009-06-23 23:46:48
Agreed this is not ideal for maintenance and maybe even a bit of hack. I'm not sure about the casting issue though, because there are two ways to get at the data. (1) via the indexers which treat everything as type object and (2) strongly typed properties this is where the strong typing check occurs. I'm not sure which type of properties the fill method uses on typed datasets, but if it's the indexers this should work. Additionally you can manually load a typed dataset via the indexers (i.e. ds.Tables["myTable"].Rows[0][1] = 4).
James
2009-06-24 00:17:34
A:
Technically you can't. Enum's are static typed, they are designed to be used when you know all of the values at compile time. While there are some work-arounds, I would highly recommend that you do not do this.
Look at a unmodifiable dataset, this will give most of the benefits of a Enum and can be created on the fly.
While data cannot be stored as an Enum, they can be used as a fancy filter as long as the data in the database column is of an integer type. That is a complete different question if that is what you are after. Go to the MSDN page and read up on Enum's.
WolfmanDragon
2009-06-23 23:54:14
+2
A:
I don't mean to burst everyone's bubble, but you can easily map an integer to an Enum using a Strongly Typed DataSet. I do it all the time. Rather than type the whole thing out here I have created an entry on my Blog describing in detail how to accomplish this.
Josh
2009-06-24 04:05:35
+1 Nice job doing that proof-of-concept. I knew it was possible with a typed dataset. With this approach though, won't you have to re-do the change in the xsd file every time you make any change to the dataset since the xsd will be stomped by the typed dataset designer?
James
2009-06-24 16:56:59
Yes, the xsd will be clobbered which is problematic. Hopefully usage of custom enum types in datasets is sparing, and the maintenance will be minimized. However, I much prefer to deal with the enum type directly rather than having to cast back and forth to an integer. There are only a few places we do this, and those datasets change rarely, so the impact is minimal. If you are going to be changing your datasets a lot, the pain will increase substantially :(
Josh
2009-06-24 19:38:04
You could use CodeSmith to generate the dataset from the database to get control over this sort of thing. Or add a new property in a partial class that is of the type you want.
Ian Ringrose
2009-06-30 17:35:07