Simple.Data extensions

DataReaderExtensions.cs

using System.Collections.Generic;
using System.Data;
using System.Linq;
using Simple.Data.Ado;

namespace Simple.Data.Sql
{
    public static class DataReaderExtensions
    {
        public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDataReader reader)
        {
            return reader.ToMultipleDictionaries().ToResultSets();
        }

        public static IEnumerable<dynamic> ToRows(this IDataReader reader)
        {
            return reader.ToDictionaries().ToRows();
        }

        public static dynamic ToRow(this IDataRecord record)
        {
            return record.ToDictionary().ToRow();
        }

        private static IEnumerable<IEnumerable<SimpleRecord>> ToResultSets(this IEnumerable<IEnumerable<IDictionary<string, object>>> data)
        {
            return data.Select(ToRows).ToArray().AsEnumerable();
        }

        private static IEnumerable<SimpleRecord> ToRows(this IEnumerable<IDictionary<string, object>> rsData)
        {
            return rsData.Select(ToRow).ToArray().AsEnumerable();
        }

        private static SimpleRecord ToRow(this IDictionary<string, object> rowData)
        {
            return new SimpleRecord(rowData);
        }
    }
}

DbCommandBuilder.cs

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

namespace Simple.Data.Sql
{
    public class DbCommandBuilder
    {

        public IDbCommand BuildCommand(IDbConnection connection, string sql, IEnumerable<KeyValuePair<string, object>> parameters)
        {
            return BuildCommand(connection, sql,
                                parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
        }

        public IDbCommand BuildCommand(IDbConnection connection, string sql, IDictionary<string, object> parameters)
        {
            var cmd = connection.CreateCommand();
            cmd.Connection = connection;
            cmd.CommandText = sql;

            parameters.ToList()
                .ForEach(cmd.AddParameter);

            return cmd;
        }

    }
}

DbCommandExtensions.cs

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

namespace Simple.Data.Sql
{
    public static class DbCommandExtensions
    {
        public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbCommand command)
        {
            using (var rdr = command.ExecuteReader())
            {
                return rdr.ToResultSets();
            }
        }

        public static IEnumerable<dynamic> ToRows(this IDbCommand command)
        {
            using (var rdr = command.ExecuteReader())
            {
                return rdr.ToRows();
            }
        }

        public static dynamic ToRow(this IDbCommand command)
        {
            using (var rdr = command.ExecuteReader())
            {
                return rdr.ToRow();
            }
        }

        public static void AddParameter(this IDbCommand command, KeyValuePair<string, object> parameterData)
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = parameterData.Key;
            parameter.Value = parameterData.Value ?? DBNull.Value;
            command.Parameters.Add(parameter);
        }

    }
}

DbConnectionExtensions.cs

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

namespace Simple.Data.Sql
{
    public static class DbConnectionExtensions
    {

        private const string SqlEmptyOrWhitespace = "Sql statement can't be empty or whitespace.";

        public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbConnection connection, string sql,
                                                                    IDictionary<string, object> parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (sql == null) throw new ArgumentNullException("sql");
            if (string.IsNullOrWhiteSpace(sql))
                throw new ArgumentException(SqlEmptyOrWhitespace, "sql");

            parameters = parameters ?? new Dictionary<string, object>();
            var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
            return connection.WithOpenConnection(() => cmd.ToResultSets());
        }

        public static IEnumerable<IEnumerable<dynamic>> ToResultSets(this IDbConnection connection, string sql,
                                                                    params KeyValuePair<string, object>[] parameters)
        {
            parameters = parameters ?? new KeyValuePair<string, object>[0];
            return connection.ToResultSets(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
        }

        public static IEnumerable<dynamic> ToRows(this IDbConnection connection, string sql,
                                                  IDictionary<string, object> parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (sql == null) throw new ArgumentNullException("sql");
            if (string.IsNullOrWhiteSpace(sql))
                throw new ArgumentException(SqlEmptyOrWhitespace, "sql");

            parameters = parameters ?? new Dictionary<string, object>();
            var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
            return connection.WithOpenConnection(() => cmd.ToRows());
        }

        public static IEnumerable<dynamic> ToRows(this IDbConnection connection, string sql,
                                                  params KeyValuePair<string, object>[] parameters)
        {
            parameters = parameters ?? new KeyValuePair<string, object>[0];
            return connection.ToRows(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
        }

        public static dynamic ToRow(this IDbConnection connection, string sql, IDictionary<string, object> parameters)
        {
            if (connection == null) throw new ArgumentNullException("connection");
            if (sql == null) throw new ArgumentNullException("sql");
            if (string.IsNullOrWhiteSpace(sql))
                throw new ArgumentException(SqlEmptyOrWhitespace, "sql");

            parameters = parameters ?? new Dictionary<string, object>();
            var cmd = new DbCommandBuilder().BuildCommand(connection, sql, parameters);
            return connection.WithOpenConnection(() => cmd.ToRow());
        }

        public static dynamic ToRow(this IDbConnection connection, string sql,
                                    params KeyValuePair<string, object>[] parameters)
        {
            parameters = parameters ?? new KeyValuePair<string, object>[0];
            return connection.ToRow(sql, parameters.ToDictionary(kv => kv.Key, kv => kv.Value));
        }

        private static T WithOpenConnection<T>(this IDbConnection connection, Func<T> func)
        {
            if (connection.State != ConnectionState.Closed)
                return func();

            try
            {
                connection.Open();
                return func();
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

Program.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

namespace Simple.Data.Sql
{
    class Program
    {
        static void Main(string[] args)
        {

            const string connStr = @"Server=.\SQLExpress;Database=Chinook;Integrated Security=SSPI";

            const string sql =@"
                -- Data Query
                SELECT TOP 5 Album.AlbumId, Artist.Name, Album.Title
                    FROM Artist 
	                    INNER JOIN Album 
		                    ON Album.ArtistId = Artist.ArtistId
                WHERE Album.Title LIKE @p0
                ORDER BY Album.Title, Artist.Name;

                -- Count Query
                SELECT COUNT(*) AS [Count] 
                    FROM Artist 
                        INNER JOIN Album 
                            ON Album.ArtistId = Artist.ArtistId
                WHERE Album.Title LIKE @p0";

            var data = new SqlConnection(connStr)
                .ToResultSets(sql, new KeyValuePair<string, object>("p0", "a%"))
                .ToArray();

            var rows = data.ElementAt(0).ToArray();
            var count = (long)data.ElementAt(1).Single().Count;

            Console.WriteLine("Displaying rows {0} through {1} of {2}", rows.Any() ? 1 : 0, rows.Length, count);
            Console.WriteLine();

            foreach (var row in rows)
            {
                Console.WriteLine("{0} by {1}", row.Title, row.Name);
            }

            Console.WriteLine();
            Console.WriteLine("Press any key to quit");
            Console.ReadKey();
        }
    }
}