C# 如何轻松地将 DataReader 转换为 List<T>？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1464883/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I easily convert DataReader to List<T>?
提问by Laxman
I have data in a DataReaderwhich I want to be converted to a List<T>.
What is a possible simple solution for this?
我在 aDataReader中有数据，我想将其转换为List<T>. 对此有什么可能的简单解决方案？
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>.
例如，在 CustomerEntity 类中，我有 CustomerId 和 CustomerName 属性。如果我的 DataReader 将这两列作为数据返回，那么如何将其转换为List<CustomerEntity>.
采纳答案by Ian Ringrose
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 considerthe “high road” if you are doing this a lot.
我见过使用反射和属性或字段上的属性将 DataReader 映射到对象的系统。（有点像什么呢LinqToSql。）他们节省一点打字和编码的DBNull等时，一旦缓存生成的代码，他们可以更快那么大多数手工编写代码，以及可减少错误的数量，所以不要考虑了如果您经常这样做，则为“高速公路”。
See "A Defense of Reflection in .NET"for one example of this.
有关此示例，请参阅“.NET 中的反射防御”。
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)
（AutoMap()，是一个扩展方法）
@Stilgar, thanks for a greatcomment
@Stilgar，感谢您的精彩评论
If are able toyou are likely to be better of using NHibernate, EF or Linq to Sql, etcHowever 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”
如果可以的话，您可能会更好地使用 NHibernate、EF 或 Linq to Sql 等但是在旧项目上（或出于其他（有时是有效的）原因，例如“不是在这里发明的”、“对存储过程的热爱”等）并不总是可以使用 ORM，因此重量较轻的系统对于“袖手旁观”很有用
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 architectureof the system you are working on. That is not to say it's a good architecture to start with..
如果您也需要编写大量 IDataReader 循环，您将看到减少编码（和错误）的好处，而无需更改您正在处理的系统的体系结构。这并不是说它是一个很好的架构。
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.
我假设 CustomerDTO 不会脱离数据访问层，复合对象等将由数据访问层使用 DTO 对象构建。
A few years after I wrote this answer Dapperentered 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.
在我写这个答案Dapper进入 .NET 世界几年后，它可能是编写 onw AutoMapper 的一个很好的起点，也许它会完全消除你这样做的需要。
回答by RameshVel
You cant simply (directly) convert the datareader to list.
您不能简单地（直接）将数据读取器转换为列表。
You have to loop through all the elements in datareader and insert into list
您必须遍历 datareader 中的所有元素并插入到列表中
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);
}
}
回答by Jon Skeet
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:
然后，您可以根据需要使用 LINQ 的ToList()方法将其转换为 a 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();
}
I would actually suggest putting a FromDataReadermethod in Customer(or somewhere else):
我实际上建议FromDataReader在Customer（或其他地方）放置一个方法：
public static Customer FromDataReader(IDataReader reader) { ... }
That would leave:
那将留下：
using (IDataReader reader = ...)
{
List<Customer> customers = reader.Select<Customer>(Customer.FromDataReader)
.ToList();
}
(I don't thinktype inference would work in this case, but I could be wrong...)
（我不认为在这种情况下类型推断会起作用，但我可能是错的......）
回答by Zack Evans
I've covered this in a pet project.. use what you want.
我已经在一个宠物项目中介绍了这个……使用你想要的。
Note that the ListEx implements the IDataReader interface.
请注意，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 = "[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
回答by Mohsen
The simplest Solution :
最简单的解决方案：
var dt=new DataTable();
dt.Load(myDataReader);
list<DataRow> dr=dt.AsEnumerable().ToList();
回答by Ali YAL?IN
I have written the following method using this case.
我已经使用这种情况编写了以下方法。
First, add the namespace: System.Reflection
首先，添加命名空间： System.Reflection
For Example: Tis return type(ClassName) and dris parameter to mapping DataReader
例如：T是返回类型（ClassName）并且dr是映射的参数DataReader
C#, Call mapping method like the following:
C#，调用映射方法如下：
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:
VB.NET，调用映射方法如下：
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
回答by Kevin
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?
既然 SqlDataReader 已经实现了 IEnumerable，为什么还需要在记录上创建一个循环？
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)
我一直在使用下面的方法，没有任何问题，也没有任何性能问题：到目前为止，我已经使用 IList、List(Of T)、IEnumerable、IEnumerable(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
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
回答by Phil Cooper
I would (and have) started to use Dapper. To use your example would be like (written from memory):
我会（并且已经）开始使用Dapper。使用你的例子就像（从记忆中写的）：
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.
CreateConnection()将处理访问您的数据库并返回连接。
Dapper handles mapping datafields to properties automatically. It also supports multiple types and result sets and is very fast.
Dapper 自动处理将数据字段映射到属性。它还支持多种类型和结果集，并且速度非常快。
Query returns IEnumerablehence the ToList().
查询IEnumerable因此返回ToList().
回答by Ruben Bartelink
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:
显然@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();
}
}
As in @Jon Skeet's answer, the
正如在@Jon Skeet的回答中，
.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
13 年 3 月 9 日更新：另请参阅一些出色的更微妙的编码技术，以在此答案中拆分样板
