Question Issue in mapping property from excel data table to list

Palak Shah

Well-known member
Joined
Apr 29, 2020
Messages
97
Programming Experience
1-3
I have testdata model, and I want to map the column values to existing class hierarchy property's and currently it is not mapping the values and shows NULL value

For instance in test data model i have field paymentOptions and in excel sheet the value is KlarnaOptions but after the map of datatable to list - the value of paymentOptions is showing NULL

Test Data Model:


Test Data Model:
using ContradoWebsiteAutoFramework.Constants;
using ContradoWebsiteAutoFramework.Model;
using ContradoWebsiteAutoFramework.Model.PaymentOptions;
using Framework.Model.Excel;
using System.Collections.Generic;

namespace ContradoWebSiteAutoFramework.Model.Excel
{
    public partial class TestDataModel
    {
    
        public TestDataModel() {

        
        }
    
    
        [DataNames("TestName")]
        public string TestName { get; set; }

    

        [DataNames("productId")]
        public int productId { get; set; }

        [DataNames("orderId")]
        public int orderId { get; set; }

    
        [DataNames("designMethod")]
        public DesignMethod designMethod { get; set; }

        [DataNames("signedIn")]
        public bool signedIn { get; set; }

        [DataNames("increaseBasketQty")]
        public bool increaseBasketQty { get; set; }

        [DataNames("signedInCMS")]
        public bool signedInCMS { get; set; }

        [DataNames("editable")]
        public bool editable { get; set; }

        [DataNames("paymentOptions")]
        public PaymentOptions paymentOptions { get; set; }

        [DataNames("checkInVoice")]
        public bool checkInVoice { get; set; }

        [DataNames("navigateToDesign")]
        public bool navigateToDesign { get; set; }

        [DataNames("checkOrderAuthorsie")]
        public bool checkOrderAuthorsie { get; set; }

        [DataNames("checkSplitOrder")]
        public bool checkSplitOrder { get; set; }

        [DataNames("SiteId")]
        public string SiteId { get; set; }

        [DataNames("SiteUrl")]
        public string SiteUrl { get; set; }

        [DataNames("CultureCode")]
        public string CultureCode { get; set; }

        [DataNames("SiteGroupId")]
        public string SiteGroupId { get; set; }

        [DataNames("NickName")]
        public string NickName { get; set; }

        [DataNames("byCard")]
        public KlarnaOptions byCard { get; set; }

        [DataNames("payLater")]
        public bool payLater { get; set; }

        [DataNames("sliceIt")]
        public bool sliceIt { get; set; }

        [DataNames("portal")]
        public PaymentPortal portal { get; set; }

        [DataNames("delivery")]
        public DeliveryMethod delivery{get;set;}

    }

}

Mapper for converting Datatable to list:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Framework.Model.Excel
{
    public class DataNamesMapper<TEntity> where TEntity : class, new()
    {
        public TEntity Map(DataRow row)
        {
            TEntity entity = new TEntity();
            return Map(row, entity);
        }

        public TEntity Map(DataRow row, TEntity entity)
        {
            var columnNames = row.Table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
            var properties = (typeof(TEntity)).GetProperties()
                                              .Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())
                                              .ToList();
            foreach (var prop in properties)
            {
                PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
            }

            return entity;
        }

        public IEnumerable<TEntity> Map(DataTable table)
        {
            List<TEntity> entities = new List<TEntity>();
            var columnNames = table.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToList();
            var properties = (typeof(TEntity)).GetProperties()
                                              .Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())
                                              .ToList();
            foreach (DataRow row in table.Rows)
            {
                TEntity entity = new TEntity();
                foreach (var prop in properties)
                {
                    PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
                }
                entities.Add(entity);
            }

            return entities;
        }
    }
}

