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); Listteste = 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 ListVB.NET UsageToObjectList (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; }
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