Build an entity, based on a defined model by comparing table column to entity names and building

Question

Can I optimize the BuildEntity method more?

protected IEnumerable<GroupTitle> BuildProductGroup()
    {
        var productGroups = new List<GroupTitle>();
        using (var connection = new SqlConnection(_dbConnection))
        using (var command = new SqlCommand(_getProductGroups, connection))
        {
            connection.Open();
            command.CommandType = CommandType.StoredProcedure;
            using (var reader = command.ExecuteReader())
                while(reader.Read())
                {
                    var productGroup = new GroupTitle();
                    BuildEntity<GroupTitle>(reader, ref productGroup);
                    productGroups.Add(productGroup);
                }
        }

        return productGroups;
    }

    protected TEntity BuildEntity<TEntity>(IDataReader reader, TEntity model)
    {
        var type = model.GetType();
        var table = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();

        foreach (var column in table)
        {
            var matchColumnToProperty = type.GetProperties().FirstOrDefault(property => String.Compare(property.Name, column, true) == 0);
            if (matchColumnToProperty != null && !reader.IsDBNull(reader.GetOrdinal(matchColumnToProperty.Name)))
                matchColumnToProperty.SetValue(model, reader.GetValue(reader.GetOrdinal(matchColumnToProperty.Name)), null);
        }

        return model;
    }

Answer 1

You can cache the properties to improve performance. No need to retrieve them repeatedly within the body of that for loop.

Also, simplify the LINQ expression to gather all the matching properties, and then iterate over the result set in one fell swoop.

protected IEnumerable<GroupTitle> BuildProductGroup()
{
    var productGroups = new List<GroupTitle>();
    using (var connection = new SqlConnection(_dbConnection))
    using (var command = new SqlCommand(_getProductGroups, connection))
    {
        connection.Open();
        command.CommandType = CommandType.StoredProcedure;
        using (var reader = command.ExecuteReader())
        {
            while(reader.Read())
            {
                var productGroup = new GroupTitle();
                BuildEntity<GroupTitle>(reader, ref productGroup);
                productGroups.Add(productGroup);
            }
        }
    }

    return productGroups;
}

protected TEntity BuildEntity<TEntity>(IDataReader reader, TEntity model)
{
    var type = model.GetType();
    var table = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();

    var properties = type.GetProperties();
    var matchedColumns = properties.Where(property => string.Compare(property.Name, 
                                                                     column, 
                                                                     true) == 0 
                                                      && !reader.IsDBNull(reader.GetOrdinal(matchColumnToProperty.Name)));

    foreach (var property in matchedColumns)
    {
        var value = reader.GetValue(reader.GetOrdinal(matchColumnToProperty.Name));
        matchColumnToProperty.SetValue(model, value, null);
    }

    return model;
}

Answer 2

I ended up improving performance and tidying the code up a bit by doing the following:

    public IList<TEntity> List<TEntity>(string query, CommandType commandType, params SqlParameter[] parameters) where TEntity : class, new()
    {
        using (var connection = new SqlConnection(dbConnection))
        using (var command = new SqlCommand(query, connection))
        {
            connection.Open();
            command.CommandType = commandType;

            foreach (var parameter in parameters)
                command.Parameters.Add(parameter);

            return BuildEntity(command, new TEntity());
        }
    }

Then BuildEntity would be as follows:

    public List<TEntity> BuildEntity<TEntity>(SqlCommand command, TEntity entity) where TEntity : class, new()
    {
        var collection = new List<TEntity>();
        var properties = GetColumnDataFromProperty<TEntity>();
        using (var reader = command.ExecuteReader())
            while(reader.Read())
                collection.Add(MapEntity<TEntity>(reader, properties));

        return collection;
    }