Easy and simple way to bind ADO object to POCO object
Simple one way binding of ADO database objects to POCO
This article is base don the article I wrote in September last year. Basically I wrote this piece of code because I needed something really lite for one way binding.
There are a bunch of light ORMs and ORM-Like solution, but both of them provide two way binding, but for me insert/delete/update commands were already handled by stored procedures as it was an existing solution which needed some new features, so I needed something to make reading a lot easier. You figure this out when you for example try to use DataTable or DataSet as a model in MVC Razor view for example.
This is the moment you realize you need something that will give you POCO objects out of that old Data objects produced by ADO, but since I needed it only for one application and keep the speed for performance critical existing functionalities, I decided to make something of my own which will do the following:
- DataRow to single POCO class
- DataTable to single POCO class
- DataTable to multiple POCO classes
- DataSet to single POCO class
- DataSet to multiple POCO classes
- SqlDataReader to single POCO class
- SqlDataReader to multiple POCO classes
- One POCO class to another POCO class
Someone might say it is reinventing the wheel, but it is a good practice to see how the basic object mapping can work and in the end you get a lightweight solution that does only what you need without any unnecessary functionality.
Attribute defined mapping
Since columns in result DataTable might not follow any naming convention (as it is a common practice in large scale solutions developed by different teams in a different time frame), for sure you do not want your POCO models to have same property names as table columns in database. For this purpose I created an attribute class which represents mapping from column to specific property.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MicroMapper
{
[AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
public class DataBind:Attribute
{
#region Fields
private string columnName;
#endregion
#region Properties
public string ColumnName
{
get
{
return this.columnName;
}
set
{
this.columnName = value;
}
}
#endregion
#region Constructors
public DataBind(string columnName)
{
this.columnName = columnName;
}
#endregion
}
}
Data binding via Reflection
Now you can define which model property will be bound to which DataTable column. You only need to make sure that types match.
Next thing is to do the actual binding of the DataTable DataRow to a POCO object instance. I did this with reflection and generic types to keep the flexibility. The core method for this whole binding solution is the following one:
public static T BindModel<T>(DataRow dataRow) where T : class, new()
{
T item = new T();
if (dataRow.Table != null)
{
foreach (DataColumn column in dataRow.Table.Columns)
{
var objectProperty = GetTargetProperty<T>(column.ColumnName);
if (objectProperty != null)
{
var dataValue = dataRow[column.ColumnName];
objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
}
}
}
return item;
}
To avoid code repeating, fetching the property from the POCO object instance is isolated in a method.
private static PropertyInfo GetTargetProperty<T>(string name)
{
return typeof(T).GetProperties()
.Where(p => p.GetCustomAttributes(typeof(DataBind), true)
.Where(a => ((DataBind)a).ColumnName == name)
.Any()
).FirstOrDefault();
}
This way DataRow as a collection item of DataTable can be bound to a POCO object instance.
Since DataTable and DataSet are really heavy objects, I needed to cover binding of a DataReader class too.
public static IEnumerable<T> BindModels<T>(SqlDataReader dataReader) where T : class, new()
{
if (dataReader.HasRows)
{
while (dataReader.Read())
{
T item = new T();
for (int columnIndex = 0; columnIndex < dataReader.FieldCount; columnIndex++)
{
var objectProperty = GetTargetProperty<T>(dataReader.GetName(columnIndex));
if (objectProperty != null)
{
var dataValue = dataReader.GetValue(columnIndex);
if (objectProperty.PropertyType == typeof(List<int>))
{
objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue.ToString().Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries).Select(i => int.Parse(i.Trim())).ToList<int>());
}
else
{
objectProperty.SetValue(item, DBNull.Value.Equals(dataValue) ? null : dataValue);
}
}
}
yield return item;
}
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
As DataTable is a container of DataRow collection and DataSet is a container of DataTable collection, binding methods are just wrappers around DataRow binding methods.
DataTable binding
public static IEnumerable<T> BindModels<T>(DataTable dataTable) where T : class, new()
{
if (dataTable != null && dataTable.Rows.Count > 0)
{
foreach (DataRow row in dataTable.Rows)
{
yield return BindModel<T>(row);
}
}
}
DataSet binding
public static IEnumerable<T> BindModels<T>(DataSet dataSet, int tableIndex = 0) where T : class, new()
{
if (dataSet != null && dataSet.Tables.Count - 1 > tableIndex)
{
return BindModels<T>(dataSet.Tables[tableIndex]);
}
return null;
}
public static IEnumerable<T> BindModels<T>(DataSet dataSet, string tableName) where T : class, new()
{
if (dataSet != null && dataSet.Tables[tableName] != null)
{
return BindModels<T>(dataSet.Tables[tableName]);
}
return null;
}
Source code of the whole solution is atteched to this article and a copy can be also fetched from Github project page.
References
Disclaimer
Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.
About the author
DEJAN STOJANOVICDejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects
Comments for this article