Convert DataTable to List

Question

0.00/5 (No votes)

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:

C#

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 4-Mar-13 2:14am

ArunAmalraj

Add a Solution

Comments

Maciej Los 4-Mar-13 8:16am

Do not repost!!!
http://www.codeproject.com/Questions/556304/ConvertplusDatasetplustableplustoplusaplusList

3 solutions

Add a Solution

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

Vikash saini · Answer 1 · 2016-02-02T20:27:00

A common class for convert datatable to list

C#

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;
        }
    }
}

RajeshRaushan · Answer 2 · 2013-03-04T03:17:00

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:

C#

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;
    }

Mehdi Gholam · Answer 3 · 2013-03-04T02:55:00

Solution 1

Read this : Working with Entities instead of DataTable Objects...[^]

Posted 4-Mar-13 2:55am

Mehdi Gholam

Convert DataTable to List

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Convert DataTable to List

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us