前言：
>所有数据连接字符串,
使用创建连接等
DbProviderFactories.
>代码混合了C#和VB.Net
多个图书馆.
我正在将DbDataReader映射到实体并具有一些基准：
[0] retrieved 159180 records in 45135 ms [1] retrieved 159180 records in 45008 ms [2] retrieved 159180 records in 44814 ms [3] retrieved 159180 records in 44987 ms [4] retrieved 159180 records in 44914 ms [5] retrieved 159180 records in 45224 ms [6] retrieved 159180 records in 45829 ms [7] retrieved 159180 records in 60762 ms [8] retrieved 159180 records in 52128 ms [9] retrieved 159180 records in 47982 ms
考虑到从sql Server Management Studio查询只需要17秒,这是一个非常大的时间和极差.我的选择声明：
“SELECT * FROM tbl_MyTable”
表包含43个字段,可能没有最佳索引;但是,执行全选,我不希望索引有问题.所以…这就是我在做的事情：
定义一个实体：
public class Concept
{
#region Columns
[DataParameter("ConceptID",DbType.Int32)]
public Int32 ConceptID
{ get; set; }
[DataParameter("ConceptName",DbType.String)]
public string ConceptName
{ get; set; }
[DataParameter("ConceptTypeID",DbType.Int32)]
public Int32 ConceptTypeID
{ get; set; }
[DataParameter("ActiveYN",DbType.Boolean)]
public bool ActiveYN
{ get; set; }
#endregion
}
查询DataReader：
for (int i = 0; i <= 99; i++)
{
sw.Start();
var results = session.QueryReader<Concept>(
new sqlCommand(command),dr => new Concept());
sw.Stop();
Console.WriteLine("[{0}] retrieved {1} records in {2} ms",i,results.Count(),sw.ElapsedMilliseconds);
sw.Reset();
}
……打电话：
Public Function QueryReader(Of TEntity As {Class,New})(ByVal Command As DbCommand,_
ByVal Projection As Func(Of DbDataReader,TEntity)) _
As IEnumerable(Of TEntity)
Dim list As IEnumerable(Of TEntity)
Command.Connection = dataReader.NewConnection
Command.Connection.Open()
Using _reader As DbDataReader = Command.ExecuteReader()
list = _reader.Query(Of TEntity)(Projection).ToList()
End Using
Command.Connection.Close()
Return list
End Function
…和扩展方法QueryReader< T>：编辑新TEntity()的位置 – 谢谢@Henk
public static IEnumerable<TEntity> Query<TEntity>(this DbDataReader Reader,Func<DbDataReader,TEntity> Projection)
where TEntity : class,new()
{
// moving this reflection to another class
Dictionary<string,PropertyInfo> props;
while (Reader.Read())
{
TEntity entity = new TEntity();
if (!entities.TryGetValue(typeof(TEntity).ToString(),out props))
{
// reflection over TEntity
props = (from p in entity.GetType().GetProperties()
from a in p.GetCustomAttributes(typeof(DataParameterAttribute),false)
select p)
.ToDictionary(p => p.Name);
entities.Add(typeof(TEntity).ToString(),props);
}
foreach (KeyValuePair<string,PropertyInfo> field in props)
{
if (null != Reader[field.Key] && Reader[field.Key] != DBNull.Value)
{ field.Value.SetValue(entity,Reader[field.Key],null); }
}
yield return entity;
}
}
任何有关提高性能的建议都将受到高度赞赏……
更新
我实现了@EtienneT建议的dapper-dot-net – 这里是检索时间：
[0] retrieved 159180 records in 6874 ms [1] retrieved 159180 records in 6866 ms [2] retrieved 159180 records in 6570 ms [3] retrieved 159180 records in 6785 ms [4] retrieved 159180 records in 6693 ms [5] retrieved 159180 records in 6735 ms [6] retrieved 159180 records in 6627 ms [7] retrieved 159180 records in 6739 ms [8] retrieved 159180 records in 6569 ms [9] retrieved 159180 records in 6666 ms
