Convert generic List Enumerable to DataTable

Question

I have few methods that returns different Generic Lists    Exists in  net any class static method or whatever to convert any list into a datatable  The only thing that i can imagine is use Reflection to do this   IF i have this   List lt Whatever gt  whatever   new List lt Whatever gt        This next code doesn t work of course  but i would like to have the possibility of   DataTable dt    DataTable  whatever

User · Answer

This link on MSDN is worth a visit  How to  Implement CopyToDataTable lt T gt  Where the Generic Type T Is Not a DataRow  This adds an extension method that lets you do this      Create a sequence   Item   items   new Item      new Book Id   1  Price   13 50  Genre    Comedy   Author    Gustavo Achong       new Book Id   2  Price   8 50  Genre    Drama   Author    Jessie Zeng      new Movie Id   1  Price   22 99  Genre    Comedy   Director    Marissa Barnes      new Movie Id   1  Price   13 40  Genre    Action   Director    Emmanuel Fernandez         Query for items with price greater than 9 99  var query   from i in items              where i Price  gt  9 99              orderby i Price              select i      Load the query results into new DataTable  DataTable table   query CopyToDataTable

User · Answer

private DataTable CreateDataTable IList lt T gt  item                        Type type   typeof T               var properties   type GetProperties                 DataTable dataTable   new DataTable                foreach  PropertyInfo info in properties                                dataTable Columns Add new DataColumn info Name  Nullable GetUnderlyingType info PropertyType     info PropertyType                               foreach  T entity in item                                object   values   new object properties Length                   for  int i   0  i  lt  properties Length  i                                          values i    properties i  GetValue entity                                      dataTable Rows Add values                             return dataTable

User · Answer

List lt YourModel gt  data   new List lt YourModel gt     DataTable dataTable   Newtonsoft Json JsonConvert DeserializeObject lt DataTable gt  Newtonsoft Json JsonConvert SerializeObject data

User · Answer

Simplest answer could be this one      https   stackoverflow com a 60776314 10235747  1

User · Answer

I ve written a small library myself to accomplish this task  It uses reflection only for the first time an object type is to be translated to a datatable  It emits a method that will do all the work translating an object type   Its blazing fast  You can find it here  ModelShredder on GoogleCode

User · Answer

using System  using System Collections Generic  using System Linq  using System Web  using System Web UI  using System Web UI WebControls  using System Data  using System ComponentModel   public partial class Default3   System Web UI Page       protected void Page Load object sender  EventArgs e                DataTable dt   new DataTable            dt   lstEmployee ConvertToDataTable              public static DataTable ConvertToDataTable lt T gt  IList lt T gt  list  where T   class               try                       DataTable table   CreateDataTable lt T gt                 Type objType   typeof T               PropertyDescriptorCollection properties   TypeDescriptor GetProperties objType               foreach  T item in list                                DataRow row   table NewRow                    foreach  PropertyDescriptor property in properties                                        if   CanUseType property PropertyType   continue                      row property Name    property GetValue item     DBNull Value                                     table Rows Add row                             return table                    catch  DataException ex                        return null                    catch  Exception ex                        return null                       private static DataTable CreateDataTable lt T gt    where T   class               Type objType   typeof T           DataTable table   new DataTable objType Name           PropertyDescriptorCollection properties   TypeDescriptor GetProperties objType           foreach  PropertyDescriptor property in properties                        Type propertyType   property PropertyType              if   CanUseType propertyType   continue                 nullables must use underlying types             if  propertyType IsGenericType  amp  amp  propertyType GetGenericTypeDefinition      typeof Nullable lt  gt                    propertyType   Nullable GetUnderlyingType propertyType                 enums also need special treatment             if  propertyType IsEnum                  propertyType   Enum GetUnderlyingType propertyType               table Columns Add property Name  propertyType                     return table              private static bool CanUseType Type propertyType                  only strings and value types         if  propertyType IsArray  return false          if   propertyType IsValueType  amp  amp  propertyType    typeof string   return false          return true

User · Answer

Dim counties As New List Of County   Dim dtCounties As DataTable  dtCounties    combinedRefRepository Get Counties    If dtCounties Rows Count  lt  gt  0 Then     For Each row As DataRow In dtCounties Rows       Dim county As New County       county CountyId   row Item 0  ToString         county CountyName   row Item 1  ToString   ToUpper         counties Add county      Next     dtCounties Dispose    End If

User · Answer

