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

Question

I have data in a DataReader which I want to be converted to a List<T>. 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<CustomerEntity>.

Odoacer

Answer 1

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<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
                                    .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.

Weathered

The problem with this approach is that you get into much trouble once you start using composite objects. If the Customer is associated with Company you need a Company property. You can go recursive but the Company probably has List<Customer> property and then you have to traverse a graph. To do this you need an attribute representing an association. This is what LINQ to SQL and Entity Framework do but they are big products and you cannot easily develop inhouse solution. And if you are going to do this why not use EF instead? – Impartial

why i haven't AutoMap() – Bebeeru

What references do I need to add to make [Field(“id”)] work? – Edition

@Impartial Using EF against an existing database with "non EF rules" is one very compelling reason not to use EF .. (and no, this comment isn't 6 years late as EF still has the same issues as it did back then) – Hallsy

Do you have an implementation of AutoMap somewhere? – Kirven

Yes, where to find this AutoMap extension method? Or it is the Automapper method you forgot to mention? :) – Oculist

Hi, you can use KORM. Object relation mapper, which has provider for MsAccess Kros.KORM.MsAccess. KORM allow materialize ADO.NET classies like DbReader to IEnumerable<T>. For example database.ModelBuilder.Materialize<Person>(reader) – Karns

Answer 2

I would suggest writing an extension method for this:

public static IEnumerable<T> Select<T>(this IDataReader reader,
                                       Func<IDataReader, T> projection)
{
    while (reader.Read())
    {
        yield return projection(reader);
    }
}

You can then use LINQ's ToList() method to convert that into a List<T> if you want, like this:

