You can create an extension for DataTables like the following.:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Dynamic;
using System.Xml.Serialization;
using System.Reflection;
using System.ComponentModel.DataAnnotations.Schema;
public static class DataTableExtensions
{
private static Dictionary<Type, Dictionary<string, PropertyInfo>> types;
static DataTableExtensions()
{
types = new Dictionary<Type, Dictionary<string, PropertyInfo>>();
}
public static List<T> ToList<T>(this DataTable dataTable) where T : class
{
if (!types.ContainsKey(typeof(T)))
{
types.Add(typeof(T), typeof(T).GetProperties().ToDictionary(x =>
{
var column = x.GetCustomAttribute(typeof(ColumnAttribute)) as ColumnAttribute;
return column == null ? x.Name : column.Name;
}, x => x));
}
var list = new List<T>();
var columns = dataTable.Columns.Cast<DataColumn>();
var props =
from prop in types[typeof(T)]
join column in columns on prop.Key equals column.ColumnName
select new {
PropertyInfo = prop.Value,
Column = column
};
foreach (var row in dataTable.Rows.Cast<DataRow>())
{
var item = Activator.CreateInstance<T>() as T;
foreach(var prop in props)
{
var value = row[prop.Column.ColumnName];
if (value != DBNull.Value)
prop.PropertyInfo.SetValue(item, value);
}
list.Add(item);
}
return list;
}
public static List<dynamic> ToList(this DataTable dataTable)
{
var list = new List<dynamic>();
var columns = dataTable.Columns.Cast<DataColumn>();
foreach (var row in dataTable.Rows.Cast<DataRow>())
{
var item = new ExpandoObject() as IDictionary<string, object>;
foreach(var column in columns)
{
var value = row[column.ColumnName];
item.Add(column.ColumnName, value != DBNull.Value ? value : null);
}
list.Add(item);
}
return list;
}
}
Here is an example of Usage:
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Xml.Serialization;
using System.ComponentModel.DataAnnotations.Schema;
using Newtonsoft.Json;
public class Entity
{
public Guid EntityID { get; set; }
public String Titulo { get; set; }
public DateTime DataCriacao { get; set; }
[Column("Detalhes")]
public String Descricao { get; set; }
}
public class Program
{
public static void Main()
{
var dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn() { DataType = typeof(Guid), ColumnName = "EntityID" });
dataTable.Columns.Add(new DataColumn() { DataType = typeof(String), ColumnName = "Titulo" });
dataTable.Columns.Add(new DataColumn() { DataType = typeof(DateTime), ColumnName = "DataCriacao" });
dataTable.Columns.Add(new DataColumn() { DataType = typeof(String), ColumnName = "Detalhes" });
for (int i = 0; i < 10; i++) {
var row = dataTable.NewRow();
row["EntityID"] = Guid.NewGuid();
row["Titulo"] = "Titulo " + i;
row["DataCriacao"] = DateTime.Now;
row["Detalhes"] = "Descricao " + i;
dataTable.Rows.Add(row);
}
var listA = dataTable.ToList<Entity>();
Console.WriteLine(JsonConvert.SerializeObject(listA, Formatting.Indented));
var listB = dataTable.ToList();
Console.WriteLine(JsonConvert.SerializeObject(listB, Formatting.Indented));
}
}
Follow the output.:
var listA = [
{
"EntityID": "1bbd4f4a-1614-4af9-a3f8-0798674502eb",
"Titulo": "Titulo 0",
"DataCriacao": "2017-11-07T14:22:51.9708648",
"Descricao": "Descricao 0"
},
{
"EntityID": "9a2d301e-f5e7-4a70-a5bf-200010a7f665",
"Titulo": "Titulo 1",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 1"
},
{
"EntityID": "4ac9b4cc-57c2-4c46-b124-4c36638858ef",
"Titulo": "Titulo 2",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 2"
},
{
"EntityID": "e4609c4c-cbe0-416f-bdcd-f03d17c69046",
"Titulo": "Titulo 3",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 3"
},
{
"EntityID": "9d8ebc9b-b153-40a2-b9e6-0bbb1e6f08c5",
"Titulo": "Titulo 4",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 4"
},
{
"EntityID": "ec25f555-3d5e-4355-9d9a-5ab453dde269",
"Titulo": "Titulo 5",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 5"
},
{
"EntityID": "dea54049-45c5-45ab-8ab4-ef099561aa37",
"Titulo": "Titulo 6",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 6"
},
{
"EntityID": "58df83fb-e341-479e-b5d5-d7d4b0e14f98",
"Titulo": "Titulo 7",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 7"
},
{
"EntityID": "97db14e4-fa79-412c-a2ae-913e0ce6824f",
"Titulo": "Titulo 8",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 8"
},
{
"EntityID": "38e49b63-d3d9-4add-bab2-959c24b3599c",
"Titulo": "Titulo 9",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Descricao": "Descricao 9"
}
];
var listB = [
{
"EntityID": "1bbd4f4a-1614-4af9-a3f8-0798674502eb",
"Titulo": "Titulo 0",
"DataCriacao": "2017-11-07T14:22:51.9708648",
"Detalhes": "Descricao 0"
},
{
"EntityID": "9a2d301e-f5e7-4a70-a5bf-200010a7f665",
"Titulo": "Titulo 1",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 1"
},
{
"EntityID": "4ac9b4cc-57c2-4c46-b124-4c36638858ef",
"Titulo": "Titulo 2",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 2"
},
{
"EntityID": "e4609c4c-cbe0-416f-bdcd-f03d17c69046",
"Titulo": "Titulo 3",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 3"
},
{
"EntityID": "9d8ebc9b-b153-40a2-b9e6-0bbb1e6f08c5",
"Titulo": "Titulo 4",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 4"
},
{
"EntityID": "ec25f555-3d5e-4355-9d9a-5ab453dde269",
"Titulo": "Titulo 5",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 5"
},
{
"EntityID": "dea54049-45c5-45ab-8ab4-ef099561aa37",
"Titulo": "Titulo 6",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 6"
},
{
"EntityID": "58df83fb-e341-479e-b5d5-d7d4b0e14f98",
"Titulo": "Titulo 7",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 7"
},
{
"EntityID": "97db14e4-fa79-412c-a2ae-913e0ce6824f",
"Titulo": "Titulo 8",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 8"
},
{
"EntityID": "38e49b63-d3d9-4add-bab2-959c24b3599c",
"Titulo": "Titulo 9",
"DataCriacao": "2017-11-07T14:22:52.0333271",
"Detalhes": "Descricao 9"
}
];
Note that because DataTable
has a column with a different name of the property in the class, I needed to decorate the class with ColumnAttribute
.