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