Saturday, 22 October 2016

Different ways to convert DataTable to List in C# (C-Sharp)

In this article, I will explain you different ways to convert Datatable to List in C#. I have complied 3 ways to convert DataTable to List below.

  1. Using LINQ
  2. Using ForLoop
  3. Using Extension Method

I'm assuming that you have a Employee class like below and a DataTable.

public class Employee
{
    public int EmpId { get; set; }
    public string EmpName { get; set; }
    public string EmpCountry { get; set; }
}
DataTable dt = new DataTable();
dt.Columns.Add("EmpId", typeof(Int32));
dt.Columns.Add("EmpName", typeof(string));
dt.Columns.Add("EmpCountry", typeof(string));
dt.Rows.Add(1, "Rahul", "India");
dt.Rows.Add(2, "John", "USA");
dt.Rows.Add(3, "Mary", "UK");
dt.Rows.Add(4, "Mathew", "Australia");

Using LINQ

Language Integrated Query(LINQ) is the advanced technique to access in-memory objects, databases, XML documents etc.

public void UsingLINQ()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("EmpId", typeof(Int32));
    dt.Columns.Add("EmpName", typeof(string));
    dt.Columns.Add("EmpCountry", typeof(string));
    dt.Rows.Add(1, "Rahul", "India");
    dt.Rows.Add(2, "John", "USA");
    dt.Rows.Add(3, "Mary", "UK");
    dt.Rows.Add(4, "Mathew", "Australia");

    //Method 1
    List<Employee> employeeList = new List<Employee>();
    employeeList = (from DataRow dr in dt.Rows
                    select new Employee()
                        {
                            EmpId = Convert.ToInt32(dr["EmpId"]),
                            EmpName = dr["EmpName"].ToString(),
                            EmpCountry = dr["EmpCountry"].ToString()
                        }).ToList();

    //Method 2
    List<Employee> employeeList2 = dt.AsEnumerable().Select(row => new Employee
    {
        EmpId = row.Field<Int32?>(0).GetValueOrDefault(),
        EmpName = row.Field<string>(1),
        EmpCountry = row.Field<string>(2),
    }).ToList();

    //Method 3
    var empEnumerable = dt.AsEnumerable();
    List<Employee> employeeList3 = (from item in empEnumerable
                                    select new Employee
                                        {
                                            EmpId = item.Field<Int32>("EmpId"),
                                            EmpName = item.Field<string>("EmpName"),
                                            EmpCountry = item.Field<string>("EmpCountry")
                                        }).ToList();
}

Using ForLoop

Using for Loop and foreach loop.

public void UsingForLoop()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("EmpId", typeof(Int32));
    dt.Columns.Add("EmpName", typeof(string));
    dt.Columns.Add("EmpCountry", typeof(string));
    dt.Rows.Add(1, "Rahul", "India");
    dt.Rows.Add(2, "John", "USA");
    dt.Rows.Add(3, "Mary", "UK");
    dt.Rows.Add(4, "Mathew", "Australia");

    //Method 1
    List<Employee> employeeList = new List<Employee>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        Employee employee = new Employee();
        employee.EmpId = Convert.ToInt32(dt.Rows[i]["EmpId"]);
        employee.EmpName = dt.Rows[i]["EmpName"].ToString();
        employee.EmpCountry = dt.Rows[i]["EmpCountry"].ToString();
        employeeList.Add(employee);
    }

    //Method 2
    List<Employee> employeeList2 = new List<Employee>();
    foreach (DataRow dr in dt.Rows)
    {
        employeeList2.Add(new Employee
        {
            EmpId = Convert.ToInt32(dr["EmpId"]),
            EmpName = Convert.ToString(dr["EmpName"]),
            EmpCountry = Convert.ToString(dr["EmpCountry"])
        });
    }
}

Using Extension Method

Using extension method, it will convert DataTable to List. Add below namespace.

using System.Reflection;
public static class clsExtension
{
    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);
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }
                    catch
                    {
                        continue;
                    }
                }
                list.Add(obj);
            }
            return list;
        }
        catch
        {
            return null;
        }
    }
}

Below is code to call extension method.

public void UsingExtensionMethod()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("EmpId", typeof(Int32));
    dt.Columns.Add("EmpName", typeof(string));
    dt.Columns.Add("EmpCountry", typeof(string));
    dt.Rows.Add(1, "Rahul", "India");
    dt.Rows.Add(2, "John", "USA");
    dt.Rows.Add(3, "Mary", "UK");
    dt.Rows.Add(4, "Mathew", "Australia");

    // calling extension method 
    List<Employee> employeeList = dt.DataTableToList<Employee>();
    
    // calling extension method 
    List<Employee> employeeList2 = new List<Employee>();
    employeeList2 = clsExtension.DataTableToList<Employee>(dt);
}
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON