Problem: I need to convert Generic list to Datatable and Data table to Generic list. How can I do this in C#?
Solution: You may often require to convert your Datatable to Generic list of your Generic list to Datatable. Following code snippet use extension method for the conversion:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web;
using System.Text;
/// <summary>
/// Summary description for
GenericListExtensionMethod
/// </summary>
public static class GenericListExtensionMethod
{
public static
DataTable GetDataTable<T>(this List<T> obj)
{
DataTable dt = new DataTable();
//special handling for value types and string
if (typeof(T).IsValueType
|| typeof(T).Equals(typeof(string)))
{
DataColumn dc = new DataColumn("Value");
dt.Columns.Add(dc);
foreach (T item in
obj)
{
DataRow dr = dt.NewRow();
dr[0] = item;
dt.Rows.Add(dr);
}
}
else//for reference types
other than string
{
//find all the public properties of this Type using
reflection
PropertyInfo[] piT = typeof(T).GetProperties();
foreach (PropertyInfo
pi in piT)
{
//create a datacolumn for each property
if (pi.PropertyType.Name.Contains("Nullable"))
{
DataColumn dc = new DataColumn(pi.Name, typeof(string));
dt.Columns.Add(dc);
}
else
{
DataColumn dc = new DataColumn(pi.Name,
pi.PropertyType);
dt.Columns.Add(dc);
}
}
//now we iterate through all the items in current instance,
take the corresponding values and add a new row in dt
for (int item = 0;
item < obj.Count; item++)
{
DataRow dr = dt.NewRow();
for (int
property = 0; property < dt.Columns.Count; property++)
{
dr[property] = piT[property].GetValue(obj[item], null);
}
dt.Rows.Add(dr);
}
}
return dt;
}
public static List<T> ToCollection<T>(this DataTable dt)
{
List<T> lst = new
List<T>();
Type tClass = typeof(T);
PropertyInfo[] pClass = tClass.GetProperties();
List<DataColumn> dc =
dt.Columns.Cast<DataColumn>().ToList();
T cn;
foreach (DataRow item in
dt.Rows)
{
cn = (T)Activator.CreateInstance(tClass);
foreach (PropertyInfo
pc in pClass)
{
string ptp = pc.PropertyType.Name;
DataColumn d = dc.Find(c =>
c.ColumnName == pc.Name);
if (d != null
&& item[pc.Name] != null &&
item[pc.Name] != DBNull.Value)
{
string tt = d.DataType.Name;
switch (tt)
{
case "String":
pc.SetValue(cn,
Convert.ToString(item[pc.Name]), null);
break;
case "Int16":
pc.SetValue(cn, Convert.ToInt16(item[pc.Name]), null);
break;
case "Int32":
pc.SetValue(cn, Convert.ToInt32(item[pc.Name]), null);
break;
case "Decimal":
pc.SetValue(cn, Convert.ToDecimal(item[pc.Name]), null);
break;
case "DateTime":
pc.SetValue(cn, Convert.ToDateTime(item[pc.Name]), null);
break;
default:
pc.SetValue(cn, Convert.ToString(item[pc.Name]), null);
break;
}
}
}
lst.Add(cn);
}
return lst;
}
}
And here is how you can use these methods (in Ext.Net):
protected void btnExport_Click(object sender, DirectEventArgs e)
{
string json = e.ExtraParams["AllValues"];
if (string.IsNullOrEmpty(json))
{
return;
}
List<dividend> lst = JSON.Deserialize<List<dividend>>(json);
if (lst == null)
{
return;
}
try
{
DataTable dt = new DataTable();
dt = lst.GetDataTable();
string exportAs = "BankReturn"
+ CurrentDateString() + ".xlsx";
List<string>
columnNames = new List<string>() { "dividendyear",
"declareid", "wno", "boid",
"name", "bankcorr",
"branch", "accno",
"StatusName", "LastAction", "remarks"
};
Export(dt, columnNames, exportAs);
}
catch (Exception
ex)
{
X.Msg.Alert("Message", string.Format("{0}",
ex.ToString())).Show();
return;
}
}
protected void
fuImport_FileSelected(object sender,
DirectEventArgs e)
{
string json = e.ExtraParams["AllValues"];
if (string.IsNullOrEmpty(json))
{
X.Msg.Alert("Message", string.Format("{0}",
"Please search some data first to update on
import")).Show();
return;
}
List<dividend> lst = JSON.Deserialize<List<dividend>>(json);
DataSet ds = ImportExcelXLS(fuImport.PostedFile, true);
List<dividend> impLst =
ds.Tables[0].ToCollection<dividend>();
gpBoInfoStore.DataSource = lst;
gpBoInfoStore.DataBind();
}
