Hi Jay,
Realize that if you are using an enumeration at all then you are constraining
your design to only use the constants that you initially define. As others
have mentioned, a lookup table is more flexible than an enum because you can
define new values at a later time without recompiling. However, lookups add
complexity that may be inappropriate when you are sure that you don't need the
flexibility it provides. For instance, if I have to code different business
logic based on a person's home state I'd much rather use enums than a lookup
table since the lookup data is probably not going to change anytime soon:
if (aPerson.State == USStates.NY)
...
The above is really easy to accomplish, allowing for database check
constraints to ensure data integrity just like you could have using a lookup
table. I like to use check constraints on an integer column to confine the
values to those within the enum and use a strong-typed dataset when
loading/saving the data. Some people like to use the string values so they
know exactly what data is stored when looking at raw data, however that means
you have to define the enum twice: once in code and once in the check
constraint. I prefer to avoid this at the sacrifice of looking at "ugly", raw
data. Implementing my example below using strings instead of integers is a
simple matter of defining a new strong-typed column, in code, that calls
Enum.Parse on the string column.
This approach (as opposed to a lookup table) also provides type-safety because
the compiler will catch if you try to use USStates.Panama, for example, and a
database constraint can be used to ensure that you'll never get a number in a
result set that doesn't correspond to a defined constant. Using a string
value in the database provides the same amount of type-safety as integer if a
check constraint is used.
Define the enum first:
public enum Specialty
{
ForeignCars, // auto-assigned 0
DomesticCars,
ClassicCars,
SpecialtyCars // auto-assigned 3
}
Create the table definition as follows (here I use T-SQL in Sql Server 2005,
which may or may not be similar to the RDBMS that you are using):
CREATE TABLE AutoMechanics (
[Name] varchar(50) NOT NULL,
[Specialty] int NOT NULL
CHECK (Specialty BETWEEN 0 AND 3) -- very important!
)
Fill in some test data:
INSERT AutoMechanics SELECT 'Fred', 0 -- ForeignCars
INSERT AutoMechanics SELECT 'Joe', 3 -- SpecialtyCars
INSERT AutoMechanics SELECT 'Ann', 3 -- SpecialtyCars
INSERT AutoMechanics SELECT 'Susie', 2 -- ClassicCars
INSERT AutoMechanics SELECT 'Tom', 1 -- DomesticCars
Define the strong-typed dataset as normal. After the DataTable is defined in
the XSD designer, simply change the column type from System.Int32 to the Type
name of your enum and you're all set. The name for this example would just be
"Specialty", since I never declared any namespace.
(Note: You may have to build your project once with the enum defined before
assigning the new value in the designer. Also, this approach is much easier
in VS 2005. In earlier versions you have to change the XSD schema manually,
but it still works IIRC).
Here's an example of how all this can be used in your business logic code:
void RelocateMechanicsBySpecialty()
{
AppData data = new AppData();
// In VS 2005 a strong-typed SqlDataAdapter is created along with the
// DataSet, however you can use any code you'd like to fill "data".
using (AppDataTableAdapters.AppDataTableAdapter adapter =
new AppDataTableAdapters.AppDataTableAdapter())
{
adapter.Fill(data.AutoMechanics);
}
foreach (AppData.AutoMechanicsRow mechanic in data.AutoMechanics)
{
switch (mechanic.Specialty)
{
case Specialty.ClassicCars:
Console.WriteLine("{0} has a choice where to move.",
mechanic.Name);
break;
case Specialty.DomesticCars:
Console.WriteLine("{0} should be moved to Detroit.",
mechanic.Name);
break;
case Specialty.ForeignCars:
Console.WriteLine("{0} should be deported.", mechanic.Name);
break;
case Specialty.SpecialtyCars:
Console.WriteLine("{0} can stay put.", mechanic.Name);
break;
}
}
}
Here's the console output:
Fred should be deported.
Joe can stay put.
Ann can stay put.
Susie has a choice where to move.
Tom should be moved to Detroit.
--
Dave Sexton
"Jay" <-wrote in message news:uj******************@TK2MSFTNGP02.phx.gbl...
In C# I can set up an Enum so that number are represented as keywords, which
is very useful.
Is there such a datatype in a database?
I suppose I could use an extra table, with the ID column as the number, and
a corresponding column
with strings representing the keywords. This sounds a bit inefficient - is
there a better way?
