How to convert Data Table Into Generic List using Custom Attribute and Reflection

Here we have two class customer and sales, if we need a common method for converting data table into a list of any type we can use refection.

public class Customer
{
public string Name{set;get;}
public string Address{set;get}
}
public class Sales
{
public string Product{set;get;}
public int Quantity{set;get}
}

Problem is that when the name of properties and column is diffrent what we do? in that case we can use a custome atribute that allows us to keep declarative information ie column name
so our custome atribute is like this

public class DataTableColName : Attribute
{
string coulumnName = string.Empty;

public string CoulumnName
{
get { return coulumnName; }
set { coulumnName = value; }
}
public DataTableColName(string colName)
{
coulumnName = colName;
}
}

Above class will be

public class Customer
{
[DataTableColName("CustomerName")]
public string Name{set;get;}
[DataTableColName("CustomerAddress")]
public string Address{set;get}
}
public class Sales
{
[DataTableColName("ProductName")]
public string Product{set;get;}
[DataTableColName("ProductQty")]
public int Quantity{set;get}
}

So generic method for converting DataTble is

public static List<T> GetList<T>(DataTable dt)
{
List<T> lst = new List<T>();
foreach (DataRow dw in dt.Rows)
{
Type Tp = typeof(T);
//create instance of the type
T obj = Activator.CreateInstance<T>();
//fetch all properties
PropertyInfo[]pf= Tp.GetProperties();
foreach (PropertyInfo pinfo in pf)
{
///read the implimeted custome atribute for a property
object[] colname = pinfo.GetCustomAttributes(typeof(DataTableColName), false);
if (colname == null) continue;
if (colname.Length == 0) continue;
///read column name from that atribute object
string col = (colname[0] as DataTableColName).CoulumnName;
if (!dt.Columns.Contains(col)) continue;
if (dw[col] == null) continue;
if (dw[col] == DBNull.Value) continue;
///set property value
pinfo.SetValue(obj, dw[col], null);
}
lst.Add(obj);
}
return lst;
}

In same way we can create a method for generating SqlParametr array for inserting and updating into table.

public static SqlParameter[] GetSqlParametr<T>(T data)
{
List<SqlParameter> parms = new List<SqlParameter>();
Type Tp = typeof(T);
PropertyInfo[] pf = Tp.GetProperties();
foreach (PropertyInfo pinfo in pf)
{
object[] colname = pinfo.GetCustomAttributes(typeof(DataTableColName), false);
if (colname == null) continue;
if (colname.Length == 0) continue;
string col = (colname[0] as DataTableColName).CoulumnName;
if (string.IsNullOrEmpty(col)) continue;

object obj= pinfo.GetValue(data, null);
SqlParameter sqlparam = new SqlParameter("@"+col,obj);
parms.Add(sqlparam);
}
return parms.ToArray();
}

1 Comment

Join the conversation! Your thoughts help the community grow.

clementedwin14y ago
I'm currently working on a huge public interfacing web site...we are in the process of deciding the Data Access Technology...I personally have a feeling that LINQ 2 SQL & Entity Framework or for smaller or less mission-critical applications where performance is not a major criteria... I'm suggesting the client to use Data Access Application Block with Stored Procedures...with a combination of "Custom Attribute / System.Reflection " to map the physical model to the conceptual model... Any suggestions...