How can I easily convert DataReader to List T

Question

I have data in a DataReader which I want to be converted to a List lt T gt   What is a possible simple solution for this   For e g  in CustomerEntity class  I have CustomerId and CustomerName properties If my DataReader returns these two columns as data  then how can I convert it into List lt CustomerEntity gt

User · Answer

I would  and have  started to use Dapper   To use your example would be like  written from memory    public List lt CustomerEntity gt  GetCustomerList         using  DbConnection connection   CreateConnection                  return connection Query lt CustomerEntity gt   procToReturnCustomers   commandType  CommandType StoredProcedure  ToList              CreateConnection   would handle accessing your db and returning a connection   Dapper handles mapping datafields to properties automatically   It also supports multiple types and result sets and is very fast   Query returns IEnumerable hence the ToList

User · Answer

I would suggest writing an extension method for this   public static IEnumerable lt T gt  Select lt T gt  this IDataReader reader                                         Func lt IDataReader  T gt  projection        while  reader Read                  yield return projection reader             You can then use LINQ s ToList   method to convert that into a List lt T gt  if you want  like this   using  IDataReader reader              List lt Customer gt  customers   reader Select r   gt  new Customer           CustomerId   r  id   is DBNull   null   r  id   ToString            CustomerName   r  name   is DBNull   null   r  name   ToString           ToList        I would actually suggest putting a FromDataReader method in Customer  or somewhere else    public static Customer FromDataReader IDataReader reader            That would leave   using  IDataReader reader              List lt Customer gt  customers   reader Select lt Customer gt  Customer FromDataReader                                        ToList         I don t think type inference would work in this case  but I could be wrong

User · Answer

I ve covered this in a pet project   use what you want   Note that the ListEx implements the IDataReader interface    people   new ListExCommand command   Map p   new ContactPerson       Age   p GetInt32 p GetOrdinal  Age       FirstName   p GetString p GetOrdinal  FirstName       IdNumber   p GetInt64 p GetOrdinal  IdNumber       Surname   p GetString p GetOrdinal  Surname       Email    z evans caprisoft co za      ToListEx    Where  FirstName    Peter      Or use object mapping like in the following example    people   new ListExAutoMap personList   Map p    new ContactPerson         Age   p Age      FirstName   p FirstName      IdNumber   p IdNumber      Surname   p Surname      Email    z evans caprisoft co za      ToListEx    Where contactPerson    contactPerson FirstName     Zack      Have a look at http   caprisoft codeplex com

User · Answer

You cant simply  directly  convert the datareader to list   You have to loop through all the elements in datareader and insert into list  below the sample code  using  drOutput                   System Collections Generic List lt CustomerEntity  gt  arrObjects   new System Collections Generic List lt CustomerEntity  gt                         int customerId   drOutput GetOrdinal  customerId                 int CustomerName   drOutput GetOrdinal  CustomerName              while  drOutput Read                                  CustomerEntity obj new CustomerEntity                 obj customerId    drOutput customerId      Convert DBNull    drOutput customerId   ToString     null              obj CustomerName    drOutput CustomerName      Convert DBNull    drOutput CustomerName   ToString     null              arrObjects  Add obj

User · Answer

The simplest Solution    var dt new DataTable    dt Load myDataReader   list lt DataRow gt  dr dt AsEnumerable   ToList

User · Answer

I have written the following method using this case   First  add the namespace  System Reflection    For Example  T is return type ClassName  and dr is parameter to mapping DataReader  C   Call mapping method like the following   List lt Person gt  personList   new List lt Person gt     personList   DataReaderMapToList lt Person gt  dataReaderForPerson     This is the mapping method   public static List lt T gt  DataReaderMapToList lt T gt  IDataReader dr        List lt T gt  list   new List lt T gt         T obj   default T       while  dr Read              obj   Activator CreateInstance lt T gt             foreach  PropertyInfo prop in obj GetType   GetProperties                  if   object Equals dr prop Name   DBNull Value                     prop SetValue obj  dr prop Name   null                                   list Add obj             return list      VB NET  Call mapping method like the following   Dim personList As New List Of Person  personList   DataReaderMapToList Of Person  dataReaderForPerson    This is the mapping method   Public Shared Function DataReaderMapToList Of T  ByVal dr As IDataReader  As List Of T          Dim list As New List Of T          Dim obj As T         While dr Read               obj   Activator CreateInstance Of T                For Each prop As PropertyInfo In obj GetType   GetProperties                   If Not Object Equals dr prop Name   DBNull Value  Then                     prop SetValue obj  dr prop Name   Nothing                  End If             Next             list Add obj          End While         Return list     End Function

User · Answer

