Convert IEnumerable to DataTable

Question

Is there a nice way to convert an IEnumerable to a DataTable   I could use reflection to get the properties and the values  but that seems a bit inefficient  is there something build-in    I know the examples like  ObtainDataTableFromIEnumerable   EDIT  This question notified me of a problem handling null values  The code I wrote below handles the null values properly   public static DataTable ToDataTable lt T gt  this IEnumerable lt T gt  items             Create the result table  and gather all properties of a T             DataTable table   new DataTable typeof T  Name        PropertyInfo   props   typeof T  GetProperties BindingFlags Public   BindingFlags Instance             Add the properties as columns to the datatable     foreach  var prop in props             Type propType   prop PropertyType               Is it a nullable type  Get the underlying type          if  propType IsGenericType  amp  amp  propType GetGenericTypeDefinition   Equals typeof Nullable lt  gt                  propType   new NullableConverter propType  UnderlyingType             table Columns Add prop Name  propType                    Add the property values per T as rows to the datatable     foreach  var item in items              var values   new object props Length             for  var i   0  i  lt  props Length  i                 values i    props i  GetValue item  null               table Rows Add values                 return table

User · Answer

I solve this problem by adding extension method to IEnumerable   public static class DataTableEnumerate       public static void Fill lt T gt   this IEnumerable lt T gt  Ts  ref DataTable dt  where T   class                 Get Enumerable Type         Type tT   typeof T              Get Collection of NoVirtual properties         var T props   tT GetProperties   Where p   gt   p GetGetMethod   IsVirtual  ToArray               Fill Schema         foreach  PropertyInfo p in T props              dt Columns Add p Name  p GetMethod ReturnParameter ParameterType BaseType              Fill Data         foreach  T t in Ts                        DataRow row   dt NewRow                 foreach  PropertyInfo p in T props                  row p Name    p GetValue t                dt Rows Add row

User · Answer

