With all of the hype around Entity Framework and LINQ to SQL, there are still
some of us out there that are still going old school and rolling our own POCOs.
I know ORMs are supposed to be super-duper-bleeding-edge, but as much as I want
to be like all of the other cool kids, for large reporting-based systems I end
up writing some prettty nasty stored procs and fill up my POCOs.
At my last gig we had a pretty sweet architecture built around a custom "typed
table" concept. The idea was to simplify the DAL code (e.g.
DataAdapter.Fill(customTable)) while getting the syntactic sugar in the IDE.
Another idea I have is similar to that, except in the form of an extension
method on DataReader.
First, we create a custom attribute type. This is much like LINQ to SQL where
the column mapping is made between the property and the query result (or column
name) from the database. We'll apply this to our POCO properties where
applicable.
namespace PocoFiller {
[AttributeUsage(AttributeTargets.Property)]
public class ColumnMappingAttribute : Attribute {
string source = string.Empty;
public ColumnMappingAttribute() { }
public string Source { get { return this.source; } set { this.source = value; } }
}
}
Next, we create the extension method on data reader. It has to account for
nullable types as well as non-nullables and we do that using some reflection
trickery (yes this could be optimized and cleaned up a bit, but you get the
idea)...
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
namespace PocoFiller {
static public class DataReaderExtensions {
static public System.Collections.Generic.List<T> ToList<T>(this SqlDataReader reader) where T : new() {
return reader.ToList<T>(true);
}
static public System.Collections.Generic.List<T> ToList<T>(this SqlDataReader reader, bool ignoreConvertExceptions) where T : new() {
var results = new List<T>();
while (reader.Read()) {
T result = new T();
results.Add(result);
Type t = result.GetType();
PropertyInfo[] properties = t.GetProperties(System.Reflection.BindingFlags.IgnoreCase
| System.Reflection.BindingFlags.Public
| System.Reflection.BindingFlags.Instance);
foreach (var property in properties) {
object[] attributes = property.GetCustomAttributes(typeof(ColumnMappingAttribute), true);
if (attributes != null && attributes.Length > 0) {
ColumnMappingAttribute cma = attributes[0] as ColumnMappingAttribute;
object dataValue = reader[cma.Source];
if (DBNull.Value == dataValue) dataValue = null;
if (dataValue != null && property.PropertyType != dataValue.GetType()) {
try {
if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) {
Type[] genericArgs = property.PropertyType.GetGenericArguments();
if (genericArgs.Length > 0) {
dataValue = Convert.ChangeType(dataValue, genericArgs[0]);
}
}
else {
dataValue = Convert.ChangeType(dataValue, property.PropertyType);
}
}
catch {
throw;
}
}
try {
property.SetValue(result, dataValue, null);
}
catch (ArgumentException) {
if (!ignoreConvertExceptions) {
throw;
}
}
}
}
}
return results;
}
}
}
Then we simply create our POCO type(s) and apply the attribute to map the
properties to the column values. In this example, I alias FirstName to
"first_name" just for example...
namespace PocoFiller {
public class Employee {
[ColumnMapping(Source = "ContactID")]
public int ContactID { get; set; }
[ColumnMapping(Source = "EmployeeID")]
public int? EmployeeID { get; set; }
[ColumnMapping(Source = "first_name")]
public string FirstName { get; set; }
[ColumnMapping(Source = "LastName")]
public string LastName { get; set; }
[ColumnMapping(Source = "Title")]
public string Title { get; set; }
[ColumnMapping(Source = "HireDate")]
public DateTime? HireDate { get; set; }
}
}
Use the ToList<T> extension method and voila!
using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections.Generic;
namespace PocoFiller {
class Program {
const string connectionString = "Integrated Security=SSPI;Data Source=.;Initial Catalog=AdventureWorks";
static void Main(string[] args) {
var sql = @"select c.ContactID, e.EmployeeID, c.FirstName first_name, c.LastName, e.Title, e.HireDate
from HumanResources.Employee e inner join Person.Contact c
on c.ContactID = e.ContactID
where e.ManagerID = 21";
var employees = new List<Employee>();
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(sql, connection)) {
command.CommandType = CommandType.Text;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader()) {
employees = reader.ToList<Employee>();
}
}
foreach (var employee in employees) {
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", employee.ContactID,
employee.EmployeeID,
employee.FirstName,
employee.LastName,
employee.Title);
}
}
}
}