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.

Extension Methods meet the Database

A co-worker and I had a conversation about extension methods today. Somehow this triggered a thought that I needed to see what could be done about the database monotony. Here's the result.

/*
 * DbExtensions.cs
 * Author: Josh Bush (digitalbush.com)
 */

using System;
using System.Data;
using System.Collections.Generic;

namespace Bush.Data{    
    public static class DbExtensions{        

        private delegate T DbAction(IDbCommand cmd);

        /// 
        /// Helper method that does the connection and paramter setup.
        /// 
        private static T ExecuteDbAction(IDbConnection conn, string commandText, IDbDataParameter[] parameters,DbAction dba){
            if (conn.State != ConnectionState.Open)
                conn.Open();
            using (IDbCommand cmd = conn.CreateCommand()){
                cmd.CommandText = commandText;
                cmd.Connection = conn;
                if (parameters != null){
                    foreach (IDbDataParameter p in parameters)
                        cmd.Parameters.Add(p);
                }
                return dba(cmd);
            } 
        }
        
        /// 
        /// Extension method to execute a query and return the resulting records
        /// 
        public static IEnumerable Query(this IDbConnection conn, string commandText, params IDbDataParameter[] parameters){
            return ExecuteDbAction>(
                conn,
                commandText,
                parameters,
                queryHelper  //ugh, can't use yield inside of an anonymous method
            );            
        }
        public static IEnumerable Query(this IDbConnection conn, string commandText){
            return conn.Query(commandText, null);
        }
        private static IEnumerable queryHelper(IDbCommand cmd){
            using (IDataReader r = cmd.ExecuteReader()){
                while (r.Read())
                    yield return r;                
            }
        }

        /// 
        /// Extension method to execute scalar query.
        /// 
        public static object QueryValue(this IDbConnection conn, string commandText, params IDbDataParameter[] parameters){
            return ExecuteDbAction(
             conn,
             commandText,
             parameters,
             cmd=>cmd.ExecuteScalar()
         );  
        }
        public static object QueryValue(this IDbConnection conn, string commandText){
            return conn.QueryValue(commandText, null);
        }

        /// 
        /// Extension method to execute a non-query.
        ///         
        public static int Execute(this IDbConnection conn, string commandText, params IDbDataParameter[] parameters){
            return ExecuteDbAction(
                conn,
                commandText,
                parameters,
                cmd => cmd.ExecuteNonQuery()
            );
        }
        public static int Execute(this IDbConnection conn, string commandText){
            return conn.Execute(commandText, null);
        }
    }
}


I haven't had a chance to really run this code through it's paces, but I did the following test. I used the MySql Connector, but the methods above should work for any database provider as long as it implements the IDbConnection interface.

using (MySqlConnection mConn = new MySqlConnection(ConnectionString)){
    foreach(IDataRecord r in mConn.Query("select post_title from posts"))
        Console.WriteLine(r["post_title"].ToString());               
}

Compare that to the craptacular way of doing it without the extension methods.

using (MySqlConnection mConn = new MySqlConnection(ConnectionString)){
    mConn.Open();
    using (MySqlCommand mCmd = new MySqlCommand("select post_title from posts", mConn)){
        using (MySqlDataReader r = mCmd.ExecuteReader()){
            while (r.Read())                        
                Console.WriteLine(r["post_title"].ToString());            
        }
    }                    
}

I like the improved look. I haven't had a chance to test this with transactions or much of anything else for that matter, so just consider this a proof of concept at the moment.

You can download the extension methods to see for yourself: DbExtensions.cs

.NET Rubyisms

I'm still really intrigued by these extension methods + lambda expressions. Check out the following snippet.

public static void times(this int i, Action a)
{
   for (int j = 0; j < i; j++)            
      a(j);              
}

Now I can replace a standard for loop

for(int i=0;i<10;i++){
   doSomething();
}

With this

10.times(i=>doSomething());

I'm not entirely sure how useful it is, but I'm still exploring the new features.

.NET 3.5 Extension Methods

I can't wait to start using extension methods in production. I've already thought of a few great uses for this. For a while I've been jealous of other languages and their seamless integration of regular expressions. Regular expressions are executed against strings, and so I feel it's appropriate to have them married together. With .NET 3.5, I can get a little bit closer with the following class:

    public static class StringExtensions
    {        
        private static Dictionary cache = new Dictionary();
        private static Regex cacheRegex(string r)
        {
            if (!cache.ContainsKey(r))
                cache[r] = new Regex(r, RegexOptions.Compiled);
            return cache[r];
        }

        public static bool IsMatch(this string s, string regex)
        {
            Regex r = cacheRegex(regex);
            return r.IsMatch(s);            
        }
        
        public static MatchCollection Matches(this string s,string regex){
            Regex r = cacheRegex(regex);
            return r.Matches(s);  
        }

        public static Match Match(this string s, string regex)
        {
            Regex r = cacheRegex(regex);
            return r.Match(s);
        }

        public static string[] Split(this string s, string regex)
        {
            Regex r = cacheRegex(regex);
            return r.Split(s);            
        }

        public static string Replace(this string s, string regex,string replacement)
        {
            Regex r = cacheRegex(regex);
            return r.Replace(s, replacement);
        }
    }

Sure, the caching could actually do something more useful, but the idea is there. Now I can do something like this:

"Josh".IsMatch("^J");

To me that is way better than this:

Regex startsWithJ=new Regex("^J");
startsWithJ.IsMatch("Josh");