private static async Task <QueryResult> SingleQuery(DatabaseDefinition db, string query) { var result = new QueryResult { Messages = new List <string>() }; // var isDbNameUnique = databases.Count(d => d.DatabaseName == db.DatabaseName) == 1; // var friendlyDbName = db.DatabaseName; // if (!isDbNameUnique) // { // friendlyDbName += " - " + db.Server.DisplayName; // } var friendlyDbName = $"{db.DatabaseName} - {db.Server.DisplayName}"; var builder = new SqlConnectionStringBuilder(db.Server.ConnectionString) { InitialCatalog = db.DatabaseName }; using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { try { Debug.WriteLine($"connecting to {friendlyDbName}"); await connection.OpenAsync(); var command = connection.CreateCommand(); command.CommandText = query; Debug.WriteLine("querying " + friendlyDbName); using (var dataReader = await command.ExecuteReaderAsync()) { Debug.WriteLine($"reading results from {friendlyDbName}"); result.ResultTable = ReadTable(dataReader); result.ResultTable.TableName = friendlyDbName; Debug.WriteLine($"finished reading results from {friendlyDbName}"); // Add a column that shows which DB it came from. var sourceCol = new DataColumn("Source Database", typeof(string)); result.ResultTable.Columns.Add(sourceCol); foreach (var col in result.ResultTable.Columns.Cast <DataColumn>().ToList()) { if (col != sourceCol) { col.SetOrdinal(col.Ordinal + 1); } } sourceCol.SetOrdinal(0); foreach (var row in result.ResultTable.Rows.Cast <DataRow>()) { row[sourceCol] = friendlyDbName; } } } catch (Exception ex) { result.Messages.Add($"Error on {friendlyDbName}: {ex.Message}"); } } return(result); }
private static async Task <QueryResult> SingleQuery(DatabaseDefinition db, string query, int sqlTimeout) { var result = new QueryResult { Messages = new List <string>() }; var friendlyDbName = db.DatabaseName + " - " + db.Server.DisplayName; var builder = new SqlConnectionStringBuilder(db.Server.ConnectionString); builder.InitialCatalog = db.DatabaseName; using (SqlConnection connection = new SqlConnection(builder.ConnectionString)) { // Adds poor man support for SMSS 'GO' keyword to split sql into separate command batches. // In this mode, the last sql statement is expected to be the one that returns the results. query += "\nGO"; // make sure last batch is executed. var sql = query .Split(new string[2] { "\n", "\r" }, StringSplitOptions.RemoveEmptyEntries) .Aggregate(new SqlTextState { CurrentCommand = "", CompleteCommands = new List <string>() }, (s, line) => { if (line.ToUpperInvariant().Trim() == "GO") { s.CompleteCommands.Add(s.CurrentCommand); s.CurrentCommand = ""; } else { s.CurrentCommand += line + "\n"; } return(s); }); try { Debug.WriteLine("connecting to " + friendlyDbName); await connection.OpenAsync(); int i = 0; foreach (var sqlCommand in sql.CompleteCommands) { var command = connection.CreateCommand(); command.CommandText = sqlCommand; command.CommandTimeout = sqlTimeout; Debug.WriteLine("querying " + friendlyDbName); if (i < sql.CompleteCommands.Count - 1) // if not last one { await command.ExecuteNonQueryAsync(); } else { using (var dataReader = await command.ExecuteReaderAsync()) { Debug.WriteLine("reading results from " + friendlyDbName); result.ResultTable = ReadTable(dataReader); result.ResultTable.TableName = friendlyDbName; Debug.WriteLine("finished reading results from " + friendlyDbName); // Add columns that shows which DB it came from. var sourceServerCol = new DataColumn("Server", typeof(string)); var sourceDatabaseCol = new DataColumn("Database", typeof(string)); result.ResultTable.Columns.Add(sourceServerCol); result.ResultTable.Columns.Add(sourceDatabaseCol); foreach (var col in result.ResultTable.Columns.Cast <DataColumn>().ToList()) { if (col != sourceServerCol && col != sourceDatabaseCol) { col.SetOrdinal(col.Ordinal + 2); } } sourceServerCol.SetOrdinal(0); sourceDatabaseCol.SetOrdinal(1); foreach (var row in result.ResultTable.Rows.Cast <DataRow>()) { row[sourceServerCol] = db.Server.DisplayName; row[sourceDatabaseCol] = db.DatabaseName; } } } i++; } } catch (Exception ex) { result.Messages.Add($"Error on {friendlyDbName}: {ex.Message}"); } } return(result); }