Obviously  Ian Ringrose s central thesis that you should be using a library for this is the best single answer here  hence a  1   but for minimal throwaway or demo code here s a concrete illustration of  SLaks s subtle comment on  Jon Skeet s more granular   1 d  answer   public List lt XXX gt  Load   lt  lt args gt  gt          using   var connection   CreateConnection         using   var command   Create lt  lt ListXXX gt  gt Command   lt  lt args gt  gt   connection                   connection Open            using   var reader   command ExecuteReader                 return reader Cast lt IDataRecord gt                     Select  x   gt  new XXX  x GetString  0    x GetString  1                        ToList                As in  Jon Skeet s answer  the                Select  x   gt  new XXX  x GetString  0    x GetString  1         bit can be extracted into a helper  I like to dump them in the query class        public static XXX FromDataRecord  this IDataRecord record                return new XXX  record GetString  0    record GetString  1              and used as                Select  FromDataRecord       UPDATE Mar 9 13  See also Some excellent further subtle coding techniques to split out the boilerplate in this answer

User · Answer

I know this question is old  and already answered  but     Since SqlDataReader already implements IEnumerable  why is there a need to create a loop over the records    I ve been using the method below without any issues  nor without any performance issues   So far I have tested with IList  List Of T   IEnumerable  IEnumerable Of T   IQueryable  and IQueryable Of T   Imports System Data SqlClient Imports System Data Imports System Threading Tasks  Public Class DataAccess Implements IDisposable   Region     Properties          lt summary gt      Set the Query Type      lt  summary gt       lt value gt  lt  value gt       lt remarks gt  lt  remarks gt  Public WriteOnly Property QueryType   As CmdType     Set ByVal value As CmdType           QT   value     End Set End Property Private  QT As CmdType       lt summary gt      Set the query to run      lt  summary gt       lt value gt  lt  value gt       lt remarks gt  lt  remarks gt  Public WriteOnly Property Query   As String     Set ByVal value As String           Qry   value     End Set End Property Private  Qry As String       lt summary gt      Set the parameter names      lt  summary gt       lt value gt  lt  value gt       lt remarks gt  lt  remarks gt  Public WriteOnly Property ParameterNames   As Object     Set ByVal value As Object           PNs   value     End Set End Property Private  PNs As Object       lt summary gt      Set the parameter values      lt  summary gt       lt value gt  lt  value gt       lt remarks gt  lt  remarks gt  Public WriteOnly Property ParameterValues   As Object     Set ByVal value As Object           PVs   value     End Set End Property Private  PVs As Object       lt summary gt      Set the parameter data type      lt  summary gt       lt value gt  lt  value gt       lt remarks gt  lt  remarks gt  Public WriteOnly Property ParameterDataTypes   As DataType       Set ByVal value As DataType             DTs   value     End Set End Property Private  DTs As DataType         lt summary gt      Check if there are parameters  before setting them      lt  summary gt       lt value gt  lt  value gt       lt returns gt  lt  returns gt       lt remarks gt  lt  remarks gt  Private ReadOnly Property AreParams   As Boolean     Get         If  IsArray  PVs  And IsArray  PNs   Then             If   PVs GetUpperBound 0     PNs GetUpperBound 0   Then                 Return True             Else                 Return False             End If         Else             Return False         End If     End Get End Property       lt summary gt      Set our dynamic connection string      lt  summary gt       lt value gt  lt  value gt       lt returns gt  lt  returns gt       lt remarks gt  lt  remarks gt  Private ReadOnly Property  ConnString   As String     Get         If System Diagnostics Debugger IsAttached OrElse My Settings AttachToBeta OrElse Not  Common CheckPaid  Then             Return My Settings DevConnString         Else             Return My Settings TurboKitsv2ConnectionString         End If     End Get End Property  Private  Rdr As SqlDataReader Private  Conn As SqlConnection Private  Cmd As SqlCommand   End Region   Region     Methods         lt summary gt      Fire us up       lt  summary gt       lt remarks gt  lt  remarks gt  Public Sub New       Parallel Invoke Sub                            Conn   New SqlConnection  ConnString                      End Sub                      Sub                            Cmd   New SqlCommand                     End Sub  End Sub       lt summary gt      Get our results      lt  summary gt       lt returns gt  lt  returns gt       lt remarks gt  lt  remarks gt  Public Function GetResults   As SqlDataReader     Try         Parallel Invoke Sub                               If AreParams Then                                 PrepareParams  Cmd                              End If                              Cmd Connection    Conn                              Cmd CommandType    QT                              Cmd CommandText    Qry                              Cmd Connection Open                                Rdr    Cmd ExecuteReader CommandBehavior CloseConnection                          End Sub          If  Rdr HasRows Then             Return  Rdr         Else             Return Nothing         End If     Catch sEx As SqlException         Return Nothing     Catch ex As Exception         Return Nothing     End Try End Function       lt summary gt      Prepare our parameters      lt  summary gt       lt param name  objCmd  gt  lt  param gt       lt remarks gt  lt  remarks gt  Private Sub PrepareParams ByVal objCmd As Object      Try         Dim  DataSize As Long         Dim  PCt As Integer    PVs GetUpperBound 0           For i As Long   0 To  PCt             If IsArray  DTs  Then                 Select Case  DTs i                      Case 0  33  6  9  13  19                          DataSize   8                     Case 1  3  7  10  12  21  22  23  25                          DataSize   Len  PVs i                       Case 2  20                          DataSize   1                     Case 5                          DataSize   17                     Case 8  17  15                          DataSize   4                     Case 14                          DataSize   16                     Case 31                          DataSize   3                     Case 32                          DataSize   5                     Case 16                          DataSize   2                     Case 15                 End Select                 objCmd Parameters Add  PNs i    DTs i    DataSize  Value    PVs i              Else                 objCmd Parameters AddWithValue  PNs i    PVs i               End If         Next     Catch ex As Exception     End Try End Sub   End Region   Region  IDisposable Support   Private disposedValue As Boolean   To detect redundant calls    IDisposable Protected Overridable Sub Dispose ByVal disposing As Boolean      If Not Me disposedValue Then         If disposing Then         End If         Try             Erase  PNs   Erase  PVs   Erase  DTs              Qry   String Empty              Rdr Close                Rdr Dispose                Cmd Parameters Clear                Cmd Connection Close                Conn Close                Cmd Dispose                Conn Dispose           Catch ex As Exception          End Try     End If     Me disposedValue   True End Sub    TODO  override Finalize   only if Dispose ByVal disposing As Boolean  above has code to free unmanaged resources  Protected Overrides Sub Finalize         Do not change this code   Put cleanup code in Dispose ByVal disposing As Boolean  above      Dispose False      MyBase Finalize   End Sub    This code added by Visual Basic to correctly implement the disposable pattern  Public Sub Dispose   Implements IDisposable Dispose       Do not change this code   Put cleanup code in Dispose ByVal disposing As Boolean  above      Dispose True      GC SuppressFinalize Me  End Sub   End Region  End Class   Strong Typing Class  Public Class OrderDCTyping     Public Property OrderID As Long   0     Public Property OrderTrackingNumber As String   String Empty     Public Property OrderShipped As Boolean   False     Public Property OrderShippedOn As Date   Nothing     Public Property OrderPaid As Boolean   False     Public Property OrderPaidOn As Date   Nothing     Public Property TransactionID As String End Class   Usage  Public Function GetCurrentOrders   As IEnumerable Of OrderDCTyping      Try         Using db As New DataAccess             With db                  QueryType   CmdType StoredProcedure                  Query     Desktop   CurrentOrders                   Using  Results    GetResults                       If  Results IsNot Nothing Then                          Qry    From row In  Results Cast Of DbDataRecord                                        Select New OrderDCTyping   With                                            OrderID   Common IsNull Of Long  row  0  0                                            OrderTrackingNumber   Common IsNull Of String  row  1  String Empty                                            OrderShipped   Common IsNull Of Boolean  row  2  False                                            OrderShippedOn   Common IsNull Of Date  row  3  Nothing                                            OrderPaid   Common IsNull Of Boolean  row  4  False                                            OrderPaidOn   Common IsNull Of Date  row  5  Nothing                                            TransactionID   Common IsNull Of String  row  6  String Empty                                         ToList                       Else                          Qry   Nothing                     End If                 End Using                 Return  Qry             End With         End Using     Catch ex As Exception         Return Nothing     End Try End Function

