Convert a Generic List to a DataTable Using Reflection

Today I needed a method to convert a generic list to a DataTable. The list contained simple records such as the class below:

public class MyRecord
{
    public int IntField;
    public int IntProperty {get; set;}
    public string StringProperty {get; set;}
    public bool BoolProperty {get; set;}
    public DateTime DateProperty {get; set;}
}

My first inclination was to just slap together something quick and dirty like:

#region MyRecordListToDataTable
/// <summary>
/// Converts a List<MyRecord> into a DataTable.
/// </summary>
/// <param name="list"></param>
/// <returns>DataTable</returns>
public static DataTable MyRecordListToDataTable(List<MyRecord> list)
{
    DataTable dt = new DataTable("MyRecordList");
    dt.Columns.Add("IntField", list[0].IntField.GetType());
    dt.Columns.Add("IntProperty", list[0].IntProperty.GetType());
    dt.Columns.Add("StringProperty", list[0].StringProperty.GetType());
    dt.Columns.Add("BoolProperty", list[0].BoolProperty.GetType());
    dt.Columns.Add("DateProperty", list[0].DateProperty.GetType());
 
    foreach (MyRecord item in list)
    {
        dt.Rows.Add(
            item.IntField,
            item.IntProperty,
            item.StringProperty,
            item.BoolProperty,
            item.DateProperty);
    }
 
    return dt;
}
#endregion

Admittedly, not the most elegant piece of code ever written, but it got the job done. Then I got to thinking... I use lots of generic lists -- wouldn't it be nice if I had a method that could convert any generic list into a DataTable without me having to hand-code the fields? So I started playing around with reflection to see if I could get what I needed from it.

The first tricky part was figuring out what the object type stored in the list was. I looked around Google and basically only found bad, fragile looking solutions involving parsing various string properties on the Type object. Eventually, I discovered the GetGenericArguments() method on MSDN of all places (MSDN is usually the last place I look for useful information). GetGenericArguments() returns a list of the types used declare the generic type:

Type elementType = listType.GetGenericArguments()[0];

So I was off and running. Getting a list of the members of the list's underlying type was pretty straight-forward, but also kind of useless:

MemberInfo[] miArray = elementType.GetMembers(
    BindingFlags.Public | BindingFlags.Instance);

As I stepped through my code, I could see the data types of my object's various fields using the debugger, but none of the MemberInfo object's public properties exposed the member's type. Finally, I figured out that I had to cast the MemberInfo into either a FieldInfo or a PropertyInfo to get at the good stuff. So now I could construct the table's schema:

foreach (MemberInfo mi in miArray)
{
    if (mi.MemberType == MemberTypes.Property)
    {
        PropertyInfo pi = mi as PropertyInfo;
        dt.Columns.Add(pi.Name, pi.PropertyType);
    }
    else if (mi.MemberType == MemberTypes.Field)
    {
        FieldInfo fi = mi as FieldInfo;
        dt.Columns.Add(fi.Name, fi.FieldType);
    }
}

Now came the really fun part -- how do I iterate through a list (that doesn't know it's a list) containing unknown objects?  First I tried to cast it to a List<object> on the theory that once I could iterate the list I'd be in a much better position to get at the objects it contained. But when I tried to cast it, I got a nasty error message that said: "Unable to cast object of type 'System.Collections.Generic.List`1[MyRecord]' to type 'System.Collections.Generic.List`1[System.Object]'". Well, that kind of sucks.

Next, I decided that there must be some way to use reflection to do the cast since, in theory, it knows everything it needs to about the objects involved but, after about an hour of Googling around, I concluded that there was no way to do it. If someone else knows a way, please drop me a line.

Just when I was about to give up, I stumbled across a post on how to determine if you are dealing with an indexed property when using reflection. It had an example that used the IList interface. Now, the IList interface is an old interface from the System.Collections namespace. I was using System.Collections.Generic, so I didn't really have high hopes, but decided to give it a try anyway. I added a using statement for System.Collections and tried to cast my list to an IList. It worked! I was back in the game:

IList il = list as IList; 

The final piece needed was a way to get at the values of each of my object's fields. I figured it would be possible via the MemberInfo/PropertyInfo/FieldInfo classes. A few minutes of scrounging around the web confirmed this. The Type class had a GetMember() method that took the name of the member to return as a string. And the PropertyInfo and FieldInfo classes had a GetValue() method that, if handed the instance of the object, would extract the value of the property or field they represented.

Since my columns were constructed using the names of the various fields and properties, I had everything I needed populate the table's rows. So, pulling it all together:

#region GenericListToDataTable
/// <summary>
/// Converts a generic List<> into a DataTable.
/// </summary>
/// <param name="list"></param>
/// <returns>DataTable</returns>
public static DataTable GenericListToDataTable(object list)
{
    DataTable dt = null;
    Type listType = list.GetType();
    if (listType.IsGenericType)
    {
        //determine the underlying type the List<> contains
        Type elementType = listType.GetGenericArguments()[0];
 
        //create empty table -- give it a name in case
        //it needs to be serialized
        dt = new DataTable(elementType.Name + "List");
 
        //define the table -- add a column for each public
        //property or field
        MemberInfo[] miArray = elementType.GetMembers(
            BindingFlags.Public | BindingFlags.Instance);
        foreach (MemberInfo mi in miArray)
        {
            if (mi.MemberType == MemberTypes.Property)
            {
                PropertyInfo pi = mi as PropertyInfo;
                dt.Columns.Add(pi.Name, pi.PropertyType);
            }
            else if (mi.MemberType == MemberTypes.Field)
            {
                FieldInfo fi = mi as FieldInfo;
                dt.Columns.Add(fi.Name, fi.FieldType);
            }
        }
 
        //populate the table
        IList il = list as IList;
        foreach (object record in il)
        {
            int i = 0;
            object[] fieldValues = new object[dt.Columns.Count];
            foreach (DataColumn c in dt.Columns)
            {
                MemberInfo mi = elementType.GetMember(c.ColumnName)[0];
                if (mi.MemberType == MemberTypes.Property)
                {
                    PropertyInfo pi = mi as PropertyInfo;
                    fieldValues[i] = pi.GetValue(record, null);
                }
                else if (mi.MemberType == MemberTypes.Field)
                {
                    FieldInfo fi = mi as FieldInfo;
                    fieldValues[i] = fi.GetValue(record);
                }
                i++;
            }
            dt.Rows.Add(fieldValues);
        }
    }
    return dt;
}
#endregion

The astute developers out there will recognize that there are two important things missing from the code above: error handling and code to ensure that only simple data types that SQL understands are included in the table. I left this stuff out of the example above to cut down on the clutter. Obviously, you should add it back in before using this code snippet.