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:
@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?
 
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...
 
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 :(
 
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.
 
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.
 
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.

I would like to write code for my specific problem looking at the present scenario where I'm struggling to understand few things
 
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.
I'm wondering shall I go ahead with Auto Mapper, where I will create DTO for product options and klarnaloptions class and use DTO and do the mapping?

Can that solve the problem?
 
@Skydiver - Hey apologies for asking one more time but I'm not able to parse the object to desired type, can you please help?


Tried parsing PaymentOptions:
else if (prop.PropertyType == typeof(PaymentOptions))
            {
                prop.SetValue(entity, prop.Name, null);
            }

But it's not working and I'm not able to understand how do I get the value mapped
 
currently we are accessing and initialising as below

Earlier we used to access and assign the values as below

Assigning Values

C#:
public class WorkflowParameters

    {
        public Site _site = default(Site);
        public PaymentOptions paymentOptions = default(PaymentOptions);
    }
var param = new WorkflowParameters()

            {
paymentOptions = new KlarnaOptions()

                {

                    delivery = DeliveryMethod.Billing,

                    payLater = true

                }

            };


But I'm not able to A. Parse the object and B. after parsing how do I add "delivery" and "payLater" options in paymentOptions

As a newbie in this area, it is really getting tough
 
Here resides the problem. . .

You took code from another blog or page, used it without knowing how it worked or understood it. You came here for help, and with that somewhere along the line you were linked these set of docs to follow :
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.
Now you've come back asking how to use the auto mapper even though the docs are not severely lacking in material. You expected to "google" or "search" your way through this project you are making despite lacking some fundamental basics. You're trying to build up some type of payment system, which will probably result in it being haXeD. I have learned over the years that its impossible to teach people advanced coding skills when they skipped out on the basics. And I've proved to myself that helping people in your situation is a waste of time.

No offense! All I can tell you is that your approach to understanding how this works is all wrong, and I won't waste my own time trying to explain it to you. When I was younger, I too used to use forums in my very early days, and what I found most that helped me was people who kept linking me to MSDN, and other documentation. Reading is how you learn, and asking for a handout is how you remain ignorant to the learning curve which this language requires.

Something I suggest reading : Getting Started Guide — AutoMapper documentation
Moreover I suggest MSDN, and taking the crash course they provide in the getting started section of the docs. You can find a link in my signature. I'll sum it up by telling you; you won't be able to complete this project with your current approach.
 
Here resides the problem. . .

You took code from another blog or page, used it without knowing how it worked or understood it. You came here for help, and with that somewhere along the line you were linked these set of docs to follow :

Now you've come back asking how to use the auto mapper even though the docs are not severely lacking in material. You expected to "google" or "search" your way through this project you are making despite lacking some fundamental basics. You're trying to build up some type of payment system, which will probably result in it being haXeD. I have learned over the years that its impossible to teach people advanced coding skills when they skipped out on the basics. And I've proved to myself that helping people in your situation is a waste of time.

No offense! All I can tell you is that your approach to understanding how this works is all wrong, and I won't waste my own time trying to explain it to you. When I was younger, I too used to use forums in my very early days, and what I found most that helped me was people who kept linking me to MSDN, and other documentation. Reading is how you learn, and asking for a handout is how you remain ignorant to the learning curve which this language requires.

Something I suggest reading : Getting Started Guide — AutoMapper documentation
Moreover I suggest MSDN, and taking the crash course they provide in the getting started section of the docs. You can find a link in my signature. I'll sum it up by telling you; you won't be able to complete this project with your current approach.
Sure I'll go over the Automapper documentation and try understanding it
 
@Skydiver - Hey apologies for asking one more time but I'm not able to parse the object to desired type, can you please help?


Tried parsing PaymentOptions:
else if (prop.PropertyType == typeof(PaymentOptions))
            {
                prop.SetValue(entity, prop.Name, null);
            }

But it's not working and I'm not able to understand how do I get the value mapped
Considering that you are setting the value of the property to null, why are you surprised that it is not working. Try instantiating an a Klarna payment object and setting the value of the property to a reference to the new instance.
 
Back
Top Bottom