code snippets for "Batching SqlCommand Queries" post

s1.cs

    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);
        }
    }

s2.cs

                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();

s3.cs

                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
                    }
                }