To all   Note that the accepted answer has a bug in it relating to nullable types and the DataTable  The fix is available at the linked site  http   www chinhdo com 20090402 convert-list-to-datatable   or in my modified code below                                                                                   lt summary gt          Convert a List to a DataTable           lt  summary gt           lt remarks gt          Based on MIT-licensed code presented at http   www chinhdo com 20090402 convert-list-to-datatable  as  ToDataTable           lt para  gt Code modifications made by Nick Campbell           lt para  gt Source code provided on this web site  chinhdo com  is under the MIT license           lt para  gt Copyright    2010 Chinh Do          lt para  gt Permission is hereby granted  free of charge  to any person obtaining a copy of this software and associated documentation files  the    Software      to deal in the Software without restriction  including without limitation the rights to use  copy  modify  merge  publish  distribute  sublicense  and or sell copies of the Software  and to permit persons to whom the Software is furnished to do so  subject to the following conditions           lt para  gt The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software           lt para  gt THE SOFTWARE IS PROVIDED    AS IS     WITHOUT WARRANTY OF ANY KIND  EXPRESS OR IMPLIED  INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT  IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM  DAMAGES OR OTHER LIABILITY  WHETHER IN AN ACTION OF CONTRACT  TORT OR OTHERWISE  ARISING FROM  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE           lt para  gt  As per http   www chinhdo com 20080825 transactional-file-manager            lt  remarks gt           lt typeparam name  T  gt Type representing the type to convert  lt  typeparam gt           lt param name  l oItems  gt List of requested type representing the values to convert  lt  param gt           lt returns gt  lt  returns gt                                                                                  lt LastUpdated gt February 15  2010 lt  LastUpdated gt      public static DataTable ToDataTable lt T gt  List lt T gt  l oItems            DataTable oReturn   new DataTable typeof T  Name           object   a oValues          int i                      Collect the a oProperties for the passed T         PropertyInfo   a oProperties   typeof T  GetProperties BindingFlags Public   BindingFlags Instance                       Traverse each oProperty   Add ing each  Name  BaseType into our oReturn value                        NOTE  The call to  BaseType is required as DataTables DataSets do not support nullable types  so it s non-nullable counterpart Type is required in the  Column definition         foreach PropertyInfo oProperty in a oProperties                oReturn Columns Add oProperty Name  BaseType oProperty PropertyType                                  Traverse the l oItems         foreach  T oItem in l oItems                           Collect the a oValues for this loop             a oValues   new object a oProperties Length                           Traverse the a oProperties  populating each a oValues as we go             for  i   0  i  lt  a oProperties Length  i                      a oValues i    a oProperties i  GetValue oItem  null                                          Add the  Row that represents the current a oValues into our oReturn value             oReturn Rows Add a oValues                                 Return the above determined oReturn value to the caller         return oReturn                                                                                         lt summary gt          Returns the underlying base type of nullable types           lt  summary gt           lt remarks gt          Based on MIT-licensed code presented at http   www chinhdo com 20090402 convert-list-to-datatable  as  GetCoreType           lt para  gt Code modifications made by Nick Campbell           lt para  gt Source code provided on this web site  chinhdo com  is under the MIT license           lt para  gt Copyright    2010 Chinh Do          lt para  gt Permission is hereby granted  free of charge  to any person obtaining a copy of this software and associated documentation files  the    Software      to deal in the Software without restriction  including without limitation the rights to use  copy  modify  merge  publish  distribute  sublicense  and or sell copies of the Software  and to permit persons to whom the Software is furnished to do so  subject to the following conditions           lt para  gt The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software           lt para  gt THE SOFTWARE IS PROVIDED    AS IS     WITHOUT WARRANTY OF ANY KIND  EXPRESS OR IMPLIED  INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT  IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM  DAMAGES OR OTHER LIABILITY  WHETHER IN AN ACTION OF CONTRACT  TORT OR OTHERWISE  ARISING FROM  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE           lt para  gt  As per http   www chinhdo com 20080825 transactional-file-manager            lt  remarks gt           lt param name  oType  gt Type representing the type to query  lt  param gt           lt returns gt Type representing the underlying base type  lt  returns gt                                                                                  lt LastUpdated gt February 15  2010 lt  LastUpdated gt      public static Type BaseType Type oType                       If the passed oType is valid   IsValueType and is logicially nullable   Get its UnderlyingType         if  oType    null  amp  amp  oType IsValueType  amp  amp              oType IsGenericType  amp  amp  oType GetGenericTypeDefinition      typeof Nullable lt  gt                           return Nullable GetUnderlyingType oType                                Else the passed oType was null or was not logicially nullable  so simply return the passed oType         else               return oType                    Note that both of these example are NOT extension methods like the example above   Lastly    apologies for my extensive excessive comments  I had a anal mean prof that beat it into me

User · Answer

I also came across this problem  In my case  I didn t know the type of the IEnumerable  So the answers given above wont work  However  I solved it like this   public static DataTable CreateDataTable IEnumerable source                var table   new DataTable            int index   0          var properties   new List lt PropertyInfo gt             foreach  var obj in source                        if  index    0                                foreach  var property in obj GetType   GetProperties                                          if  Nullable GetUnderlyingType property PropertyType     null                                                continue                                            properties Add property                       table Columns Add new DataColumn property Name  property PropertyType                                                object   values   new object properties Count               for  int i   0  i  lt  properties Count  i                                  values i    properties i  GetValue obj                             table Rows Add values               index                      return table          Keep in mind that using this method  requires at least one item in the IEnumerable  If that s not the case  the DataTable wont create any columns

User · Answer

