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