/// <summary>
/// Executes the SQL command and returns the output in text format.
/// </summary>
private static string ExecuteCommand(MultiShardConnection conn, string commandText)
{
try
{
StringBuilder output = new StringBuilder();
output.AppendLine();
int rowsAffected = 0;
using (MultiShardCommand cmd = conn.CreateCommand())
{
cmd.CommandText = commandText;
cmd.CommandTimeout = s_commandLine.QueryTimeout;
cmd.CommandTimeoutPerShard = s_commandLine.QueryTimeout;
// Execute command and time with a stopwatch
Stopwatch stopwatch = Stopwatch.StartNew();
cmd.ExecutionPolicy = s_commandLine.ExecutionPolicy;
cmd.ExecutionOptions = s_commandLine.ExecutionOptions;
using (MultiShardDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
stopwatch.Stop();
// Get column names
IEnumerable <string> columnNames = GetColumnNames(reader).ToArray();
// Create table formatter
TableFormatter tableFormatter = new TableFormatter(columnNames.ToArray());
// Read results from db
while (reader.Read())
{
rowsAffected++;
// Add the row to the table formatter
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
tableFormatter.AddRow(values);
}
// Write formatter output
output.AppendLine(tableFormatter.ToString());
}
output.AppendLine();
output.AppendFormat("({0} rows affected - {1:hh}:{1:mm}:{1:ss} elapsed)", rowsAffected, stopwatch.Elapsed);
output.AppendLine();
}
return(output.ToString());
}
catch (MultiShardAggregateException e)
{
return(e.ToString());
}
}
public List <string[]> ExecuteMultiShardQuery(ListShardMap <int> shardMap, string credentialsConnectionString)
{
// Get the shards to connect to
IEnumerable <Shard> shards = shardMap.GetShards();
// Create the multi-shard connection
using (MultiShardConnection conn = new MultiShardConnection(shards, credentialsConnectionString))
{
// Create a simple command
using (MultiShardCommand cmd = conn.CreateCommand())
{
// Because this query is grouped by CustomerID, which is sharded,
// we will not get duplicate rows.
cmd.CommandText = @"
SELECT
c.CustomerId,
c.Name AS CustomerName,
o.OrderID AS OrderCount
FROM
dbo.Customers AS c INNER JOIN
dbo.Orders AS o
ON c.CustomerID = o.CustomerID
ORDER BY
1";
// Append a column with the shard name where the row came from
cmd.ExecutionOptions = MultiShardExecutionOptions.IncludeShardNameColumn;
// Allow for partial results in case some shards do not respond in time
cmd.ExecutionPolicy = MultiShardExecutionPolicy.PartialResults;
// Allow the entire command to take up to 30 seconds
cmd.CommandTimeout = 30;
// Execute the command.
// We do not need to specify retry logic because MultiShardDataReader will internally retry until the CommandTimeout expires.
using (MultiShardDataReader reader = cmd.ExecuteReader())
{
// Get the column names
TableFormatter formatter = new TableFormatter(GetColumnNames(reader).ToArray());
int rows = 0;
while (reader.Read())
{
// Read the values using standard DbDataReader methods
object[] values = new object[reader.FieldCount];
reader.GetValues(values);
// Extract just database name from the $ShardLocation pseudocolumn to make the output formater cleaner.
// Note that the $ShardLocation pseudocolumn is always the last column
int shardLocationOrdinal = values.Length - 1;
values[shardLocationOrdinal] = ExtractDatabaseName(values[shardLocationOrdinal].ToString());
// Add values to output formatter
formatter.AddRow(values);
rows++;
}
Console.WriteLine(formatter.ToString());
Console.WriteLine("({0} rows returned)", rows);
return(formatter._rows);
}
}
}
}
