如何轻松地将 DataReader 转换为 List < T > ？

我有数据在一个 DataReader，我想要转换成一个 List<T>。 解决这个问题的简单方法是什么？

例如，在 CustomerEntity 类中，我有 CustomerId 和 CustomerName 属性。

347337 次浏览
小开

您不能简单地(直接地)将数据读取器转换为列表。

您必须遍历数据读取器中的所有元素并插入 list

在示例代码下面

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);
}


}
小开

我建议为此编写一个扩展方法:

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

然后你可以使用 LINQ 的 ToList()方法将其转换成 List<T>，如下所示:

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();
}

我实际上建议在 Customer(或其他地方)中放置一个 FromDataReader方法:

public static Customer FromDataReader(IDataReader reader) { ... }

剩下的就是:

using (IDataReader reader = ...)
{
List<Customer> customers = reader.Select<Customer>(Customer.FromDataReader)
.ToList();
}

(在这种情况下，我不会进行 好好想想类型推断，但是我可能会错...)

小开
最佳答案

我见过使用“属性”或字段上的反射和属性将 DataReader 映射到对象的系统。(有点像 LinqToSql 所做的。)它们可以节省一点输入，并且可以减少在编写 DBNull 等代码时的错误数量。一旦你缓存生成的代码，他们可以比大多数手写的代码更快，所以做 考虑一下的“公路”，如果你这样做了很多。

请参阅 “ . NET 中反射的防御”以获得这方面的一个示例。

然后您可以编写如下代码

class CustomerDTO
{
[Field("id")]
public int? CustomerId;


[Field("name")]
public string CustomerName;
}

...

using (DataReader reader = ...)
{
List<CustomerDTO> customers = reader.AutoMap<CustomerDTO>()
.ToList();
}

(AutoMap ()是一种扩展方法)

@ Stilgar 感谢 很好的评论

如果你是 能够，你可能会更好地使用 NHibernate，EF 或 Linq to Sql 等但是在旧的项目(或其他(有时有效)的原因，如“不发明在这里”，“爱的存储处理器”等) ，它并不总是可能使用 ORM，所以一个更轻的系统可以有用的有“在你的袖子”

如果您每次都需要编写大量的 IDataReader 循环，那么您将看到减少所处理系统的编码(和错误) 而不需要改变架构的好处。这并不是说它是一个很好的架构开始。.

我假设 CustomerDTO 不会离开数据访问层，复合对象等将由数据访问层使用 DTO 对象构建。

几年后，我写了这个答案 衣冠楚楚进入了世界。NET，这可能是一个非常好的开始编写您自动映射程序，也许它将完全消除您这样做的需要。

小开

我在一个宠物项目中报道过这个. 。 想用什么就用什么。

注意，ListEx 实现了 IDataReader 接口。


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");

或者像下面的示例那样使用对象映射。


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");

看看 http://caprisoft.codeplex.com

小开

最简单的解决方案:

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

我用这个例子写了下面的方法。

首先，添加名称空间: System.Reflection

例如: T是返回类型(ClassName) ，dr是映射 DataReader的参数

C # ，调用映射方法如下:

List<Person> personList = new List<Person>();
personList = DataReaderMapToList<Person>(dataReaderForPerson);

这就是映射方法:

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;
}

调用映射方法如下:

Dim personList As New List(Of Person)
personList = DataReaderMapToList(Of Person)(dataReaderForPerson)

这就是映射方法:

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
小开

我知道这个问题很老套，而且已经有答案了，但是..。

既然 SqlDataReader 已经实现了 IEnumable，为什么还需要在记录上创建循环？

我一直在使用下面的方法，没有任何问题，也没有任何性能问题: 到目前为止，我已经测试了 IList、 List (Of T)、 IEnumable、 IEnumable (Of T)、 IQueryable 和 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

强力打字班

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

用法

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
小开

我会(并且已经)开始使用 衣冠楚楚。使用你的例子就像(从内存中写出) :

public List<CustomerEntity> GetCustomerList()
{
using (DbConnection connection = CreateConnection())
{
return connection.Query<CustomerEntity>("procToReturnCustomers", commandType: CommandType.StoredProcedure).ToList();
}
}

CreateConnection()将处理访问数据库和返回连接的问题。

Dapper 自动处理数据字段到属性的映射。它还支持多种类型和结果集，并且非常快。

查询返回 IEnumerable，因此返回 ToList()。

小开

显然，@Ian Ringrose的中心论点是你应该使用一个库来解决这个问题，这是最好的单一答案(因此是 + 1) ，但是对于最小的一次性代码或演示代码，这里有一个具体的例子来说明 @SLaks对 @Jon Skeet更细粒度(+ 1’d)的答案的微妙评论:

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();
}
}

在 @Jon Skeet的回答中,

            .Select( x => new XXX( x.GetString( 0 ), x.GetString( 1 ) ) )

位可以提取到一个助手(我喜欢在查询类中转储它们) :

    public static XXX FromDataRecord( this IDataRecord record)
{
return new XXX( record.GetString( 0 ), record.GetString( 1 ) );
}

用作:

            .Select( FromDataRecord )

更新3月9日13: 参见 一些优秀的进一步微妙的编码技术，以拆分出样板在这个答案


提交答案