Alex Lonyay

Alex Lonyay

  • 1.8k
  • 55
  • 873

Issue in mapping property from excel data table to list

Jun 28 2020 8:02 AM
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:
 
  1. using AutoFramework.Constants;  
  2. using AutoFramework.Model;  
  3. using AutoFramework.Model.PaymentOptions;  
  4. using Framework.Model.Excel;  
  5. using System.Collections.Generic;  
  6.   
  7. namespace AutoFramework.Model.Excel  
  8. {  
  9.     public partial class TestDataModel  
  10.     {  
  11.           
  12.         public TestDataModel() {  
  13.   
  14.               
  15.         }  
  16.           
  17.           
  18.         [DataNames("TestName")]  
  19.         public string TestName { getset; }  
  20.   
  21.           
  22.   
  23.         [DataNames("productId")]  
  24.         public int productId { getset; }  
  25.   
  26.         [DataNames("orderId")]  
  27.         public int orderId { getset; }  
  28.   
  29.           
  30.         [DataNames("designMethod")]  
  31.         public DesignMethod designMethod { getset; }  
  32.   
  33.         [DataNames("signedIn")]  
  34.         public bool signedIn { getset; }  
  35.   
  36.         [DataNames("increaseBasketQty")]  
  37.         public bool increaseBasketQty { getset; }  
  38.   
  39.         [DataNames("signedInCMS")]  
  40.         public bool signedInCMS { getset; }  
  41.   
  42.         [DataNames("editable")]  
  43.         public bool editable { getset; }  
  44.   
  45.         [DataNames("paymentOptions")]  
  46.         public PaymentOptions paymentOptions { getset; }  
  47.   
  48.         [DataNames("checkInVoice")]  
  49.         public bool checkInVoice { getset; }  
  50.   
  51.         [DataNames("navigateToDesign")]  
  52.         public bool navigateToDesign { getset; }  
  53.   
  54.         [DataNames("checkOrderAuthorsie")]  
  55.         public bool checkOrderAuthorsie { getset; }  
  56.   
  57.         [DataNames("checkSplitOrder")]  
  58.         public bool checkSplitOrder { getset; }  
  59.   
  60.         [DataNames("SiteId")]  
  61.         public string SiteId { getset; }  
  62.   
  63.         [DataNames("SiteUrl")]  
  64.         public string SiteUrl { getset; }  
  65.   
  66.         [DataNames("CultureCode")]  
  67.         public string CultureCode { getset; }  
  68.   
  69.         [DataNames("SiteGroupId")]  
  70.         public string SiteGroupId { getset; }  
  71.   
  72.         [DataNames("NickName")]  
  73.         public string NickName { getset; }  
  74.   
  75.         [DataNames("byCard")]  
  76.         public KlarnaOptions byCard { getset; }  
  77.   
  78.         [DataNames("payLater")]  
  79.         public bool payLater { getset; }  
  80.   
  81.         [DataNames("sliceIt")]  
  82.         public bool sliceIt { getset; }  
  83.   
  84.         [DataNames("portal")]  
  85.         public PaymentPortal portal { getset; }  
  86.   
  87.         [DataNames("delivery")]  
  88.         public DeliveryMethod delivery{get;set;}  
  89.   
  90.     }  
  91.       
  92. }  
 Mapper Class

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Text;  
  6. using System.Threading.Tasks;  
  7.   
  8. namespace Framework.Model.Excel  
  9. {  
  10.     public class DataNamesMapper where TEntity : classnew()  
  11.     {  
  12.         public TEntity Map(DataRow row)  
  13.         {  
  14.             TEntity entity = new TEntity();  
  15.             return Map(row, entity);  
  16.         }  
  17.   
  18.         public TEntity Map(DataRow row, TEntity entity)  
  19.         {  
  20.             var columnNames = row.Table.Columns.Cast().Select(x => x.ColumnName).ToList();  
  21.             var properties = (typeof(TEntity)).GetProperties()  
  22.                                               .Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())  
  23.                                               .ToList();  
  24.             foreach (var prop in properties)  
  25.             {  
  26.                 PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);  
  27.             }  
  28.   
  29.             return entity;  
  30.         }  
  31.   
  32.         public IEnumerable Map(DataTable table)  
  33.         {  
  34.             List entities = new List();  
  35.             var columnNames = table.Columns.Cast().Select(x => x.ColumnName).ToList();  
  36.             var properties = (typeof(TEntity)).GetProperties()  
  37.                                               .Where(x => x.GetCustomAttributes(typeof(DataNamesAttribute), true).Any())  
  38.                                               .ToList();  
  39.             foreach (DataRow row in table.Rows)  
  40.             {  
  41.                 TEntity entity = new TEntity();  
  42.                 foreach (var prop in properties)  
  43.                 {  
  44.                     PropertyMapHelper.Map(typeof(TEntity), row, prop, entity);  
  45.                 }  
  46.                 entities.Add(entity);  
  47.             }  
  48.   
  49.             return entities;  
  50.         }  
  51.     }  
  52. }  
