Extension Methods meet the Database

November 06th, 2007 * Comments(2)

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
  BOO: First impressions 
 September 23rd, 2007 * Comments(0) 
I keep seeing mention of this obscure .NET language called BOO and I had to check it out.  I haven't delved into it very deep yet, but I like what I'm seeing.  Of course, I'll actually need to write some code with it in order to really get a feel for it.  Regardless, I wanted to touch on a few things that spoke to me with the syntax.
  • It has a python feel to it.  Years ago in college we did some code in Python.  I really like how python gets rid of the braces and instead uses indention as it's scope identification.  That style of coding forces a certain aesthetic which I like.
  • Built-in literals.  Seriously, if I have to type "Regex something=new Regex(@"^\d{10}");" one more damn time, I'm going to scream.  Why can't the c# spec support "/^\d{10}/" just like other languages.  Oh yes, the shorter syntax for arrays, lists, and hashes are nice too.
  • String Formatting.  It's like a mini template system.  "Hello, my name is ${MyName}" looks so much nicer than String.Format("Hello, my name is {0}",MyName).
  • Syntactic Macros and Attributes.  I'm not sure what to make of this, but it looks insanely powerful.  Anything that means I can write less code sounds great to me! 
Once I have an opportunity to write some code with this, I'll report back my findings.  I like code brevity, and I am encouraged by this find.
  .NET Rubyisms 
 August 06th, 2007 * Comments(0) 
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 
 July 16th, 2007 * Comments(1) 
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");