If one is not using Linq-To-Sql and the entities are not being generated and loaded, one has to create their own entities and manually load the data into the it. Sure the process for doing one or two entities is ok when done by hand; but it becomes very repetitive grunt work for two or more. This article shows how to decorate an entity with attributes and then using reflection in a generic method load the data from a dataset in C#. This can be done with any version of .Net from 3.5 or greater.
Entity
To make this magic happen we need to decorate our entity with a C# attribute which describes the target column name in the database. Thanks to the System.Xml.Serialization namespace we can use the XmlElement attribute. It has an attribute within itself the ElementName attribute which is an excellent vehicle for what we need to do.
In the class below we have two strings, a DateTime and a decimal. Using the XmlElement we define what all of the target database column names are for each individual mapping of the property to hold the data.
public class Site
{
[XmlElement(ElementName = "ID")]
public decimal ID { get; set; }
[XmlElement(ElementName = "Site_Code")]
public string Code { get; set; }
[XmlElement(ElementName="Site_Description")]
public string Description { get; set; }
[XmlElement(ElementName = "REQ_PROP_ORIG")]
public DateTime? Origination { get; set; }
}
Using
Here are the usings if you don’t want to do <CTRL><.> on each error in the file to resolve the namespace for the next sections code.
using System.Reflection;
using System.Xml.Serialization;
using System.ComponentModel;
using System.Globalization;
Generic Method and Reflection
Here is the generic method in a static class. In the next section I describe what is going on when this class is called with a data row and the target entity instance is returned. (See the section Example Usage below to see it in action.)
public static class EntityHelper
{
public static TEntity CreateEntityFromDataRow<TEntity>(DataRow drData)
where TEntity : class, new()
{
Attribute aTargetAttribute;
Type tColumnDataType;
TEntity targetClass = new TEntity();
Type targetType = targetClass.GetType(); // The target object's type
PropertyInfo[] properties = targetType.GetProperties(BindingFlags.Instance | BindingFlags.NonPublic | BindingFlags.Public);
// Enumerate the properties and each property which has the XmlElementAttribute defined match the column name
// and set the new objects value..
foreach (PropertyInfo piTargetProperty in properties)
{
aTargetAttribute = Attribute.GetCustomAttribute(piTargetProperty, typeof(XmlElementAttribute));
if (aTargetAttribute != null)
{
try
{
foreach (DataColumn column in drData.Table.Columns)
{
if (((XmlElementAttribute)aTargetAttribute).ElementName.ToUpper() == column.ColumnName.ToUpper())
{
if (drData[column.ToString()] != DBNull.Value) // Only pull over actual values
{
tColumnDataType = drData[column.ToString()].GetType();
// Is the data in the database a string format and do we
// want a DateTime? Do the below checks and if so covert to datetime.
if ((tColumnDataType != null) &&
(tColumnDataType == typeof(System.String)) &&
(piTargetProperty.PropertyType.IsGenericType) &&
(piTargetProperty.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) &&
((new NullableConverter(piTargetProperty.PropertyType)).UnderlyingType == typeof(System.DateTime)))
{
// The below pattern dd-MMM-YY is for an Oracle date target. You may need to change this depending
// on the database being used.
DateTime dt = DateTime.ParseExact(drData[column.ToString()].ToString(), "dd-MMM-YY", CultureInfo.CurrentCulture);
piTargetProperty.SetValue(targetClass, dt, null);
}
else // Set the value which matches the property type.
piTargetProperty.SetValue(targetClass, drData[column.ToString()], null);
}
break; // Column name and data associated, no need to look at the rest of the columns.
}
}
}
catch (Exception ex)
{
throw new ApplicationException(string.Format("Load Failure of the Attribute ({0}) for {1}. Exception:{2}",
((XmlElementAttribute)aTargetAttribute).ElementName, targetType.Name, ex.Message));
}
}
}
return targetClass;
}
}
Explanation
- Line 4: The generic method is defined to accept a generic type definition (the entity we are working with) but accept a DataRow as the actual data and return the generic type as a valid instance.
- Line 5: This line requires that the generic type is a class and can be new’ed up. New because we will creating new entity instance at run time. See line 10.
- Line 11: Simply new up a new entity to return.
- Line 13/15: We begin to examine the generic type which will lead to the discovery of the properties we are looking at. Note we want all properties regardless of their access level. You can specify just public or private by removing the flags setup on line 14. (For more on this see my blog article .Net Reflection 101: Accessing The String Properties of an Active Object).
- Line 19: We will enumerate upon the reflected properties of the type and match by reflected Element Name to Column Name.
- Line 21: Here we reflect and get the custom attributes of the type for usage later.
- Line 27: Now we begin to find the associated column by enumerating over each column found in the row.
- Line 30: Does the reflected name match the current column name?
- Line 35: What is the reported data type from the current row column? This is usede in the if of 39-43.
- Line 39-43: Some databases return a string value for specific dates/times. If that is the case and we are looking at a string held date value do specialized processing to extract the date/time. If your database returns a DateTime object you can remove this if check.
- Line 51: Assign the data in the column to the instances property via reflection in SetValue.
Example Usage
List<Site> SiteList = new List<Site>();
DataSet ds = new DataSet();
{Data Adaptor}.Fill( ds ); // Fill the dataset from a data adapter.
// Convert the data in the dataset into a list of the target types.
foreach (DataRow dr in ds.Tables[0].Rows)
SiteList.Add(EntityHelper.CreateEntityFromDataRow<Site>(dr));
