Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Following code helps me to automatically map the query result with Entity properties. For example, result of the following query “ReportTypeCode“ is mapped with Report object’s ReportTypeCode property.

SELECT R.report_type_code AS ReportTypeCode FROM Report_Type R

QUESTIONS

  1. Is there any datatype or scenario that it will not be able to handle?
  2. Is there any improvement suggestions?

CODE

public static class EntityDataMappingHelper
{
    /// <summary>
    /// Method for filling entity  from data
    /// </summary>
    public static void FillEntityFromRecord(Object entity, Dictionary<string, object> record)
    {
        if (entity != null && record != null)
        {
            PropertyInfo[] propertyInfoArray = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in propertyInfoArray)
            {
                if (record.ContainsKey(prop.Name))
                {
                    if (String.Equals(prop.PropertyType.FullName, "System.String"))
                    {
                        prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? null : Convert.ToString(record[prop.Name], CultureInfo.InvariantCulture), null);
                    }
                    else if (String.Equals(prop.PropertyType.FullName, "System.Decimal"))
                    {
                        prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? 0 : 
                            Convert.ToDecimal(record[prop.Name], CultureInfo.InvariantCulture), null);
                    }
                    else
                    {
                        prop.SetValue(entity, DBNull.Value.Equals(record[prop.Name]) ? null : record[prop.Name], null);
                    }
                }
            }
        }
    }

    /// <summary>
    /// Method for selecting records from Data Reader
    /// </summary>
    public static ArrayList SelectRecords(Collection<Object> entityList, IDataReader reader)
    {
        ArrayList resultList = new ArrayList();
        if (entityList != null && reader != null)
        {
            List<string> propertiesOfAllEntities = new List<string>();
            foreach (Object entity in entityList)
            {
                PropertyInfo[] propertyInfo = entity.GetType().GetProperties();
                foreach (PropertyInfo prop in propertyInfo)
                {
                    propertiesOfAllEntities.Add(prop.Name);
                }
            }
            while (reader.Read())
            {
                Dictionary<string, object> record = MapPropertiesToReaderValues(propertiesOfAllEntities, reader);
                resultList.Add(record);
            }
        }
        return resultList;
    }

    /// <summary>
    /// Helper method for mapping properties with reader values
    /// </summary>
    private static Dictionary<string, object> MapPropertiesToReaderValues(List<string> propertiesOfAllEntities, IDataReader reader)
    {
        Dictionary<string, object> propertyResultList = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
        for (int i = 0; i < reader.FieldCount; i++)
        {
            string readerFieldName = reader.GetName(i);
            //Whether propertiesOfAllEntities.Contains the property
            if (propertiesOfAllEntities.FindIndex(x => x.Equals(readerFieldName, StringComparison.OrdinalIgnoreCase)) != -1)
            {
                propertyResultList.Add(readerFieldName, reader[i]);
            }

        }
        return propertyResultList;
    }

}

Client to Test

static void Main(string[] args)
{

    Collection<Report> reports = new Collection<Report>();
    string connectionString = "Data Source=myserver;Initial Catalog=mydatabase;Integrated Security=SSPI";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string commandText = @"SELECT R.report_type_code AS ReportTypeCode 
                                    FROM Report_Type R";

        using (SqlCommand command = new SqlCommand(commandText, connection))
        {
            command.CommandType = System.Data.CommandType.Text;
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    Collection<Object> entityList = new Collection<Object>();
                    entityList.Add(new Report());
                    ArrayList records = EntityDataMappingHelper.SelectRecords(entityList, reader);
                    for (int i = 0; i < records.Count; i++)
                    {
                        Report report = new Report();
                        Dictionary<string, object> currentRecord = (Dictionary<string, object>)records[i];
                        EntityDataMappingHelper.FillEntityFromRecord(report, currentRecord);
                        reports.Add(report);
                    }
                }
            }
        }
    }
    Console.ReadLine();
}

DTO/Entity

public class Report
{
    public Int16? ReportTypeCode { get; set; }
    public string ReportName { get; set; }
}
share|improve this question
3  
One idea is have you considered using existing ORM's such as EF or LinqSQL? –  dreza Jan 1 '13 at 23:20
    
@dreza My application is intensive data read only (to be displayed as tabular data). Hence ORM will be an unwanted performance hit here. –  Lijo Jan 21 '13 at 11:58
add comment

1 Answer

Observations

  • You are reinventing the wheel. Yes, an Object/Relational Mapper such as Entity Framework adds an overhead, but if your queries are select abc from xyz I don't see it being an issue.

  • Using reflection has an overhead and performance hit, too.

  • Projecting R.report_type_code into a full-fledged Report instance makes no sense. If you want to select report type codes, return report type codes, not a bunch of reports without a description.

  • If a command needed a parameter, would you concatenate it into a WHERE statement within the command text, or use an SqlParameter? Would it be the client code's responsibility? Your code isn't crystal-clear about this.

  • A static class called EntityDataMappingHelper can be considered a code smell. From this Programmers.SE answer (emphasis mine):

    if a helper method has any external dependency (e.g. a DB) which makes it - thus its callers - hard to unit test, it is better to declare it non-static. This allows dependency injection, thus making the method's callers easier to unit test.


Recommendations

  • The SelectRecords method doesn't need an instance, it needs a type - make your method generic, substitute that Collection<object> for a <TEntity> type parameter with some where TEntity : class, new() type constraint.

  • You shouldn't be calling your strongly-typed return value records - call it reports instead, it's less confusing. Or call it entities if you prefer a more generic name; a "record" is a low-level thing that a "report" doesn't even know/care about.

  • If the database schema is all yours, I think your Report entity wants an Id property. Just in case there's eventually another "entity" that wants to refer to a specific report. Much better than indexing the ReportName column.

  • Use List<T> over the obsolete ArrayList:

    • from StackOverflow: ArrayList belongs to the days that C# didn't have generics. It's deprecated in favor of List. You shouldn't use ArrayList in new code that targets .NET >= 2.0 unless you have to interface with an old API that uses it.

Alternatives

  • Give Entity Framework (Code-First) a try. I'm sure you won't even notice the "performance hit" (given your current code it might actually be a performance increase), and your client code will be much, much simpler. How about this:

    IEnumerable<Report> reports;
    IEnumerable<Int16> reportTypeCodes;
    using (var context = new MyEntityFrameworkContext())
    {
        reports = context.Reports.ToList();
        reportTypeCodes = context.Reports
                                 .Where(report => report.ReportTypeCode != null)
                                 .GroupBy(report => report.ReportTypeCode)
                                 .Select(grouping => grouping.Key)
                                 .ToList();
    }
    
  • If you really don't want to use EF, you might actually fall in love with Dapper.NET, a micro-ORM - from the SO tag wiki:

    it focuses on making the materialization as fast as possible, with no overheads from things like identity managers - just "run this query and give me the (typed) data". [...] Quite possibly the fastest materializer available for .NET.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.