Extension Methods meet the Database

November 06th, 2007

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. 
            
  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.
    

    
		
    
					
    
    3. 
          

      
  
Leave a reply