Convert DataTable to IEnumerable T

Question

I am trying to convert a DataTable to an IEnumerable  Where T is a custom type I created  I know I can do it by creating a List lt T gt  but I was thinking if there is a slicker way to do it using IEnumerable  Here is what I have now      private IEnumerable lt TankReading gt  ConvertToTankReadings DataTable dataTable                var tankReadings   new List lt TankReading gt             foreach  DataRow row in dataTable Rows                        var tankReading   new TankReading                                                                           TankReadingsID   Convert ToInt32 row  quot TRReadingsID quot                                           TankID   Convert ToInt32 row  quot TankID quot                                           ReadingDateTime   Convert ToDateTime row  quot ReadingDateTime quot                                           ReadingFeet   Convert ToInt32 row  quot ReadingFeet quot                                           ReadingInches   Convert ToInt32 row  quot ReadingInches quot                                           MaterialNumber   row  quot MaterialNumber quot   ToString                                          EnteredBy   row  quot EnteredBy quot   ToString                                          ReadingPounds   Convert ToDecimal row  quot ReadingPounds quot                                           MaterialID   Convert ToInt32 row  quot MaterialID quot                                           Submitted   Convert ToBoolean row  quot Submitted quot                                                      tankReadings Add tankReading                     return tankReadings AsEnumerable           The key part being I am creating a List lt T gt  then returning it using AsEnumerable

User · Accepted Answer

Nothing wrong with that implementation  You might give the yield keyword a shot  see how you like it   private IEnumerable lt TankReading gt  ConvertToTankReadings DataTable dataTable                foreach  DataRow row in dataTable Rows                        yield return new TankReading                                                                           TankReadingsID   Convert ToInt32 row  TRReadingsID                                           TankID   Convert ToInt32 row  TankID                                           ReadingDateTime   Convert ToDateTime row  ReadingDateTime                                           ReadingFeet   Convert ToInt32 row  ReadingFeet                                           ReadingInches   Convert ToInt32 row  ReadingInches                                           MaterialNumber   row  MaterialNumber   ToString                                          EnteredBy   row  EnteredBy   ToString                                          ReadingPounds   Convert ToDecimal row  ReadingPounds                                           MaterialID   Convert ToInt32 row  MaterialID                                           Submitted   Convert ToBoolean row  Submitted                                                             Also the AsEnumerable isn t necessary  as List lt T gt  is already an IEnumerable lt T gt

User · Answer

I wrote an article on this subject over here  I think it could help you   Typically it s doing something like that   static void Main string   args           Convert from a DataTable source to an IEnumerable      var usersSourceDataTable   CreateMockUserDataTable        var usersConvertedList   usersSourceDataTable ToEnumerable lt User gt             Convert from an IEnumerable source to a DataTable      var usersSourceList   CreateMockUserList        var usersConvertedDataTable   usersSourceList ToDataTable lt User gt

User · Answer

Universal extension method for DataTable  May be somebody be interesting  Idea creating dynamic properties I take from another post  https   stackoverflow com a 15819760 8105226      public static IEnumerable lt dynamic gt  AsEnumerable this DataTable dt                List lt dynamic gt  result   new List lt dynamic gt             Dictionary lt string  object gt  d          foreach  DataRow dr in dt Rows                        d   new Dictionary lt string  object gt                  foreach  DataColumn dc in dt Columns                  d Add dc ColumnName  dr dc                 result Add GetDynamicObject d                      return result AsEnumerable lt dynamic gt                public static dynamic GetDynamicObject Dictionary lt string  object gt  properties                return new MyDynObject properties              public sealed class MyDynObject   DynamicObject               private readonly Dictionary lt string  object gt   properties           public MyDynObject Dictionary lt string  object gt  properties                         properties   properties                     public override IEnumerable lt string gt  GetDynamicMemberNames                         return  properties Keys                     public override bool TryGetMember GetMemberBinder binder  out object result                        if   properties ContainsKey binder Name                                 result    properties binder Name                   return true                            else                               result   null                  return false                                   public override bool TrySetMember SetMemberBinder binder  object value                        if   properties ContainsKey binder Name                                  properties binder Name    value                  return true                            else                               return false

