Enums in a database

[Jay]

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?

 

[joelcochran]

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?

You can override the default numeric values of an Enum like this:
(examples from
http://www.c-sharpcorner.com/Language/EnumeratorsInCsharpRVS.asp)

enum Months
{
jan = 10, feb = 20, mar = 30, apr=40
}


According to the article, more than one Enumerator may have the same
value:

enum Months
{
jan = 1, feb = 1, mar, apr
}

To get the integer value associated with the enum, cast it like so:

int x = (int) Months.jan ; is a valid statement.


Hope this helps...

Joel

 

[Guest]

Jay,
Unless sombody a lot smarter than I can come up with something I've never
seen, I am afraid you will have to use the lookup table as a surrogate for
your enums.
Peter

 

[Samuel R. Neff]

Using a lookup table is the standard approach. If you also have the
enum values in a c# class then a lot of times you can avoid using the
lookup table and look at the numeric data directly.

One thing we've found useful is to write a unit test for each enum
that maps to a lookup table to verify that the values in the enum
match the values in the lookup table. Also if the lookup tables use a
consistent naming convention you can generate the enum classes from
the lookup table data automatically.

HTH,

Sam

 

[ssamuel]

Jay,

I assume that you've exhausted the possibility of an int key
corresponding to your enum value. There are many solutions floating
around the internet for using attributes to decorate particular enums
for extended data access, including integration with databases.

That's entirely more efficient for data storage than string/varchar
value. I also assume that you're worried about storage space
efficiency, not ease-of-programming efficiency. Ask a RAD developer and
you might get a "who cares how efficient it is to store" response.

You'll need to be more specific about "a database." This is a C# board,
so many of the people here are probably defaulting to MSSQL. Some other
databases have native enum data types and a well-written data access
driver could map DB enums to CLR enums. Better yet, use an OR database,
where storing your object is a single undecorated call.

If you are looking at MSSQL, it's important to understand that its data
model doesn't support the concept of an enum. Asking that sort of DB
for an enum is like asking an auto mechanic how he feeds the horses. It
has a similar-but-different approach: normalization. Full circle
complete, you're back to your lookup tables. Any way you look at this,
if you want a robust app, you'll need to deal with keeping your data
clean. You can change a string in a database much easier than you can
change the name in an enum.


Stephan

 

[Jay]

Thanks Joel + Peter + Samuel for your fast responses - very helpful.

Thinking about it, I will have to use a table since I will want the end user of the database to be
able to add keywords to the database, effectively adding more "enums". I guess to improve
efficiency, I could read all of the table in my C# programme so that I don't have to make database
query everytime I want to find a corresponding number for a keyword. Does that make sense? I'm a
beginner to database and C# programming, so this might sound a bit basic.

Jay


"Jay" <-> wrote in message 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?

 

[joelcochran]

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?

You can override the default numeric values of an Enum like this:
(examples from
http://www.c-sharpcorner.com/Language/EnumeratorsInCsharpRVS.asp)

enum Months
{
jan = 10, feb = 20, mar = 30, apr=40
}


According to the article, more than one Enumerator may have the same
value:

enum Months
{
jan = 1, feb = 1, mar, apr
}

To get the integer value associated with the enum, cast it like so:

int x = (int) Months.jan ; is a valid statement.


Hope this helps...

Joel

 

[Guest]

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?

A user-defined function may do the trick. The following function returns
the number for the month entered into the function. Otherwise it returns
null, if the string you enter is not valid.


CREATE FUNCTION [GetMonthNo](@month varchar(10))
RETURNS int
AS
BEGIN
DECLARE @monthNo int
SET @monthNo =
CASE @month
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END

RETURN @monthNo
END
GO

/* Calling the function */

SELECT dbo.GetMonthNo('January')
SELECT dbo.GetMonthNo('September')
SELECT dbo.GetMonthNo('Something invalid')

 

[Otis Mukinfus]

Thanks Joel + Peter + Samuel for your fast responses - very helpful.

Thinking about it, I will have to use a table since I will want the end user of the database to be
able to add keywords to the database, effectively adding more "enums". I guess to improve
efficiency, I could read all of the table in my C# programme so that I don't have to make database
query everytime I want to find a corresponding number for a keyword. Does that make sense? I'm a
beginner to database and C# programming, so this might sound a bit basic.

Jay


"Jay" <-> wrote in message 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?

Jay,

Yes it does make sense. In fact you can load the values into a Dictionary
object and use the int as the key for the lookup value or the other way around.

using System;
using System.Collections.Generic;

namespace TestConsole
{
class Program
{
static void Main(string[] args)
{
// using an int key and string value
Dictionary<int, string> testDict = new Dictionary<int, string>();
testDict.Add(10, "DescriptionOfEnum");

// get the string value using the int key
string str = string.Empty;
bool exists = testDict.TryGetValue(10, out str);

// or

// using a string key and int value
Dictionary<string, int> testDict2 = new Dictionary<string, int>();
testDict2.Add("DescriptionOfEnum", 10);

// get the int value using a string key
int i = 0;
exists = testDict2.TryGetValue("DescriptionOfEnum", out i);


}
}
}
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Dave Sexton]

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.

 

[Jay]

Hi,

I will be using MSSQL Express, which is free but has storage space limitations. If these limitations
do become a problem, I will probably move to PostgreSQL, but still using C#.net.

Actually, the int key would correspond to my enum value, so perhaps a lookup table is an efficient
method after all.

Jay

Jay,

I assume that you've exhausted the possibility of an int key
corresponding to your enum value. There are many solutions floating
around the internet for using attributes to decorate particular enums
for extended data access, including integration with databases.

That's entirely more efficient for data storage than string/varchar
value. I also assume that you're worried about storage space
efficiency, not ease-of-programming efficiency. Ask a RAD developer and
you might get a "who cares how efficient it is to store" response.

You'll need to be more specific about "a database." This is a C# board,
so many of the people here are probably defaulting to MSSQL. Some other
databases have native enum data types and a well-written data access
driver could map DB enums to CLR enums. Better yet, use an OR database,
where storing your object is a single undecorated call.

If you are looking at MSSQL, it's important to understand that its data
model doesn't support the concept of an enum. Asking that sort of DB
for an enum is like asking an auto mechanic how he feeds the horses. It
has a similar-but-different approach: normalization. Full circle
complete, you're back to your lookup tables. Any way you look at this,
if you want a robust app, you'll need to deal with keeping your data
clean. You can change a string in a database much easier than you can
change the name in an enum.


Stephan