A small change to Marc s answer to make it work with value types like List lt string gt  to data table  public static DataTable ListToDataTable lt T gt  IList lt T gt  data        DataTable table   new DataTable           special handling for value types and string     if  typeof T  IsValueType    typeof T  Equals typeof string                   DataColumn dc   new DataColumn  quot Value quot   typeof T            table Columns Add dc           foreach  T item in data                        DataRow dr   table NewRow                dr 0    item              table Rows Add dr                       else               PropertyDescriptorCollection properties   TypeDescriptor GetProperties typeof T            foreach  PropertyDescriptor prop in properties                        table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType                     foreach  T item in data                        DataRow row   table NewRow                foreach  PropertyDescriptor prop in properties                                try                                       row prop Name    prop GetValue item     DBNull Value                                    catch  Exception ex                                        row prop Name    DBNull Value                                              table Rows Add row                       return table

User · Answer

To convert a generic list to data table  you could use the DataTableGenerator  This library lets you convert your list into a data table with multi-feature like    Translate data table header   specify some column to show

User · Answer

I also had to come up with an alternate solution  as none of the options listed here worked in my case  I was using an IEnumerable which returned an IEnumerable and the properties couldn t be enumerated  This did the trick      remove  this  if not on C  3 0    NET 3 5 public static DataTable ConvertToDataTable lt T gt  this IEnumerable lt T gt  data        List lt IDataRecord gt  list   data Cast lt IDataRecord gt    ToList         PropertyDescriptorCollection props   null      DataTable table   new DataTable        if  list    null  amp  amp  list Count  gt  0                props   TypeDescriptor GetProperties list 0            for  int i   0  i  lt  props Count  i                          PropertyDescriptor prop   props i               table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType                       if  props    null                object   values   new object props Count           foreach  T item in data                        for  int i   0  i  lt  values Length  i                                  values i    props i  GetValue item     DBNull Value                            table Rows Add values                       return table

User · Answer

I realize that this has been closed for a while  however  I had a solution to this specific problem but needed a slight twist  the columns and data table needed to be predefined   already instantiated   Then I needed to simply insert the types into the data table   So here s an example of what I did   public static class Test       public static void Main                 var dataTable   new System Data DataTable Guid NewGuid   ToString              var columnCode   new DataColumn  Code            var columnLength   new DataColumn  Length            var columnProduct   new DataColumn  Product             dataTable Columns AddRange new DataColumn                                 columnCode                  columnLength                  columnProduct                          var item   new List lt SomeClass gt              item Select data   gt  new                       data Id              data Name              data SomeValue            AddToDataTable dataTable            static class Extensions       public static void AddToDataTable lt T gt  this IEnumerable lt T gt  enumerable  System Data DataTable table                if  enumerable FirstOrDefault      null                        table Rows Add new    string Empty                return                     var properties   enumerable FirstOrDefault   GetType   GetProperties             foreach  var item in enumerable                        var row   table NewRow                foreach  var property in properties                                row property Name    item GetType   InvokeMember property Name  BindingFlags GetProperty  null  item  null                             table Rows Add row

User · Answer

If you are using VB NET then this class does the job   Imports System Reflection      lt summary gt      Convert any List Of T  to a DataTable with correct column types and converts Nullable Type values to DBNull      lt  summary gt   Public Class ConvertListToDataset      Public Function ListToDataset Of T  ByVal list As IList Of T   As DataTable          Dim dt As New DataTable               Create the DataTable columns            For Each pi As PropertyInfo In GetType T  GetProperties               If pi PropertyType IsValueType Then                 Debug Print pi Name              End If             If IsNothing Nullable GetUnderlyingType pi PropertyType   Then                 dt Columns Add pi Name  pi PropertyType              Else                 dt Columns Add pi Name  Nullable GetUnderlyingType pi PropertyType               End If         Next              Populate the DataTable with the values in the Items in List            For Each item As T In list             Dim dr As DataRow   dt NewRow               For Each pi As PropertyInfo In GetType T  GetProperties                   dr pi Name    IIf IsNothing pi GetValue item    DBNull Value  pi GetValue item               Next             dt Rows Add dr          Next         Return dt      End Function  End Class

User · Answer

