none
how to convert datatable to list using c#

Answers

  • you can use the following code 

     public static class EnumerableExtension
        {

            /// <summary>
            /// 
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="enumeration"></param>
            /// <param name="action"></param>
            public static void ForEach<T>(this IEnumerable<T> enumeration, Action<T> action)
            {

                foreach (T item in enumeration)
                {
                    action(item);
                }
            }

            /// <summary>
            /// Convert  list to Data Table
            /// </summary>
            /// <typeparam name="T">Target Class</typeparam>
            /// <param name="varlist">list you want to convert it to Data Table</param>
            /// <param name="fn">Delegate Function to Create Row</param>
            /// <returns>Data Table That Represent List data</returns>
            public static DataTable ToADOTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
            {
                DataTable toReturn = new DataTable();

                // Could add a check to verify that there is an element 0
                T TopRec = varlist.ElementAtOrDefault(0);

                if (TopRec == null)
                    return toReturn;

                // Use reflection to get property names, to create table
                // column names

                PropertyInfo[] oProps = ((Type)TopRec.GetType()).GetProperties();

                foreach (PropertyInfo pi in oProps)
                {
                    Type pt = pi.PropertyType;
                    if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                        pt = Nullable.GetUnderlyingType(pt);
                    toReturn.Columns.Add(pi.Name, pt);
                }

                foreach (T rec in varlist)
                {
                    DataRow dr = toReturn.NewRow();
                    foreach (PropertyInfo pi in oProps)
                    {
                        object o = pi.GetValue(rec, null);
                        if (o == null)
                            dr[pi.Name] = DBNull.Value;
                        else
                            dr[pi.Name] = o;
                    }
                    toReturn.Rows.Add(dr);
                }

                return toReturn;
            }

            /// <summary>
            /// Convert  list to Data Table
            /// </summary>
            /// <typeparam name="T">Target Class</typeparam>
            /// <param name="varlist">list you want to convert it to Data Table</param>
            /// <returns>Data Table That Represent List data</returns>
            public static DataTable ToADOTable<T>(this IEnumerable<T> varlist)
            {
                DataTable toReturn = new DataTable();

                // Could add a check to verify that there is an element 0
                T TopRec = varlist.ElementAtOrDefault(0);

                if (TopRec == null)
                    return toReturn;

                // Use reflection to get property names, to create table
                // column names

                PropertyInfo[] oProps = ((Type)TopRec.GetType()).GetProperties();

                foreach (PropertyInfo pi in oProps)
                {
                    Type pt = pi.PropertyType;
                    if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                        pt = Nullable.GetUnderlyingType(pt);
                    toReturn.Columns.Add(pi.Name, pt);
                }

                foreach (T rec in varlist)
                {
                    DataRow dr = toReturn.NewRow();
                    foreach (PropertyInfo pi in oProps)
                    {
                        object o = pi.GetValue(rec, null);

                        if (o == null)
                            dr[pi.Name] = DBNull.Value;
                        else
                            dr[pi.Name] = o;
                    }
                    toReturn.Rows.Add(dr);
                }

                return toReturn;
            }

            
            /// <summary>
            /// Convert Data Table To List of Type T
            /// </summary>
            /// <typeparam name="T">Target Class to convert data table to List of T </typeparam>
            /// <param name="datatable">Data Table you want to convert it</param>
            /// <returns>List of Target Class</returns>
            public  static List<T> ToList<T>(this DataTable datatable) where T : new()
            {
                List<T> Temp = new List<T>();
                try
                {
                    List<string> columnsNames = new List<string>();
                    foreach (DataColumn DataColumn in datatable.Columns)
                        columnsNames.Add(DataColumn.ColumnName);
                    Temp = datatable.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsNames));
                    return Temp;
                }
                catch { return Temp; }
            }

            public static T getObject<T>(DataRow row, List<string> columnsName) where T : new()
            {
                T obj = new T();
                try
                {
                    string columnname = "";
                    string value = "";
                    PropertyInfo[] Properties; Properties = typeof(T).GetProperties();
                    foreach (PropertyInfo objProperty in Properties)
                    {
                        columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower());
                        if (!string.IsNullOrEmpty(columnname))
                        {
                            value = row[columnname].ToString();
                            if (!string.IsNullOrEmpty(value))
                            {
                                if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
                                {
                                    value = row[columnname].ToString().Replace("$", "").Replace(",", "");
                                    objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
                                }
                                else
                                {
                                    value = row[columnname].ToString().Replace("%", "");
                                    objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
                                }
                            }
                        }
                    } return obj;
                }
                catch { return obj; }
            }

            public delegate object[] CreateRowDelegate<T>(T t);



        }

    after you add the above code you will find new property add to datatable as the following

    yourdatatable.ToList<predefinedClass>();

    • Proposed as answer by Mahmoud Soufi Friday, August 30, 2013 6:28 PM
    • Marked as answer by Neno farid Friday, August 30, 2013 6:36 PM
    Friday, August 30, 2013 6:28 PM
  • You can use Linq to query the datatable and return a List<T> right on the fly.

    http://msdn.microsoft.com/en-us/library/bb552415.aspx

    or

    After you do the Linq query on the datatable and get the results, you can use Linq to query the result of the Linq query of the datatable instead of doing a foreach loop and trying to find something.

    • Marked as answer by Neno farid Saturday, August 31, 2013 11:26 PM
    Friday, August 30, 2013 7:03 PM

