Question Issue in mapping property from excel data table to list

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
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:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
Thank you for using code tags!
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
Where is the code for PropertyMapHelper?

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

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
Where is the code for PropertyMapHelper?

Are you sure that that code within this loop is executed?
Code:
           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
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
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.
 

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
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:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
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.
 

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
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:

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
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?
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,259
Location
UK
Programming Experience
10+
I find it odd that you could be missing code if you wrote this yourself.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,259
Location
UK
Programming Experience
10+
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...
 

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
@Skydiver - I'm new in this coding world, so not that much aware on possible approaches which I can take! - For custom class I'm still figuring out on possible solutions and if I should use Automapper package to get the desired result which I'm looking for?

Need your bit help in understanding which approach I should go with and how can I start on that?
 

Sheepings

Senior Programmer
Staff member
Joined
Sep 5, 2018
Messages
1,259
Location
UK
Programming Experience
10+
Most automapper ORM's use Entity Framework 6. Which is absolute dirt. If you want to know why I don't like EF, search my name on the forums along with the keywords entity framework and you will find all my reasons.

If your aim is to learn about the language and the frameworks, then no, don't use an automapper. And study the documentation on what you're trying to do.

If your aim is to complete a project which you'd prefer to complete with as little effort as possible, then some people will probably recommend an automapper. I'd preference dapper, if you do go that route.

ORM's aren't plain sailing and they don't always work out of the box, and sometimes you need to dabble with the generated code.

I don't get it, you like the suggestion where i said to write your own code, but in the next breath you ask about auto-generators to help surpass your problem...
 

Palak Shah

Active member
Joined
Apr 29, 2020
Messages
34
Programming Experience
1-3
Most automapper ORM's use Entity Framework 6. Which is absolute dirt. If you want to know why I don't like EF, search my name on the forums along with the keywords entity framework and you will find all my reasons.

If your aim is to learn about the language and the frameworks, then no, don't use an automapper. And study the documentation on what you're trying to do.

If your aim is to complete a project which you'd prefer to complete with as little effort as possible, then some people will probably recommend an automapper. I'd preference dapper, if you do go that route.

ORM's aren't plain sailing and they don't always work out of the box, and sometimes you need to dabble with the generated code.

I don't get it, you like the suggestion where i said to write your own code, but in the next breath you ask about auto-generators to help surpass your problem...
I completely understand your point of views, now my only worry is at this point where I'm left with custom class mapping of excel column values - I need to do write my own code as I did for enum but I don't know where should I start with or what should I search so I get some examples and can implement for my classes :(
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
I was referring specifically to Automapper:

It is not specifically tied to EntityFramework. It's for anytime you need to map one kind of object to another kind of object.
 

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
1,406
Location
Virginia Beach, VA
Programming Experience
10+
I don't know where should I start with or what should I search so I get some examples and can implement for my classes
This depends if you want to write a generic solution like that blogger did, or if you want to write a specific solution to your particular problem. Writing code for your specific problem will involve less code overall and be easier to understand, but it will be tightly coupled to your classes.

Basically, you would have a ParseObjects() method whose job is to instantiate and fill in object properties. That means your ParsePrimitive() should return whether it parsed a primitive value or not, and that you should have something that checks that so that it will call your ParseObjects() instead.

Within te ParseObjects() you would again inspect the type of the property to determine if it's one of your known class types. You then would instantiate the appropriate class, and assign that instance to the property of the parent object. The next you would start reading values out of your row and setting them on properties of that instance (as opposed to the ParsePrimitive() which was setting the the values directly to the property of the parent object). If you are smart, you can just call ParsePrimitive() and pass in this new instance instance instead of the parent object. You may need to do this recursively if this new instance also has non-primitive properties.

For the generic solution, you would need to change the code for the DataNamesAttribute to also take an optional class type. This is for the case when the property type is base or abstract type, but you want instantiate a specific type further down the hierarchy. Then in the ParseObjects() class instead of checking for your well known class types, you would have to instantiate either the type specified in the attribute, or if not specified in the attribute, the type of the property. Then the rest is the same as the previous paragraph regarding setting parent object properties and child object properties.
 
Top Bottom