468,738 Members | 1,653 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,738 developers. It's quick & easy.

Sync'ing Enums to Database values.

I haven't come across an elegant way to do this yet, and would like to
know what other people have come up with.

There are two tables:

CREATE TABLE EmployeeStatus
(
pkId int not null primary key identity,
status varchar( 50 ) not null
)

CREATE TABLE Employees
(
pkId int not null primary key identity,
fkEmployeeStatusId int not null references EmployeeStatus( pkId ),
firstName varchar( 50 ),
lastName varchar( 50 ),
-- etc, etc, etc
)

The EmployeeStatus table would have values:
1 : Active
2 : Inactive

In the C# code there will be an Employee class that will reflect the
Employee table, with public properties for the data.

If I want to get a collection of Employees that are active or inactive,
I currently pass in the status value.

GetEmployees( 'Active' )

If the value in the db changes, then this won't work.

I've seen people make enums that have the same values as the primary
key id in the database.

GetEmployees( EmployeeStatus.Active )

But then the enum values need to match the database values, and we run
into the same issue.

I'd like to find a way to get the values, have it be strongly typed,
and not depend on any database values.

Is this possible?

Thanks.

-Josh

Nov 17 '05 #1
7 2333
Hello Josh,

Well, as far as I know it can not be done completely. You mention an enum
referencing the description and one referencing the id of the status. Because
the database is (possibly) always volatile, real strong typing will not be
available.

You could on the other hand make a sort of enumclass that contains several
other classes that represent the status' you need. As soon as you enter your
IDE you could let it check with the db to 'fill' itself with classes, so
you have a strong-type check every time you compile your code.
In runtime though it could cause an error, as could the other ways you already
mentioned.

HTH
Christiaan
I haven't come across an elegant way to do this yet, and would like to
know what other people have come up with.

There are two tables:

CREATE TABLE EmployeeStatus
(
pkId int not null primary key identity,
status varchar( 50 ) not null
)
CREATE TABLE Employees
(
pkId int not null primary key identity,
fkEmployeeStatusId int not null references EmployeeStatus( pkId ),
firstName varchar( 50 ),
lastName varchar( 50 ),
-- etc, etc, etc
)
The EmployeeStatus table would have values:
1 : Active
2 : Inactive
In the C# code there will be an Employee class that will reflect the
Employee table, with public properties for the data.

If I want to get a collection of Employees that are active or
inactive, I currently pass in the status value.

GetEmployees( 'Active' )

If the value in the db changes, then this won't work.

I've seen people make enums that have the same values as the primary
key id in the database.

GetEmployees( EmployeeStatus.Active )

But then the enum values need to match the database values, and we run
into the same issue.

I'd like to find a way to get the values, have it be strongly typed,
and not depend on any database values.

Is this possible?

Thanks.

-Josh

Nov 17 '05 #2
Well, I am not sure how elegant this is, but I create an ApplicationConstant table
and whenever I create an enum, I put it in the table. It is not elegant, but it does
work.

<na****@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I haven't come across an elegant way to do this yet, and would like to
know what other people have come up with.

There are two tables:

CREATE TABLE EmployeeStatus
(
pkId int not null primary key identity,
status varchar( 50 ) not null
)

CREATE TABLE Employees
(
pkId int not null primary key identity,
fkEmployeeStatusId int not null references EmployeeStatus( pkId ),
firstName varchar( 50 ),
lastName varchar( 50 ),
-- etc, etc, etc
)

The EmployeeStatus table would have values:
1 : Active
2 : Inactive

In the C# code there will be an Employee class that will reflect the
Employee table, with public properties for the data.

If I want to get a collection of Employees that are active or inactive,
I currently pass in the status value.

GetEmployees( 'Active' )

If the value in the db changes, then this won't work.

I've seen people make enums that have the same values as the primary
key id in the database.

GetEmployees( EmployeeStatus.Active )

But then the enum values need to match the database values, and we run
into the same issue.

I'd like to find a way to get the values, have it be strongly typed,
and not depend on any database values.

Is this possible?

