Click here to Skip to main content
14,239,810 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello,

Task: To convert an DataTable To List

So far, I have done this using EnumerableRowCollection<trow>. and the code goes as below:
private void LoadAppointments()
        {
            var conn = new SqlConnection(ConnectionString);
            var adapter = new SqlDataAdapter("LoadAppointment", conn);
            var ds = new DataSet();
            adapter.Fill(ds);
            
            var myData = ds.Tables[0].AsEnumerable().Select(r => new
            {
                column1 = r.Field<int>("AppointmentId"),
                column2 = r.Field<string>("Subject"),
                column3 = r.Field<DateTime>("StartDate"),
                column4 = r.Field<DateTime>("EndDate"),
                column5 = r.Field<bool>("AllDay"),
                column6 = r.Field<int>("Status"),
                column7= r.Field<int>("Label"),
                column8 = r.Field<string>("CustomName"),
                column9 = r.Field<string>("CustomName")
            });

            var list = myData.ToList();
            schedulerStorage1.Appointments.DataSource = list;
   }


But, I have a Custom class with some properties. I would like to Create a list out of that class instead of using EnumerableRowCollection<trow>.

To be more specific: I would like to go for an alternative way other than EnumerableRowCollection<trow>.

Any help regarding this will be appreciated a lot!!
Posted
Comments
Rate this:
Please Sign up or sign in to vote.

Solution 1

   
Rate this:
Please Sign up or sign in to vote.

Solution 2

While selecting you can provide the type you want the result in and fill the properties accordingly. If you don't provide then by default it creates AnonymousType. See it in the sample code here where i am getting a list of employees as output:

public void datatest() {
            DataTable dt = new DataTable("table1");
            dt.Columns.Add("name");
            dt.Columns.Add("Role");

            dt.Rows.Add("rama", "god");
            dt.Rows.Add("krishna", "god");
            dt.Rows.Add("ravana", "devil");

            List<emp> output = dt.AsEnumerable().Select(r => new Emp() { Name = r.Field<string>("name"), Role = r.Field<string>("Role") }).ToList(); 
 
        }
    }
    class Emp
    {
        public string Name = string.Empty;
        public string Role = string.Empty;
    }
   
v3
Rate this:
Please Sign up or sign in to vote.

Solution 3

A common class for convert datatable to list

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BL
{
    public class clsCommonFunctions
    {
    }

    public static class Helper
    {
        
        public static List<T> DataTableToList<T>(this DataTable dataTable) where T : new()
        {
            var dataList = new List<T>();

            //Define what attributes to be read from the class
            const System.Reflection.BindingFlags flags = System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance;

            //Read Attribute Names and Types
            var objFieldNames = typeof(T).GetProperties(flags).Cast<System.Reflection.PropertyInfo>().
                Select(item => new
                {
                    Name = item.Name,
                    Type = Nullable.GetUnderlyingType(item.PropertyType) ?? item.PropertyType
                }).ToList();

            //Read Datatable column names and types
            var dtlFieldNames = dataTable.Columns.Cast<DataColumn>().
                Select(item => new
                {
                    Name = item.ColumnName,
                    Type = item.DataType
                }).ToList();

            foreach (DataRow dataRow in dataTable.AsEnumerable().ToList())
            {
                var classObj = new T();

                foreach (var dtField in dtlFieldNames)
                {
                    System.Reflection.PropertyInfo propertyInfos = classObj.GetType().GetProperty(dtField.Name);

                    var field = objFieldNames.Find(x => x.Name == dtField.Name);

                    if (field != null)
                    {
                        
                        if (propertyInfos.PropertyType == typeof(DateTime))
                        {
                            propertyInfos.SetValue
                            (classObj, convertToDateTime(dataRow[dtField.Name]), null);
                        }
                        else if (propertyInfos.PropertyType == typeof(Nullable<DateTime>))
                        {
                            propertyInfos.SetValue
                            (classObj, convertToDateTime(dataRow[dtField.Name]), null);
                        }
                        else if (propertyInfos.PropertyType == typeof(int))
                        {
                            propertyInfos.SetValue
                            (classObj, ConvertToInt(dataRow[dtField.Name]), null);
                        }
                        else if (propertyInfos.PropertyType == typeof(long))
                        {
                            propertyInfos.SetValue
                            (classObj, ConvertToLong(dataRow[dtField.Name]), null);
                        }
                        else if (propertyInfos.PropertyType == typeof(decimal))
                        {
                            propertyInfos.SetValue
                            (classObj, ConvertToDecimal(dataRow[dtField.Name]), null);
                        }
                        else if (propertyInfos.PropertyType == typeof(String))
                        {
                            if (dataRow[dtField.Name].GetType() == typeof(DateTime))
                            {
                                propertyInfos.SetValue
                                (classObj, ConvertToDateString(dataRow[dtField.Name]), null);
                            }
                            else
                            {
                                propertyInfos.SetValue
                                (classObj, ConvertToString(dataRow[dtField.Name]), null);
                            }
                        }
                    }
                }
                dataList.Add(classObj);
            }
            return dataList;
        }

        private static string ConvertToDateString(object date)
        {
            if (date == null)
                return string.Empty;

            return date == null ? string.Empty : Convert.ToDateTime(date).ConvertDate();
        }

        private static string ConvertToString(object value)
        {
            return Convert.ToString(ReturnEmptyIfNull(value));
        }

        private static int ConvertToInt(object value)
        {
            return Convert.ToInt32(ReturnZeroIfNull(value));
        }

        private static long ConvertToLong(object value)
        {
            return Convert.ToInt64(ReturnZeroIfNull(value));
        }

        private static decimal ConvertToDecimal(object value)
        {
            return Convert.ToDecimal(ReturnZeroIfNull(value));
        }

        private static DateTime convertToDateTime(object date)
        {
            return Convert.ToDateTime(ReturnDateTimeMinIfNull(date));
        }

        public static string ConvertDate(this DateTime datetTime, bool excludeHoursAndMinutes = false)
        {
            if (datetTime != DateTime.MinValue)
            {
                if (excludeHoursAndMinutes)
                    return datetTime.ToString("yyyy-MM-dd");
                return datetTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
            }
            return null;
        }
        public static object ReturnEmptyIfNull(this object value)
        {
            if (value == DBNull.Value)
                return string.Empty;
            if (value == null)
                return string.Empty;
            return value;
        }
        public static object ReturnZeroIfNull(this object value)
        {
            if (value == DBNull.Value)
                return 0;
            if (value == null)
                return 0;
            return value;
        }
        public static object ReturnDateTimeMinIfNull(this object value)
        {
            if (value == DBNull.Value)
                return DateTime.MinValue;
            if (value == null)
                return DateTime.MinValue;
            return value;
        }
    }
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100