using (IDataReader reader = ...)
{
    List<Customer> customers = reader.Select(r => 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<Customer> customers = reader.Select<Customer>(Customer.FromDataReader)
                                     .ToList();
}

(I don't think type inference would work in this case, but I could be wrong...)

Bos

Shouldn't the extension method be: while (reader.Read()) instead of while (drOutput.Read()) – Damalus

awesome, I was just about to do some research on how I can incorporate more IEnumerable in my DL classes. – Albaugh

Excellent. Especially since this approach (with a little adjustment) can be used with anonymous types as well, which simplifies ad hoc queries quite a bit. – Keffiyeh

Note that this is the same as reader.Cast<IDataReader>().Select. – Pelage

@SLaks: I don't see how, given that IDataReader doesn't implement IEnumerable - what am I missing? – Bos

@Jon: DbDataRader does. msdn.microsoft.com/en-us/library/… – Pelage

@SLaks: True, although the OP never mentioned which sort of DataReader is being used. Personally I'm quite fond of the extension method though :) – Bos

I don't think you'll find any IDataReaders that don't inherit DbDataReader. Once you're making an extension method, it would be nice to make it pass a DynamicObject around the DataReader. – Pelage

@SLaks: That may be true - although I would generally prefer programming against the interface than even the abstract class. It feels a little cleaner. I'm not sure where the benefit of DynamicObject would be - could you go into that in more detail? – Bos

You could take a Func<dynamic, T> and write d => new Customer { CustomerId = d.id } (You could also just return an IEnumerable<dynamic>) – Pelage

@Jon: I'm not following - the 'FromDataReader' is an extension method, right? Is the 2nd snippet using (DataReader reader = ...) the body of FromDataReader? – Cardiac

@IAbstract: No, the second snippet is calling FromDataReader (or rather, it's creating a delegate which would use that). – Bos

@Jon: the 2nd snippet, prefaced with 'use LINQ's ToList() method to convert ... ', is what I'm referring to. Does that snippet demonstrate the body of a FromDataReader method? – Cardiac

@IAbstract: No. FromDataReader would convert the current contents of a DataReader to a single customer... separating that task from the one of iterating over the multiple rows within a DataReader. – Bos

reader.Select(reader => doesn't compile, I had to change it to reader.Select(r =>. A local variable named 'reader' cannot be declared in this scope because it would give a different meaning to 'reader', which is already used in a 'parent or current' scope to denote something else – Phototype

Use with OdbcDataReader <br> public static IEnumerable<T> Select<T>(this OdbcDataReader reader, Func<OdbcDataReader, T> projection) { while (reader.Read()) { yield return projection(reader); } } – Sparing

I dont understand what is the content of FromDataReader ? – Eleanor

@Wel: It would be a method you'd write yourself to create a Customer from "the current row in the given DataReader". – Bos

@Pelage "I don't think you'll find any IDataReaders that don't inherit DbDataReader." Dapper's IDataReader comes to mind. – Corabelle

Answer 3

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<Person> personList = new List<Person>();
personList = DataReaderMapToList<Person>(dataReaderForPerson);

This is the mapping method:

public static List<T> DataReaderMapToList<T>(IDataReader dr)
{
    List<T> list = new List<T>();
    T obj = default(T);
    while (dr.Read()) {
        obj = Activator.CreateInstance<T>();
        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

Demagogy

Actually the innards of your DataReaderMapToList would work well as a default projection for Jon Skeets answer above. – Class

This works well. I have one minor suggestion for the C# code: Change the line that starts with prop.SetValue to prop.SetValue(obj, Convert.ChangeType(dr[prop.Name], prop.PropertyType), null);. This will make the code work for types other than strings. – Nape

this will throw an exception trying to convert Nullable<> to Types if a column is nullable. here is the solution for that https://mcmap.net/q/167930/-invalid-cast-from-39-system-int32-39-to-39-system-nullable-1-system-int32-mscorlib – Ellieellinger

Might seem obvious, but your field names in the SELECT command must match those in your data object – Swordplay

Thanks! I complemented the solution with the suggestion from Brad W, and it would be good add a statement dr.Close(); after the while loop – Faerie

Answer 4

The simplest Solution :

var dt = new DataTable();
dt.Load(myDataReader);
List<DataRow> rows = dt.AsEnumerable();
var customers = rows.Select(dr=>new Customer(...)).ToList();

Policewoman

I did not find ToList on enumerator of dt. Does this code work? – Fab

@coolcake:msdn.microsoft.com/en-us/library/… – Policewoman

@coolcake:Add using System.Data;, its an Extension method. – Policewoman

This is a cool solution +1, but remember DataTable solution takes away the biggest advantage of data reader, i.e. demand on load. DataTable reads the entire data to memory first. Btw, AsEnumerable extension method is in System.Data.DataSetExtensions assembly (weird name for an assembly I must say, sounds more like namespace). – Kirven

.Load() is indeed an wonderful tool but I m facing issue while I m expecting 2 Result Set (through 2 ref cursors). After I load the first one, & moving the reader to next cursor by ReadNext(), the second one always coming null. – Celibacy

This line says "DataTable does not contain a constructor that takes 0 arguments". So I cannot instantiate the data table like you do in the first line. Does this work in core? – Noblesse

@Sam:Take look at this: msdn.microsoft.com/en-us/library/9ha04z01(v=vs.110).aspx – Policewoman

Am I missing something here? This solution doesn't produce a List(Of {MyType}), it produces a List(Of DataRow) which is quite a difference to the Op's requested solution. – Culler

give him an Oscar for programming. – Nievesniflheim

@SteveCinq: Add .Select(..) to the end. – Policewoman

Answer 5

I would (and have) started to use Dapper. To use your example would be like (written from memory):

public List<CustomerEntity> GetCustomerList()
{
    using (DbConnection connection = CreateConnection())
    {
        return connection.Query<CustomerEntity>("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().

Whetstone

This is an awesome answer!!! I just downloaded Dapper and it works great, saving me a lot of time and headaches! Thanks – Goda

Answer 6

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<XXX> Load( <<args>> )
{
    using ( var connection = CreateConnection() )
    using ( var command = Create<<ListXXX>>Command( <<args>>, connection ) )
    {
        connection.Open();
        using ( var reader = command.ExecuteReader() )
            return reader.Cast<IDataRecord>()
                .Select( x => new XXX( x.GetString( 0 ), x.GetString( 1 ) ) )
                .ToList();
    }
}

---

As in @Jon Skeet's answer, the

            .Select( x => 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

Subsequence

Answer 7

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<CustomerEntity > arrObjects = new System.Collections.Generic.List<CustomerEntity >();        
            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);
        }

}

Electrotechnics

Answer 8

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 = "[email protected]"
})
.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 = "[email protected]"
})
.ToListEx()
.Where(contactPerson => contactPerson.FirstName == "Zack");

Have a look at http://caprisoft.codeplex.com

Myrta

Isn't this doing exactly the same as Func<IDataReader, T> approach by Jon Skeet? Yes it is. And by introducing your own ICommand and IConnections its harder to be interoperable between various ADO.NET providers. I'm not getting why is it required in the first place. – Kirven

In your ListExCommand class why are you using reflection at all to bind properties if we have to manually provide a mapper? In your ListExAutoMap class where do we get the IEnumerable<T> to pass to constructor as all we are left with is just IEnumerable (DbDataReader). If we have to a foreach on it manually (like reader.Cast<IDataRecord>()) or so then thats leaving your inner foreach loop in the class redundant, overall making this approach very slow. +1 for the effort.. – Kirven

Answer 9

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  "

''' <summary>
''' Set the Query Type
''' </summary>
''' <value></value>
''' <remarks></remarks>
Public WriteOnly Property QueryType() As CmdType
    Set(ByVal value As CmdType)
        _QT = value
    End Set
End Property
Private _QT As CmdType

''' <summary>
''' Set the query to run
''' </summary>
''' <value></value>
''' <remarks></remarks>
Public WriteOnly Property Query() As String
    Set(ByVal value As String)
        _Qry = value
    End Set
End Property
Private _Qry As String

''' <summary>
''' Set the parameter names
''' </summary>
''' <value></value>
''' <remarks></remarks>
Public WriteOnly Property ParameterNames() As Object
    Set(ByVal value As Object)
        _PNs = value
    End Set
End Property
Private _PNs As Object

''' <summary>
''' Set the parameter values
''' </summary>
''' <value></value>
''' <remarks></remarks>
Public WriteOnly Property ParameterValues() As Object
    Set(ByVal value As Object)
        _PVs = value
    End Set
End Property
Private _PVs As Object

''' <summary>
''' Set the parameter data type
''' </summary>
''' <value></value>
''' <remarks></remarks>
Public WriteOnly Property ParameterDataTypes() As DataType()
    Set(ByVal value As DataType())
        _DTs = value
    End Set
End Property
Private _DTs As DataType()

''' <summary>
''' Check if there are parameters, before setting them
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
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

''' <summary>
''' Set our dynamic connection string
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
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 "

''' <summary>
''' Fire us up!
''' </summary>
''' <remarks></remarks>
Public Sub New()
    Parallel.Invoke(Sub()
                        _Conn = New SqlConnection(_ConnString)
                    End Sub,
                    Sub()
                        _Cmd = New SqlCommand
                    End Sub)
End Sub

''' <summary>
''' Get our results
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
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

''' <summary>
''' Prepare our parameters
''' </summary>
''' <param name="objCmd"></param>
''' <remarks></remarks>
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

Plainlaid

IDataReader already inherits IEnumerable IDataReader does not inherit IEnumerable. Atleast the standard one in the .NET 4 Framework in the System.Data namespace doesn't on my machine. – Analgesia

You're right... not inherits.... implements:msdn.microsoft.com/en-us/library/… – Plainlaid

I was right for the wrong reasons, I apologize I was looking at the Interface IDataReader instead of the DataReader itself. The nitpick of implements and inherits was actually unintentional. – Analgesia