Property Map Helper:
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Framework.Model.Excel
{
    public static class PropertyMapHelper
    {

        public static void Map(Type type, DataRow row, PropertyInfo prop, object entity)
        {
            List<string> columnNames = AttributeHelper.GetDataNames(type, prop.Name);

            foreach (var columnName in columnNames)
            {
                if (!String.IsNullOrWhiteSpace(columnName) && row.Table.Columns.Contains(columnName))
                {
                    var propertyValue = row[columnName];
                    if (propertyValue != DBNull.Value)
                    {
                        ParsePrimitive(prop, entity, row[columnName]);
                        break;
                    }
                }
            }
        }

        private static void ParsePrimitive(PropertyInfo prop, object entity, object value)
        {
            if (prop.PropertyType == typeof(string))
            {
                prop.SetValue(entity, value.ToString().Trim(), null);
            }
            else if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))
            {
                if (value == null)
                {
                    prop.SetValue(entity, null, null);
                }
                else
                {
                    prop.SetValue(entity, ParseBoolean(value.ToString()), null);
                }
            }
            else if (prop.PropertyType == typeof(long))
            {
                prop.SetValue(entity, long.Parse(value.ToString()), null);
            }
            else if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(int?))
            {
                if (value == null)
                {
                    prop.SetValue(entity, null, null);
                }
                else
                {
                    prop.SetValue(entity, int.Parse(value.ToString()), null);
                }
            }
            else if (prop.PropertyType == typeof(decimal))
            {
                prop.SetValue(entity, decimal.Parse(value.ToString()), null);
            }
            else if (prop.PropertyType == typeof(double) || prop.PropertyType == typeof(double?))
            {
                double number;
                bool isValid = double.TryParse(value.ToString(), out number);
                if (isValid)
                {
                    prop.SetValue(entity, double.Parse(value.ToString()), null);
                }
            }
            else if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(Nullable<DateTime>))
            {
                DateTime date;
                bool isValid = DateTime.TryParse(value.ToString(), out date);
                if (isValid)
                {
                    prop.SetValue(entity, date, null);
                }
                else
                {
                    isValid = DateTime.TryParseExact(value.ToString(), "MMddyyyy", new CultureInfo("en-US"), DateTimeStyles.AssumeLocal, out date);
                    if (isValid)
                    {
                        prop.SetValue(entity, date, null);
                    }
                }
            }
 else if (prop.PropertyType.IsEnum)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                var enumValue = Enum.Parse(type,value.ToString(), true);
                prop.SetValue(entity, enumValue, null);
            }
            else if (prop.PropertyType == typeof(Guid))
            {
                Guid guid;
                bool isValid = Guid.TryParse(value.ToString(), out guid);
                if (isValid)
                {
                    prop.SetValue(entity, guid, null);
                }
                else
                {
                    isValid = Guid.TryParseExact(value.ToString(), "B", out guid);
                    if (isValid)
                    {
                        prop.SetValue(entity, guid, null);
                    }
                }


            }
         
        }

        public static bool ParseBoolean(object value)
        {
            if (value == null || value == DBNull.Value) return false;

            switch (value.ToString().ToLowerInvariant())
            {
                case "1":
                case "y":
                case "yes":
                case "true":
                    return true;

                case "0":
                case "n":
                case "no":
                case "false":
                default:
                    return false;
            }
        }
    }
}

Custom Property Type to which I want to map columns:
using ContradoWebsiteAutoFramework.Entities;

namespace ContradoWebsiteAutoFramework.Model.PaymentOptions
{
    public class PaymentOptions
    {
        public PaymentPortal portal;
        public DeliveryMethod delivery = DeliveryMethod.Billing;

