1

I have a dynamic list in the below format and wanted to convert into a datatable.

The reason behind dynamic is the columns will keep varying based in this input.

Input: 

{{
  "EMP_ID": "12345",
  "Client": "abcdef",
  "Business_Type": "Temping",
  "EMP NO": "098765",
  "Associate Id": "mki0987"
}}

The above list is generated from a json string

{[  {    
 "EMP_ID": "12345",   
 "Client": "abcdef",   
 "Business_Type": "Temping",   
 "EMP NO": "098765",   
 "Associate Id": "mki0987"
 }]}

Expected Output: 

EMP_ID  | Client  |  Business_Type  |  EMP NO  |  Associate ID

--------|---------|-----------------|----------|-------------------

12345   | abcdef  |   Temping       | 098765   |   mki0987

--------|---------|-----------------|----------|-------------------

Any ideas to achieve this.

I tried doing this using reflection as mentioned below 

public static DataTable ToDataTable<T>(List<T> items) {
    DataTable dataTable = new DataTable(typeof(T).Name);

    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties();
    foreach (PropertyInfo prop in Props) {
        //Defining type of data column gives proper data table
        //var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
        //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;
}

But in this particular code Get properties is not returning me anything as column names

gehbiszumeis
  • 3,525
  • 4
  • 24
  • 41
Suman Babu
  • 13
  • 3
  • 1
    What about using Newtonsoft.Json? You just have to deserialize your JSON string into a datatable – mylee Feb 19 '19 at 06:44
  • Possible duplicate of [Convert deserialized json class to datatable](https://stackoverflow.com/questions/27808470/convert-deserialized-json-class-to-datatable) – xdtTransform Feb 19 '19 at 07:27
  • And https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/implement-copytodatatable-where-type-not-a-datarow – xdtTransform Feb 19 '19 at 07:28

1 Answers1

1
// GET Dynamic list
List<dynamic> resultado = ExecutaQuery(sql);

// Converted Dynamic list to Json 
string json = JsonConvert.SerializeObject(resultado);

// Converted json to Datatable 
DataTable dataTable = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
Antonio Guimaraes
  • 11
  • 1