Friday, February 10, 2012

How to convert DataTable to generic List

I have discussed about conversion of DataTable to Generic List<t>. We can do it in various way. I want to share with you some of them....

Solution 1:
DataTable dt = CreateDataTable();
List<datarow> list = new List<datarow>();
foreach (DataRow dr in dt.Rows)
{
list.Add(dr);
}

Solution 2:
DataTable table = new DataTable {
Columns = {
{"Foo", typeof(int)},
{"Bar", typeof(string)}
}
};
for (int i = 0; i < 5000; i++) {
table.Rows.Add(i, "Row " + i);
}

List<T> data = new List<t>(table.Rows.Count);
foreach (DataRow row in table.Rows) {
data.Add(new T((int)row[0], (string)row[1]));
}

Solution 3:
Using Linq/lamda expression. It return data in List<t>.
List<string> list =dataTable.Rows.OfType<datarow>().Select(dr => dr.Field<string>(0)).ToList();

Solution 4:
Using Linq/lamda expression.
List<employee> list= new List<employee>();
list= (from DataRow row in dt.Rows
select new Employee
{
FirstName = row["ColumnName"].ToString(),
LastName = row["ColumnName"].ToString()

}).ToList();


Solution 5:
Using Linq/lamda expression.
List<t> target = dt.AsEnumerable()
.Select(row => new T
{
// assuming column 0's type is Nullable<long>
ID = row.Field<long?>(0).GetValueOrDefault()
Name = String.IsNullOrEmpty(row.Field<string>(1))
? "not found"
: row.Field<string>(1)
})
.ToList();


Solution 6:
Using Linq/lamda expression. All are return array of datarow in List<t>.

List<datarow> list1= dataTable.Select().ToList();
List<datarow> list2= dataTable.Rows.Cast<datarow>().ToList();
List<datarow> list3 = dataTable.AsEnumerable().ToList();
List<datarow> list4 = new List<datarow>(dataTable.select());

Here result will return all rows of datatable, as an array of datarows, and the List constructor accepts that array of objects as an argument to initially fill our List<datarow> with.

Solution 7:
Using reflection PropertyInfo class.

sealed class Tuple<T1, T2>
{
public Tuple() {}
public Tuple(T1 value1, T2 value2) {Value1 = value1; Value2 = value2;}
public T1 Value1 {get;set;}
public T2 Value2 {get;set;}
}


public static List<T> Convert<T>(DataTable table)
where T : class, new()
{
List<Tuple<DataColumn, PropertyInfo>> map =
new List<Tuple<DataColumn,PropertyInfo>>();

foreach(PropertyInfo pi in typeof(T).GetProperties())
{
ColumnAttribute col = (ColumnAttribute)
Attribute.GetCustomAttribute(pi, typeof(ColumnAttribute));
if(col == null) continue;
if(table.Columns.Contains(col.FieldName))
{
map.Add(new Tuple<DataColumn,PropertyInfo>(
table.Columns[col.FieldName], pi));
}
}

List<T> list = new List<T>(table.Rows.Count);
foreach(DataRow row in table.Rows)
{
if(row == null)
{
list.Add(null);
continue;
}
T item = new T();
foreach(Tuple<DataColumn,PropertyInfo> pair in map) {
object value = row[pair.Value1];
if(value is DBNull) value = null;
pair.Value2.SetValue(item, value, null);
}
list.Add(item);
}
return list;
}

Solution 8:
Another class using reflection PropertyInfo class.

public List<T> ConvertTo<T>(DataTable datatable) where T : new()
    {
        List<T> Temp = new List<T>();
        try
        {
            List<string> columnsNames = new List<string>();
            foreach (DataColumn DataColumn in datatable.Columns)
                columnsNames.Add(DataColumn.ColumnName);
            Temp = datatable.AsEnumerable().ToList().ConvertAll<T>(row => getObject<T>(row, columnsNames));
            return Temp;
        }
        catch
        {
            return Temp;
        }

    }
    public T getObject<T>(DataRow row, List<string> columnsName) where T : new()
    {
        T obj = new T();
        try
        {
            string columnname = "";
            string value = "";
            PropertyInfo[] Properties;
            Properties = typeof(T).GetProperties();
            foreach (PropertyInfo objProperty in Properties)
            {
                columnname = columnsName.Find(name => name.ToLower() == objProperty.Name.ToLower());
                if (!string.IsNullOrEmpty(columnname))
                {
                    value = row[columnname].ToString();
                    if (!string.IsNullOrEmpty(value))
                    {
                        if (Nullable.GetUnderlyingType(objProperty.PropertyType) != null)
                        {
                            value = row[columnname].ToString().Replace("$", "").Replace(",", "");
                            objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(Nullable.GetUnderlyingType(objProperty.PropertyType).ToString())), null);
                        }
                        else
                        {
                            value = row[columnname].ToString().Replace("%", "");
                            objProperty.SetValue(obj, Convert.ChangeType(value, Type.GetType(objProperty.PropertyType.ToString())), null);
                        }
                    }
                }
            }
            return obj;
        }
        catch
        {
            return obj;
        }
    }


Hopefully it will help other developers.

3 comments:

Anonymous said...

Thanks. Simply outstanding work with variety of stuff.

Anonymous said...

First of all Ι want to ѕаy fаntаѕtiс blog!
I haԁ a quick queѕtion which Ι'd like to ask if you do not mind. I was interested to know how you center yourself and clear your head before writing. I'vе
had trοublе сlearing my thoughtѕ іn getting my iԁeaѕ out.
I do enϳoy wгiting but it just ѕeems liκе the first 10 to
15 minutes arе usually wastеd just tгyіng tо figure out how to bеgin.
Anу recοmmеndationѕ or hintѕ?

Appгeciatе it!
Also visit my web site take

Joel Pérez said...

Good work!

I'm using the last solution in my project and works fine!

Thanks!