User · Answer

If you want to convert any DataTable to a equivalent IEnumerable vector function    Please take a look at the following generic function  this may help your needs  you may need to include write cases for different datatypes based on your needs         lt summary gt          Get entities from DataTable          lt  summary gt           lt typeparam name  T  gt Type of entity lt  typeparam gt           lt param name  dt  gt DataTable lt  param gt           lt returns gt  lt  returns gt      public IEnumerable lt T gt  GetEntities lt T gt  DataTable dt                if  dt    null                        return null                     List lt T gt  returnValue   new List lt T gt             List lt string gt  typeProperties   new List lt string gt              T typeInstance   Activator CreateInstance lt T gt              foreach  DataColumn column in dt Columns                        var prop   typeInstance GetType   GetProperty column ColumnName  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public               if  prop    null                                typeProperties Add column ColumnName                                    foreach  DataRow row in dt Rows                        T entity   Activator CreateInstance lt T gt                  foreach  var propertyName in typeProperties                                 if  row propertyName     DBNull Value                                        string str   row propertyName  GetType   FullName                       if  entity GetType   GetProperty propertyName  PropertyType    typeof System String                                                 object Val   row propertyName  ToString                            entity GetType   GetProperty propertyName  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  SetValue entity  Val  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  null  null  null                                             else if  entity GetType   GetProperty propertyName  PropertyType    typeof System Guid                                                  object Val   Guid Parse row propertyName  ToString                             entity GetType   GetProperty propertyName  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  SetValue entity  Val  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  null  null  null                                             else                                               entity GetType   GetProperty propertyName  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  SetValue entity  row propertyName   BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  null  null  null                                                           else                                       entity GetType   GetProperty propertyName  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  SetValue entity  null  BindingFlags NonPublic   BindingFlags Instance   BindingFlags Public  null  null  null                                                returnValue Add entity                      return returnValue AsEnumerable

User · Answer

If you are producing the DataTable from an SQL query  have you considered simply using Dapper instead   Then  instead of making a SqlCommand with SqlParameters and a DataTable and a DataAdapter and on and on  which you then have to laboriously convert to a class  you just define the class  make the query column names match the field names  and the parameters are bound easily by name  You already have the TankReading class defined  so it will be really simple   using Dapper      Below can be SqlConnection cast to DatabaseConnection  too  DatabaseConnection connection      whatever IEnumerable lt TankReading gt  tankReadings   connection Query lt TankReading gt       SELECT   from TankReading WHERE Value    value      new   value    tank1       note how  value  maps to   value     return tankReadings    Now isn t that awesome  Dapper is very optimized and will give you darn near equivalent performance as reading directly with a DataAdapter   If your class has any logic in it at all or is immutable or has no parameterless constructor  then you probably do need to have a DbTankReading class  as a pure POCO Plain Old Class Object       internal because it should only be used in the data source project and not elsewhere internal sealed class DbTankReading      int TankReadingsID   get  set       DateTime  ReadingDateTime   get  set       int ReadingFeet   get  set       int ReadingInches   get  set       string MaterialNumber   get  set       string EnteredBy   get  set       decimal ReadingPounds   get  set       int MaterialID   get  set       bool Submitted   get  set        You d use that like this   IEnumerable lt TankReading gt  tankReadings   connection     Query lt DbTankReading gt          SELECT   from TankReading WHERE Value    value         new   value    tank1       note how  value  maps to   value           Select tr   gt  new TankReading        tr TankReadingsID        tr ReadingDateTime        tr ReadingFeet        tr ReadingInches        tr MaterialNumber        tr EnteredBy        tr ReadingPounds        tr MaterialID        tr Submitted          Despite the mapping work  this is still less painful than the data table method  This also lets you perform some kind of logic  though if the logic is any more than very simple straight-across mapping  I d put the logic into a separate TankReadingMapper class

