我有一个 DataReader
中的数据,我想将其转换为 List<T>
。你有什么简单的解决方案吗?
比如,在 CustomerEntity 类中,我有 CustomerId 和 CustomerName 属性。如果我的 DataReader 返回这两列数据,那么我该如何将它们转换为 List<CustomerEntity>
。
我有一个 DataReader
中的数据,我想将其转换为 List<T>
。你有什么简单的解决方案吗?
比如,在 CustomerEntity 类中,我有 CustomerId 和 CustomerName 属性。如果我的 DataReader 返回这两列数据,那么我该如何将它们转换为 List<CustomerEntity>
。
我建议为此编写一个扩展方法:
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();
}
(我认为在这种情况下类型推断无法奏效,但我可能错了...)
我使用以下方法编写了这个案例。
首先,添加命名空间:System.Reflection
例如:T
是返回类型(类名),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;
}
在VB.NET中,可以像下面这样调用映射方法:
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
prop.SetValue
开头的那一行改为 prop.SetValue(obj, Convert.ChangeType(dr[prop.Name], prop.PropertyType), null);
。这样可以让代码适用于除字符串以外的其他类型。 - Brad WNullable<>
转换为类型会抛出异常。这里提供了解决方案:https://dev59.com/aWMl5IYBdhLWcg3w7qlq#18015612 - Niklas我曾经看到一些系统使用反射和属性或字段上的属性,将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对象构建。
在我写下这个答案的几年后,Dapper进入了.NET世界,它可能是编写您自己的AutoMapper的一个很好的起点,也许它完全消除了您无需这样做的需要。
[Field("id")]
起作用? - Drew Chapin最简单的解决方案:
var dt = new DataTable();
dt.Load(myDataReader);
List<DataRow> rows = dt.AsEnumerable();
var customers = rows.Select(dr=>new Customer(...)).ToList();
using System.Data;
,这是一个扩展方法。 - MohsenList(Of {MyType})
,而是会产生一个 List(Of DataRow)
,这与 Op 请求的解决方案有很大的区别。 - SteveCinq我已经开始使用Dapper了。要使用你的示例,可以这样写(从记忆中写出):
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)的具体说明:
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:另请参见此答案中拆分样板代码的一些优秀技术细节
你不能直接将数据读取器转换为列表。
你需要循环遍历数据读取器中的所有元素,并插入到列表中。
以下是示例代码:
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);
}
}
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");
ICommand
和IConnection
,在各种 ADO.NET 提供程序之间实现互操作性更加困难。我不明白为什么一开始就需要它。 - nawfalListExCommand
类中,如果我们必须手动提供映射器,为什么要使用反射来绑定属性?在你的ListExAutoMap
类中,我们从哪里获取IEnumerable<T>
以传递给构造函数,因为我们只剩下IEnumerable
(DbDataReader
)。如果我们必须手动进行foreach
(例如reader.Cast<IDataRecord>()
),那么这将使类中的内部foreach循环变得多余,从而使这种方法非常缓慢。对于你的努力点个赞。 - nawfal我知道这个问题很老了,已经有答案了,但是...
既然SqlDataReader已经实现了IEnumerable,为什么还需要创建一个记录循环呢?
我一直在使用下面的方法,没有任何问题,也没有性能问题:到目前为止,我已经测试过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
强类型类
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
reader.Cast<IDataReader>().Select
相同。 - SLaksDbDataReader
的IDataReader
。一旦你创建了一个扩展方法,最好让它在DataReader周围传递一个DynamicObject
。 - SLaksFunc<dynamic, T>
并编写d => new Customer { CustomerId = d.id }
(你也可以只返回一个IEnumerable<dynamic>
)。 - SLaks