Sync'ing Enums to Database values.

N

narshe

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
 
C

Chris van Bergen

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
 
M

Mike Malter

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.
 
S

Scott Roberts

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").
 
C

Chris van Bergen

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
 
N

narshe

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.
 
S

Scott Roberts

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.
 
S

SP

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top