前言:

  • 所有数据连接字符串,
    连接等是使用创建的
    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秒,这是一个相当长的时间,而且非常糟糕。我的选择语句:

    “选择*从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
    }
    

    查询数据读取器:
    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
    

    最佳答案

    您是否考虑过像dapper.net这样的微型ORM?

    https://github.com/StackExchange/dapper-dot-net

    它由StackOverflow的开发人员制作,并将SQL查询直接映射到您的对象。它生成并缓存IL代码,以将SQL结果映射到您的对象。因此,每种类型仅生成一次IL代码。从未使用过它,但是如果您需要性能以将SQL结果映射到.net对象,则它是您需要的库。

    关于c# - 如何将DataReader映射到类属性并保持性能?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6142659/

    10-13 05:36