gistfile1.txt

// Slapped together from a StackOverflow thread with "LendReader" pulled in
// from "Design Patterns in C#" by Steven John Metsker 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;


    public class DataServices {
        private static string _connectionString;
        public static string ConnectionString {
            get { return _connectionString ?? ConfigurationManager.ConnectionStrings["XX"].ConnectionString; } 
            set { _connectionString = value; } 
        }
        private static string _provider;
        public static string Provider {
            get { return _provider ?? ConfigurationManager.ConnectionStrings["XX"].ProviderName; }
            set { _provider = value; }
        }

        public static object LendReader(string sql, Func<IDataReader,object> borrower) {
            using (DbConnection cn = CreateConnection()) 
            using(var cmd = CreateCommand(sql, cn))
            using (var rdr = cmd.ExecuteReader()) {
                return borrower(rdr);
            }
        }
        public static IEnumerable<IDataRecord> LendRecord(string sql) {
            using (DbConnection cn = CreateConnection()) 
            using( var cmd = CreateCommand(sql, cn) ) 
            using (var rdr = cmd.ExecuteReader()) 
            while(rdr.Read()) { yield return rdr; }
        }

        public static DbConnection CreateConnection() { return CreateConnection(true); }
        public static DbConnection CreateConnection(bool withOpen) {
            var cn = GetFactory().CreateConnection();
            cn.ConnectionString = ConnectionString;
            if(withOpen) cn.Open();
            return cn;
        }
        public static DbCommand CreateCommand(string sql, DbConnection cxn) {
            var cmd = GetFactory().CreateCommand();
            cmd.Connection = cxn;
            cmd.CommandText = sql;
            return cmd;
        }
        public static DbProviderFactory GetFactory() {
            return DbProviderFactories.GetFactory(Provider);
        }
    }