C# Convert DataTable to List of Objects Dynamically

• Download Helpers.zip - 12.5 KB

Introduction

This tip describes how to convert any DataTable to List of Objects at runtime using Reflection.

Using the Code

I was refactoring a project that I did before and I noticed the need to convert the DataTables to their correspondent classes dynamically instead of repeating the whole code for every single DataTable, so I wrote and extension for DataTable and I used .NET reflection in order to be able to do it dynamically.

The idea is pretty simple.

• Get the columns names and types for DataTable
• Get the class attribute names and types
• Write converts function for DataTable Data Types to your properties Data Types

Fill the object with the related field in the DataRow based on the field name, but to be able to do it smoothly, you need to have the DataTable column names match the class properties names and that’s about it.

public static class Extensions
{
    /// <summary>
    /// Converts datatable to list<T> dynamically
    /// </summary>
    /// <typeparam name="T">Class name</typeparam>
    /// <param name="dataTable">data table to convert</param>
    /// <returns>List<T></returns>
    public static List<T> ToList<T>(this DataTable dataTable) where T : new()
    {
        var dataList = new List<T>();

        //Define what attributes to be read from the class
        const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance;

        //Read Attribute Names and Types
        var objFieldNames = typeof(T).GetProperties(flags).Cast<PropertyInfo>().
            Select(item => new 
            { 
                Name = item.Name, 
                Type = Nullable.GetUnderlyingType(item.PropertyType) ?? item.PropertyType 
            }).ToList();

        //Read Datatable column names and types
        var dtlFieldNames = dataTable.Columns.Cast<DataColumn>().
            Select(item => new { 
                Name = item.ColumnName, 
                Type=item.DataType 
            }).ToList();

        foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
        {
            var classObj = new T();
           
            foreach (var dtField in dtlFieldNames)
            {
                PropertyInfo propertyInfos = classObj.GetType().GetProperty(dtField.Name);

                var field = objFieldNames.Find(x => x.Name == dtField.Name);

                if (field != null)
                {

                    if (propertyInfos.PropertyType == typeof(DateTime))
                    {
                        propertyInfos.SetValue
                        (classObj, convertToDateTime(dataRow[dtField.Name]), null);
                    }
                    else if (propertyInfos.PropertyType == typeof(int))
                    {
                        propertyInfos.SetValue
                        (classObj, ConvertToInt(dataRow[dtField.Name]), null);
                    }
                    else if (propertyInfos.PropertyType == typeof(long))
                    {
                        propertyInfos.SetValue
                        (classObj, ConvertToLong(dataRow[dtField.Name]), null);
                    }
                    else if (propertyInfos.PropertyType == typeof(decimal))
                    {
                        propertyInfos.SetValue
                        (classObj, ConvertToDecimal(dataRow[dtField.Name]), null);
                    }
                    else if (propertyInfos.PropertyType == typeof(String))
                    {
                        if (dataRow[dtField.Name].GetType() == typeof(DateTime))
                        {
                            propertyInfos.SetValue
                            (classObj, ConvertToDateString(dataRow[dtField.Name]), null);
                        }
                        else
                        {
                            propertyInfos.SetValue
                            (classObj, ConvertToString(dataRow[dtField.Name]), null);
                        }
                    }
                }                
            }
            dataList.Add(classObj);
        }
        return dataList;
    }

    private static string ConvertToDateString(object date) 
    {
        if (date == null)
            return string.Empty;
       
        return SpecialDateTime.ConvertDate(Convert.ToDateTime(date));
    }

    private static string ConvertToString(object value)
    {
        return Convert.ToString(HelperFunctions.ReturnEmptyIfNull(value));
    }

    private static int ConvertToInt(object value) 
    {
        return Convert.ToInt32(HelperFunctions.ReturnZeroIfNull(value));
    }

    private static long ConvertToLong(object value)
    {
        return Convert.ToInt64(HelperFunctions.ReturnZeroIfNull(value));
    }

    private static decimal ConvertToDecimal(object value)
    {
        return Convert.ToDecimal(HelperFunctions.ReturnZeroIfNull(value));
    }

    private static DateTime convertToDateTime(object date)
    {
        return Convert.ToDateTime(HelperFunctions.ReturnDateTimeMinIfNull(date));
    }
}

Finally, you call it like this:

List<MyClass> list =  dt.ToList<MyClass>

Why Do You Need Such a Scenario

Will it be an arguable thing. Reflection comes at a cost of performance because of all those steps to be taken to identify object Class methods and properties, and execute them at run time, but it will come in handy when you want to abstract a method to accept Type and you don’t have many types, or you have minimal data to set or get per Type, not to mention that you need to do this for every time that the method was called, some say why not cache it since it is a costly operation as somebody commented, well this is also debatable how important is the data and what is the performance that you will get for such method in case all your data have a small footprint but a lot. Anyway, there are a lot of factors that should be taken into consideration when you come to a cross road and ask yourself if you need to cache objects? But this is beyond the scope of this tip.

What I’m trying to convey in this tip is how to use reflection to generalize the conversion from DataTable to a List of Objects and that’s about it. You can do the same thing in different ways but it all goes down on the same factors Readability, Performance, Scalability, and as a personal preference I always choose Readability, Scalability, and flexibility over performance if the later was negligible or find some moderate solution for the problem if performance was pretty poor.

History

• Version 1.0 - > 2014-12-06