User · Answer

There s also a DataSetExtension method called  AsEnumerable     in System Data  that takes a DataTable and returns an Enumerable   See the MSDN doc for more details  but it s basically as easy as   dataTable AsEnumerable     The downside is that it s enumerating DataRow  not your custom class   A  Select    LINQ call could convert the row data  however   private IEnumerable lt TankReading gt  ConvertToTankReadings DataTable dataTable        return dataTable AsEnumerable   Select row   gt  new TankReading                                           TankReadingsID   Convert ToInt32 row  TRReadingsID                           TankID   Convert ToInt32 row  TankID                           ReadingDateTime   Convert ToDateTime row  ReadingDateTime                           ReadingFeet   Convert ToInt32 row  ReadingFeet                           ReadingInches   Convert ToInt32 row  ReadingInches                           MaterialNumber   row  MaterialNumber   ToString                          EnteredBy   row  EnteredBy   ToString                          ReadingPounds   Convert ToDecimal row  ReadingPounds                           MaterialID   Convert ToInt32 row  MaterialID                           Submitted   Convert ToBoolean row  Submitted

User · Answer

PagedDataSource objPage   new PagedDataSource             DataView dataView   listData DefaultView          objPage AllowPaging   true          objPage DataSource   dataView          objPage PageSize   PageSize           TotalPages   objPage PageCount           objPage CurrentPageIndex   CurrentPage - 1             Convert PagedDataSource to DataTable         System Collections IEnumerator pagedData   objPage GetEnumerator             DataTable filteredData   new DataTable            bool flagToCopyDTStruct   false          while  pagedData MoveNext                          DataRowView rowView    DataRowView pagedData Current              if   flagToCopyDTStruct                                filteredData   rowView Row Table Clone                    flagToCopyDTStruct   true                            filteredData LoadDataRow rowView Row ItemArray  true                        Here is your filtered DataTable         return filterData

User · Answer

Simple method using System Data DataSetExtensions   table AsEnumerable   Select row   gt  new TankReading          TankReadingsID   Convert ToInt32 row  TRReadingsID             TankID   Convert ToInt32 row  TankID             ReadingDateTime   Convert ToDateTime row  ReadingDateTime             ReadingFeet   Convert ToInt32 row  ReadingFeet             ReadingInches   Convert ToInt32 row  ReadingInches             MaterialNumber   row  MaterialNumber   ToString            EnteredBy   row  EnteredBy   ToString            ReadingPounds   Convert ToDecimal row  ReadingPounds             MaterialID   Convert ToInt32 row  MaterialID             Submitted   Convert ToBoolean row  Submitted               Or    TankReading TankReadingFromDataRow DataRow row       return new TankReading          TankReadingsID   Convert ToInt32 row  TRReadingsID             TankID   Convert ToInt32 row  TankID             ReadingDateTime   Convert ToDateTime row  ReadingDateTime             ReadingFeet   Convert ToInt32 row  ReadingFeet             ReadingInches   Convert ToInt32 row  ReadingInches             MaterialNumber   row  MaterialNumber   ToString            EnteredBy   row  EnteredBy   ToString            ReadingPounds   Convert ToDecimal row  ReadingPounds             MaterialID   Convert ToInt32 row  MaterialID             Submitted   Convert ToBoolean row  Submitted                  Now you can do this table AsEnumerable   Select row   gt  return TankReadingFromDataRow row      Or  better yet  create a TankReading DataRow r  constructor  then this becomes        table AsEnumerable   Select row   gt  return new TankReading row

[c#] Convert DataTable to IEnumerable<T>

Examples related to c#

Examples related to datatable

Examples related to ienumerable