locked
How to convert LINQ ToList() function to datatable c# RRS feed

  • Question

  • User264732274 posted

    this is how my xml look like

    <?xml version="1.0" encoding="utf-8"?>
    <Root>
      <orders>
        <OrderID>10248</OrderID>
        <CustomerID>VINET</CustomerID>
        <EmployeeID>5</EmployeeID>
        <OrderDate>1996-07-04T00:00:00</OrderDate>
        <RequiredDate>1996-08-01T00:00:00</RequiredDate>
        <ShippedDate>1996-07-16T00:00:00</ShippedDate>
        <ShipVia>3</ShipVia>
        <Freight>32.3800</Freight>
        <ShipName>Vins et alcools Chevalier</ShipName>
        <ShipAddress>59 rue de l'Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
      </orders>
    </Root>

    i parse the above xml using LINQ this way

    document.Descendants("orders").Select(c => c).ToList()

    i want to list xml data in tabular format just like sql query return data. i search and got a link

    http://stackoverflow.com/questions/18608959/convert-linq-query-results-to-datatable-c-sharp

    the link suggested to use

    CopyToDataTable() extension

    method which i followed but still i am not getting result in tabular format.

    so guide me how could i convert my

    ToList() to Datatable

    as a result output would look like

    OrderID      CustomerID      EmployeeID
    -------      ----------      -----------
    10248        VINET           5
    10249        AJAY            11
    11027        Smith           09

    here is my full code for CopyToDataTable which i got from MSDN

    public static class DataSetLinqOperators
    {
        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source)
        {
            return new ObjectShredder<T>().Shred(source, null, null);
        }
    
        public static DataTable CopyToDataTable<T>(this IEnumerable<T> source,
                                                    DataTable table, LoadOption? options)
        {
            return new ObjectShredder<T>().Shred(source, table, options);
        }
    
    }
    
    public class ObjectShredder<T>
    {
        private FieldInfo[] _fi;
        private PropertyInfo[] _pi;
        private Dictionary<string, int> _ordinalMap;
        private Type _type;
    
        public ObjectShredder()
        {
            _type = typeof(T);
            _fi = _type.GetFields();
            _pi = _type.GetProperties();
            _ordinalMap = new Dictionary<string, int>();
        }
    
        public DataTable Shred(IEnumerable<T> source, DataTable table, LoadOption? options)
        {
            if (typeof(T).IsPrimitive)
            {
                return ShredPrimitive(source, table, options);
            }
    
    
            if (table == null)
            {
                table = new DataTable(typeof(T).Name);
            }
    
            // now see if need to extend datatable base on the type T + build ordinal map
            table = ExtendTable(table, typeof(T));
    
            table.BeginLoadData();
            using (IEnumerator<T> e = source.GetEnumerator())
            {
                while (e.MoveNext())
                {
                    if (options != null)
                    {
                        table.LoadDataRow(ShredObject(table, e.Current), (LoadOption)options);
                    }
                    else
                    {
                        table.LoadDataRow(ShredObject(table, e.Current), true);
                    }
                }
            }
            table.EndLoadData();
            return table;
        }
    
        public DataTable ShredPrimitive(IEnumerable<T> source, DataTable table, LoadOption? options)
        {
            if (table == null)
            {
                table = new DataTable(typeof(T).Name);
            }
    
            if (!table.Columns.Contains("Value"))
            {
                table.Columns.Add("Value", typeof(T));
            }
    
            table.BeginLoadData();
            using (IEnumerator<T> e = source.GetEnumerator())
            {
                Object[] values = new object[table.Columns.Count];
                while (e.MoveNext())
                {
                    values[table.Columns["Value"].Ordinal] = e.Current;
    
                    if (options != null)
                    {
                        table.LoadDataRow(values, (LoadOption)options);
                    }
                    else
                    {
                        table.LoadDataRow(values, true);
                    }
                }
            }
            table.EndLoadData();
            return table;
        }
    
        public DataTable ExtendTable(DataTable table, Type type)
        {
            // value is type derived from T, may need to extend table.
            foreach (FieldInfo f in type.GetFields())
            {
                if (!_ordinalMap.ContainsKey(f.Name))
                {
                    DataColumn dc = table.Columns.Contains(f.Name) ? table.Columns[f.Name]
                        : table.Columns.Add(f.Name, f.FieldType);
                    _ordinalMap.Add(f.Name, dc.Ordinal);
                }
            }
            foreach (PropertyInfo p in type.GetProperties())
            {
                if (!_ordinalMap.ContainsKey(p.Name))
                {
                    DataColumn dc = table.Columns.Contains(p.Name) ? table.Columns[p.Name]
                        : table.Columns.Add(p.Name, p.PropertyType);
                    _ordinalMap.Add(p.Name, dc.Ordinal);
                }
            }
            return table;
        }
    
        public object[] ShredObject(DataTable table, T instance)
        {
    
            FieldInfo[] fi = _fi;
            PropertyInfo[] pi = _pi;
    
            if (instance.GetType() != typeof(T))
            {
                ExtendTable(table, instance.GetType());
                fi = instance.GetType().GetFields();
                pi = instance.GetType().GetProperties();
            }
    
            Object[] values = new object[table.Columns.Count];
            foreach (FieldInfo f in fi)
            {
                values[_ordinalMap[f.Name]] = f.GetValue(instance);
            }
    
            foreach (PropertyInfo p in pi)
            {
                values[_ordinalMap[p.Name]] = p.GetValue(instance, null);
            }
            return values;
        }
    }

    and then call this way

    DataTable dtt =document.Descendants("orders").Select(c => c).ToList().CopyToDataTable();

    but it did not worked the way i want the output.

    Monday, July 20, 2015 11:12 AM

