Currently, I'm using:
DataTable dt = CreateDataTableInSomeWay();
List<DataRow> list = new List<DataRow>();
foreach (DataRow dr in dt.Rows)
{
list.Add(dr);
}
Is there a better/magic way?
How do you convert a DataTable into a generic list?
Viewed 567665
28 Answers
If you're using .NET 3.5, you can use DataTableExtensions.AsEnumerable (an extension method) and then if you really need a List<DataRow> instead of just IEnumerable<DataRow> you can call Enumerable.ToList:
IEnumerable<DataRow> sequence = dt.AsEnumerable();
or
using System.Linq;
...
List<DataRow> list = dt.AsEnumerable().ToList();
With C# 3.0 and System.Data.DataSetExtensions.dll,
List<DataRow> rows = table.Rows.Cast<DataRow>().ToList();
You could use
List<DataRow> list = new List<DataRow>(dt.Select());
dt.Select() will return all rows in your table, as an array of datarows, and the List constructor accepts that array of objects as an argument to initially fill your list with.
A more 'magic' way, and doesn't need .NET 3.5.
If, for example, DBDatatable was returning a single column of Guids (uniqueidentifier in SQL) then you could use:
Dim gList As New List(Of Guid)
gList.AddRange(DirectCast(DBDataTable.Select(), IEnumerable(Of Guid)))
DataTable.Select() doesnt give the Rows in the order they were present in the datatable.
If order is important I feel iterating over the datarow collection and forming a List is the right way to go or you could also use overload of DataTable.Select(string filterexpression, string sort).
But this overload may not handle all the ordering (like order by case ...) that SQL provides.
The Easiest way of Converting the DataTable into the Generic list of class
using Newtonsoft.Json;
var json = JsonConvert.SerializeObject(dataTable);
var model = JsonConvert.DeserializeObject<List<ClassName>>(json);
/* This is a generic method that will convert any type of DataTable to a List
*
*
* Example : List< Student > studentDetails = new List< Student >();
* studentDetails = ConvertDataTable< Student >(dt);
*
* Warning : In this case the DataTable column's name and class property name
* should be the same otherwise this function will not work properly
*/
The following are the two functions in which if we pass a DataTable and a user defined class. It will then return the List of that class with the DataTable data.
public static List<T> ConvertDataTable<T>(DataTable dt)
{
List<T> data = new List<T>();
foreach (DataRow row in dt.Rows)
{
T item = GetItem<T>(row);
data.Add(item);
}
return data;
}
private static T GetItem<T>(DataRow dr)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();
foreach (DataColumn column in dr.Table.Columns)
{
foreach (PropertyInfo pro in temp.GetProperties())
{
//in case you have a enum/GUID datatype in your model
//We will check field's dataType, and convert the value in it.
if (pro.Name == column.ColumnName){
try
{
var convertedValue = GetValueByDataType(pro.PropertyType, dr[column.ColumnName]);
pro.SetValue(obj, convertedValue, null);
}
catch (Exception e)
{
//ex handle code
throw;
}
//pro.SetValue(obj, dr[column.ColumnName], null);
}
else
continue;
}
}
return obj;
}
This method will check the datatype of field, and convert dataTable value in to that datatype.
private static object GetValueByDataType(Type propertyType, object o)
{
if (o.ToString() == "null")
{
return null;
}
if (propertyType == (typeof(Guid)) || propertyType == typeof(Guid?))
{
return Guid.Parse(o.ToString());
}
else if (propertyType == typeof(int) || propertyType.IsEnum)
{
return Convert.ToInt32(o);
}
else if (propertyType == typeof(decimal) )
{
return Convert.ToDecimal(o);
}
else if (propertyType == typeof(long))
{
return Convert.ToInt64(o);
}
else if (propertyType == typeof(bool) || propertyType == typeof(bool?))
{
return Convert.ToBoolean(o);
}
else if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
{
return Convert.ToDateTime(o);
}
return o.ToString();
}
To call the preceding method, use the following syntax:
List< Student > studentDetails = new List< Student >();
studentDetails = ConvertDataTable< Student >(dt);
Change the Student class name and dt value based on your requirements. In this case the DataTable column's name and class property name should be the same otherwise this function will not work properly.
lPerson = dt.AsEnumerable().Select(s => new Person()
{
Name = s.Field<string>("Name"),
SurName = s.Field<string>("SurName"),
Age = s.Field<int>("Age"),
InsertDate = s.Field<DateTime>("InsertDate")
}).ToList();
Link to working DotNetFiddle Example
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.DataSetExtensions;
public static void Main()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("SurName", typeof(string));
dt.Columns.Add("Age", typeof(int));
dt.Columns.Add("InsertDate", typeof(DateTime));
var row1= dt.NewRow();
row1["Name"] = "Adam";
row1["SurName"] = "Adam";
row1["Age"] = 20;
row1["InsertDate"] = new DateTime(2020, 1, 1);
dt.Rows.Add(row1);
var row2 = dt.NewRow();
row2["Name"] = "John";
row2["SurName"] = "Smith";
row2["Age"] = 25;
row2["InsertDate"] = new DateTime(2020, 3, 12);
dt.Rows.Add(row2);
var row3 = dt.NewRow();
row3["Name"] = "Jack";
row3["SurName"] = "Strong";
row3["Age"] = 32;
row3["InsertDate"] = new DateTime(2020, 5, 20);
dt.Rows.Add(row3);
List<Person> lPerson = new List<Person>();
lPerson = dt.AsEnumerable().Select(s => new Person()
{
Name = s.Field<string>("Name"),
SurName = s.Field<string>("SurName"),
Age = s.Field<int>("Age"),
InsertDate = s.Field<DateTime>("InsertDate")
}).ToList();
foreach(Person pers in lPerson)
{
Console.WriteLine("{0} {1} {2} {3}", pers.Name, pers.SurName, pers.Age, pers.InsertDate);
}
}
public class Person
{
public string Name { get; set; }
public string SurName { get; set; }
public int Age { get; set; }
public DateTime InsertDate { get; set; }
}
}
You can use a generic method like that for datatable to generic list
public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
{
try
{
List<T> list = new List<T>();
foreach (var row in table.AsEnumerable())
{
T obj = new T();
foreach (var prop in obj.GetType().GetProperties())
{
try
{
PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
if (propertyInfo.PropertyType.IsEnum)
{
propertyInfo.SetValue(obj, Enum.Parse(propertyInfo.PropertyType, row[prop.Name].ToString()));
}
else
{
propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
}
}
catch
{
continue;
}
}
list.Add(obj);
}
return list;
}
catch
{
return null;
}
}
Converting DataTable to Generic Dictionary
public static Dictionary<object,IList<dynamic>> DataTable2Dictionary(DataTable dt)
{
Dictionary<object, IList<dynamic>> dict = new Dictionary<dynamic, IList<dynamic>>();
foreach(DataColumn column in dt.Columns)
{
IList<dynamic> ts = dt.AsEnumerable()
.Select(r => r.Field<dynamic>(column.ToString()))
.ToList();
dict.Add(column, ts);
}
return dict;
}
Use Extension :
public static class Extensions
{
#region Convert Datatable To List
public static IList<T> ToList<T>(this DataTable table) where T : new()
{
IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
IList<T> result = new List<T>();
foreach (var row in table.Rows)
{
var item = CreateItemFromRow<T>((DataRow)row, properties);
result.Add(item);
}
return result;
}
private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
{
T item = new T();
foreach (var property in properties)
{
property.SetValue(item, row[property.Name], null);
}
return item;
}
#endregion
}
To assign the DataTable rows to the generic List of class
List<Candidate> temp = new List<Candidate>();//List that holds the Candidate Class,
//Note:The Candidate class contains RollNo,Name and Department
//tb is DataTable
temp = (from DataRow dr in tb.Rows
select new Candidate()
{
RollNO = Convert.ToInt32(dr["RollNO"]),
Name = dr["Name"].ToString(),
Department = dr["Department"].ToString(),
}).ToList();
you can use following two Generic functions
private static List<T> ConvertDataTable<T>(DataTable dt)
{
List<T> data = new List<T>();
foreach (DataRow row in dt.Rows)
{
T item = GetItem<T>(row);
data.Add(item);
}
return data;
}
private static T GetItem<T>(DataRow dr)
{
Type temp = typeof(T);
T obj = Activator.CreateInstance<T>();
foreach (DataColumn column in dr.Table.Columns)
{
foreach (PropertyInfo pro in temp.GetProperties())
{
if (pro.Name == column.ColumnName)
pro.SetValue(obj, dr[column.ColumnName].ToString(), null);
else
continue;
}
}
return obj;
}
and use it as following
List<StudentScanExamsDTO> studentDetails = ConvertDataTable<StudentScanExamsDTO>(dt);
If anyone want's to create custom function to convert datatable to list
class Program
{
static void Main(string[] args)
{
DataTable table = GetDataTable();
var sw = new Stopwatch();
sw.Start();
LinqMethod(table);
sw.Stop();
Console.WriteLine("Elapsed time for Linq Method={0}", sw.ElapsedMilliseconds);
sw.Reset();
sw.Start();
ForEachMethod(table);
sw.Stop();
Console.WriteLine("Elapsed time for Foreach method={0}", sw.ElapsedMilliseconds);
Console.ReadKey();
}
private static DataTable GetDataTable()
{
var table = new DataTable();
table.Columns.Add("ID", typeof(double));
table.Columns.Add("CategoryName", typeof(string));
table.Columns.Add("Active", typeof(double));
var rand = new Random();
for (int i = 0; i < 100000; i++)
{
table.Rows.Add(i, "name" + i, rand.Next(0, 2));
}
return table;
}
private static void LinqMethod(DataTable table)
{
var list = table.AsEnumerable()
.Skip(1)
.Select(dr =>
new Category
{
Id = Convert.ToInt32(dr.Field<double>("ID")),
CategoryName = dr.Field<string>("CategoryName"),
IsActive =
dr.Field<double>("Active") == 1 ? true : false
}).ToList();
}
private static void ForEachMethod(DataTable table)
{
var categoryList = new List<Category>(table.Rows.Count);
foreach (DataRow row in table.Rows)
{
var values = row.ItemArray;
var category = new Category()
{
Id = Convert.ToInt32(values[0]),
CategoryName = Convert.ToString(values[1]),
IsActive = (double)values[2] == 1 ? true : false
};
categoryList.Add(category);
}
}
private class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
public bool IsActive { get; set; }
}
}
If we execute above code, Foreach method finishes in 56ms while linq one takes 101ms ( for 1000 records). So Foreach method is better to use. Source:Ways to Convert Datatable to List in C# (with performance test example)
try this using Newtonsoft Json:
var json = JsonConvert.SerializeObject(dataTable);
var YourConvertedDataType = JsonConvert.DeserializeObject<YourDataType>(json);
To get List of values instead of ItemArray, do this:
List<string> s = dt.AsEnumerable().Select(x => x[0].ToString()).ToList();
The above assumes that you want list of string values from column 0.
Related