        public PaymentOptions()
        {
        }
        public PaymentOptions(Site site)
        {

        }
    }
}
DataName Attribut Class:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Framework.Model.Excel
{
    [AttributeUsage(AttributeTargets.Property)]
    public class DataNamesAttribute : Attribute
    {
        protected List<string> _valueNames { get; set; }

        public List<string> ValueNames
        {
            get
            {
                return _valueNames;
            }
            set
            {
                _valueNames = value;
            }
        }

        public DataNamesAttribute()
        {
            _valueNames = new List<string>();
        }

        public DataNamesAttribute(params string[] valueNames)
        {
            _valueNames = valueNames.ToList();
        }
    }
}
Custom class is further inherited:
namespace ContradoWebsiteAutoFramework.Model.PaymentOptions
{
    public class KlarnaOptions : PaymentOptions
    {
        //default - don't use card payment by deffault
        public bool byCard = false;
        public bool payLater = false;
        public bool sliceIt = false;
        public KlarnaOptions()
        {
            portal = PaymentPortal.Klarna;
        }
    }
}
 
Last edited:
Thank you for using code tags!
 
Where is the code for PropertyMapHelper?

Are you sure that that code within this loop is executed?
C#:
           foreach (var prop in properties)
            {
                PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
            }
 
Where is the code for PropertyMapHelper?

Are you sure that that code within this loop is executed?
C#:
           foreach (var prop in properties)
            {
                PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);
            }
Yes, but for custom property type i.e for paymentOptions / portal columns it is not falling in any of the conditions mentioned in Map Method
 
Nice that paymentOptions and portal are enums.

Look closely at the code for PropertyMapHelper.ParsePrimitive(). Notice that there is no code there for handling parsing enums. Looks like you'll need to add code for doing that parsing.
 
Nice that paymentOptions and portal are enums.

Look closely at the code for PropertyMapHelper.ParsePrimitive(). Notice that there is no code there for handling parsing enums. Looks like you'll need to add code for doing that parsing.
Hey, yes I was not handling "Enum" mapping, after adding code it worked for few columns,

C#:
 else if (prop.PropertyType.IsEnum)
            {
                var type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                var enumValue = Enum.Parse(type,value.ToString(), true);
                prop.SetValue(entity, enumValue, null);
            }

however for payment options I still not getting the value set because

Currently in my excel it has value as "KlarnaOptions" and after mapping it shows null, and in my code Klarnaptions class is again inherited from PaymentOptions so how do I map that

Before excel mapping we used to set value as below, so currently for all those options whose further inherited in that case I'm not getting value which I have passed in excel, I don't know how to map such a case


C#:
paymentOptions = new KlarnaOptions()
                {
                    delivery = DeliveryMethod.Billing,
                    byCard = true
                }
 
Last edited:
Added Property Map Helper Function in Thread
Please stop editing your original question. This is not StackOverflow where it is customary and expected to keep on massaging the question. This is a standard forum where there is a timeline associated with the thread of posts. If people don't look closely at the edit times, it makes people wonder why I would be asking about the mapper helper class in post #5 when it seems to be present in post #1... but only because you added it after seeing post #5. Same deal where in post #7 I say something about enums not being parsed, then suddenly it shows up in post #1. It makes me look like I'm on drugs or not paying attention.
 
Please stop editing your original question. This is not StackOverflow where it is customary and expected to keep on massaging the question. This is a standard forum where there is a timeline associated with the thread of posts. If people don't look closely at the edit times, it makes people wonder why I would be asking about the mapper helper class in post #5 when it seems to be present in post #1... but only because you added it after seeing post #5. Same deal where in post #7 I say something about enums not being parsed, then suddenly it shows up in post #1. It makes me look like I'm on drugs or not paying attention.
ohh sorry for that :( - I was not knowing about the standards..now no more editing original thread!!

@skydriver Can you plz help on this mapping issue?
 
Last edited:
Seems pretty obvious: Just like when you did not have code to handle enums, it looks like you don't have code to handle custom classes either. Not you have to add that code as well.

Out of curiosity, why are you rolling your own mapper when there are already great libraries like AutoMapper which exist?
 
I see. Nothing better and more reliable than writing your own code. At least if something is wrong with it, you have an idea where to start looking...
 
Back
Top Bottom