private static string GetFilePath(string database, CustomQuery query, bool multipleDatabases, string filename)
{
var filePath = "results.txt";
if (!string.IsNullOrWhiteSpace(filename))
{
filePath = multipleDatabases
? string.Format("{0} {1} {2}{3}", database, Path.GetFileNameWithoutExtension(filename),
DateTime.Now.ToString("yyyy-MM-dd HH-mm"), Path.GetExtension(filename))
: filename;
}
return(Path.Combine(query.Directory, filePath));
}
private bool ExecuteQuery(string database, CustomQuery query, bool multipleDatabases)
{
using (var dc = new SqlConnection(Framework.Properties.Settings.Default.AryaDbConnectionString))
{
dc.Open();
dc.ChangeDatabase(database);
//First execute the query generator sql
var commandQuery = new SqlCommand(query.QueryGeneratorSql, dc)
{
CommandTimeout = 0
};
Queries.GlobalParameters.ForEach(p => commandQuery.Parameters.AddWithValue(p.Name, p.Value));
query.Parameters.ForEach(p => commandQuery.Parameters.AddWithValue(p.Name, p.Value));
var logFilePath = GetFilePath(string.Empty, query, false, "outerQuery.sql");
using (TextWriter file = new StreamWriter(logFilePath, true))
{
file.WriteLine("--{0} {1}", DateTime.Now, query.Name);
file.WriteLine("USE [{0}]", database);
file.WriteLine(commandQuery.CommandText);
}
var sqlTable = new DataTable("SqlQueries");
SqlDataReader queries;
try
{
queries = commandQuery.ExecuteReader();
}
catch (SqlException ex)
{
var message = ex.Errors.Cast <SqlError>()
.Aggregate("Sql Exception: ",
(current, error) =>
current + string.Format("{0} at line {1}. ", error.Message, error.LineNumber));
Summary.SetError(new Exception(message, ex));
return(false);
}
using (queries)
{
if (queries.HasRows)
{
sqlTable.Load(queries);
}
else
{
throw new Exception("No queries were generated by the SqlGenerator");
}
}
using (TextWriter file = new StreamWriter(logFilePath, true))
file.WriteLine("--{0} {1} Queries Generated", DateTime.Now, sqlTable.Rows.Count);
var queryFilePath = GetFilePath(string.Empty, query, false, "innerQueries.sql");
var iCtr = 0;
foreach (DataRow sqlRow in sqlTable.Rows)
{
var filePath = GetFilePath(database, query, multipleDatabases,
sqlTable.Columns.Count == 1 ? query.Filename : sqlRow[1].ToString());
//Write (append) the query to a log/sql file
var queryText = sqlRow[0].ToString();
using (TextWriter file = new StreamWriter(queryFilePath, true))
{
file.WriteLine("--{0} {1}", DateTime.Now,
query.Name + (sqlTable.Rows.Count == 1 ? string.Empty : "[" + (++iCtr) + "]"));
file.WriteLine("USE [{0}]", database);
file.WriteLine(queryText);
}
if (Queries.GenerateQueriesOnly)
{
//Do not execute the inner queries, just generate them
continue;
}
using (TextWriter file = new StreamWriter(logFilePath, true))
file.WriteLine("--{0} Running Query {1}", DateTime.Now, iCtr);
var start = DateTime.Now;
if (!ProcessQuery(dc, queryText, filePath))
{
return(false);
}
var timeTaken = DateTime.Now.Subtract(start).ToString(@"hh\:mm\:ss");
using (TextWriter file = new StreamWriter(logFilePath, true))
{
file.WriteLine("--{0} Time taken: {1}", DateTime.Now, timeTaken);
file.WriteLine();
}
}
}
return(true);
}