I have few methods that returns different Generic Lists.
Exists in .net any class static method or whatever to convert any list into a datatable? The only thing that i can imagine is use Reflection to do this.
IF i have this:
List<Whatever> whatever = new List<Whatever>();
(This next code doesn't work of course, but i would like to have the possibility of:
DataTable dt = (DataTable) whatever;
I realize that this has been closed for a while; however, I had a solution to this specific problem but needed a slight twist: the columns and data table needed to be predefined / already instantiated. Then I needed to simply insert the types into the data table.
So here's an example of what I did:
public static class Test
{
public static void Main()
{
var dataTable = new System.Data.DataTable(Guid.NewGuid().ToString());
var columnCode = new DataColumn("Code");
var columnLength = new DataColumn("Length");
var columnProduct = new DataColumn("Product");
dataTable.Columns.AddRange(new DataColumn[]
{
columnCode,
columnLength,
columnProduct
});
var item = new List<SomeClass>();
item.Select(data => new
{
data.Id,
data.Name,
data.SomeValue
}).AddToDataTable(dataTable);
}
}
static class Extensions
{
public static void AddToDataTable<T>(this IEnumerable<T> enumerable, System.Data.DataTable table)
{
if (enumerable.FirstOrDefault() == null)
{
table.Rows.Add(new[] {string.Empty});
return;
}
var properties = enumerable.FirstOrDefault().GetType().GetProperties();
foreach (var item in enumerable)
{
var row = table.NewRow();
foreach (var property in properties)
{
row[property.Name] = item.GetType().InvokeMember(property.Name, BindingFlags.GetProperty, null, item, null);
}
table.Rows.Add(row);
}
}
}
Dim counties As New List(Of County)
Dim dtCounties As DataTable
dtCounties = _combinedRefRepository.Get_Counties()
If dtCounties.Rows.Count <> 0 Then
For Each row As DataRow In dtCounties.Rows
Dim county As New County
county.CountyId = row.Item(0).ToString()
county.CountyName = row.Item(1).ToString().ToUpper()
counties.Add(county)
Next
dtCounties.Dispose()
End If
A 2019 answer if you're using .NET Core - use the Nuget ToDataTable library. Advantages:
Disclaimer - I'm the author of ToDataTable
Performance - I span up some Benchmark .Net tests and included them in the ToDataTable repo. The results were as follows:
Creating a 100,000 Row Datatable:
MacOS Windows
Reflection 818.5 ms 818.3 ms
FastMember from 1105.5 ms 976.4 ms
Mark's answer
Improved FastMember 524.6 ms 456.4 ms
ToDataTable 449.0 ms 376.5 ms
The FastMember method suggested in Marc's answer seemed to perform worse than Mary's answer which used reflection, but I rolled another method using a FastMember TypeAccessor and it performed much better. Nevertheless the ToDataTable package outperformed the lot.
Se probó el método para que acepte campos con null.
// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ToDataTable<T>(IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
Type Propiedad = null;
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
Propiedad = prop.PropertyType;
if (Propiedad.IsGenericType && Propiedad.GetGenericTypeDefinition() == typeof(Nullable<>))
{
Propiedad = Nullable.GetUnderlyingType(Propiedad);
}
table.Columns.Add(prop.Name, Propiedad);
}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
private DataTable CreateDataTable(IList<T> item)
{
Type type = typeof(T);
var properties = type.GetProperties();
DataTable dataTable = new DataTable();
foreach (PropertyInfo info in properties)
{
dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
}
foreach (T entity in item)
{
object[] values = new object[properties.Length];
for (int i = 0; i < properties.Length; i++)
{
values[i] = properties[i].GetValue(entity);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
I also had to come up with an alternate solution, as none of the options listed here worked in my case. I was using an IEnumerable which returned an IEnumerable and the properties couldn't be enumerated. This did the trick:
// remove "this" if not on C# 3.0 / .NET 3.5
public static DataTable ConvertToDataTable<T>(this IEnumerable<T> data)
{
List<IDataRecord> list = data.Cast<IDataRecord>().ToList();
PropertyDescriptorCollection props = null;
DataTable table = new DataTable();
if (list != null && list.Count > 0)
{
props = TypeDescriptor.GetProperties(list[0]);
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
}
if (props != null)
{
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(values);
}
}
return table;
}
This is the simple Console Application to convert List to Datatable.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.ComponentModel;
namespace ConvertListToDataTable
{
public static class Program
{
public static void Main(string[] args)
{
List<MyObject> list = new List<MyObject>();
for (int i = 0; i < 5; i++)
{
list.Add(new MyObject { Sno = i, Name = i.ToString() + "-KarthiK", Dat = DateTime.Now.AddSeconds(i) });
}
DataTable dt = ConvertListToDataTable(list);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine();
for (int x = 0; x < dt.Columns.Count; x++)
{
Console.Write(row[x].ToString() + " ");
}
}
Console.ReadLine();
}
public class MyObject
{
public int Sno { get; set; }
public string Name { get; set; }
public DateTime Dat { get; set; }
}
public static DataTable ConvertListToDataTable<T>(this List<T> iList)
{
DataTable dataTable = new DataTable();
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor propertyDescriptor = props[i];
Type type = propertyDescriptor.PropertyType;
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
type = Nullable.GetUnderlyingType(type);
dataTable.Columns.Add(propertyDescriptor.Name, type);
}
object[] values = new object[props.Count];
foreach (T iListItem in iList)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(iListItem);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
}
}
This link on MSDN is worth a visit: How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow
This adds an extension method that lets you do this:
// Create a sequence.
Item[] items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
// Query for items with price greater than 9.99.
var query = from i in items
where i.Price > 9.99
orderby i.Price
select i;
// Load the query results into new DataTable.
DataTable table = query.CopyToDataTable();
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.ComponentModel;
public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt = lstEmployee.ConvertToDataTable();
}
public static DataTable ConvertToDataTable<T>(IList<T> list) where T : class
{
try
{
DataTable table = CreateDataTable<T>();
Type objType = typeof(T);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(objType);
foreach (T item in list)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor property in properties)
{
if (!CanUseType(property.PropertyType)) continue;
row[property.Name] = property.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
catch (DataException ex)
{
return null;
}
catch (Exception ex)
{
return null;
}
}
private static DataTable CreateDataTable<T>() where T : class
{
Type objType = typeof(T);
DataTable table = new DataTable(objType.Name);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(objType);
foreach (PropertyDescriptor property in properties)
{
Type propertyType = property.PropertyType;
if (!CanUseType(propertyType)) continue;
//nullables must use underlying types
if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
propertyType = Nullable.GetUnderlyingType(propertyType);
//enums also need special treatment
if (propertyType.IsEnum)
propertyType = Enum.GetUnderlyingType(propertyType);
table.Columns.Add(property.Name, propertyType);
}
return table;
}
private static bool CanUseType(Type propertyType)
{
//only strings and value types
if (propertyType.IsArray) return false;
if (!propertyType.IsValueType && propertyType != typeof(string)) return false;
return true;
}
}
I had to modify Marc Gravell's sample code to handle nullable types and null values. I have included a working version below. Thanks Marc.
public static DataTable ToDataTable<T>(this IList<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
table.Rows.Add(row);
}
return table;
}
try this
public static DataTable ListToDataTable<T>(IList<T> lst)
{
currentDT = CreateTable<T>();
Type entType = typeof(T);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entType);
foreach (T item in lst)
{
DataRow row = currentDT.NewRow();
foreach (PropertyDescriptor prop in properties)
{
if (prop.PropertyType == typeof(Nullable<decimal>) || prop.PropertyType == typeof(Nullable<int>) || prop.PropertyType == typeof(Nullable<Int64>))
{
if (prop.GetValue(item) == null)
row[prop.Name] = 0;
else
row[prop.Name] = prop.GetValue(item);
}
else
row[prop.Name] = prop.GetValue(item);
}
currentDT.Rows.Add(row);
}
return currentDT;
}
public static DataTable CreateTable<T>()
{
Type entType = typeof(T);
DataTable tbl = new DataTable(DTName);
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entType);
foreach (PropertyDescriptor prop in properties)
{
if (prop.PropertyType == typeof(Nullable<decimal>))
tbl.Columns.Add(prop.Name, typeof(decimal));
else if (prop.PropertyType == typeof(Nullable<int>))
tbl.Columns.Add(prop.Name, typeof(int));
else if (prop.PropertyType == typeof(Nullable<Int64>))
tbl.Columns.Add(prop.Name, typeof(Int64));
else
tbl.Columns.Add(prop.Name, prop.PropertyType);
}
return tbl;
}
Marc Gravell's answer but in VB.NET
Public Shared Function ToDataTable(Of T)(data As IList(Of T)) As DataTable
Dim props As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
Dim table As New DataTable()
For i As Integer = 0 To props.Count - 1
Dim prop As PropertyDescriptor = props(i)
table.Columns.Add(prop.Name, prop.PropertyType)
Next
Dim values As Object() = New Object(props.Count - 1) {}
For Each item As T In data
For i As Integer = 0 To values.Length - 1
values(i) = props(i).GetValue(item)
Next
table.Rows.Add(values)
Next
Return table
End Function
List<YourModel> data = new List<YourModel>();
DataTable dataTable = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(Newtonsoft.Json.JsonConvert.SerializeObject(data));
if you have properties in your class this line of code is OK !!
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
but if you have all public fields then use this:
public static DataTable ToDataTable<T>( IList<T> data)
{
FieldInfo[] myFieldInfo;
Type myType = typeof(T);
// Get the type and fields of FieldInfoClass.
myFieldInfo = myType.GetFields(BindingFlags.NonPublic | BindingFlags.Instance
| BindingFlags.Public);
DataTable dt = new DataTable();
for (int i = 0; i < myFieldInfo.Length; i++)
{
FieldInfo property = myFieldInfo[i];
dt.Columns.Add(property.Name, property.FieldType);
}
object[] values = new object[myFieldInfo.Length];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = myFieldInfo[i].GetValue(item);
}
dt.Rows.Add(values);
}
return dt;
}
the original answer is from above , I just edited to use fields instead of properties
and to use it do this
DataTable dt = new DataTable();
dt = ToDataTable(myBriefs);
gridData.DataSource = dt;
gridData.DataBind();
If you are using VB.NET then this class does the job.
Imports System.Reflection
''' <summary>
''' Convert any List(Of T) to a DataTable with correct column types and converts Nullable Type values to DBNull
''' </summary>
Public Class ConvertListToDataset
Public Function ListToDataset(Of T)(ByVal list As IList(Of T)) As DataTable
Dim dt As New DataTable()
'/* Create the DataTable columns */
For Each pi As PropertyInfo In GetType(T).GetProperties()
If pi.PropertyType.IsValueType Then
Debug.Print(pi.Name)
End If
If IsNothing(Nullable.GetUnderlyingType(pi.PropertyType)) Then
dt.Columns.Add(pi.Name, pi.PropertyType)
Else
dt.Columns.Add(pi.Name, Nullable.GetUnderlyingType(pi.PropertyType))
End If
Next
'/* Populate the DataTable with the values in the Items in List */
For Each item As T In list
Dim dr As DataRow = dt.NewRow()
For Each pi As PropertyInfo In GetType(T).GetProperties()
dr(pi.Name) = IIf(IsNothing(pi.GetValue(item)), DBNull.Value, pi.GetValue(item))
Next
dt.Rows.Add(dr)
Next
Return dt
End Function
End Class
I've written a small library myself to accomplish this task. It uses reflection only for the first time an object type is to be translated to a datatable. It emits a method that will do all the work translating an object type.
Its blazing fast. You can find it here: ModelShredder on GoogleCode
Another approach is the above:
List<WhateEver> lst = getdata();
string json = Newtonsoft.Json.JsonConvert.SerializeObject(lst);
DataTable pDt = JsonConvert.DeserializeObject<DataTable>(json);
To Convert Generic list into DataTable
using Newtonsoft.Json;
public DataTable GenericToDataTable(IList<T> list)
{
var json = JsonConvert.SerializeObject(list);
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
return dt;
}
It's also possible through XmlSerialization.
The idea is - serialize to `XML` and then `readXml` method of `DataSet`.
I use this code (from an answer in SO, forgot where)
public static string SerializeXml<T>(T value) where T : class
{
if (value == null)
{
return null;
}
XmlSerializer serializer = new XmlSerializer(typeof(T));
XmlWriterSettings settings = new XmlWriterSettings();
settings.Encoding = new UnicodeEncoding(false, false);
settings.Indent = false;
settings.OmitXmlDeclaration = false;
// no BOM in a .NET string
using (StringWriter textWriter = new StringWriter())
{
using (XmlWriter xmlWriter = XmlWriter.Create(textWriter, settings))
{
serializer.Serialize(xmlWriter, value);
}
return textWriter.ToString();
}
}
so then it's as simple as:
string xmlString = Utility.SerializeXml(trans.InnerList);
DataSet ds = new DataSet("New_DataSet");
using (XmlReader reader = XmlReader.Create(new StringReader(xmlString)))
{
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
ds.ReadXml(reader);
}
Not sure how it stands against all the other answers to this post, but it's also a possibility.
I think it's more convenient and easy to use.
List<Whatever> _lobj= new List<Whatever>();
var json = JsonConvert.SerializeObject(_lobj);
DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));
This is a simple mix of the solutions. It work with Nullable types.
public static DataTable ToDataTable<T>(this IList<T> list)
{
PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
object[] values = new object[props.Count];
foreach (T item in list)
{
for (int i = 0; i < values.Length; i++)
values[i] = props[i].GetValue(item) ?? DBNull.Value;
table.Rows.Add(values);
}
return table;
}
To convert a generic list to data table, you could use the DataTableGenerator
This library lets you convert your list into a data table with multi-feature like
A small change to Marc's answer to make it work with value types like List<string> to data table:
public static DataTable ListToDataTable<T>(IList<T> data)
{
DataTable table = new DataTable();
//special handling for value types and string
if (typeof(T).IsValueType || typeof(T).Equals(typeof(string)))
{
DataColumn dc = new DataColumn("Value", typeof(T));
table.Columns.Add(dc);
foreach (T item in data)
{
DataRow dr = table.NewRow();
dr[0] = item;
table.Rows.Add(dr);
}
}
else
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
foreach (PropertyDescriptor prop in properties)
{
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
try
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
catch (Exception ex)
{
row[prop.Name] = DBNull.Value;
}
}
table.Rows.Add(row);
}
}
return table;
}
Simplest answer could be this one :
If you want to use reflection and set columns order/ include only some columns/ Exclude some columns try this:
private static DataTable ConvertToDataTable<T>(IList<T> data, string[] fieldsToInclude = null,
string[] fieldsToExclude = null)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
{
if ((fieldsToInclude != null && !fieldsToInclude.Contains(prop.Name)) ||
(fieldsToExclude != null && fieldsToExclude.Contains(prop.Name)))
continue;
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (T item in data)
{
var atLeastOnePropertyExists = false;
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
if ((fieldsToInclude != null && !fieldsToInclude.Contains(prop.Name)) ||
(fieldsToExclude != null && fieldsToExclude.Contains(prop.Name)))
continue;
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
atLeastOnePropertyExists = true;
}
if(atLeastOnePropertyExists) table.Rows.Add(row);
}
if (fieldsToInclude != null)
SetColumnsOrder(table, fieldsToInclude);
return table;
}
private static void SetColumnsOrder(DataTable table, params String[] columnNames)
{
int columnIndex = 0;
foreach (var columnName in columnNames)
{
table.Columns[columnName].SetOrdinal(columnIndex);
columnIndex++;
}
}
public DataTable ConvertToDataTable<T>(IList<T> data)
{
PropertyDescriptorCollection properties =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
foreach (PropertyDescriptor prop in properties)
table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
foreach (T item in data)
{
DataRow row = table.NewRow();
foreach (PropertyDescriptor prop in properties)
{
row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
}
table.Rows.Add(row);
}
return table;
}
Source: Stackoverflow.com