39
\$\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\$
  • \$\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\$ – Jesse C. Slicer Sep 3 '13 at 4:52
  • \$\begingroup\$ Thanks Jesse. Anything else to improve? \$\endgroup\$ – devspider Sep 3 '13 at 4:55
  • 2
    \$\begingroup\$ I gotta say, this looks pretty tip-top and concise. \$\endgroup\$ – Jesse C. Slicer Sep 3 '13 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 '13 at 18:38
37
\$\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\$
  • 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 '15 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 '15 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 '15 at 21:54
  • 4
    \$\begingroup\$ @Nap The performance of this code would be horrifically slow due to reflection. Elegant solution with terrible performance. \$\endgroup\$ – Zer0 May 9 '15 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 '15 at 16:39
2
\$\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\$
  • \$\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\$ – moarboilerplate Aug 24 '15 at 20:45
  • \$\begingroup\$ @moarboilerplate easily added. \$\endgroup\$ – Jesse C. Slicer Aug 24 '15 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 '16 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 '16 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 '16 at 8:24

protected by Jamal Apr 28 '15 at 22:46

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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