DataSet does not support System.Nullable<> in Export

Question

I was trying to generate a Report using Export to Excell, PDF, TextFile. Well I am doing this in MVC. I have a class which I named SPBatch (which is the exact name of my Stored Procedure in my SQL) and it contains the following:

public string BatchNo { get; set; } public string ProviderName { get; set; } public Nullable<System.Int32> NoOfClaims { get; set; } public Nullable<System.Int32> TotalNoOfClaims { get; set; } public Nullable<System.Decimal> TotalBilled { get; set; } public Nullable<System.Decimal> TotalInputtedBill { get; set; } public Nullable<System.DateTime> DateCreated { get; set; } public Nullable<System.DateTime> DateSubmitted { get; set; } public Nullable<System.DateTime> DueDate { get; set; } public string Status { get; set; } public string RefNo { get; set; } public string BatchStatus { get; set; } public string ClaimType { get; set; } 

as you can see some of my Columns are declared as Nullable. It went smoothly from searching and displaying the results in a table. I have several buttons below which are image buttons for export and every time I try to export in Excel, I always get the problem "DataSet does not support System.Nullable<>" in this part of my code:

foreach (MemberInfo mi in miArray) {     if (mi.MemberType == MemberTypes.Property)     {         PropertyInfo pi = mi as PropertyInfo;         dt.Columns.Add(pi.Name, pi.PropertyType); //where the error pop's up.      }     else if (mi.MemberType == MemberTypes.Field)     {         FieldInfo fi = mi as FieldInfo;         dt.Columns.Add(fi.Name, fi.FieldType);     } } 

the error shows up on the one with a comment. Can you help me what to do? I tried adding DBNull in my code but still I get the same error. I tried removing Nullable in my SPBatch but I get an error that some tables are need to be declared as Nullable.

What should I do?

Answer 1

try with

dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(             pi.PropertyType) ?? pi.PropertyType); 

Answer 2

Thanks to a C# version of a generating a datatable and some hacking around, I can offer this answer in VB - I put it on here because I've just had a lot of hassle wanting to get a filterable dataset from a stored proc whilst using a simple datalayer. I hope it helps someone else!

Note: The use case is where you wish to use BindingSource.Filter = "some query string":

Imports System.Reflection  Public Module Extenders <System.Runtime.CompilerServices.Extension> Public Function ToDataTable(Of T)(collection As IEnumerable(Of T), tableName As String) As DataTable     Dim tbl As DataTable = ToDataTable(collection)     tbl.TableName = tableName     Return tbl End Function  <System.Runtime.CompilerServices.Extension> Public Function ToDataTable(Of T)(collection As IEnumerable(Of T)) As DataTable     Dim dt As New DataTable()      Dim tt As Type = GetType(T)     Dim pia As PropertyInfo() = tt.GetProperties()      'Create the columns in the DataTable      For Each pi As PropertyInfo In pia         Dim a =  If(Nullable.GetUnderlyingType(pi.PropertyType), pi.PropertyType)         dt.Columns.Add(pi.Name, If(Nullable.GetUnderlyingType(pi.PropertyType), pi.PropertyType))     Next      'Populate the table      For Each item As T In collection         Dim dr As DataRow = dt.NewRow()         dr.BeginEdit()          For Each pi As PropertyInfo In pia              dr(pi.Name) = If(Nullable.GetUnderlyingType(pi.PropertyType) Is GetType(DateTime), DBNull.Value, pi.GetValue(item, Nothing))         Next         dr.EndEdit()         dt.Rows.Add(dr)     Next     Return dt End Function  End Module