5

I have a requirement to convert LINQ to DataTable.

I stole the following Extension Method from StackOverflow:

public static DataTable ToDataTable<T>(this IEnumerable<T> items)
        {
            var tb = new DataTable(typeof(T).Name);
            PropertyInfo[] props = 
            typeof(T).GetProperties(BindingFlags.Public
                                  | BindingFlags.Instance);

            foreach (var prop in props)
            {
                tb.Columns.Add(prop.Name, prop.PropertyType);
            }

            foreach (var item in items)
            {
                var values = new object[props.Length];
                for (var i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }
            return tb;
  }

When the table contains null values it throws exception. (i.e) 

DataSet does not support System.Nullable<>

Comission (Decimal type) column contains null value)

at

tb.Columns.Add(prop.Name, prop.PropertyType);

How to fix it?

Dhina
  • 53
  • 1
  • 3

2 Answers2

5

Here's a pimped version:

public static DataTable ToDataTable<T>(this IEnumerable<T> items) { 
    DataTable table = new DataTable(typeof(T).Name);
    PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); 

    foreach (var prop in props) {
        Type propType = prop.PropertyType;

        // Is it a nullable type? Get the underlying type
        if (propType.IsGenericType && propType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            propType = new NullableConverter(propType).UnderlyingType; 

        table.Columns.Add(prop.Name, propType);
    } 

    foreach (var item in items) { 
        var values = new object[props.Length]; 
        for (var i = 0; i < props.Length; i++)
            values[i] = props[i].GetValue(item, null);  

        table.Rows.Add(values); 
    }

    return table;
}

Edit: Modified my code a bit, tested it, it works! :)

Yvo
  • 18,681
  • 11
  • 71
  • 90
1

You could check for null, and then store DBNull.Value as the value instead. There is an MSDN article about null values, that specifically calls out the lack of support for Nullable<> by Datasets.

Where you have

values[i] = props[i].GetValue(item, null);

make it

var value = props[i].GetValue(item, null);
values[i] = value ?? ((object)DBNull.Value);
TheDon
  • 388
  • 1
  • 2
  • 11