public void ExecuteQueries ( IEnumerable <string> servers, string username, string password, string scriptLocation, string outputFolder, IEnumerable <string> databases, bool useTrusted, bool autoFitColumns, int queryTimeoutSeconds, IEnumerable <string> ExcludeQueryNumbers ) { var parser = new QueryFileParser(scriptLocation); var queries = parser.Load(); var serverQueries = queries.Where(q => q.ServerScope).ToList(); var dbQueries = queries.Where(q => !q.ServerScope).ToList(); if (ExcludeQueryNumbers != null) { serverQueries = serverQueries.Where(q => !ExcludeQueryNumbers.Contains(q.QueryNumber)).ToList(); dbQueries = dbQueries.Where(q => !ExcludeQueryNumbers.Contains(q.QueryNumber)).ToList(); } foreach (var servername in servers) { _worksheetNames.Clear(); var outputFilepath = GetOutputFilepath(outputFolder, servername); using (var fs = new FileStream(outputFilepath, FileMode.Create)) { using (var pck = new ExcelPackage(fs)) { var connectionString = GetConnectionStringTemplate(servername, "master", username, password, useTrusted); ExecuteQueriesAndSaveToExcel(pck, connectionString, serverQueries, "", "", autoFitColumns, queryTimeoutSeconds); // Not enough room in Excel WorkSheet tab names for the full database name, so prefix with DB number 1 to N. int databaseNo = 1; foreach (var db in databases) { connectionString = GetConnectionStringTemplate(servername, db, username, password, useTrusted); // TODO: check if database exists... ExecuteQueriesAndSaveToExcel(pck, connectionString, dbQueries, db.Trim(), databaseNo.ToString(), autoFitColumns, queryTimeoutSeconds); databaseNo++; } pck.Save(); } } } }
public void ExecuteQueries( string servername, string username, string password, string scriptLocation, string outputFolder, IList<string> databases, bool useTrusted, bool autoFitColumns, int queryTimeoutSeconds ) { _dictWorksheet.Clear(); string dateString = DateTime.Now.ToString("yyyyMMdd_hhmmss_"); List<SqlQuery> queries = new List<SqlQuery>(); foreach (var file in Directory.EnumerateFiles(scriptLocation, "*.sql")) { var parser = new QueryFileParser(file); queries.AddRange(parser.Load()); } string outputFilepath = GetOutputFilepath(outputFolder, servername, dateString); using (var fs = new FileStream(outputFilepath, FileMode.Create)) { using (var pck = new ExcelPackage(fs)) { string connectionString = GetConnectionStringTemplate(servername, "master", username, password, useTrusted); var serverQueries = queries.Where(q => q.ServerScope).ToList(); ExecuteQueriesAndSaveToExcel(pck, connectionString, serverQueries, "", "", autoFitColumns, queryTimeoutSeconds); if (databases.Count > 0) { int databaseNo = 1; var dbQueries = queries.Where(q => !q.ServerScope).ToList(); foreach (var db in databases) { connectionString = GetConnectionStringTemplate(servername, db, username, password, useTrusted); ExecuteQueriesAndSaveToExcel(pck, connectionString, dbQueries, db.Trim(), databaseNo.ToString(CultureInfo.InvariantCulture), autoFitColumns, queryTimeoutSeconds); databaseNo++; } } foreach (var file in Directory.EnumerateFiles(scriptLocation, "*.ps1")) { ExecutePoShAndSaveToExcel(pck, file, autoFitColumns); } pck.Save(); } } }
public void ExecuteQueries( IList<string> servers, string username, string password, string scriptLocation, string outputFolder, IList<string> databases, bool useTrusted, bool autoFitColumns, int queryTimeoutSeconds ) { var parser = new QueryFileParser(scriptLocation); var queries = parser.Load(); var serverQueries = queries.Where(q => q.ServerScope).ToList(); var dbQueries = queries.Where(q => !q.ServerScope).ToList(); foreach (var servername in servers) { var dateString = DateTime.Now.ToString("yyyyMMdd_hhmmss_"); _dictWorksheet.Clear(); var outputFilepath = GetOutputFilepath(outputFolder, servername, dateString); using (var fs = new FileStream(outputFilepath, FileMode.Create)) { using (var pck = new ExcelPackage(fs)) { var connectionString = GetConnectionStringTemplate(servername, "master", username, password, useTrusted); ExecuteQueriesAndSaveToExcel(pck, connectionString, serverQueries, "", "", autoFitColumns, queryTimeoutSeconds); int databaseNo = 1; foreach (var db in databases) { connectionString = GetConnectionStringTemplate(servername, db, username, password, useTrusted); ExecuteQueriesAndSaveToExcel(pck, connectionString, dbQueries, db.Trim(), databaseNo.ToString(CultureInfo.InvariantCulture), autoFitColumns, queryTimeoutSeconds); databaseNo++; } pck.Save(); } } } }