From DataReader to Objects

It's been a while since I posted some actual code on my blog.  I'm working on a reporting project at work and ran into a case where I just wanted to execute some arbitrary SQL against an Oracle database and get some plain objects back.  Those objects will be passed into a crystal report and turned into something suitable for digestion by the managament of our company. Nothing fancy here.  

First I'll show you my extension methods and then follow with some explanation:

static Regex underscore = new Regex(@"(^|_)(.)");
static string convertName(string s) {
	return underscore.Replace(s.ToLower(), m => m.Groups[0].ToString().ToUpper().Replace("_",""));
}

static T ToObject<T>(this IDataRecord r) where T:new() {
	T obj = new T();
	for (int i = 0; i < r.FieldCount; i++) {
		var p=typeof(T).GetProperty(convertName(r.GetName(i)));
		if (p != null) {
			if (p.PropertyType == r[i].GetType())
				p.SetValue(obj, r[i], null);
			else {
				var c = TypeDescriptor.GetConverter(r[i]);
				if (c.CanConvertTo(p.PropertyType))
					p.SetValue(obj, c.ConvertTo(r[i],p.PropertyType), null);
			}
		}
	}
	return obj;
}

public static IEnumerable<T> GetObjects<T>(this IDbCommand c) where T : new() {
	using (IDataReader r = c.ExecuteReader()) {
		while (r.Read()) {
			yield return r.ToObject<T>();
		}
	}
}

The meat of the work happens in the extension method for IDataRecord called ToObject.  It's a generic method that tries to map columns to properties.  At our company, the naming convention for column names is underscore delimited identifiers since oracle knows nothing about case unless you use quotes. For my c# objects, I much prefer Pascal Case.  The convertName method gets called to perform this conversion for me.  Finally, the last interesting bit is the TypeDescriptor usage.  When there is a difference in the type coming from the DB and the type in the object for a particular field, I'm using TypeDescriptor to find a converter if possible. This is useful if, for example, the type coming from the DB is a decimal, but my class needs an int.  

The last method, GetObjects is just a wrapper around the ToObject method.  It fires up a datareader and calls ToObject for every row.

Finally, I'd like to show how this is being used. Assume the following silly reporting class.

public class EmployeePerformance{
	public string FirstName {get; set;}
	public string LastName {get; set;}
	public decimal HoursWorked {get; set;}
	public int WidgetsCreated {get; set;}
}

All we have to do now is create a DbCommand object and call our GetObjects extension method.  I'll give an example as if I were running some report just so you have some context.

var rpt=new EmployeePerformanceReport();

using (var cx = new OracleConnection(MyConnectionString)) {
	cx.Open();
	using (var cmd = cx.CreateCommand()) {
		cmd.CommandText = @"
			SELECT
				first_name,
				last_name,
				hours_worked,
				widgets_created
			FROM
				employee_performance
			WHERE
				work_day between :startDate and :endDate
		";
		cmd.Parameters.Add("startDate",new DateTime(2008,1,1));
		cmd.Parameters.Add("endDate",new DateTime(2008,12,31));
		rpt.SetDataSource(cmd.GetObjects<EmployeePerformance>());
	}
}

//Do something with your report

It's just that simple.  I'm sure there could be some caching/compilation of the mapping after the first object to speed things up a bit. Speed hasn't been an issue so far, so I didn't persue that any further. I just wanted to illustrate this technique that lets me avoid using a DataSet which I've come to loathe in most situations. Please let me know what you think.

  • http://www.davetheninja.net Dave the Ninja

    Why not just use NHibernate?

    You can map your objects to your oracle DB.

    Simples’

    Dave the Ninja

  • NickV

    Even better: SubSonic.
    No need to write and maintain an XML mapping file. I was up and running just a few minutes after watching the screencast here:
    http://subsonicproject.com/setup/gettingstarted/

    It’s not the most efficient approach, but it certainly reduces the amount of time and effort you put into maintaining the project. What I like best is, I can make a change to the database, run the code generation tool, recompile, and find most of the places in code that would’ve caused problems at runtime.

  • jonie

    I fix some bugs ((int?) -> (enum?) converting).

    object ToObject(IDataRecord r, Type to)
    {
    //T obj = new T();
    object obj = Activator.CreateInstance(to);
    for (int i = 0; i < r.FieldCount; i++)
    {
    if (r.IsDBNull(i))
    continue;

    var p = to.GetProperty(r.GetName(i));
    if (p != null)
    {
    var propType = p.PropertyType;
    if (propType.IsGenericType && propType.Name.Equals(typeof(Nullable).Name))
    propType = propType.GetGenericArguments()[0];
    if (propType == r[i].GetType())
    p.SetValue(obj, r[i], null);
    else
    {
    var c = TypeDescriptor.GetConverter(r[i]);
    if (c.CanConvertTo(propType))
    p.SetValue(obj, c.ConvertTo(r[i], propType), null);
    else if (propType.IsEnum)
    {
    p.SetValue(obj, Enum.ToObject(propType, r[i]), null);
    }
    #if DEBUG
    else
    {
    Debug.WriteLine(“Cant find converter for field [” + r.GetName(i) + “]”);
    }
    #endif
    }
    }
    #if DEBUG
    else
    {
    Debug.WriteLine(“Cant find property for field [” + r.GetName(i) + “]”);
    }
    #endif
    }
    return obj;
    }