SubscribeFrom DataReader to Objects
Comments(3) 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 reportIt'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.
