In my previous article, I have explained few ways to convert list into datatable using C#, now in this article, I will explain various ways to convert datatable into List using C# in ASP.NET.
Simplest and easiest way in one line would be as below
//consider we have a DataTable object as 'dataTable'
dataTable.Table.AsEnumerable(); Suppose here is the our class Object
public class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
public bool IsActive { get; set; }
} Then we can convert Datatable of above class into List using these methods:
- Using Extension method
- Using Foreach loop(faster than 1)
- Using Linq (Slower than 2)
Let's try each of the above methods
Using Extention method
public static class Helper
{
private static readonly IDictionary<Type, ICollection<PropertyInfo>> _Properties =
new Dictionary<Type, ICollection<PropertyInfo>>();
/// <summary>
/// Converts a DataTable to a list with generic objects
/// </summary>
/// <typeparam name="T">Generic object</typeparam>
/// <param name="table">DataTable</param>
/// <returns>List with generic objects</returns>
public static IEnumerable<T> DataTableToList<T>(this DataTable table) where T : class, new()
{
try
{
var objType = typeof(T);
ICollection<PropertyInfo> properties;
lock (_Properties)
{
if (!_Properties.TryGetValue(objType, out properties))
{
properties = objType.GetProperties().Where(property => property.CanWrite).ToList();
_Properties.Add(objType, properties);
}
}
var list = new List<T>(table.Rows.Count);
foreach (var row in table.AsEnumerable().Skip(1))
{
var obj = new T();
foreach (var prop in properties)
{
try
{
var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
var safeValue = row[prop.Name] == null ? null : Convert.ChangeType(row[prop.Name], propType);
prop.SetValue(obj, safeValue, null);
}
catch
{
continue;
}
}
list.Add(obj);
}
return list;
}
catch
{
return Enumerable.Empty<T>();
}
}
}the above method has reflection, therefore, it is slower than other methods. Reflection comes at a cost of performance because of all those steps to be taken to identify object Class methods and properties, and execute them at runtime, but it will come in handy when you want to abstract a method to accept Type and you don’t have many types, or you have minimal data to set or get per Type.
Using Foreach loop
private static void ConvertUsingForEach(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 = values[0],
CategoryName = values[1],
IsActive = (double)values[2] == 1 ? true : false
};
categoryList.Add(category);
}
}Using LINQ method
public void ConvertToDatatableUsingLinq()
{
DataTable dt = new DataTable("Category");
dt.Columns.Add("Id", typeof(Int32));
dt.Columns.Add("CategoryName", typeof(string));
dt.Columns.Add("IsActive", typeof(bool));
//Data
dt.Rows.Add(1, "Kids Wear", true);
dt.Rows.Add(2, "Mens Wear", true);
dt.Rows.Add(3, "Womens Wear", true);
List<Category> CategoryList = new List<Category>();
CategoryList = (from DataRow dr in dt.Rows
select new Category()
{
Id = Convert.ToInt32(dr["Id"]),
CategoryName = dr["CategoryName"].ToString(),
IsActive = Convert.ToBoolean(dr["IsActive"]),
}).ToList();
} the above method is widely used nowadays to convert datatable into List, but second method of foreach is better performance wise than LINQ, to test this I tried to execute both the (2 & 3)methods by creating a new console project in Visual Studio, here is the code for complete console project (method 1 is not tested by me for performance as it is slower than 2 & 3 because of use of Reflection.)
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp1
{
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; }
}
}
}
As you can see in the above method we have 1000 rows, and the output of the above code of the elapsed time(in ms) as below
Elapsed time for Linq Method=101
Elapsed time for Foreach method=56Here is the image of the output
That's it, it makes all the things more clear, which method to use when.
You may also like to read:
Buy us a coffee
Become a Patron