protected override string[] LowThresholdColumns(DTO.QueryComposer.QueryComposerResponseDTO response) { List <string> ltCols = new List <string>(); foreach (IEnumerable <Dictionary <string, object> > table in response.Results) { Dictionary <string, object> row = table.FirstOrDefault(); if (row != null && row.Count > 0) { try { foreach (string column in row.Keys) { if (SummaryQueryUtil.IsCheckedColumn(column)) { ltCols.Add(column); var depCols = SummaryQueryUtil.GetDependentComputedColumns(column, row); if (depCols != null && depCols.Count > 0) { ltCols.AddRange(depCols); } } } } catch { } } } return(ltCols.ToArray()); }
protected IEnumerable <Dictionary <string, object> > ExecuteQuery(string query, List <DTO.QueryComposer.QueryComposerResponseErrorDTO> errors, bool viewSQL) { List <Dictionary <string, object> > results = new List <Dictionary <string, object> >(); List <string> ltCols = new List <string>(); System.Data.IDbConnection conn = null; try { if (!viewSQL) { conn = Utilities.OpenConnection(settings, logger); using (var cmd = conn.CreateCommand()) { cmd.CommandText = query; cmd.CommandTimeout = Convert.ToInt32(settings.GetSetting("CommandTimeout", 120)); logger.Debug("Executing query:" + Environment.NewLine + query); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Dictionary <string, object> row = new Dictionary <string, object>(); for (int i = 0; i < reader.FieldCount; i++) { row.Add(reader.GetName(i), reader.GetValue(i).ConvertDBNullToNull()); } results.Add(row); } } var testRow = results.FirstOrDefault(); if (testRow != null && testRow.Count > 0) { try { foreach (string column in testRow.Keys) { if (SummaryQueryUtil.IsCheckedColumn(column)) { ltCols.Add(column); var depCols = SummaryQueryUtil.GetDependentComputedColumns(column, testRow); if (depCols != null && depCols.Count > 0) { ltCols.AddRange(depCols); } } } } catch { } } var columnNames = ltCols.ToArray(); if (columnNames != null && columnNames.Length != 0 && _lowThresholdValue.HasValue) { foreach (Dictionary <string, object> row in results) { try { foreach (string column in columnNames) { object currentValue; if (row.TryGetValue(column, out currentValue)) { double value = Convert.ToDouble(currentValue); if (value > 0 && value < _lowThresholdValue) { row[LowThresholdColumnName] = true; } } } } catch { } } } } } else { Dictionary <string, object> row = new Dictionary <string, object>(); row.Add("SQL", query); results.Add(row); } } catch (Exception ex) { errors.Add(new DTO.QueryComposer.QueryComposerResponseErrorDTO { Code = "999", Description = ex.UnwindException(true) }); } finally { if (conn != null) { conn.Dispose(); conn = null; } } return(results); }