A while ago, I had this issue, I have an older system which *still* works with the DataTable class, but still, I want to create my POCO (as we call it these days, the plain old CLR object) and work with it, LINQ it, etc, instead of the ugly DataTable object.
So I decided to do a method (that could be used as extension method on the DataTable class) that converted my DataTable to my POCO, on the fly.
The problem: mapping DataTable columns to POCO properties.
The solution: create a custom attribute class so I can define the correspondent column of the DataTable for each POCO property.
Here is the code:
C#
Usage
var dt = new DataTable();
dt.Columns.AddRange(
new DataColumn[]
{
new DataColumn {ColumnName = "nome", DataType = typeof (string)},
new DataColumn {ColumnName = "idade", DataType = typeof (int)}
});
dt.LoadDataRow(new object[] { "ricardo", 24 }, true);
dt.LoadDataRow(new object[] { "manel", 45 }, true);
List teste = ToObjectList(dt);
POCO
public class Person
{
[ColumnMapping(FieldName = "nome")]
public string Name
{
get;
set;
}
[ColumnMapping(FieldName = "idade")]
public int Age
{
get;
set;
}
}
The magic
public class ColumnMapping : Attribute
{
public string FieldName
{
get;
set;
}
}
private List ToObjectList(DataTable dtSource) where T : new()
{
var returnList = new List();
for (var i = 0; i <= dtSource.Rows.Count - 1; i++)
{
var obj = new T();
for (var j = 0; j <= dtSource.Columns.Count - 1; j++)
{
var col = (DataColumn) dtSource.Columns[j];
foreach (var pi in obj.GetType().GetProperties())
{
var fieldName = ((ColumnMapping)pi.GetCustomAttributes(typeof(ColumnMapping), false)[0]).FieldName;
if (fieldName == col.ColumnName)
{
pi.SetValue(obj,dtSource.Rows[i][j],null);
break;
}
}
}
returnList.Add(obj);
}
return returnList;
}
VB.NET
Usage
Dim dt = New DataTable()
dt.Columns.AddRange(New DataColumn() {New DataColumn(), New DataColumn()})
dt.LoadDataRow(New Object() {"ricardo", 24}, True)
dt.LoadDataRow(New Object() {"manel", 45}, True)
Dim teste As List(Of Person) = ToObjectList(Of Person)(dt)
POCO
Public Class Person Private _Name As StringThe Magic_ Public Property Name() As String Get Return _Name End Get Set(ByVal value As String) _Name = value End Set End Property Private _Age As Integer _ Public Property Age() As Integer Get Return _Age End Get Set(ByVal value As Integer) _Age = value End Set End Property End Class
Public Class ColumnMapping
Inherits Attribute
Private _FieldName As String
Public Property FieldName() As String
Get
Return _FieldName
End Get
Set(ByVal value As String)
_FieldName = value
End Set
End Property
End Class
Public Shared Function ToObjectList(Of T As New)(ByVal dtSource As DataTable) As List(Of T)
Dim returnList = New List(Of T)()
For i = 0 To dtSource.Rows.Count - 1
Dim obj = New T()
For j = 0 To dtSource.Columns.Count - 1
Dim col = DirectCast(dtSource.Columns(j), DataColumn)
For Each pi In obj.[GetType]().GetProperties()
Dim fieldName = DirectCast(pi.GetCustomAttributes(GetType(ColumnMapping), False)(0), ColumnMapping).FieldName
If fieldName = col.ColumnName Then
pi.SetValue(obj, dtSource.Rows(i)(j), Nothing)
Exit For
End If
Next
Next
returnList.Add(obj)
Next
Return returnList
End Function
2 comments:
How do i call ToObjectList method?
Sorry for the late answer, you can call it like:
(put the ToObjectList method on a class)
ClassName.ToObjectList(yourDataTable)
Post a Comment