ConvertDataTableToList

Convert DataTable to List C#

Convert DataTable to List in C#

We have already explained Is Application running already in WPFExtension Method in C#Reflection Introduction c#Insert data into Database table using SQLBulkCopy class in C#as keyword C#, Import/Upload Excel file in asp.net with C#The Microsoft Jet database engine cannot open the file. It is already opened exclusively by another user,   Call method after specific time interval C#Alert from code behind asp.net,required field validator in asp.net,Difference between RegisterClientScriptBlock and RegisterStartupScript asp.netDifference between ref and out parameters.

Today we will look “How to convert DataTable to List in C#!!!”

There are 3 different ways to convert DataTable To List:

  1. Loop through
  2. LINQ
  3. Generic Method

To explain this concept using different ways, I am creating a class of Employees

public class Employee
{
    public int EmployeeId { get; set; }
    public string EmployeeName { get; set; }
    public string Address { get; set; }
    public string MobileNo { get; set; }
}

and DataTable with some data

private static DataTable DataTableWithData()
{
    DataTable dt = new DataTable("Employee");
    dt.Columns.Add("EmployeeId", typeof(Int32));
    dt.Columns.Add("EmployeeName", typeof(string));
    dt.Columns.Add("Address", typeof(string));
    dt.Columns.Add("MobileNo", typeof(string));

    dt.Rows.Add(1, "E1", "A1", "0000000000");
    dt.Rows.Add(2, "E2", "A2", "111111111");
    dt.Rows.Add(3, "E3", "A3", "1222222222");
    dt.Rows.Add(4, "E4", "A4", "3333333333");
    return dt;
}

Now we will convert DataTable to List using all three methods

1. Loop Through

Lets start with first method with which we will convert DataTable to List<Employee>.

private static DataTable DataTableWithData()
{
    DataTable dt = new DataTable("Employee");
    dt.Columns.Add("EmployeeId", typeof(Int32));
    dt.Columns.Add("EmployeeName", typeof(string));
    dt.Columns.Add("Address", typeof(string));
    dt.Columns.Add("MobileNo", typeof(string));

    dt.Rows.Add(1, "E1", "A1", "0000000000");
    dt.Rows.Add(2, "E2", "A2", "111111111");
    dt.Rows.Add(3, "E3", "A3", "1222222222");
    dt.Rows.Add(4, "E4", "A4", "3333333333");
    return dt;
}
public List<Employee> EmployeeList()
{
    DataTable dt = DataTableWithData();

    List<Employee> EmployeeList = new List<Employee>();
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        Employee Employee = new Employee();
        Employee.EmployeeId = Convert.ToInt32(dt.Rows[i]["EmployeeId"]);
        Employee.EmployeeName = dt.Rows[i]["EmployeeName"].ToString();
        Employee.Address = dt.Rows[i]["Address"].ToString();
        Employee.MobileNo = dt.Rows[i]["MobileNo"].ToString();
        EmployeeList.Add(Employee);
    }
    return EmployeeList;
}

2. LINQ

Now we will look into how we could convert DataTable to List<Employee> using LINQ

private static DataTable DataTableWithData()
{
    DataTable dt = new DataTable("Employee");
    dt.Columns.Add("EmployeeId", typeof(Int32));
    dt.Columns.Add("EmployeeName", typeof(string));
    dt.Columns.Add("Address", typeof(string));
    dt.Columns.Add("MobileNo", typeof(string));

    dt.Rows.Add(1, "E1", "A1", "0000000000");
    dt.Rows.Add(2, "E2", "A2", "111111111");
    dt.Rows.Add(3, "E3", "A3", "1222222222");
    dt.Rows.Add(4, "E4", "A4", "3333333333");
    return dt;
}

public List<Employee> EmployeeListUsingLinq()
{
    DataTable dt = DataTableWithData();

    List<Employee> EmployeeList = new List<Employee>();
    EmployeeList = (from DataRow dr in dt.Rows
                    select new Employee()
                    {
                        EmployeeId = Convert.ToInt32(dr["EmployeeId"]),
                        EmployeeName = dr["EmployeeName"].ToString(),
                        Address = dr["Address"].ToString(),
                        MobileNo = dr["MobileNo"].ToString()
                    }).ToList();
    return EmployeeList;

}

3. Generic Method

Now we will convert DataTable to List<Employee> using Generic Method.

private static DataTable DataTableWithData()
{
    DataTable dt = new DataTable("Employee");
    dt.Columns.Add("EmployeeId", typeof(Int32));
    dt.Columns.Add("EmployeeName", typeof(string));
    dt.Columns.Add("Address", typeof(string));
    dt.Columns.Add("MobileNo", typeof(string));

    dt.Rows.Add(1, "E1", "A1", "0000000000");
    dt.Rows.Add(2, "E2", "A2", "111111111");
    dt.Rows.Add(3, "E3", "A3", "1222222222");
    dt.Rows.Add(4, "E4", "A4", "3333333333");
    return dt;
}

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], null);
            else
                continue;
        }
    }
    return obj;
}

Now we will call ConvertDataTable in our main method as shown below:

DataTable dt = DataTableWithData();
List<Employee> lstEmp = ConvertDataTable<Employee>(dt);

Let me know your feedback and views.

You could also download Convert DataTable to List C#

One comment

Leave a Reply