Let’s say we have a DataTable with the following columns:
DataTable Columns Respectively
1. EmpId (this is Int DataType)
2. EmpName (this is varchar DataType)
3. EmpAddress (this is varchar DataType)
4. EmpPhone (this is varchar DataType)
5. Status (this is Boolean DataType)
6. EmpRelationKey (this is int DataType)
and we wish to convert the DataTable above to the following Class Object
public class Employee
{
public int EmpId;
public string EmpName = "";
public string EmpAddress = "";
public string EmpPhone = "";
public bool Status;
public int EmpRelationKey;
}Below is the traditional way of converting the DataTable to List<object>:
List<Employee>employees = new List<Employee>();
foreach (DataRow row in dt.Rows)
{
employees.Add(new Employee
{
EmpId = Convert.ToInt32(row["EmpId"]),
EmpName = row["EmpName"].ToString() ,
EmpAddress = row["EmpName"].ToString(),
Emphone = row["EmpPhone"].ToString(),
Status = Convert.toBoolean(row["Status"])
});
} Following is a generic method that can bind any DataTable to any class object:
using System.Reflection;
public static List<T> BindList<T>(DataTable dt)
{
// Example 1:
// Get private fields + non properties
//var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
// Example 2: Your case
// Get all public fields
var fields = typeof(T).GetFields();
List<T> lst = new List<T>();
foreach (DataRow dr in dt.Rows)
{
// Create the object of T
var ob = Activator.CreateInstance<T>();
foreach (var fieldInfo in fields)
{
foreach (DataColumn dc in dt.Columns)
{
// Matching the columns with fields
if (fieldInfo.Name == dc.ColumnName)
{
// Get the value from the datatable cell
object value = dr[dc.ColumnName];
// Set the value into the object
fieldInfo.SetValue(ob, value);
break;
}
}
}
lst.Add(ob);
}
return lst;
}Example of usage:
DataTable dt1 = SqlHelper.GetTable("select * from employee;");
List<Employee> employees = BindList<Employee>(dt1);
DataTable dt2 = SqlHelper.GetTable("select * from membership;");
List<Membership> lstMembership = BindList<Membership>(dt2);
DataTable dt3 = SqlHelper.GetTable("select * from car order by name;");
List<Car> lstCar = BindList<Car>(dt3);Extended Version
Above example assume that the data holds inside the DataTable has the same data type as your Class object’s fields.
What if the data is not same as the class object’s fields?
Such as null?
So, we might want to extend the method to take care just in case both data type are not the same.
public static List<T> BindList<T>(DataTable dt)
{
// Example 1:
// Get private fields + non properties
//var fields = typeof(T).GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
// Example 2: Your case
// Get all public fields
var fields = typeof(T).GetFields();
List<T> lst = new List<T>();
foreach (DataRow dr in dt.Rows)
{
// Create the object of T
var ob = Activator.CreateInstance<T>();
foreach (var fieldInfo in fields)
{
foreach (DataColumn dc in dt.Columns)
{
// Matching the columns with fields
if (fieldInfo.Name == dc.ColumnName)
{
Type type = fieldInfo.FieldType;
// Get the value from the datatable cell
object value = GetValue(dr[dc.ColumnName], type);
// Set the value into the object
fieldInfo.SetValue(ob, value);
break;
}
}
}
lst.Add(ob);
}
return lst;
}
static object GetValue(object ob, Type targetType)
{
if (targetType == null)
{
return null;
}
else if (targetType == typeof(String))
{
return ob + "";
}
else if (targetType == typeof(int))
{
int i = 0;
int.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(short))
{
short i = 0;
short.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(long))
{
long i = 0;
long.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(ushort))
{
ushort i = 0;
ushort.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(uint))
{
uint i = 0;
uint.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(ulong))
{
ulong i = 0;
ulong.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(double))
{
double i = 0;
double.TryParse(ob + "", out i);
return i;
}
else if (targetType == typeof(DateTime))
{
// do the parsing here...
}
else if (targetType == typeof(bool))
{
// do the parsing here...
}
else if (targetType == typeof(decimal))
{
// do the parsing here...
}
else if (targetType == typeof(float))
{
// do the parsing here...
}
else if (targetType == typeof(byte))
{
// do the parsing here...
}
else if (targetType == typeof(sbyte))
{
// do the parsing here...
}
else if........
..................
return ob;
}