Code snippets for the "The Select Command Batcher" post

s1.cs

        public IEnumerable<Product> TransformTableToListOfProducts(DataTable table)
        {
            var products = new List<Product>();
 
            foreach (DataRow row in table.Rows)
            {
                products.Add(TransformRowToProduct(row));
            }
 
            return products;
        }
 
        public Product TransformRowToProduct(DataRow row)
        {
            // normally you'd put the values of the row in the product entity
            return new Product();
        }

s10.cs

    public class SelectCommandBatcher : Disposable, ISelectCommandBatcher
    {
        private readonly SqlConnection sqlConnection;
        private readonly Dictionary<string, int> queryResultPositions;
        private readonly List<SqlCommand> commandList;
 
        private DataSet results;
 
        public SelectCommandBatcher(SqlConnection sqlConnection)
        {
            this.sqlConnection = sqlConnection;
            queryResultPositions = new Dictionary<string, int>();
            commandList = new List<SqlCommand>();
        }
 
        protected override void DisposeManagedResources()
        {
            if (results != null) results.Dispose();
            // don't dispose the connection, we didn't create it...
        }
 
        public void AddCommand(string key, SqlCommand command)
        {
            commandList.Add(command);       
            queryResultPositions.Add(key, commandList.Count - 1);
        }
 
        public IEnumerable<T> GetEnumerableResult<T>(string key,
            Func<DataTable, IEnumerable<T>> transformTableToListOfEntitiesMethod)
        {
            ExecuteQueriesIfNecessary();
            return transformTableToListOfEntitiesMethod(GetResultTable(key));
        }
 
        public IEnumerable<T> GetEnumerableResult<T>(string key,
            Func<DataRow, T> transformRowToEntityMethod)
        {
            ExecuteQueriesIfNecessary();
 
            return GetEnumerableResult(key, table =>
                {
                    var resultList = new List<T>();
 
                    foreach (DataRow row in table.Rows)
                    {
                        resultList.Add(transformRowToEntityMethod(row));
                    }
 
                    return resultList;
                });
        }
 
        public T GetSingleResult<T>(string key)
        {
            ExecuteQueriesIfNecessary();
            return (T)GetResultTable(key).Rows[0].ItemArray[0];
        }
 
        public T GetSingleResult<T>(string key, Func<DataRow, T> transformRowToEntityMethod)
        {
            ExecuteQueriesIfNecessary();
 
            var resultTable = GetResultTable(key);
 
            if (resultTable.Rows.Count > 0)
            {
                return transformRowToEntityMethod(resultTable.Rows[0]);
            }
            else
            {
                return default(T);
            }
        }
 
        public DataTable GetResultAsDataTable(string key)
        {
            ExecuteQueriesIfNecessary();
            return GetResultTable(key);
        }
 
        private void ExecuteQueriesIfNecessary()
        {
            if (results == null)
            {
                var combinedCommand = new SelectCommandCombiner(commandList).CreateCombinedCommand();
                combinedCommand.Connection = sqlConnection;
 
                using (var adapter = new SqlDataAdapter(combinedCommand))
                {
                    results = new DataSet();
                    adapter.Fill(results);
                }
            }
        }
 
        private DataTable GetResultTable(string key)
        {
            return results.Tables[queryResultPositions[key]];
        }
    }

s2.cs

commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
var results = commandBatcher.GetEnumerableResult<Product>("products", TransformTableToListOfProducts);

s3.cs

IEnumerable<T> GetEnumerableResult<T>(string key, Func<DataTable, IEnumerable<T>> transformTableToListOfEntitiesMethod);

s4.cs

commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
var results = commandBatcher.GetEnumerableResult<Product>("products", TransformRowToProduct);

s5.cs

IEnumerable<T> GetEnumerableResult<T>(string key, Func<DataRow, T> transformRowToEntityMethod);

s6.cs

commandBatcher.AddCommand("firstProduct", new SqlCommand("SELECT TOP 1 * FROM dbo.Products"));
var product = commandBatcher.GetSingleResult<Product>("firstProduct", TransformRowToProduct);

s7.cs

commandBatcher.AddCommand("productCount", new SqlCommand("SELECT COUNT(*) FROM dbo.Products"));
var count = commandBatcher.GetSingleResult<int>("productCount");

s8.cs

commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
var dataTable = commandBatcher.GetResultAsDataTable("products");

s9.cs

commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
commandBatcher.AddCommand("supplier", new SqlCommand("SELECT * FROM dbo.Suppliers"));
 
var products = commandBatcher.GetEnumerableResult<Product>("products", TransformRowToProduct);
var suppliers = commandBatcher.GetEnumerableResult<Supplier>("supplier", TransformRowToSupplier);