January 9, 2013

Convert LINQ List To Datatable

Some times you need to convert LINQ query list to datatable in specific cases but there is none of any built-in function present in .net framework to convert it into datatable.

After using some built-in, I successfully able to convert LINQ list into datatable. Checkout my code.

------------
C#.NET :
------------

public static DataTable LINQToDataTable<T>(IEnumerable<T> linqList) 
{
    var dtReturn = new DataTable();
    PropertyInfo[] columnNameList = null;

    if (linqList == null) return dtReturn;

    foreach (T t in linqList) 
    {
        // Use reflection to get property names, to create table, Only first time, others will follow 
        if (columnNameList == null) 
        {
            columnNameList = ((Type)t.GetType()).GetProperties();

            foreach (PropertyInfo columnName in columnNameList) 
            {
                Type columnType = columnName.PropertyType;

                if ((columnType.IsGenericType) && (columnType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    columnType = columnType.GetGenericArguments()[0];
                }

                dtReturn.Columns.Add(new DataColumn(columnName.Name, columnType));
            }
        }

        DataRow dataRow = dtReturn.NewRow();

        foreach (PropertyInfo columnName in columnNameList)
        {
            dataRow[columnName.Name] = 
                columnName.GetValue(t, null) == null ? DBNull.Value : columnName.GetValue(t, null);
        }

        dtReturn.Rows.Add(dataRow);
    }

    return dtReturn;
}

------------
VB.NET :
------------

Public Shared Function LINQToDataTable(Of T)(linqList As IEnumerable(Of T)) As DataTable
 Dim dtReturn = New DataTable()
 Dim columnNameList As PropertyInfo() = Nothing

  If linqList Is Nothing Then
  Return dtReturn
 End If

  For Each t As T In linqList
  ' Use reflection to get property names, to create table, Only first time, others will follow 
  If columnNameList Is Nothing Then
   columnNameList = DirectCast(t.[GetType](), Type).GetProperties()

    For Each columnName As PropertyInfo In columnNameList
    Dim columnType As Type = columnName.PropertyType

     If (columnType.IsGenericType) AndAlso (columnType.GetGenericTypeDefinition() = GetType(Nullable(Of ))) Then
     columnType = columnType.GetGenericArguments()(0)
    End If

     dtReturn.Columns.Add(New DataColumn(columnName.Name, columnType))
   Next
  End If

   Dim dataRow As DataRow = dtReturn.NewRow()

   For Each columnName As PropertyInfo In columnNameList
   dataRow(columnName.Name) = If(columnName.GetValue(t, Nothing) Is Nothing, DBNull.Value, columnName.GetValue(t, Nothing))
  Next

   dtReturn.Rows.Add(dataRow)
 Next

  Return dtReturn
End Function