The Select Command Batcher
Posted by Davy Brion on September 3rd, 2008
As promised, this post describes a way to make batching of select queries through SqlCommands as easy to use as my QueryBatcher for NHibernate.
So naturally, i came up with the SelectCommandBatcher class (i know, i really need to come up with better names). It’s very similar in usage to the QueryBatcher for NHibernate. Obviously, there is one important difference: NHibernate takes care of transforming the database result into entity objects automagically. For the SelectCommandBatcher, you either have to provide some functionality that takes care of mapping the database results to objects, or you can access the results through DataTables (yikes!).
Let’s just get straight to the code, shall we? For the first couple of examples, i’ll focus specifically on how you can retrieve your results so each example only uses one command. At the end of the examples, there is of course one example that illustrates how you can add multiple SqlCommands and fetch the results in one roundtrip (which is after all the purpose of the SelectCommandBatcher class).
Suppose you have some code that is able to transform the content of a DataTable (or a DataRow) to a List of entities (or one entity):
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();
}
Obviously, this simply returns a new Product for each DataRow in the DataTable. If this were real code, you’d have to put the data of the DataRow into the Product instance. Feel free to consider me lazy 
With the SelectCommandBatcher, you can now do this:
commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
IEnumerable<Product> results =
commandBatcher.GetEnumerableResult<Product>("products", TransformTableToListOfProducts);
Notice how the TransformTableToListOfProducts method is passed to the GetEnumerableResult method as a parameter. How does it work? It’s pretty simple really: The GetEnumerableResult method’s definition looks like this:
IEnumerable<T> GetEnumerableResult<T>(string key,
Func<DataTable, IEnumerable<T>> transformTableToListOfEntitiesMethod);
Basically, this method requires you to provide a delegate which accepts a DataTable as its sole incoming parameter, and returns an IEnumerable of T with T being any Type your code transforms the result to.
What if your Data Access Layer only has methods to transform individual rows to entities? No problem:
commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
IEnumerable<Product> results = commandBatcher.GetEnumerableResult<Product>("products", TransformRowToProduct);
This code uses an overload of the GetEnumerableResult method that looks like this:
IEnumerable<T> GetEnumerableResult<T>(string key,
Func<DataRow, T> transformRowToEntityMethod);
You just need to pass a delegate which takes a DataRow as its sole incoming parameter, and returns an instance of T with T being any Type your code transforms the result to. The delegate that gets passed in will be called for every DataRow in the DataTable which contains the results of that specific select command.
Or if your query only returns a single row, you can do this:
commandBatcher.AddCommand("firstProduct", new SqlCommand("SELECT TOP 1 * FROM dbo.Products"));
Product product = commandBatcher.GetSingleResult<Product>("firstProduct", TransformRowToProduct);
Scalar values you say? How about this:
commandBatcher.AddCommand("productCount", new SqlCommand("SELECT COUNT(*) FROM dbo.Products"));
int count = commandBatcher.GetSingleResult<int>("productCount");
Or if you’re old-school (sorry, couldn’t resist) and just want the DataTable, you can use this:
commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
DataTable table = commandBatcher.GetResultAsDataTable("products");
And of course, here’s how you can execute multiple select queries in one roundtrip:
commandBatcher.AddCommand("products", new SqlCommand("SELECT * FROM dbo.Products"));
commandBatcher.AddCommand("supplier", new SqlCommand("SELECT * FROM dbo.Suppliers"));
IEnumerable<Product> products =
commandBatcher.GetEnumerableResult<Product>("products", TransformRowToProduct);
IEnumerable<Supplier> suppliers =
commandBatcher.GetEnumerableResult<Supplier>("supplier", TransformRowToSupplier);
The queries are executed as soon as you try to retrieve the first result.
That’s all pretty easy to use right? So how does it work? Here’s the code:
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]];
}
}
I’ve also added it to my Library, although not yet in the downloadable file. You can find the code in the svn repo though.
September 3rd, 2008 at 3:10 pm
[...] The Select Command Batcher (Davy Brion) [...]
September 8th, 2008 at 1:24 am
[...] The Select Command Batcher [...]