Answers

  • User-657100335 posted

    Try this:

    //Create data table
    DataTable dt = new DataTable();
    dt.Columns.Add("OrderID", typeof(Int32));
    dt.Columns.Add("CustomerID", typeof(string));
    dt.Columns.Add("EmployeeID", typeof(Int32));
    
    //For each item in your list, add those items
    //in your data table
    foreach (var item in yourList) 
    {
       dt.Rows.Add(item.OrderID, item.CustomerID, item.EmployeeID);
    }
    
    //Sample of output from your data table
    for (int i = 0; i < dt.Rows.Count; i++)
    {
       MessageBox.Show(dt.Rows[i]["OrderID"].ToString() + " " + dt.Rows[i]["CustomerID"].ToString() + " " +
                       dt.Rows[i]["EmployeeID"].ToString());
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 20, 2015 11:07 PM

All replies

  • User-657100335 posted

    Try this:

    //Create data table
    DataTable dt = new DataTable();
    dt.Columns.Add("OrderID", typeof(Int32));
    dt.Columns.Add("CustomerID", typeof(string));
    dt.Columns.Add("EmployeeID", typeof(Int32));
    
    //For each item in your list, add those items
    //in your data table
    foreach (var item in yourList) 
    {
       dt.Rows.Add(item.OrderID, item.CustomerID, item.EmployeeID);
    }
    
    //Sample of output from your data table
    for (int i = 0; i < dt.Rows.Count; i++)
    {
       MessageBox.Show(dt.Rows[i]["OrderID"].ToString() + " " + dt.Rows[i]["CustomerID"].ToString() + " " +
                       dt.Rows[i]["EmployeeID"].ToString());
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 20, 2015 11:07 PM
  • User1724605321 posted

    Hi sudip_inn ,

    sudip_inn

    i want to list xml data in tabular format just like sql query return data. i search and got a link

    You could also use "ReadXml()" function with dataset to achieve your requirement ,code below is for your reference:

                DataSet ds = new DataSet();
                ds.ReadXml(@"d:\title.xml");
                DataTable dt = ds.Tables[0];

    For more information ,please refer to link below:

    https://support.microsoft.com/en-us/kb/311566 .

    Best Regards,

    Nan Yu

    Tuesday, July 21, 2015 1:54 AM