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();

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>();

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>();
        }
    }
}