48
\$\begingroup\$

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();
\$\endgroup\$
4
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Sep 3, 2013 at 4:52
  • \$\begingroup\$ Thanks Jesse. Anything else to improve? \$\endgroup\$
    – devspider
    Sep 3, 2013 at 4:55
  • 3
    \$\begingroup\$ I gotta say, this looks pretty tip-top and concise. \$\endgroup\$ Sep 3, 2013 at 5:05
  • \$\begingroup\$ 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. \$\endgroup\$
    – Bobson
    Sep 3, 2013 at 18:38

2 Answers 2

41
\$\begingroup\$

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>();
\$\endgroup\$
6
  • 2
    \$\begingroup\$ 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! \$\endgroup\$
    – Krummelz
    Jan 5, 2015 at 10:50
  • \$\begingroup\$ Interesting code, I am just wondering of the performance of this in contrast to doing non generic code? \$\endgroup\$
    – Nap
    Jan 8, 2015 at 7:55
  • 3
    \$\begingroup\$ 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)**) \$\endgroup\$
    – GregTank
    Apr 28, 2015 at 21:54
  • 7
    \$\begingroup\$ @Nap The performance of this code would be horrifically slow due to reflection. Elegant solution with terrible performance. \$\endgroup\$
    – Zer0
    May 9, 2015 at 10:13
  • 1
    \$\begingroup\$ 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. \$\endgroup\$
    – BornToCode
    Nov 26, 2015 at 16:39
4
\$\begingroup\$

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>();
        }
    }
}
\$\endgroup\$
9
  • \$\begingroup\$ 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 \$\endgroup\$ Aug 24, 2015 at 20:45
  • \$\begingroup\$ @moarboilerplate easily added. \$\endgroup\$ Aug 24, 2015 at 20:57
  • \$\begingroup\$ 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? \$\endgroup\$
    – Misiu
    Aug 26, 2016 at 11:41
  • \$\begingroup\$ 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. \$\endgroup\$
    – Misiu
    Aug 26, 2016 at 11:46
  • \$\begingroup\$ @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. \$\endgroup\$
    – Misiu
    Sep 2, 2016 at 8:24

Not the answer you're looking for? Browse other questions tagged or ask your own question.