CODEWITHSUNDEEP
asp.netlinqdatatable
Dhina
Dhina

Reputation: 53

Linq to DataSet - Handling Null Values

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?

Upvotes: 5

Views: 2533

Answers (2)

TheDon
TheDon

Reputation: 388

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

Upvotes: 1

Yvo
Yvo

Reputation: 19263

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! :)

Upvotes: 5

Related Questions