Mapping SQL stored procedure query results to an enumerable.

CategoryDataAccess.cs

using System;
using System.Data;
using ClassicDalHelpersSample.Business.Entities;
using System.Collections.Generic;
using System.Linq;
using Snikt;

namespace Snikt.Mock
{
    public sealed class CategoryDataAccess
    {
        private readonly MiniNWDataContext db;

        public CategoryDataAccess(MiniNWDataContext dbContext)
        {
            db = dbContext;
        }

        public IEnumerable<Product> GetAllProducts()
        {
            using (IDbCommand command = db.CreateStoreCommand("dbo.GetAllProducts"))
            {
                using (IDataReader reader = command.ExecuteReader())
                {
                    return reader.Materialize<Product>(Materialize);
                }
            }
        }

        private Product Materialize(IDataRecord record)
        {
            Product prod = record.Field<DateTime?>("discontinued_date").HasValue ?
                new DiscontinuedProduct
                {
                    Id = record.Field<int>("pid"),
                    Name = record.Field<string>("name"),
                    CategoryId = record.Field<int>("cid"),
                    DiscontinuedDate = record.Field<DateTime>("discontinued_date")
                } :
                new Product
                {
                    Id = record.Field<int>("pid"),
                    Name = record.Field<string>("name"),
                    CategoryId = record.Field<int>("cid")
                };
            
            return prod;
        }
    }
}

DataExtensions.cs

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

namespace Snikt
{
    public static class DataExtensions
    {
        public static IEnumerable<T> Materialize<T>(this IDataReader reader, Func<IDataRecord, T> materializer)
        {
            List<T> list = new List<T>();
            while (reader.Read())
            {
                list.Add(materializer(reader));
            }
            return list;
        }
        
        public static T Field<T>(this IDataRecord record, string name)
        {
            int ordinal = record.GetOrdinal(name);
            return Field<T>(record, ordinal);
        }
        
        public static T Field<T>(this IDataRecord record, int ordinal)
        {
            object value = record.IsDBNull(ordinal) ? null : record.GetValue(ordinal);
            return (T)value;
        }
        
        public static void AddParameter<T>(this IDbCommand command, string name, T value)
        {
            AddParameter<T>(command, name, value, ParameterDirection.Input);
        }
        public static void AddParameter<T>(this IDbCommand command, string name, T value, ParameterDirection direction)
        {
            // HINT: verifies the input arguments. throw relevent exceptions if error.
            InputArg.IsNull("IDbCommand command", command);
            InputArg.IsNullOrWhiteSpace("string name", name);
            
            IDbDataParameter p = command.CreateParameter();
            p.ParameterName = name;
            p.Value = value;
            p.Direction = direction;
            
            // HINT: associate the parameter with command.
            command.Parameters.Add(p);
        }
        
        public static T GetParameter<T>(this IDbCommand command, string name)
        {
            return (T)command.Parameters
                .Cast<IDbDataParameter>()
                .Single(p => p.ParameterName == name && p.Direction != ParameterDirection.Input)
                .Value;
        }
        
        public static IDbCommand CreateStoreCommand(this DataContext db, string commandText)
        {
            return CreateStoreCommand(db.Connection, commandText);
        }
        
        public static IDbCommand CreateStoreCommand(this IDbConnection connection, string commandText)
        {
            IDbCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.CommandType = CommandType.StoredProcedure;
            command.Connection.Open();
            
            return command;
        }
    }
}

dbo.GetAllProducts.sql

CREATE PROC dbo.GetAllProducts
AS
BEGIN
    SELECT pid, name, discontinued_date, cid
    FROM dbo.Products AS p;
END
GO;

In order to increase the performance, we can make the Materialize(...) method in CategoryDataAccess.cs L30 to static method.