Re: How to store and retrieve enum values in Database - C# Discussion Boards

Re: How to store and retrieve enum values in Database

5-Jul-07 21:24

Hi.

I have some enums generated from xsd files, which I'll use intensively in my program.

One task involves storing data into a database, including these enum values. The field type of the database which is use for this table is text.

How should I store, and more importantly retrieve the values of the enums?

How could I convert from string to a custom enum type?

Thanks.

Martin#5-Jul-07 21:43

Re: How to store and retrieve enum values in Database

5-Jul-07 21:43

Hello,

As enum is int Type it makes no sence to store it as a text I guess!
I would cast it to (int)MyEnum.
But you should use fixed values for your enum, which is important for consistance after updates!

All the best,

Martin

blackjack21505-Jul-07 21:48

Re: How to store and retrieve enum values in Database

5-Jul-07 21:48

Ok, so for example if I have this enum(mind the syntax):

Dog - 1
Cat - 2
Flying Squirrel - 3

and I store in the database values 1,2 or 3, is it possible after I retrieve these values to convert 1 to Dog, 2 to Cat and 3 to Flying Squirrel?

Thanks.

Martin#5-Jul-07 21:55

Re: How to store and retrieve enum values in Database

5-Jul-07 21:55

Hello,

Just do the casting in the other direction.

int dbvalue = ?;

FancyAnimals dbFancyAnimals = (FancyAnimals)dbvalue;

All the best,

Martin

Vikram A Punathambekar5-Jul-07 21:56

Re: How to store and retrieve enum values in Database

5-Jul-07 21:56

Sure, simply cast them to the enumeration and it will work! Smile | :)

enum Numbers
{
	One = 1,
	Two = 2,
	Three = 3
}

class Program
{
	static void Main(string[] args)
	{
		for (int i = 1; i <= 3; i++)
		{
			Numbers n = (Numbers) i;
			Console.WriteLine(n.ToString());
		}
	}
}

Cheers,
Vıkram.

After all is said and done, much is said and little is done.

Vikram A Punathambekar5-Jul-07 21:52

Re: How to store and retrieve enum values in Database

5-Jul-07 21:52

Martin# wrote:
As enum is int Type it makes no sence to store it as a text I guess!

I think he's talking about the names of the enumeration values. For instance:

enum Numbers
{
	One = 1,
	Two = 2,
	Three = 3
}

He wants to store "One", "Two", and "Three" in the database and somehow use them to get 1, 2, and 3. I used something like this at my previous company, but I don't remember now. D'Oh! | :doh:

Cheers,
Vıkram.

After all is said and done, much is said and little is done.

blackjack21505-Jul-07 21:54

Re: How to store and retrieve enum values in Database

5-Jul-07 21:54

Right. I want to store "One", "Two", "Three" and retrieve whatever values are required to correctly fill the enum variables in my program.

Sorry for the confusion.

Martin#5-Jul-07 21:56

Re: How to store and retrieve enum values in Database

5-Jul-07 21:56

I think I understood it.

Look at my posts.

All the best,

Martin

Vikram A Punathambekar5-Jul-07 22:06

Re: How to store and retrieve enum values in Database

5-Jul-07 22:06

Cheers,
Vıkram.

After all is said and done, much is said and little is done.

Martin#5-Jul-07 22:07

Re: How to store and retrieve enum values in Database

5-Jul-07 22:07

Vikram A Punathambekar wrote:

I wanted to say, that I understood it, but recomended an other solution!

All the best,

Martin

Martin#5-Jul-07 22:40

Re: How to store and retrieve enum values in Database

5-Jul-07 22:40

Hello.

Here is what you were searching for.
Again I don't recomend it!

string actstring ="Dog";
FancyAnimal actAnimal = (FancyAnimal)Enum.Parse(typeof(FancyAnimal), actstring, true);

Be aware that it will throw an exception if it has no member of that fits!

All the best,

Martin

blackjack21505-Jul-07 22:48

Re: How to store and retrieve enum values in Database

5-Jul-07 22:48

Thanks.
A man's gotta' do what a man's gotta' do. Even if it's not recommendable.

Martin#5-Jul-07 22:53

Re: How to store and retrieve enum values in Database

5-Jul-07 22:53

blackjack2150 wrote:
A man's gotta' do what a man's gotta' do. Even if it's not recommendable.

But safety first, my friend.

You could validate the string, if you get all the members of the Enum and store it:

string[] members = Enum.GetNames(typof(FancyAnimal));
System.Collections.Specialized.StringCollection memberscollection = new System.Collections.Specialized.StringCollection();
memberscollection.AddRange(members);

Than just make the check:

string act ="dog";
if(memberscollection.Contains(act))
{

Hope it helps!

All the best,

Martin

Vikram A Punathambekar5-Jul-07 22:53

Re: How to store and retrieve enum values in Database

5-Jul-07 22:53

Schweet.

Why do you not recommend it? Because of the exception it could throw, or because it's slow?

Cheers,
Vıkram.

After all is said and done, much is said and little is done.

Martin#5-Jul-07 22:59

Re: How to store and retrieve enum values in Database

5-Jul-07 22:59

Vikram A Punathambekar wrote:
Why do you not recommend it? Because of the exception it could throw, or because it's slow?

No, just imagen that.
Software 1:

public enum FancyAnimal
{
	dog= 0,
	cat= 1,
	squirel= 2,
}

text "squirel" is stored in db.

Than in Software 2:

public enum FancyAnimal
{
	dog= 0,
	cat= 1,
	squirrel= 2,
}

No good!

All the best,

Martin

Vikram A Punathambekar6-Jul-07 1:07

Re: How to store and retrieve enum values in Database

6-Jul-07 1:07

I assume you're trying to highlight the misspelling of Squirrel?

If the apps are unrelated, it doesn't make sense to make them share the same DB table for storage. Unsure | :~

Personally, I would go for a readable representation like Dog rather than 0 in the database.

I like your suggestion for a different reason - the code is simpler. I guess I could store the 0 along with Dog in the database and use that mapping. Smile | :)

Cheers,
Vıkram.

After all is said and done, much is said and little is done.

Martin#6-Jul-07 1:53