All replies

  • you can use the following code 

     public static class EnumerableExtension
        {

            /// <summary>
            /// 
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="enumeration"></param>
            /// <param name="action"></param>
            public static void ForEach<T>(this IEnumerable<T> enumeration, Action<T> action)
            {

                foreach (T item in enumeration)
                {
                    action(item);
                }
            }

            /// <summary>
            /// Convert  list to Data Table
            /// </summary>
            /// <typeparam name="T">Target Class</typeparam>
            /// <param name="varlist">list you want to convert it to Data Table</param>
            /// <param name="fn">Delegate Function to Create Row</param>
            /// <returns>Data Table That Represent List data</returns>
            public static DataTable ToADOTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn)
            {
                DataTable toReturn = new DataTable();

                // Could add a check to verify that there is an element 0
                T TopRec = varlist.ElementAtOrDefault(0);

                if (TopRec == null)
                    return toReturn;

                // Use reflection to get property names, to create table
                // column names

                PropertyInfo[] oProps = ((Type)TopRec.GetType()).GetProperties();

                foreach (PropertyInfo pi in oProps)
                {
                    Type pt = pi.PropertyType;
                    if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                        pt = Nullable.GetUnderlyingType(pt);
                    toReturn.Columns.Add(pi.Name, pt);
                }

                foreach (T rec in varlist)
                {
                    DataRow dr = toReturn.NewRow();
                    foreach (PropertyInfo pi in oProps)
                    {
                        object o = pi.GetValue(rec, null);
                        if (o == null)
                            dr[pi.Name] = DBNull.Value;
                        else
                            dr[pi.Name] = o;
                    }
                    toReturn.Rows.Add(dr);
                }

                return toReturn;
            }

            /// <summary>
            /// Convert  list to Data Table
            /// </summary>
            /// <typeparam name="T">Target Class</typeparam>
            /// <param name="varlist">list you want to convert it to Data Table</param>
            /// <returns>Data Table That Represent List data</returns>
            public static DataTable ToADOTable<T>(this IEnumerable<T> varlist)
            {
                DataTable toReturn = new DataTable();

                // Could add a check to verify that there is an element 0
                T TopRec = varlist.ElementAtOrDefault(0);

                if (TopRec == null)
                    return toReturn;

                // Use reflection to get property names, to create table
                // column names

                PropertyInfo[] oProps = ((Type)TopRec.GetType()).GetProperties();

                foreach (PropertyInfo pi in oProps)
                {
                    Type pt = pi.PropertyType;
                    if (pt.IsGenericType && pt.GetGenericTypeDefinition() == typeof(Nullable<>))
                        pt = Nullable.GetUnderlyingType(pt);
                    toReturn.Columns.Add(pi.Name, pt);
                }

                foreach (T rec in varlist)
                {
                    DataRow dr = toReturn.NewRow();
                    foreach (PropertyInfo pi in oProps)
                    {
                        object o = pi.GetValue(rec, null);

                        if (o == null)
                            dr[pi.Name] = DBNull.Value;
                        else
                            dr[pi.Name] = o;
                    }
                    toReturn.Rows.Add(dr);
                }

                return toReturn;
            }

            
            /// <summary>
            /// Convert Data Table To List of Type T
            /// </summary>
            /// <typeparam name="T">Target Class to convert data table to List of T </typeparam>
            /// <param name="datatable">Data Table you want to convert it</param>
            /// <returns>List of Target Class</returns>
            public  static List<T> ToList<T>(this DataTable datatable) where T : new()
            {
                List<T> Temp = new List<T>();
                try
                {
                    List<string> columnsNames = new List<string>();
                    foreach (DataColumn DataColumn in datatable.Columns)
                        columnsNames.Add(DataColumn.ColumnName);
                    Temp = datatable.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsNames));
                    return Temp;
                }
                catch { return Temp; }
            }

            public static T getObject<T>(DataRow row, List<string> columnsName) where T : new()
            {
                T obj = new T();
                try
                {
                    string columnname = "";
                    string value = "";
                    PropertyInfo[] Properties; Properties = typeof(T).GetProperties();
                    foreach (PropertyInfo objProperty in Properties)
                    {
                        columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower());
                        if (!string.IsNullOrEmpty(columnname))
                        {
                            value = row[columnname].ToString();
                            if (!string.IsNullOrEmpty(value))
                            {
                                if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
                                {
                                    value = row[columnname].ToString().Replace("$", "").Replace(",", "");
                                    objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
                                }
                                else
                                {
                                    value = row[columnname].ToString().Replace("%", "");
                                    objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
                                }
                            }
                        }
                    } return obj;
                }
                catch { return obj; }
            }

            public delegate object[] CreateRowDelegate<T>(T t);



        }

    after you add the above code you will find new property add to datatable as the following

    yourdatatable.ToList<predefinedClass>();

    • Proposed as answer by Mahmoud Soufi Friday, August 30, 2013 6:28 PM
    • Marked as answer by Neno farid Friday, August 30, 2013 6:36 PM
    Friday, August 30, 2013 6:28 PM
  • thanks very much dear its working
    Friday, August 30, 2013 6:37 PM
  • You can use Linq to query the datatable and return a List<T> right on the fly.

    http://msdn.microsoft.com/en-us/library/bb552415.aspx

    or

    After you do the Linq query on the datatable and get the results, you can use Linq to query the result of the Linq query of the datatable instead of doing a foreach loop and trying to find something.

    • Marked as answer by Neno farid Saturday, August 31, 2013 11:26 PM
    Friday, August 30, 2013 7:03 PM