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