My group regularly uses DataSet, DataTable, and DataRow objects in many of our apps.
(What? Don't look at me like that. These apps are old.)
Anyway, we're simultaneously trying to implement good C# and object-oriented programming principles while maintaining these old apps, so we often end up having to map data from a data set to a C# object. We did this enough times that I and a coworker (we'll call her Marlena) decided to sit down and just make up a new mapping system that we use to convert DataTable and DataRow objects to full C# class instances.
As always with my code-based posts, there's a GitHub project with a full working example app, so check that out too!
exceptionnotfound
One Jump Ahead
So here's a basic problem with converting from DataSet, DataTable, and DataRow objects to C# classes: we don't know at compile time what columns and tables exist in the set, so mapping solutions like AutoMapper won't work for this scenario. Our mapping system will have to assume what columns exist. But, in order to make it more reusable, we will make the mapping system return default values for any values which it does not locate.
There's also another, more complex problem: the databases we are acquiring our data from use many different column names to represent the same data. 20 years of different maintainers and little in the way of cohesive naming standards will do that do a database. So, if we needed a person's first name, the different databases might use:
- first_name
- firstName
- fname
- name_first
This, you might imagine, makes mapping anything rather difficult. Our system will also need to be able to map from many different column names.
Finally, this system wouldn't be worth much if it couldn't handle collections of objects as well as single objects, so we'll need to allow for that as well.
So, in short, our system needs to:
- Map from
DataTableandDataRowto C# classes. - Map from multiple different column names.
- Handle mapping to a collection of objects as well as a single object.
We'll need several pieces to accomplish this. But before we can even start building the mapping system, we must first acquire some sample data.
Mine, Mine, Mine
We're going to create some DataSet objects that we can test our system against. In the real world, you would use an actual database, but here (for simplicity's sake) we're just going to manually create some DataSet objects. Here's a sample class DataSetGenerator which will create two DataSet objects, Priests and Ranchers, each of which use different column names for the same data:
public static class DataSetGenerator
{
public static DataSet Priests()
{
DataTable priestsDataTable = new DataTable();
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "first_name",
DataType = typeof(string)
});
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "last_name",
DataType = typeof(string)
});
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "dob",
DataType = typeof(DateTime)
});
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "job_title",
DataType = typeof(string)
});
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "taken_name",
DataType = typeof(string)
});
priestsDataTable.Columns.Add(new DataColumn()
{
ColumnName = "is_american",
DataType = typeof(string)
});
priestsDataTable.Rows.Add(new object[] { "Lenny", "Belardo", new DateTime(1971, 3, 24), "Pontiff", "Pius XIII", "yes" });
priestsDataTable.Rows.Add(new object[] { "Angelo", "Voiello", new DateTime(1952, 11, 18), "Cardinal Secretary of State", "", "no" });
priestsDataTable.Rows.Add(new object[] { "Michael", "Spencer", new DateTime(1942, 5, 12), "Archbishop of New York", "", "yes" });
priestsDataTable.Rows.Add(new object[] { "Sofia", "(Unknown)", new DateTime(1974, 7, 2), "Director of Marketing", "", "no" });
priestsDataTable.Rows.Add(new object[] { "Bernardo", "Gutierrez", new DateTime(1966, 9, 16), "Master of Ceremonies", "", "no" });
DataSet priestsDataSet = new DataSet();
priestsDataSet.Tables.Add(priestsDataTable);
return priestsDataSet;
}
public static DataSet Ranchers()
{
DataTable ranchersTable = new DataTable();
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "firstName",
DataType = typeof(string)
});
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "lastName",
DataType = typeof(string)
});
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "dateOfBirth",
DataType = typeof(DateTime)
});
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "jobTitle",
DataType = typeof(string)
});
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "nickName",
DataType = typeof(string)
});
ranchersTable.Columns.Add(new DataColumn()
{
ColumnName = "isAmerican",
DataType = typeof(string)
});
ranchersTable.Rows.Add(new object[] { "Colt", "Bennett", new DateTime(1987, 1, 15), "Ranchhand", "", "y" });
ranchersTable.Rows.Add(new object[] { "Jameson", "Bennett", new DateTime(1984, 10, 10), "Ranchhand", "Rooster", "y" });
ranchersTable.Rows.Add(new object[] { "Beau", "Bennett", new DateTime(1944, 8, 9), "Rancher", "", "y" });
ranchersTable.Rows.Add(new object[] { "Margaret", "Bennett", new DateTime(1974, 7, 2), "Bar Owner", "Maggie", "y" });
ranchersTable.Rows.Add(new object[] { "Abigail", "Phillips", new DateTime(1987, 4, 24), "Teacher", "Abby", "y" });
DataSet ranchersDataSet = new DataSet();
ranchersDataSet.Tables.Add(ranchersTable);
return ranchersDataSet;
}
}
We'll test our system against this sample data.
Something There
Now we can build our actual mapping solution. First off, we need a way to decide what column names map to object properties. It was Marlena's idea to keep those things together, and so we came up with a class called DataNamesAttribute that looks like this:
[AttributeUsage(AttributeTargets.Property)]
public class DataNamesAttribute : Attribute
{
protected List<string> _valueNames { get; set; }
public List<string> ValueNames
{
get
{
return _valueNames;
}
set
{
_valueNames = value;
}
}
public DataNamesAttribute()
{
_valueNames = new List<string>();
}
public DataNamesAttribute(params string[] valueNames)
{
_valueNames = valueNames.ToList();
}
}
This attribute can then be used (in fact, can only be used, due to the AttributeUsage(AttributeTargets.Property) declaration) on properties of other classes. Let's say we're going to map to a Person class. We would use DataNamesAttribute like so:
public class Person
{
[DataNames("first_name", "firstName")]
public string FirstName { get; set; }
[DataNames("last_name", "lastName")]
public string LastName { get; set; }
[DataNames("dob", "dateOfBirth")]
public DateTime DateOfBirth { get; set; }
[DataNames("job_title", "jobTitle")]
public string JobTitle { get; set; }
[DataNames("taken_name", "nickName")]
public string TakenName { get; set; }
[DataNames("is_american", "isAmerican")]
public bool IsAmerican { get; set; }
}
Now that we know where the data needs to end up, let's start mapping out the mapper.
Reflection
Our mapper class will be a generic C# class so that we can map from DataTable or DataRow objects to any kind of object. We'll need two methods to get different kinds of data:
public class DataNamesMapper<TEntity> where TEntity : class, new()
{
public TEntity Map(DataRow row) { ... }
public IEnumerable<TEntity> Map(DataTable table) { ... }
}
Let's start with the Map() method. We need to do three things:
- Figure out what columns exist in this row.
- Determine if the
TEntitywe are mapping to has any properties with the same name as any of the columns (aka the Data Names) AND - Map the value from the
DataRowto theTEntity.
Here's how we do this, using just a bit of reflection:
public TEntity Map(DataRow row)
{
//Step 1 - Get the Column Names
var columnNames = row.Table.Columns
.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToList();
//Step 2 - Get the Property Data Names
var properties = (typeof(TEntity)).GetProperties()
.Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())
.ToList();
//Step 3 - Map the data
TEntity entity = new TEntity();
foreach (var prop in properties)
{
PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
}
return entity;
}
Of course, we also need to handle the other method, the one where we can get a collection of TEntity:
public IEnumerable<TEntity> Map(DataTable table)
{
//Step 1 - Get the Column Names
var columnNames = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
//Step 2 - Get the Property Data Names
var properties = (typeof(TEntity)).GetProperties()
.Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())
.ToList();
//Step 3 - Map the data
List<TEntity> entities = new List<TEntity>();
foreach (DataRow row in table.Rows)
{
TEntity entity = new TEntity();
foreach (var prop in properties)
{
PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
}
entities.Add(entity);
}
return entities;
}
You might be wondering just what the heck the PropertyMapHelper class is. If you are, you might also be about to regret it.
Dig a Little Deeper
The PropertyMapHelper, as suggested by the name, maps values to different primitive types (int, string, DateTime, etc.). Here's that Map() method we saw earlier:
public static void Map(Type type, DataRow row, PropertyInfo prop, object entity)
{
List<string> columnNames = AttributeHelper.GetDataNames(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;
}
}
}
}
There are two pieces in this method that we haven't defined yet: the AttributeHelper class and the ParsePrimitive() method. AttributeHelper is a rather simple class that merely gets the list of column names from the DataNamesAttribute:
public static List<string> GetDataNames(Type type, string propertyName)
{
var property = type
.GetProperty(propertyName)
.GetCustomAttributes(false)
.Where(x => x.GetType().Name == "DataNamesAttribute")
.FirstOrDefault();
if (property != null)
{
return ((DataNamesAttribute)property).ValueNames;
}
return new List<string>();
}
The other we need to define in ParsePrimitive(), which as its name suggests will parse the values into primitive types. Essentially what this class does is assign a value to a passed-in property reference (represented by the PropertyInfo class). I'm not going to post the full code on this post (you can see it over on GitHub), so here's a snippet of what this method does:
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);
}
}
...
}
That's the bottom of the rabbit hole, as it were. Now, we can use the DataSet objects we created earlier and our mapping system to see if we can map this data correctly.
Two Worlds
Here's a quick program that can test our new mapping system:
class Program
{
static void Main(string[] args)
{
var priestsDataSet = DataSetGenerator.Priests();
DataNamesMapper<Person> mapper = new DataNamesMapper<Person>();
List<Person> persons = mapper.Map(priestsDataSet.Tables[0]).ToList();
var ranchersDataSet = DataSetGenerator.Ranchers();
persons.AddRange(mapper.Map(ranchersDataSet.Tables[0]));
foreach (var person in persons)
{
Console.WriteLine("First Name: " + person.FirstName
+ ", Last Name: " + person.LastName
+ ", Date of Birth: "
+ person.DateOfBirth.ToShortDateString()
+ ", Job Title: " + person.JobTitle
+ ", Nickname: " + person.TakenName
+ ", Is American: " + person.IsAmerican);
}
Console.ReadLine();
}
}
When we run this app (which you can do too), we will get the following output:
Which is exactly what we want!
(I mean, really, did you expect me to blog about something that didn't work?)
Go the Distance
It concerns me that this system is overly complicated, and I'd happily take suggestions on how to make it more straightforward. While I do like how all we need to do is place the DataNamesAttribute on the correct properties and then call an instance of DataNamesMapper<T>, I feel like the whole thing could be easier somehow. Believe it or not, this version is actually simpler than the one we're actually using in our internal apps.
Also, check out the sample project over on GitHub, fork it, test it, whatever. If it helped you out, or if you can improve it, let me know in the comments!
If this post helped you, would you consider buying me a coffee? Your support helps fund all of Exception Not Found's projects and helps keep traditional ads off the site. Thanks for your support!
Finally, extra special bonus points will go to anyone who can figure out a) what in the world those odd section titles are about and b) where I got the sample data from.
Happy Coding!
