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

2 Comments so far

  1. Probashi @ November 8th, 2007

    In both the code you are using ‘r[“post_title”].ToString()’ which is not type safe.

    Is it possible to come up with something like:

    r.post_title (type safe object and property)???

    —————————

    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());
    }
    }
    }

  2. josh @ November 8th, 2007

    For type safety you would need some sort of ORM solution or datasets. This was merely an exercise to show off the power of extension methods.

Leave a reply