Data Name Attribute class
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace Framework.Model.Excel  
  8. {  
  9.     [AttributeUsage(AttributeTargets.Property)]  
  10.     public class DataNamesAttribute : Attribute  
  11.     {  
  12.         protected List<string> _valueNames { getset; }  
  13.   
  14.         public List<string> ValueNames  
  15.         {  
  16.             get  
  17.             {  
  18.                 return _valueNames;  
  19.             }  
  20.             set  
  21.             {  
  22.                 _valueNames = value;  
  23.             }  
  24.         }  
  25.   
  26.         public DataNamesAttribute()  
  27.         {  
  28.             _valueNames = new List<string>();  
  29.         }  
  30.   
  31.         public DataNamesAttribute(params string[] valueNames)  
  32.         {  
  33.             _valueNames = valueNames.ToList();  
  34.         }  
  35.     }  
  36. }  
 
 Property Map Helper
 
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Globalization;  
  5. using System.Linq;  
  6. using System.Reflection;  
  7. using System.Text;  
  8. using System.Threading.Tasks;  
  9.   
  10. namespace Framework.Model.Excel  
  11. {  
  12.     public static class PropertyMapHelper  
  13.     {  
  14.   
  15.         public static void Map(Type type, DataRow row, PropertyInfo prop, object entity)  
  16.         {  
  17.             List<string> columnNames = AttributeHelper.GetDataNames(type, prop.Name);  
  18.   
  19.             foreach (var columnName in columnNames)  
  20.             {  
  21.                 if (!String.IsNullOrWhiteSpace(columnName) && row.Table.Columns.Contains(columnName))  
  22.                 {  
  23.                     var propertyValue = row[columnName];  
  24.                     if (propertyValue != DBNull.Value)  
  25.                     {  
  26.                         ParsePrimitive(prop, entity, row[columnName]);  
  27.                         break;  
  28.                     }  
  29.                 }  
  30.             }  
  31.         }  
  32.   
  33.         private static void ParsePrimitive(PropertyInfo prop, object entity, object value)  
  34.         {  
  35.             if (prop.PropertyType == typeof(string))  
  36.             {  
  37.                 prop.SetValue(entity, value.ToString().Trim(), null);  
  38.             }  
  39.             else if (prop.PropertyType == typeof(bool) || prop.PropertyType == typeof(bool?))  
  40.             {  
  41.                 if (value == null)  
  42.                 {  
  43.                     prop.SetValue(entity, nullnull);  
  44.                 }  
  45.                 else  
  46.                 {  
  47.                     prop.SetValue(entity, ParseBoolean(value.ToString()), null);  
  48.                 }  
  49.             }  
  50.             else if (prop.PropertyType == typeof(long))  
  51.             {  
  52.                 prop.SetValue(entity, long.Parse(value.ToString()), null);  
  53.             }  
  54.             else if (prop.PropertyType == typeof(int) || prop.PropertyType == typeof(int?))  
  55.             {  
  56.                 if (value == null)  
  57.                 {  
  58.                     prop.SetValue(entity, nullnull);  
  59.                 }  
  60.                 else  
  61.                 {  
  62.                     prop.SetValue(entity, int.Parse(value.ToString()), null);  
  63.                 }  
  64.             }  
  65.             else if (prop.PropertyType == typeof(decimal))  
  66.             {  
  67.                 prop.SetValue(entity, decimal.Parse(value.ToString()), null);  
  68.             }  
  69.             else if (prop.PropertyType == typeof(double) || prop.PropertyType == typeof(double?))  
  70.             {  
  71.                 double number;  
  72.                 bool isValid = double.TryParse(value.ToString(), out number);  
  73.                 if (isValid)  
  74.                 {  
  75.                     prop.SetValue(entity, double.Parse(value.ToString()), null);  
  76.                 }  
  77.             }  
  78.             else if (prop.PropertyType == typeof(DateTime) || prop.PropertyType == typeof(Nullable<DateTime>))  
  79.             {  
  80.                 DateTime date;  
  81.                 bool isValid = DateTime.TryParse(value.ToString(), out date);  
  82.                 if (isValid)  
  83.                 {  
  84.                     prop.SetValue(entity, date, null);  
  85.                 }  
  86.                 else  
  87.                 {  
  88.                     isValid = DateTime.TryParseExact(value.ToString(), "MMddyyyy"new CultureInfo("en-US"), DateTimeStyles.AssumeLocal, out date);  
  89.                     if (isValid)  
  90.                     {  
  91.                         prop.SetValue(entity, date, null);  
  92.                     }  
  93.                 }  
  94.             }  
  95.             else if (prop.PropertyType == typeof(Guid))  
  96.             {  
  97.                 Guid guid;  
  98.                 bool isValid = Guid.TryParse(value.ToString(), out guid);  
  99.                 if (isValid)  
  100.                 {  
  101.                     prop.SetValue(entity, guid, null);  
  102.                 }  
  103.                 else  
  104.                 {  
  105.                     isValid = Guid.TryParseExact(value.ToString(), "B"out guid);  
  106.                     if (isValid)  
  107.                     {  
  108.                         prop.SetValue(entity, guid, null);  
  109.                     }  
  110.                 }  
  111.   
  112.   
  113.             }  
  114.              
  115.         }  
  116.   
  117.         public static bool ParseBoolean(object value)  
  118.         {  
  119.             if (value == null || value == DBNull.Value) return false;  
  120.   
  121.             switch (value.ToString().ToLowerInvariant())  
  122.             {  
  123.                 case "1":  
  124.                 case "y":  
  125.                 case "yes":  
  126.                 case "true":  
  127.                     return true;  
  128.   
  129.                 case "0":  
  130.                 case "n":  
  131.                 case "no":  
  132.                 case "false":  
  133.                 default:  
  134.                     return false;  
  135.             }  
  136.         }  
  137.     }  
  138. }  
 
 
Custom Property Type to which I want to map column values

  1. using AutoFramework.Entities;  
  2.   
  3. namespace AutoFramework.Model.PaymentOptions  
  4. {  
  5.     public class PaymentOptions  
  6.     {  
  7.         public PaymentPortal portal;  
  8.         public DeliveryMethod delivery = DeliveryMethod.Billing;  
  9.   
  10.         public PaymentOptions()  
  11.         {  
  12.         }  
  13.         public PaymentOptions(Site site)  
  14.         {  
  15.   
  16.         }  
  17.     }  
  18. }  
  19.   
  20. namespace AutoFramework.Model.PaymentOptions  
  21. {  
  22.     public class KlarnaOptions : PaymentOptions  
  23.     {  
  24.         //default - don't use card payment by deffault  
  25.         public bool byCard = false;  
  26.         public bool payLater = false;  
  27.         public bool sliceIt = false;  
  28.         public KlarnaOptions()  
  29.         {  
  30.             portal = PaymentPortal.Klarna;  
  31.         }  
  32.     }  
  33. }  

Brought to you by: JavaScript SDK for Bold BI dashboard and analytics embedding. Free trial.