Thanks.

-Josh

Nov 17 '05 #3

<na****@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I've seen people make enums that have the same values as the primary
key id in the database.

GetEmployees( EmployeeStatus.Active )

But then the enum values need to match the database values, and we run
into the same issue.

I'd like to find a way to get the values, have it be strongly typed,
and not depend on any database values.

Is this possible?


You don't want to synch enums to database values, you want to synch database
values to an enum.

The enum drives the data, not the other way around. In fact, your DB table
is completely unnecessary from a logic standpoint. Its only value is in
providing a human-readable description of the status (which is dictated by
the enum). The status can never change (i.e. if "Active" is "1" then it
should *always* be "1").
Nov 17 '05 #4
Hello Scott,

After reading your post, I must concur. In my former post I suggested to
fill an enumclass with content from database. Though mentioning this was
error prone, this was probably a bad suggestion.

The other way round though, might just work (but could corrupt your database
in some cases).

Cheers
Christiaan
<na****@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
I've seen people make enums that have the same values as the primary
key id in the database.

GetEmployees( EmployeeStatus.Active )

But then the enum values need to match the database values, and we
run into the same issue.

I'd like to find a way to get the values, have it be strongly typed,
and not depend on any database values.

Is this possible?

You don't want to synch enums to database values, you want to synch
database values to an enum.

The enum drives the data, not the other way around. In fact, your DB
table is completely unnecessary from a logic standpoint. Its only
value is in providing a human-readable description of the status
(which is dictated by the enum). The status can never change (i.e. if
"Active" is "1" then it should *always* be "1").

Nov 17 '05 #5
The only problem I see with this is, there is a web interface for
editing the status values. So more can eventually be added. Then these
won't be linked up anymore.

Maybe there is a way of doing this without using enums, and still
having it be strongly typed.

Nov 17 '05 #6

<na****@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
The only problem I see with this is, there is a web interface for
editing the status values. So more can eventually be added. Then these
won't be linked up anymore.

Maybe there is a way of doing this without using enums, and still
having it be strongly typed.


How can you hard code for the "Active" status if you don't know what
constitutes "Active"? You can't.

GetEmployees( 'Active' )
GetEmployees( EmployeeStatus.Active )

Neither of these is possible if the user is free to make up new codes and/or
modify existing ones and change their meanings on a whim. What you've got is
a look-up table, not enums.

Perhaps your "status code" table should contain a "status identifier" which
is the enum?

CREATE TABLE EmployeeStatus
(
pkId int not null primary key identity,
status varchar( 50 ) not null,
statusid int not null
)

"statusid" would indicate whether this particular status code (whatever its
description may be) represents an "Active" employee. If you need to hard
code "Active" into your application then it needs to be unchanging in your
database. Period.
Nov 17 '05 #7
SP
<na****@gmail.com> wrote in message
news:11*********************@g44g2000cwa.googlegro ups.com...
The only problem I see with this is, there is a web interface for
editing the status values. So more can eventually be added. Then these
won't be linked up anymore.

Maybe there is a way of doing this without using enums, and still
having it be strongly typed.


I do exactly what you are wanting to do. I have "built in" statuses which
can be edited by the users (with some restrictions) plus additional statuses
can be added by the users. The built in statuses are always represented in a
database table with the same primary keys values in all databases. A
collection is populated from the table and I provide properties on the
collection that return the known built in Active and Inactive statuses
(using the primary key as the indexer).

e.g.

public class Statuses
{ ....

public Status Active
{
get
{
return this[(long)1];
}
}

public Status Inactive
{
get
{
return this[(long)2]
}
}

Employee e = new Employee();
e.Status = Statuses.Active;

HTH,

SP

Nov 17 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by newbie programmer | last post: by
1 post views Thread by Piotrek Stachowicz | last post: by
5 posts views Thread by Ken Massey | last post: by
reply views Thread by Matthias S. | last post: by
reply views Thread by Ferry Boender | last post: by
1 post views Thread by taleran58 | last post: by
reply views Thread by Torsten Bronger | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.