Click here to Skip to main content
11,635,788 members (78,534 online)
Click here to Skip to main content

Tagged as

Mapping .NET Enumerations to the Database

, 25 Aug 2009 CPOL 15.3K 27
Rate this:
Please Sign up or sign in to vote.
How to map .NET Enumerations to and from a Database repository

Introduction

One of the commonly overlooked details in common development is the utilization of .NET enumeration and translation of such values to and from a database repository. A common approach employed by a lot of developers is to write decoding logic to convert read values from a database into enumerations or the other way around. In a diverse development environment, this could lead to interpretations and incorrect coding across teams. In the following, I will try to present a reusable technique that allows mapping the .NET enumerations to and from data read from a database repository.

Using the Code

In the following sections, we will explore how to map .NET enumerations to data fields.

While working on several development projects in the past five years, I have encountered several cases where developers turned away from using .NET enumerations when it came to tag properties with data that is saved to the database. For example, if you would design an object and one of the properties would be color, a natural approach would be to enumerate the different colors. Using an enumeration in this case can simplify the coding process around this property.

public enum EColor
{
	CustomEnumKey("0")]
	None,
	[CustomEnumKey("1")]
	White,
	[CustomEnumKey("2")]
	Red,
	[CustomEnumKey("3")]
	Black,
}

You can see that the values of the EColor enumeration have a custom attribute CustomEnumKey that stores a key value. Using the CustomEnumKey custom attribute, we associate the enumeration value with a Key that we can later use to store in the database. I will discuss later some of the options available for assigning a Key value, which can vary by the storage type used for the data.

Let's explore the CustomEnumKey custom attribute:

[AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
public class CustomEnumKey: System.Attribute
{
	public CustomEnumKey(string key)
	{
		this.Key = key;
	}
	public string Key { get; private set; }
}

The CustomEnumKey enumeration provides a constructor and exposes a read only property to access the Key value. The usage of the attribute enforces that this custom attribute can only be applied to fields in a class (in our case, the enumeration values) and that only one custom attribute can be applied at the time.

Let's create a Car class that would have a Color property of type EColor:

public class Car
{
	public string Make { get; set; }

	public EColor Color { get; private set; }
}

Now that we have the Car class, let's explore how we would translate the Color property value, represented by an enumeration value, to a Key value that we would store in a data field.

Let's look at a method that translates the EColor enumeration value to the mapped Key:

public static string ParseFromEnum<T>(T enumeration)
{
	if (enumeration.GetType().IsEnum)
	{
		var q = enumeration.GetType().GetFields()
			  .Where(f => f.GetValue(enumeration).Equals(enumeration))
			  .Select(c => new
			  {
				  Key = (c.GetCustomAttributes
					 (typeof(CustomEnumKey),
					 false) as CustomEnumKey[])
					 .DefaultIfEmpty(new CustomEnumKey
					 (string.Empty))
					 .FirstOrDefault().Key
			  });

		return q.FirstOrDefault().Key.ToString();
	}
	return string.Empty;
}

The ParseFromEnum<T> generic method will retrieve the Key value for the specified enumeration value by inspecting the custom attribute collection associated with the enumeration value. If the enumeration passed onto the method doesn't have a custom attribute of type CustomEnumKey it will return String.Empty as the return value. This method could be modified to return null if the utilization of this method requires it.

The following method allows to perform the reverse translation, from a Key value to an enumeration value:

public static T ParseToEnum<T>(string key)
{
	if (default(T).GetType().IsEnum)
	{
		var q = default(T).GetType().GetFields()
				.Where(f => ((CustomEnumKey[])
				f.GetCustomAttributes(typeof(CustomEnumKey), false))
				.Where(c => c.Key.Equals(key, 
				StringComparison.OrdinalIgnoreCase)).Count() > 0)
				.Select(o => new 
				{ KeyValue = o.GetValue(default(T)) });

		return q.Count() > 0 ? (T)q.FirstOrDefault().KeyValue : default(T);
	}
	return default(T);
}

The generic method will attempt to match an enumeration value with a provided Key value. If it is unable to, it will return the default value of the enumeration. This scenario highlights the fact the current code assumes that your enumeration can have a default value, which should not have any "business" meaning. If this is not the case, different means to handle this scenario are needed.

Now let's put it all together:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace EnumParser
{
	class Program
	{
		static void Main(string[] args)
		{
			Console.WriteLine("Mockup read from Database");

			Car carFromDb = DAL.GetCarFromDatabase();

			Console.WriteLine(string.Format
				("Car Make: {0}", carFromDb.Make));
			Console.WriteLine(string.Format
				("Car Color: {0}", carFromDb.Color.ToString()));

			Console.WriteLine("");

			Console.WriteLine("Mockup save to Database");

			Car carToDb = new Car();
			carToDb.Make = "Chevrolet";
			carToDb.Color = EColor.Red; //Enumeration Key value 2
			DAL.SaveCarToDatabase(carToDb);
			
			Console.WriteLine("");
			Console.WriteLine("Press any key");
			Console.ReadKey();
		}
	}

	[AttributeUsage(AttributeTargets.Field, AllowMultiple = false)]
	public class CustomEnumKey : System.Attribute
	{
		public CustomEnumKey(string key)
		{
			this.Key = key;
		}

		public string Key { get; private set; }
	}

	public enum EColor
	{
		[CustomEnumKey("0")]
		None,
		[CustomEnumKey("1")]
		White,
		[CustomEnumKey("2")]
		Red,
		[CustomEnumKey("3")]
		Black,
	}

	public class Car
	{
		public string Make { get; set; }

		public EColor Color { get; set; }
	}

	public static class DataParser
	{

		public static string ParseFromEnum<T>(T enumeration)
		{
			if (enumeration.GetType().IsEnum)
			{
				var q = enumeration.GetType().GetFields()
					  .Where(f => f.GetValue
					    (enumeration).Equals(enumeration))
					  .Select(c => new
					  {
						  Key = (c.GetCustomAttributes
							(typeof(CustomEnumKey), 
							false) as CustomEnumKey[])
							.DefaultIfEmpty(new 
							CustomEnumKey
							(string.Empty))
							.FirstOrDefault().Key
					  });

				return q.FirstOrDefault().Key.ToString();
			}
			return string.Empty;
		}

		public static T ParseToEnum<T>(string key)
		{
			if (default(T).GetType().IsEnum)
			{
				var q = default(T).GetType().GetFields()
						.Where(f => ((CustomEnumKey[])
						f.GetCustomAttributes
						(typeof(CustomEnumKey), false))
						.Where(c => c.Key.Equals
						(key, StringComparison.
						OrdinalIgnoreCase)).Count() > 0)
						.Select(o => new { KeyValue = 
						o.GetValue(default(T)) });

				return q.Count() > 0 ? 
					(T)q.FirstOrDefault().KeyValue : default(T);
			}
			return default(T);
		}
	}

	public static class DAL
	{
		public static Car GetCarFromDatabase()
		{
			Car car = new Car();

			DataTable dt = MockDatabaseCall();

			car.Make = dt.Rows[0]["car_make"].ToString();
			car.Color = DataParser.ParseToEnum<EColor>
					(dt.Rows[0]["car_color"].ToString());

			return car;
		}

		public static void SaveCarToDatabase(Car car)
		{
			using (DataTable dt = new DataTable())
			{
				dt.Columns.Add("car_make", typeof(string));
				dt.Columns.Add("car_color", typeof(string));

				DataRow dr = dt.NewRow();
				dr["car_make"] = car.Make;
				dr["car_color"] = DataParser.ParseFromEnum
						<EColor>(car.Color).ToString();

				dt.Rows.Add(dr);

				Console.WriteLine(string.Format("Car Make: {0}", 
					dt.Rows[0]["car_make"].ToString()));
				Console.WriteLine(string.Format("Car Color: {0}", 
					dt.Rows[0]["car_color"].ToString()));
				
				//save to database code....
			}
		}

		private static DataTable MockDatabaseCall()
		{
			using (DataTable dt = new DataTable())
			{
				dt.Columns.Add("car_make", typeof(string));
				dt.Columns.Add("car_color", typeof(string));

				DataRow dr = dt.NewRow();
				dr["car_make"] = "Ford";
				dr["car_color"] = "1"; //WHITE COLOR

				dt.Rows.Add(dr);

				return dt;
			}
		}
	}
}

Currently the code implies that the Key data type is a string. For real business purposes, a better analysis should be done to determine the appropriate data type and values that should be used for the enumeration mapping. Choosing a data type that allows indexing on the database side will help address performance with queries written to retrieve the data.

Points of Interest

While there are probably several variations of how the translation methods can be implemented, the scope of this article is to present one of the most underrated possibilities available in .NET programming: translating to and from .NET enumeration field to a stored value in a data field. By creating methods to translate between code and data stored in a database, you can leverage enumerations much easier and can lead to cleaner code.

History

  • 08-25-2009: Original article

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

tmiordan
Software Developer (Senior)
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
Generalwell written, well done Pin
Donsw29-Oct-09 15:04
memberDonsw29-Oct-09 15:04 
QuestionWhy use attributes and reflection instead of just using the enum values themselves? Pin
HightechRider25-Aug-09 7:23
memberHightechRider25-Aug-09 7:23 
AnswerRe: Why use attributes and reflection instead of just using the enum values themselves? Pin
tmiordan25-Aug-09 9:06
membertmiordan25-Aug-09 9:06 
GeneralRe: Why use attributes and reflection instead of just using the enum values themselves? Pin
HightechRider25-Aug-09 10:14
memberHightechRider25-Aug-09 10:14 
GeneralRe: Why use attributes and reflection instead of just using the enum values themselves? Pin
tmiordan25-Aug-09 12:42
membertmiordan25-Aug-09 12:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150728.1 | Last Updated 25 Aug 2009
Article Copyright 2009 by tmiordan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid