NOTE: I've got a more thorough version of this post up at Mapping DataTables and DataRows to Objects in C# using Reflection. Check it out!
We're in the process of converting an old ASP.NET WebForms application to MVC. For this particular project, we are accessing an old database, one that doesn't really play well with Entity Framework or other ORMs, so we're using ADO.NET statements to get the data back in the form of DataTables.
DataTables, however, are really difficult to work with in MVC, because:
- They're not strongly typed; the type of any given object in a DataRow's ItemArray is
object. - We can't do validation on individual data pieces because of the no-type issue.
- The tables can contain any number of rows.
This bothered me for quite a while, as I really couldn't mesh the philosophies of MVC and DataTables together in any meaningful way. The lack of strong-typing was the most egregious issue; I was reasonably sure that the value of a given column in a given row was of a particular type, but I couldn't use the benefits of that type.
Taking some inspiration from AutoMapper, we decided to create our own mapping engine that would take those DataTables and map them into collections of strongly-typed objects. I've taken those ideas and distilled them down into a sample project. This post details how we ended up creating this mapper class, and how well it worked (spoiler alert: pretty damn well).
The Problem
We had old procedures in our database that we still had to use for this rewrite. Those procedures were written in our organization's cowboy coding days, and as such had no real consistency in their naming and structure. Plus, there were a lot of them (100+). So, if we were going to build this mapping engine, we needed a generic solution that we could use for a lot of different types and source data.
To further complicate matters, the procedures' structure and naming weren't predictable at all. For example, we had two different procedures, each of which returned data representing a collection of users for this application, but each of which was named entirely differently. So we had to account for the possibility that two columns in different procedure calls would have different names but represent the same data.
Finally, we wanted the ability to not have to explicitly map each property to a column. Implicit in this requirement is the ability to determine the type and parse the source data accordingly.
In short, we needed a mapping engine that:
- Was generic and extensible
- Could handle multiple column names
- Could discover the type of the destination property and parse the source data
The Model Class
Here's the model class we will be using:
public class User
{
public int ID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime? DateOfBirth { get; set; }
public decimal CashOnHand { get; set; }
}
SourceNamesAttribute
The first part of the problem we had to solve was this: How does the code determine which columns in a DataTable map to which properties? We decided that an attribute was the best solution for this, and created the SourceNamesAttribute:
[AttributeUsage(AttributeTargets.Property)]
public class SourceNamesAttribute : Attribute
{
protected List<string> _columnNames { get; set; }
public List<string> ColumnNames
{
get
{
return _columnNames;
}
set
{
_columnNames = value;
}
}
public SourceNamesAttribute()
{
_columnNames = new List<string>();
}
public SourceNamesAttribute(params string[] columnNames)
{
_columnNames = columnNames.ToList();
}
}
The AttributeUsage attribute allows us to restrict our SourceNamesAttribute to only be used on properties of a class, not classes themselves.
SourceNamesAttribute stores a list of column names for each property, so it supports our requirement that the mapping be tolerant of multiple column names. Now we can decorate the properties of User with this attribute:
public class User
{
[SourceNames("user_id", "userId")]
public int ID { get; set; }
[SourceNames("first_name", "first")]
public string FirstName { get; set; }
[SourceNames("last_name", "last")]
public string LastName { get; set; }
[SourceNames("date_of_birth", "birthDate")]
public DateTime? DateOfBirth { get; set; }
[SourceNames("cash_on_hand", "cash", "cashOnHand")]
public decimal CashOnHand { get; set; }
}
A Generic Mapper Interface
For simplicity, we'll assume we only want to work with DataTables for now. The interface for our Mapper class looks like this:
public interface IMapper<T>
{
List<T> Map(DataTable table);
}
And our implementation looks like this:
public class Mapper<TEntity> where TEntity : class, new()
{
public List<TEntity> Map(DataTable table)
{
}
}
We needed to specify that TEntity must be a class, and will be newly created by this Mapper<>.
We still need to fill in that Map() function, but to do that we need some parts.
Getting the Source Names
We need a static method that we can use to get the source names for a given property:
public static class MappingHelper
{
private static List<string> GetSourceNames(Type type, string propertyName)
{
var property = type.GetProperty(propertyName).GetCustomAttributes(false).Where(x => x.GetType() == typeof(SourceNamesAttribute)).FirstOrDefault();
if (property != null)
{
return ((SourceNamesAttribute)property).ColumnNames;
}
return new List<string>();
}
}
The first parameter is the type of the destination model. We use propertyName to get the property of the object via Reflection, and then look for a SourceNamesAttribute on that property; if such an attribute exists, we return the source names.
Since we have proven that we can get the PropertyInfo for a given property, we can now tackle the other end of this problem: how to determine the property's type and parse accordingly.
Parsing a Given Property's Value
Here's the rest of Mapper
public class Mapper<TEntity> where TEntity : class, new()
{
...
public void Map(Type type, DataRow row, PropertyInfo prop, object entity)
{
List<string> columnNames = MappingHelper.GetSourceNames(type, prop.Name);
//Handle .NET Primitives and Structs (e.g. DateTime) here.
foreach (var columnName in columnNames)
{
if (!String.IsNullOrWhiteSpace(columnName) && row.Table.Columns.Contains(columnName))
{
var propertyValue = row[columnName];
if (propertyValue != DBNull.Value)
{
MappingHelper.ParsePrimitive(prop, entity, row[columnName]);
break; //Assumes that the first matching column contains the source data
}
}
}
}
}
public static class MappingHelper
{
...
public static void Map(Type type, DataRow row, PropertyInfo prop, object entity)
{
List<string> columnNames = GetSourceNames(type, prop.Name);
foreach (var columnName in columnNames)
{
if (!String.IsNullOrWhiteSpace(columnName) && row.Table.Columns.Contains(columnName))
{
var propertyValue = row[columnName];
if (propertyValue != DBNull.Value)
{
ParsePrimitive(prop, entity, row[columnName]);
break; //Assumes that the first matching column contains the source data
}
}
}
}
private static void ParsePrimitive(PropertyInfo prop, object entity, object value)
{
if (prop.PropertyType == typeof(string))
{
prop.SetValue(entity, value.ToString().Trim(), null);
}
else if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(int?))
{
if (value == null)
{
prop.SetValue(entity, null, null);
}
else
{
prop.SetValue(entity, int.Parse(value.ToString()), null);
}
}
else if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(Nullable<DateTime>))
{
DateTime date;
bool isValid = DateTime.TryParse(value.ToString(), out date);
if (isValid)
{
prop.SetValue(entity, date, null);
}
else
{
//Making an assumption here about the format of dates in the source data.
isValid = DateTime.TryParseExact(value.ToString(), "yyyy-MM-dd", new CultureInfo("en-US"), DateTimeStyles.AssumeLocal, out date);
if (isValid)
{
prop.SetValue(entity, date, null);
}
}
}
else if (prop.PropertyType == typeof(decimal))
{
prop.SetValue(entity, decimal.Parse(value.ToString()), null);
}
else if (prop.PropertyType == typeof(double) || prop.PropertyType == typeof(double?))
{
double number;
bool isValid = double.TryParse(value.ToString(), out number);
if (isValid)
{
prop.SetValue(entity, double.Parse(value.ToString()), null);
}
}
}
}
The Mapper
ParsePrimitive() is only set up at the moment to parse the types we need for this example, so feel free to add you own parsing scenarios.
Completing the Mapper
With all of this in place, we can finally complete Mapper<T>.Map():
public List<TEntity> Map(DataTable table)
{
List<TEntity> entities = new List<TEntity>();
var columnNames = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
var properties = (typeof(TEntity)).GetProperties()
.Where(x => x.GetCustomAttributes(typeof(SourceNamesAttribute), true).Any())
.ToList(); //Only get properties that have the SourceNamesAttribute
foreach (DataRow row in table.Rows) //Every DataRow represents a new T object
{
TEntity entity = new TEntity();
foreach (var prop in properties)
{
MappingHelper.Map(typeof(TEntity), row, prop, entity);
}
entities.Add(entity);
}
return entities;
}
Now, our controller actions in the MVC project look like this:
[HttpGet]
public ActionResult AllUsers()
{
DataTable firstTable = DataTableGenerator.GetFirstTable();
DataTable secondTable = DataTableGenerator.GetSecondTable();
Mapper<User> mapper = new Mapper<User>();
List<User> users = mapper.Map(firstTable);
users.AddRange(mapper.Map(secondTable));
return View(users);
}
Whew! That was a lot of steps. But now we have a generic, custom mapper that takes DataTables and maps them into strongly-typed objects, which are much easier to work with in MVC.
You can grab a sample project on GitHub that has all of this code and some simple usage scenarios.
Happy Coding!
