Converting DataTable to List of class

Question

I am using ExcelDataReader to import an Excel file to a dataset.

Example Excel table:

//ID Name Display Order Active
//1 John 1 1

ID, DisplayOrder and Active columns are read as double, so I have to convert them to long, int and bool types respectively. I need to create a list of type Category from the DataTable of the DataSet.

Will this code perform well? Any suggestions for a faster conversion of DataTable to List of class?

var list = result.Tables["Categories"].AsEnumerable()
.Skip(1)
.Select(dr =>
 new Category
 {
 Id = Convert.ToInt64(dr.Field<double>("ID")),
 Name = dr.Field<string>("Name"),
 DisplayOrder = Convert.ToInt32(dr.Field<double>("Display Order")),
 IsActive= dr.Field<double>("Active") == 1 ? true : false
 }
 ).ToList();

• Jesse C. Slicer: I'd probably rewrite that last line as IsActive= dr.Field<double>("Active") > 0 (your ternary is unnecessary as the comparison already evaluates to true or false) because of double rounding issues and equality comparisons.
• Jesse C. Slicer: I gotta say, this looks pretty tip-top and concise.
• Bobson: You can access DataRow fields by name directly: dr["ID"], dr["Name"] etc. They're of type object, but the Convert.To____() functions handle that.

Answer 1

I created an extension method for DataTable to convert them into a List<T>

public static class Helper
{
 /// <summary>
 /// Converts a DataTable to a list with generic objects
 /// </summary>
 /// <typeparam name="T">Generic object</typeparam>
 /// <param name="table">DataTable</param>
 /// <returns>List with generic objects</returns>
 public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
 {
 try
 {
 List<T> list = new List<T>();
 foreach (var row in table.AsEnumerable())
 {
 T obj = new T();
 foreach (var prop in obj.GetType().GetProperties())
 {
 try
 {
 PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
 propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
 }
 catch
 {
 continue;
 }
 }
 list.Add(obj);
 }
 return list;
 }
 catch
 {
 return null;
 }
 }
}

Example:

DataTable dtTable = GetEmployeeDataTable();
List<Employee> employeeList = dtTable.DataTableToList<Employee>();

• Krummelz: You are a lifesaver! I had no idea Convert.ChangeType even existed until today. This is much simpler than what I had been doing, trying to invoke TryParse on the destination type. And this also takes care of DBNull values in the DataTable. 10/10!
• Nap: Interesting code, I am just wondering of the performance of this in contrast to doing non generic code?
• GregTank: One update you may want to consider for your great answer is only update editable properties to reduce all the exceptions that get thrown. The following would update your code appropriately: foreach (var prop in obj.GetType().GetProperties()**.Where(p=>p.CanWrite)**)
• Zer0: @Nap The performance of this code would be horrifically slow due to reflection. Elegant solution with terrible performance.
• BornToCode: Just a side note: I advise to rename the method to ToList instead of DataTableToList because it's already an extension method of DataTable. And it'll also match the standard convention of ToString, ToChar etc.

Answer 2

You can lose some of the reflection badness in Gaui's answer with a little bit of refactoring and a little bit of caching as such:

public static class Helper
{
 private static readonly IDictionary<Type, ICollection<PropertyInfo>> _Properties =
 new Dictionary<Type, ICollection<PropertyInfo>>();
 /// <summary>
 /// Converts a DataTable to a list with generic objects
 /// </summary>
 /// <typeparam name="T">Generic object</typeparam>
 /// <param name="table">DataTable</param>
 /// <returns>List with generic objects</returns>
 public static IEnumerable<T> DataTableToList<T>(this DataTable table) where T : class, new()
 {
 try
 {
 var objType = typeof(T);
 ICollection<PropertyInfo> properties;
 lock (_Properties)
 {
 if (!_Properties.TryGetValue(objType, out properties))
 {
 properties = objType.GetProperties().Where(property => property.CanWrite).ToList();
 _Properties.Add(objType, properties);
 }
 }
 var list = new List<T>(table.Rows.Count);
 foreach (var row in table.AsEnumerable().Skip(1))
 {
 var obj = new T();
 foreach (var prop in properties)
 {
 try
 {
 var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
 var safeValue = row[prop.Name] == null ? null : Convert.ChangeType(row[prop.Name], propType);
 prop.SetValue(obj, safeValue, null);
 }
 catch
 {
 // ignored
 }
 }
 list.Add(obj);
 }
 return list;
 }
 catch
 {
 return Enumerable.Empty<T>();
 }
 }
}

• moarboilerplate: May want to check for a set method on the property or change to iterate over the columns in row.Table.Columns so that read-only properties are supported
• Jesse C. Slicer: @moarboilerplate easily added.
• Misiu: I really like Your solution, it works fine, but I get error because I have DateTime? and int? properties in my class, I get error saying I cant cast from System.DateTIme to System.Nullable. Can this be fixed?
• Misiu: I've found simple solution here: stackoverflow.com/a/3531824/965722 so maybe You could add this to Your code. This would help other finders like me.
• Misiu: @JesseC.Slicer I'm using this with large datatables that comes from other library (I can't change it). Your solution is using reflection. I found many articles about expression trees and lambdas, so I'm wondering it it would be possible to change Your code to use those instead of reflection.