It s also possible through XmlSerialization  The idea is - serialize to  XML  and then  readXml  method of  DataSet    I use this code  from an answer in SO  forgot where           public static string SerializeXml lt T gt  T value  where T   class               if  value    null                        return null                     XmlSerializer serializer   new XmlSerializer typeof T             XmlWriterSettings settings   new XmlWriterSettings             settings Encoding   new UnicodeEncoding false  false           settings Indent   false          settings OmitXmlDeclaration   false             no BOM in a  NET string          using  StringWriter textWriter   new StringWriter                          using  XmlWriter xmlWriter   XmlWriter Create textWriter  settings                                serializer Serialize xmlWriter  value                             return textWriter ToString                     so then it s as simple as               string xmlString   Utility SerializeXml trans InnerList            DataSet ds   new DataSet  New DataSet            using  XmlReader reader   XmlReader Create new StringReader xmlString                           ds Locale   System Threading Thread CurrentThread CurrentCulture              ds ReadXml reader               Not sure how it stands against all the other answers to this post  but it s also a possibility

User · Answer

if you have properties in your class this line of code is OK     PropertyDescriptorCollection props               TypeDescriptor GetProperties typeof T      but if you have all public fields then use this   public static DataTable ToDataTable lt T gt    IList lt T gt  data                    FieldInfo   myFieldInfo          Type myType   typeof T              Get the type and fields of FieldInfoClass          myFieldInfo   myType GetFields BindingFlags NonPublic   BindingFlags Instance               BindingFlags Public            DataTable dt   new DataTable            for  int i   0  i  lt  myFieldInfo Length  i                              FieldInfo property   myFieldInfo i               dt Columns Add property Name  property FieldType                         object   values   new object myFieldInfo Length           foreach  T item in data                            for  int i   0  i  lt  values Length  i                                      values i    myFieldInfo i  GetValue item                                 dt Rows Add values                         return dt              the original answer is from above   I just edited to use fields instead of properties  and to use it do this   DataTable dt   new DataTable                dt   ToDataTable myBriefs               gridData DataSource   dt              gridData DataBind

User · Answer

I think it s more convenient and easy to use         List lt Whatever gt   lobj  new List lt Whatever gt          var json   JsonConvert SerializeObject  lobj                   DataTable dt    DataTable JsonConvert DeserializeObject json   typeof DataTable

User · Answer

Marc Gravell s answer but in VB NET  Public Shared Function ToDataTable Of T  data As IList Of T   As DataTable     Dim props As PropertyDescriptorCollection   TypeDescriptor GetProperties GetType T       Dim table As New DataTable       For i As Integer   0 To props Count - 1             Dim prop As PropertyDescriptor   props i              table Columns Add prop Name  prop PropertyType      Next     Dim values As Object     New Object props Count - 1         For Each item As T In data             For i As Integer   0 To values Length - 1                     values i    props i  GetValue item              Next             table Rows Add values      Next     Return table End Function

User · Answer

This is a simple mix of the solutions  It work with Nullable types   public static DataTable ToDataTable lt T gt  this IList lt T gt  list      PropertyDescriptorCollection props   TypeDescriptor GetProperties typeof T      DataTable table   new DataTable      for  int i   0  i  lt  props Count  i            PropertyDescriptor prop   props i       table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType         object   values   new object props Count     foreach  T item in list          for  int i   0  i  lt  values Length  i          values i    props i  GetValue item     DBNull Value      table Rows Add values         return table

User · Answer

A 2019 answer if you re using  NET Core - use the Nuget ToDataTable library  Advantages    Better performance than FastMember Also creates structured SqlParameters for use as SQL Server Table-Valued Parameters   Disclaimer - I m the author of ToDataTable  Performance - I span up some Benchmark  Net tests and included them in the ToDataTable repo  The results were as follows   Creating a 100 000 Row Datatable                              MacOS         Windows Reflection                 818 5 ms      818 3 ms FastMember from           1105 5 ms      976 4 ms  Mark s answer Improved FastMember        524 6 ms      456 4 ms ToDataTable                449 0 ms      376 5 ms   The FastMember method suggested in Marc s answer seemed to perform worse than Mary s answer which used reflection  but I rolled another method using a FastMember TypeAccessor and it performed much better  Nevertheless the ToDataTable package outperformed the lot

User · Answer

Another approach is the above     List lt WhateEver gt  lst   getdata      string json   Newtonsoft Json JsonConvert SerializeObject lst     DataTable pDt   JsonConvert DeserializeObject lt DataTable gt  json

User · Answer

