Convert to List records gets null

Question

enter image description hereI am trying to convert my List to DataTable but while converting the records count gets 0.

Here is my code:

DataAccessProvider dap = new DataAccessProvider(
    Settings.Default.SQLServerConnection, 
    DatabaseType.MSSql);
var employees = dap.SelectAll("Employees").AsEnumerable().ToList();
DataTable Employees = new DataTable();
Employees = ToDataTable(employees);

public DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties(
        BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i++)
        {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    //put a breakpoint here and check datatable
    return dataTable;
}

public List<dynamic> SelectAll(string tableName)
{
    List<dynamic> result = new List<dynamic>();

    using (IDataReader reader = ExecuteReader("Select * From " + tableName))
    {
        while (reader.Read())
        {
            dynamic expando = new ExpandoObject();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                string columnName = reader.GetName(i);
                ((IDictionary<String, Object>)expando).Add(
                    columnName, 
                    reader[columnName]);
            }
            result.Add(expando);
        }
    }

    return result;
}

All above are my code. When I debugged I found that in ToDataTable function the Props count gets 0. So what is wrong here?

You don't have to implement your own `ToDataTable` method because you can use `ToDataTable` extension method but you need to install `MoreLinq` first. Check this answer to know how https://stackoverflow.com/a/42550827/2946329 — Salah Akbari, Jun 08 '17 at 13:27
@S.Akbari - loks like this question contains its own implementation of a method called `ToDataTable` — Jamiec, Jun 08 '17 at 13:28
@Jamiec Yes and I guess the OP has been unaware of `ToDataTable` extension method already. — Salah Akbari, Jun 08 '17 at 13:30
ExpandoObjects don't have properties like you hope they do. See https://stackoverflow.com/a/32312740/34092 — mjwills, Jun 08 '17 at 13:30
@S.Akbari right, so your comment/answer on "why is my code not working" is "screw it, use different code" — Jamiec, Jun 08 '17 at 13:31
I tried with following thread https://stackoverflow.com/a/42550827/2946329 but the result is same and DataTable has no data. `DataTable employees = dap.SelectAll("Employees").ToDataTable();` — Shweta Gupta, Jun 08 '17 at 13:33
@Jamiec Suggesting that someone use an existing library instead of writing their own code to do something is a valid comment, maybe not a good answer, but it's definitely an acceptable comment. — juharr, Jun 08 '17 at 13:34
I tried your code with `T` being a simple class having an `int` and a `string` property. It works fine for me? Did you test with different types for `T`? What exactly happens? — René Vogt, Jun 08 '17 at 13:40
ah I see. `dynamic` is just an alias for `object`. `object` does not have public instance properties. — René Vogt, Jun 08 '17 at 13:41
@Jamiec That's related to weather a question is good or not, not if a comment is. The whole point of comments is for asking for clarifications or making suggestions that are not valid as answers. — juharr, Jun 08 '17 at 13:44
@RenéVogt See https://stackoverflow.com/questions/5523031/dynamic-keyword-vs-object-data-type for object vs dynamic. — mjwills, Jun 08 '17 at 13:47
You should try using FastMember where this functionality is already implemented. You can see this post: https://stackoverflow.com/questions/564366/convert-generic-list-enumerable-to-datatable — Lukasz Cokot, Jun 08 '17 at 13:52

score 0 · Answer 1 · answered Jun 08 '17 at 13:39

dynamic objects do not have any properties, let alone public instance ones.

This code outputs zero:

dynamic expando = new ExpandoObject();
((IDictionary<String, Object>)expando).Add(
                    "col", 
                   "val");
Console.WriteLine(expando.GetType().GetProperties().Length);

(Live example: http://rextester.com/IOTK71323)

If you want to access the dynamic properties, you need to cast it to the same dictionary you do for writing to it, and read the keys/values:

dynamic expando = new ExpandoObject();
((IDictionary<String, Object>)expando).Add(
                    "col", 
                   "val");

var dict =  ((IDictionary<String, Object>)expando);
foreach(var kv in dict)
    Console.WriteLine("{0} = {1}", kv.Key,kv.Value);

(Live example: http://rextester.com/RMAW17261)

mjwills · Answer 2 · 2017-06-08T13:48:49.447

0

Consider using ((IDictionary<String, object>)expando).Keys and passing them in to a new implementation of ToDataTable that uses those keys ('property names').

This is necessary, vs your current Reflection solution, to look up the property names (since as https://stackoverflow.com/a/32312740/34092 states, that won't work).

Convert dynamic list to datatable c# may be worth a read as well.

edited Jun 08 '17 at 13:48

answered Jun 08 '17 at 13:39

mjwills

23,389
6
40
63

Convert to List records gets null

2 Answers2