473,325 Members | 2,308 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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 2613
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: newbie programmer | last post by:
Hi ! I have a small php web application that uses mysql to do calendering and keep contacts. I would like to sync it with Outlook . I am new to these kinda stuffs, so I really don't know where to...
1
by: Piotrek Stachowicz | last post by:
Hello, I've got 2 databases (both on MS-SQL Sever2k). One of them (remote) contains table which is often updated. For various reasons I'd like to maintain a copy on local MSSQLServer. Is there...
5
by: Ken Massey | last post by:
First let me say I'm not interested in replication. I want to sync a target database with a source database at regular intervals (say weekly), but in the intermediate time they may differ. The...
5
by: Karl | last post by:
Hi C# experts, I am to C# and .NET. I am writing a database application using C# and Dataset. In Sql server, I have a Acount table which contains over 100,000 records. This table will be...
0
by: Matthias S. | last post by:
Hi there, first of all, sorry for the lengthy post, but restrictions in my languages skills and the nature of the problem account for it. I've got the following situation: I'm creating a...
0
by: Ferry Boender | last post by:
Hi, I'm relatively new to Xlib programming, and I ran into a little problem. I'm trying to insert keypress events into a X window. The following code works: ...
1
by: taleran58 | last post by:
Hi all. I'm writting a multithreading program that will update a loacal Access database. I'm using OleDB connection to get access to the database from each thread and my question is: how do I...
0
by: Torsten Bronger | last post by:
Hallöchen! A TurboGears process opens a DB file with anydbm and keeps it open. The the same time, this file is updated by another process. How can I tell the TurboGears process to fetch the new...
4
by: Maciek Sz | last post by:
Hello to Everyone, I have to extend existing database application. Application is written in C++, DB is Interbase. I have to sync outlook tasks, callendar and contacts with that db application....
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.