Se prob   el m  todo para que acepte campos con null      remove  this  if not on C  3 0    NET 3 5     public static DataTable ToDataTable lt T gt  IList lt T gt  data                PropertyDescriptorCollection props               TypeDescriptor GetProperties typeof T            DataTable table   new DataTable            Type Propiedad   null          for  int i   0  i  lt  props Count  i                          PropertyDescriptor prop   props i               Propiedad   prop PropertyType              if  Propiedad IsGenericType  amp  amp  Propiedad GetGenericTypeDefinition      typeof Nullable lt  gt                                   Propiedad   Nullable GetUnderlyingType Propiedad                             table Columns Add prop Name  Propiedad                     object   values   new object props Count           foreach  T item in data                        for  int i   0  i  lt  values Length  i                                  values i    props i  GetValue item                             table Rows Add values                     return table

User · Answer

public DataTable ConvertToDataTable lt T gt  IList lt T gt  data        PropertyDescriptorCollection properties           TypeDescriptor GetProperties typeof T         DataTable table   new DataTable         foreach  PropertyDescriptor prop in properties              table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType        foreach  T item in data                DataRow row   table NewRow            foreach  PropertyDescriptor prop in properties                       row prop Name    prop GetValue item     DBNull Value                    table Rows Add row             return table

User · Answer

To Convert Generic list into DataTable  using Newtonsoft Json   public DataTable GenericToDataTable IList lt T gt  list        var json   JsonConvert SerializeObject list       DataTable dt    DataTable JsonConvert DeserializeObject json   typeof DataTable         return dt

User · Answer

This is the simple Console Application to convert List to Datatable   using System  using System Collections Generic  using System Linq  using System Text  using System Data  using System ComponentModel   namespace ConvertListToDataTable       public static class Program               public static void Main string   args                        List lt MyObject gt  list   new List lt MyObject gt                 for  int i   0  i  lt  5  i                                  list Add new MyObject   Sno   i  Name   i ToString      -KarthiK   Dat   DateTime Now AddSeconds i                                 DataTable dt   ConvertListToDataTable list               foreach  DataRow row in dt Rows                                Console WriteLine                    for  int x   0  x  lt  dt Columns Count  x                                          Console Write row x  ToString                                                       Console ReadLine                       public class MyObject                       public int Sno   get  set                public string Name   get  set                public DateTime Dat   get  set                       public static DataTable ConvertListToDataTable lt T gt  this List lt T gt  iList                        DataTable dataTable   new DataTable                PropertyDescriptorCollection props   TypeDescriptor GetProperties typeof T                for  int i   0  i  lt  props Count  i                                  PropertyDescriptor propertyDescriptor   props i                   Type type   propertyDescriptor PropertyType                   if  type IsGenericType  amp  amp  type GetGenericTypeDefinition      typeof Nullable lt  gt                        type   Nullable GetUnderlyingType type                    dataTable Columns Add propertyDescriptor Name  type                             object   values   new object props Count               foreach  T iListItem in iList                                for  int i   0  i  lt  values Length  i                                          values i    props i  GetValue iListItem                                     dataTable Rows Add values                             return dataTable

User · Answer

Here s a nice 2013 update using FastMember from NuGet  IEnumerable lt SomeType gt  data       DataTable table   new DataTable    using var reader   ObjectReader Create data         table Load reader      This uses FastMember s meta-programming API for maximum performance  If you want to restrict it to particular members  or enforce the order   then you can do that too  IEnumerable lt SomeType gt  data       DataTable table   new DataTable    using var reader   ObjectReader Create data   quot Id quot    quot Name quot    quot Description quot          table Load reader      Editor s Dis claimer  FastMember is a Marc Gravell project  It s gold and full-on flies   Yes  this is pretty much the exact opposite of this one  reflection would suffice - or if you need quicker  HyperDescriptor in 2 0  or maybe Expression in 3 5  Actually  HyperDescriptor should be more than adequate  For example     remove  quot this quot  if not on C  3 0    NET 3 5 public static DataTable ToDataTable lt T gt  this IList lt T gt  data        PropertyDescriptorCollection props           TypeDescriptor GetProperties typeof T        DataTable table   new DataTable        for int i   0   i  lt  props Count   i                  PropertyDescriptor prop   props i           table Columns Add prop Name  prop PropertyType             object   values   new object props Count       foreach  T item in data                for  int i   0  i  lt  values Length  i                          values i    props i  GetValue item                     table Rows Add values             return table             Now with one line you can make this many many times faster than reflection  by enabling HyperDescriptor for the object-type T    Edit re performance query  here s a test rig with results  Vanilla 27179 Hyper   6997  I suspect that the bottleneck has shifted from member-access to DataTable performance    I doubt you ll improve much on that    Code  using System  using System Collections Generic  using System ComponentModel  using System Data  using System Diagnostics  public class MyData       public int A   get  set        public string B   get  set        public DateTime C   get  set        public decimal D   get  set        public string E   get  set        public int F   get  set       static class Program       static void RunTest List lt MyData gt  data  string caption                GC Collect GC MaxGeneration  GCCollectionMode Forced           GC WaitForPendingFinalizers            GC WaitForFullGCComplete            Stopwatch watch   Stopwatch StartNew            for  int i   0  i  lt  500  i                          data ToDataTable                      watch Stop            Console WriteLine caption    quot  t quot    watch ElapsedMilliseconds             static void Main                 List lt MyData gt  foos   new List lt MyData gt             for  int i   0   i  lt  5000   i                  foos Add new MyData                  just gibberish                    A   i                  B   i ToString                    C   DateTime Now AddSeconds i                   D   i                  E    quot hello quot                   F   i   2                                   RunTest foos   quot Vanilla quot            Hyper ComponentModel HyperTypeDescriptionProvider Add              typeof MyData            RunTest foos   quot Hyper quot            Console ReadLine       return to exit

