Loading ...Batching SqlCommand Queries
Posted by Davy Brion on August 31st, 2008
As you know, i always like to reduce unnecessary roundtrips. With my Request/Response service layer and my QueryBatcher for NHibernate, it’s trivially easy to do so. But what if you’re in a situation where you can’t use NHibernate and are stuck with low-level SqlCommands? It’s actually not hard to enable batching those (select) queries either. (note: for insert/update/delete Commands, there is a better way).
People with a lot of straight-up ADO.NET experience probably already know this, but you can simply combine your select statements into one SqlCommand. When you execute that command, you get the results to each of the queries that was in the command. So far, nothing new here. I’m currently using a Data Access Layer at work which creates SqlCommand objects for select queries that you can build up through an API. Obviously, i’d like a way to use each query in whatever way is best for the specific scenario i’m working on. I basically want to be able to execute the SqlCommand objects as a stand-alone query, or in a batch with other queries, without having to modify the code that creates the SqlCommand objects.
So i wrote a SelectCommandCombiner class which allows you to combine multiple SqlCommands into one SqlCommand, while making sure that none of the parameters conflict with each other. Here’s the code:
public class SelectCommandCombiner
{
private readonly List<SqlCommand> commands = new List<SqlCommand>();
public SelectCommandCombiner() : this(new SqlCommand[0]) { }
public SelectCommandCombiner(IEnumerable<SqlCommand> commandsToCombine)
{
commands = new List<SqlCommand>();
if (commandsToCombine != null)
{
AddCommands(commandsToCombine);
}
}
public void AddCommands(IEnumerable<SqlCommand> commandsToAdd)
{
commands.AddRange(commandsToAdd);
}
public SqlCommand CreateCombinedCommand()
{
var combinedCommand = new SqlCommand();
var combinedCommandText = new StringBuilder();
var queryIndex = 0;
foreach (var currentCommand in commands)
{
var currentCommandText = new StringBuilder(currentCommand.CommandText);
CreateUniqueParameters(currentCommand, currentCommandText, combinedCommand, queryIndex);
combinedCommandText.Append(currentCommandText + “;” + System.Environment.NewLine);
queryIndex++;
}
combinedCommand.CommandText = combinedCommandText.ToString();
return combinedCommand;
}
private static void CreateUniqueParameters(SqlCommand currentCommand, StringBuilder currentCommandText,
SqlCommand combinedCommand, int queryIndex)
{
foreach (SqlParameter parameter in currentCommand.Parameters)
{
var clonedParameter = CloneParameter(parameter);
MakeParameterNameUnique(queryIndex, clonedParameter);
combinedCommand.Parameters.Add(clonedParameter);
ReplaceOldNameWithNewName(parameter, clonedParameter, currentCommandText);
}
}
private static SqlParameter CloneParameter(ICloneable parameter)
{
return (SqlParameter)parameter.Clone();
}
private static void MakeParameterNameUnique(int queryIndex, SqlParameter clonedParameter)
{
var modifiedParameterName = queryIndex + “_” + clonedParameter.ParameterName;
clonedParameter.ParameterName = modifiedParameterName;
}
private static void ReplaceOldNameWithNewName(SqlParameter parameter, SqlParameter clonedParameter,
StringBuilder currentCommandText)
{
currentCommandText.Replace(“@” + parameter.ParameterName, “@” + clonedParameter.ParameterName);
}
}
Now execute the command returned from the CreateCombinedCommand method and you’ll get all of the results in one roundtrip. There are a couple of ways to deal with the results… you could simply use a DataReader to loop through all the rows of all the result sets:
var reader = combinedCommand.ExecuteReader();
do
{
while (reader.Read())
{
// do something with the values in the current row
}
}
while (reader.NextResult()); // this moves to the next result set
reader.Close();
Or you could use a SqlDataAdapter to fill a DataSet:
var adapter = new SqlDataAdapter(combinedCommand);
var dataSet = new DataSet();
adapter.Fill(dataSet);
foreach (DataTable table in dataSet.Tables)
{
foreach (DataRow row in table.Rows)
{
// do something with the values
}
}
In a future post, i’ll try to make this as easy to use as the QueryBatcher for NHibernate.
September 2nd, 2008 at 9:20 am
[...] Batching SqlCommand Queries - Davy Brion shares a class that combines multiple SQL statements to allow them to be batched into a single execution on the database server, avoiding the additional cost of the round trips back and forth. Davy also highlights a similar library he created to allow NHibernate to do similar. [...]
September 3rd, 2008 at 12:32 pm
[...] Batching SqlCommand Queries [...]