Firstly you need to add a where T class constraint - you can t call GetValue on value types unless they re passed by ref   Secondly GetValue is very slow and gets called a lot   To get round this we can create a delegate and call that instead   MethodInfo method   property GetGetMethod true   Delegate CreateDelegate typeof Func lt TClass  TProperty gt    method      The problem is that we don t know TProperty  but as usual on here Jon Skeet has the answer - we can use reflection to retrieve the getter delegate  but once we have it we don t need to reflect again   public class ReflectionUtility       internal static Func lt object  object gt  GetGetter PropertyInfo property                   get the get method for the property         MethodInfo method   property GetGetMethod true               get the generic get-method generator  ReflectionUtility GetSetterHelper lt TTarget  TValue gt           MethodInfo genericHelper   typeof ReflectionUtility  GetMethod               GetGetterHelper               BindingFlags Static   BindingFlags NonPublic               reflection call to the generic get-method generator to generate the type arguments         MethodInfo constructedHelper   genericHelper MakeGenericMethod              method DeclaringType              method ReturnType               now call it  The null argument is because it s a static method          object ret   constructedHelper Invoke null  new object     method                 cast the result to the action delegate and return it         return  Func lt object  object gt   ret             static Func lt object  object gt  GetGetterHelper lt TTarget  TResult gt  MethodInfo method          where TTarget   class    target must be a class as property sets on structs need a ref param                  Convert the slow MethodInfo into a fast  strongly typed  open delegate         Func lt TTarget  TResult gt  func    Func lt TTarget  TResult gt   Delegate CreateDelegate typeof Func lt TTarget  TResult gt    method               Now create a more weakly typed delegate which will call the strongly typed one         Func lt object  object gt  ret    object target    gt   TResult  func  TTarget  target           return ret            So now your method becomes   public static DataTable ToDataTable lt T gt  this IEnumerable lt T gt  items       where T  class                create table the same way      var propGetters   new List lt Func lt T  object gt  gt     foreach  var prop in props                Func lt T  object gt  func    Func lt T  object gt   ReflectionUtility GetGetter prop           propGetters Add func                 Add the property values per T as rows to the datatable     foreach  var item in items                   var values   new object props Length             for  var i   0  i  lt  props Length  i                             values i    props i  GetValue item  null                  values i    propGetters i  item                          table Rows Add values                 return table        You could further optimise it by storing the getters for each type in a static dictionary  then you will only have the reflection overhead once for each type

User · Answer

So  10 years later this is still a thing       I ve tried every answer on this page  ATOW  and also some ILGenerator powered solutions  FastMember and Fast Reflection   But a compiled Lambda Expression seems to be the fastest  At least for my use cases  on  Net Core 2 2    This is what I am using for now   public static class EnumerableExtensions        internal static Func lt TClass  object gt  CompileGetter lt TClass gt  string propertyName            var param   Expression Parameter typeof TClass            var body   Expression Convert Expression Property param  propertyName   typeof object            return Expression Lambda lt Func lt TClass  object gt  gt  body param  Compile                    public static DataTable ToDataTable lt T gt  this IEnumerable lt T gt  collection            var dataTable   new DataTable            var properties   typeof T                           GetProperties BindingFlags Public   BindingFlags Instance                           Where p   gt  p CanRead                           ToArray             if  properties Length  lt  1  return null          var getters   new Func lt T  object gt  properties Length            for  var i   0  i  lt  properties Length  i                  var columnType   Nullable GetUnderlyingType properties i  PropertyType     properties i  PropertyType              dataTable Columns Add properties i  Name  columnType               getters i    CompileGetter lt T gt  properties i  Name                      foreach  var row in collection                var dtRow   new object properties Length               for  var i   0  i  lt  properties Length  i                      dtRow i    getters i  Invoke row     DBNull Value                            dataTable Rows Add dtRow                      return dataTable            Only works with properties  not fields  but it works on Anonymous Types

User · Answer

Look at this one   Convert List IEnumerable to DataTable DataView  In my code I changed it into a extension method   public static DataTable ToDataTable lt T gt  this List lt T gt  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 lt props Length  i                          values i    props i  GetValue item  null                      tb Rows Add values              return tb

User · Answer

A 2019 answer if you re using  NET Core - use the Nuget ToDataTable library  Advantages    Better performance than reflection or using DataTableProxy Also creates SqlParameters for use with 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   Reflection                 818 5 ms DataTableProxy           1 068 8 ms ToDataTable                449 0 ms

User · Answer

I ve written a library to handle this for me  It s called DataTableProxy and is available as a NuGet package  Code and documentation is on Github

User · Answer

There is nothing built in afaik  but building it yourself should be easy  I would do as you suggest and use reflection to obtain the properties and use them to create the columns of the table  Then I would step through each item in the IEnumerable and create a row for each  The only caveat is if your collection contains items of several types  say Person and Animal  then they may not have the same properties  But if you need to check for it depends on your use

[c#] Convert IEnumerable to DataTable

Examples related to c#

Examples related to datatable

Examples related to ienumerable