User · Answer

try this   public static DataTable ListToDataTable lt T gt  IList lt T gt  lst         currentDT   CreateTable lt T gt          Type entType   typeof T        PropertyDescriptorCollection properties   TypeDescriptor GetProperties entType       foreach  T item in lst                DataRow row   currentDT NewRow            foreach  PropertyDescriptor prop in properties                         if  prop PropertyType    typeof Nullable lt decimal gt      prop PropertyType    typeof Nullable lt int gt      prop PropertyType    typeof Nullable lt Int64 gt                                  if  prop GetValue item     null                      row prop Name    0                  else                     row prop Name    prop GetValue item                             else                 row prop Name    prop GetValue item                                          currentDT Rows Add row              return currentDT     public static DataTable CreateTable lt T gt          Type entType   typeof T       DataTable tbl   new DataTable DTName       PropertyDescriptorCollection properties   TypeDescriptor GetProperties entType       foreach  PropertyDescriptor prop in properties                if  prop PropertyType    typeof Nullable lt decimal gt                 tbl Columns Add prop Name  typeof decimal            else if  prop PropertyType    typeof Nullable lt int gt                tbl Columns Add prop Name  typeof int            else if  prop PropertyType    typeof Nullable lt Int64 gt                tbl Columns Add prop Name  typeof Int64            else              tbl Columns Add prop Name  prop PropertyType             return tbl

User · Answer

I had to modify Marc Gravell s sample code to handle nullable types and null values  I have included a working version below  Thanks Marc   public static DataTable ToDataTable lt T gt  this IList lt T gt  data        PropertyDescriptorCollection properties            TypeDescriptor GetProperties typeof T        DataTable table   new DataTable        foreach  PropertyDescriptor prop in properties          table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType       foreach  T item in data                DataRow row   table NewRow            foreach  PropertyDescriptor prop in properties               row prop Name    prop GetValue item     DBNull Value          table Rows Add row             return table

User · Answer

If you want to use reflection and set columns order  include only some columns  Exclude some columns try this           private static DataTable ConvertToDataTable lt T gt  IList lt T gt  data  string   fieldsToInclude   null  string   fieldsToExclude   null                PropertyDescriptorCollection properties   TypeDescriptor GetProperties typeof T            DataTable table   new DataTable            foreach  PropertyDescriptor prop in properties                        if   fieldsToInclude    null  amp  amp   fieldsToInclude Contains prop Name                       fieldsToExclude    null  amp  amp  fieldsToExclude Contains prop Name                    continue              table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType                      foreach  T item in data                        var atLeastOnePropertyExists   false              DataRow row   table NewRow                foreach  PropertyDescriptor prop in properties                                 if   fieldsToInclude    null  amp  amp   fieldsToInclude Contains prop Name       fieldsToExclude    null  amp  amp  fieldsToExclude Contains prop Name                        continue                   row prop Name    prop GetValue item     DBNull Value                  atLeastOnePropertyExists   true                             if atLeastOnePropertyExists  table Rows Add row                       if  fieldsToInclude    null              SetColumnsOrder table  fieldsToInclude            return table              private static void SetColumnsOrder DataTable table  params String   columnNames                int columnIndex   0          foreach  var columnName in columnNames                        table Columns columnName  SetOrdinal columnIndex               columnIndex

[c#] Convert generic List/Enumerable to DataTable?

Examples related to c#

Examples related to list

Examples related to generics

Examples related to datatable