User · Answer

I have seen systems that use Reflection and attributes on Properties or fields to maps DataReaders to objects    A bit like what LinqToSql does    They save a bit of typing and may reduce the number of errors when coding for DBNull etc   Once you cache the generated code they can be faster then most hand written code as well  so do consider the    high road    if you are doing this a lot   See  A Defense of Reflection in  NET  for one example of this    You can then write code like  class CustomerDTO          Field  id        public int  CustomerId        Field  name        public string CustomerName           using  DataReader reader                 List lt CustomerDTO gt  customers   reader AutoMap lt CustomerDTO gt                                         ToList         AutoMap    is an extension method      Stilgar  thanks for a great comment  If are able to you are likely to be better of using  NHibernate  EF or Linq to Sql  etc  However on old project  or for other  sometimes valid  reasons  e g     not invented here        love of stored procs    etc   It is not always possible to use a ORM  so a lighter weight system can be useful to have    up your sleeves     If you every needed too write lots of IDataReader loops  you will see the benefit of reducing the coding  and errors  without having to change the architecture of the system you are working on   That is not to say it   s a good architecture to start with    I am assuming that CustomerDTO will not get out of the data access layer and composite objects etc will be built up by the data access layer using the DTO objects     A few years after I wrote this answer Dapper entered the world of  NET  it is likely to be a very good starting point for writing your onw AutoMapper  maybe it will completely remove the need for you to do so

[c#] How can I easily convert DataReader to List<T>?

Examples related to c#

Examples related to datareader

Examples related to generic-list