When we want to make our applications more open to other platforms than we cannot use datasets as Therefor we need a way to convert datasets to simple objects and back. In this implementation I used a custom attribute to provide mapping between database language and business language, because that was a requirement.
Here is the custom attribute class:
Imports System.Reflection
<AttributeUsage(AttributeTargets.Field Or AttributeTargets.[Property], AllowMultiple:=False, Inherited:=True)> _
Public NotInheritable Class DataColumnAttribute
Inherits Attribute
Private Const Flags As BindingFlags = BindingFlags.[Public] Or BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.FlattenHierarchy
Private _name As String = Nothing
Public ReadOnly Property Name() As String
Get
Return _name
End Get
End Property
Public Sub New()
End Sub
Public Sub New(ByVal name As String)
_name = name
End Sub
Private Function GetName(ByVal member As MemberInfo) As String
Return If(_name, member.Name)
End Function
Public Shared Sub Bind(ByVal row As DataRow, ByVal target As Object)
Dim type As Type = target.[GetType]()
For Each column As DataColumn In row.Table.Columns
For Each field As FieldInfo In type.GetFields(Flags)
For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True)
att.Bind(column.ColumnName, target, field, row(column))
Next
Next
For Each [property] As PropertyInfo In type.GetProperties(Flags)
For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True)
att.Bind(column.ColumnName, target, [property], row(column))
Next
Next
Next
End Sub
Public Shared Sub Bind(ByRef rd As DbDataReader, ByVal target As Object)
Dim type As Type = target.[GetType]()
For i = 0 To rd.FieldCount - 1
For Each field As FieldInfo In type.GetFields(Flags)
For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True)
att.Bind(rd.GetName(i), target, field, rd.Item(i))
Next
Next
For Each [property] As PropertyInfo In type.GetProperties(Flags)
For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True)
att.Bind(rd.GetName(i), target, [property], rd.Item(i))
Next
Next
Next
End Sub
Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal field As FieldInfo, ByVal value As Object)
If GetName(field).ToUpper() = columnName.ToUpper() Then
field.SetValue(target, value)
End If
End Sub
Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal [property] As PropertyInfo, ByVal value As Object)
If value.GetType() IsNot GetType(System.DBNull) Then
If GetName([property]).ToUpper = columnName.ToUpper() Then
[property].SetValue(target, value, Nothing)
End If
End If
End Sub
End Class
Here is the converter class
Imports System.ComponentModel
Public Class DataTableConverter(Of T)
Public Function GetObjectsAsList(ByRef rd As DbDataReader) As List(Of T)
Dim list As New List(Of T)()
While rd.Read
Dim target As T = Activator.CreateInstance(Of T)()
DataColumnAttribute.Bind(rd, target)
list.Add(target)
End While
Return list
End Function
Public Function GetObjectsAsBindingList(ByRef rd As DbDataReader) As BindingList(Of T)
Dim list As New BindingList(Of T)()
While rd.Read
Dim target As T = Activator.CreateInstance(Of T)()
DataColumnAttribute.Bind(rd, target)
list.Add(target)
End While
Return list
End Function
Public Function GetObjectsAsBindingList(ByRef dt As DataTable) As BindingList(Of T)
Dim list As New BindingList(Of T)()
For Each row As DataRow In dt.Rows
Dim target As T = Activator.CreateInstance(Of T)()
DataColumnAttribute.Bind(row, target)
list.Add(target)
Next
Return list
End Function
Public Function GetObjectsAsList(ByRef dt As DataTable) As List(Of T)
Dim list As New List(Of T)()
For Each row As DataRow In dt.Rows
Dim target As T = Activator.CreateInstance(Of T)()
DataColumnAttribute.Bind(row, target)
list.Add(target)
Next
Return list
End Function
End Class
Here is the reverse converter class:
Imports System.Reflection
Imports System.ComponentModel
Public Class ListConverter
Public Function GetDataSetFromList(Of T)(ByVal list As List(Of T)) As DataSet
Dim _resultDataSet As New DataSet()
Dim _resultDataTable As New DataTable("results")
Dim _resultDataRow As DataRow = Nothing
Dim _itemProperties() As PropertyInfo = Nothing
'
' Meta Data.
'
_itemProperties = list.Item(0).GetType().GetProperties()
For Each p As PropertyInfo In _itemProperties
_resultDataTable.Columns.Add(p.Name, _
p.GetGetMethod.ReturnType())
Next
'
' Data
'
For Each item As T In list
'
' Get the data from this item into a DataRow
' then add the DataRow to the DataTable.
' Eeach items property becomes a colunm.
'
_itemProperties = item.GetType().GetProperties()
_resultDataRow = _resultDataTable.NewRow()
For Each p As PropertyInfo In _itemProperties
_resultDataRow(p.Name) = p.GetValue(item, Nothing)
Next
_resultDataTable.Rows.Add(_resultDataRow)
Next
'
' Add the DataTable to the DataSet, We are DONE!
'
_resultDataSet.Tables.Add(_resultDataTable)
Return _resultDataSet
End Function
Public Function GetDataSetFromBindingList(Of T)(ByVal list As Bindinglist(Of T)) As DataSet
Dim _resultDataSet As New DataSet()
Dim _resultDataTable As New DataTable("results")
Dim _resultDataRow As DataRow = Nothing
Dim _itemProperties() As PropertyInfo = Nothing
'
' Meta Data.
'
_itemProperties = list.Item(0).GetType().GetProperties()
For Each p As PropertyInfo In _itemProperties
For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True)
_resultDataTable.Columns.Add(p.Name, p.GetGetMethod.ReturnType())
Next
Next
'
' Data
'
For Each item As T In list
'
' Get the data from this item into a DataRow
' then add the DataRow to the DataTable.
' Eeach items property becomes a colunm.
'
_itemProperties = item.GetType().GetProperties()
_resultDataRow = _resultDataTable.NewRow()
For Each p As PropertyInfo In _itemProperties
For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True)
_resultDataRow(p.Name) = p.GetValue(item, Nothing)
Next
Next
_resultDataTable.Rows.Add(_resultDataRow)
Next
'
' Add the DataTable to the DataSet, We are DONE!
'
_resultDataSet.Tables.Add(_resultDataTable)
Return _resultDataSet
End Function
End Class