Thursday, May 27, 2010

Convert DataTable into POCO using attributes

Hello there.
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 String
      _
     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
The Magic
 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:

Satriansyah Wibowo said...

How do i call ToObjectList method?

Ricardo Rodrigues said...

Sorry for the late answer, you can call it like:

(put the ToObjectList method on a class)

ClassName.ToObjectList(yourDataTable)