public async Task <RuleExecutionLogDetailBO> ExecutionDiagnosticSqlByLogIdAsync(int id) { RuleExecutionLogDetailBO result = new RuleExecutionLogDetailBO { Columns = new List <string>(), Rows = new List <Dictionary <string, string> >() }; var ruleExecutionLog = await _queriesRuleExecutionLog.GetAsync(id); if (ruleExecutionLog != null) { var existDatabaseEnvironment = await _queriesDatabaseEnvironments.GetAsync(ruleExecutionLog.DatabaseEnvironmentId); DatabaseEnvironmentBO envBO = new DatabaseEnvironmentBO { Database = existDatabaseEnvironment.Database, DataSource = existDatabaseEnvironment.DataSource, ExtraData = existDatabaseEnvironment.ExtraData, Password = existDatabaseEnvironment.Password, SecurityIntegrated = existDatabaseEnvironment.SecurityIntegrated, TimeoutInMinutes = existDatabaseEnvironment.TimeoutInMinutes, User = existDatabaseEnvironment.User }; string connectionString = envBO.GetConnectionString(); if (!connectionString.ToLower().Contains("timeout") && envBO.TimeoutInMinutes == null) { connectionString += " Connection Timeout = 60"; } else if (envBO.TimeoutInMinutes != null) { connectionString += " Connection Timeout = " + (envBO.TimeoutInMinutes.Value * 60).ToString(); } try { using (var conn = new SqlConnection(connectionString)) { conn.Open(); using (var cmd = new SqlCommand(ruleExecutionLog.DiagnosticSql, conn)) { if (envBO.TimeoutInMinutes != null) { cmd.CommandTimeout = (envBO.TimeoutInMinutes.Value * 60); } result.RuleDiagnosticSql = ruleExecutionLog.DiagnosticSql; var reader = await cmd.ExecuteReaderAsync(); if (reader.HasRows) { DataTable dt = new DataTable(); dt.Load(reader); if (dt != null) { List <string> columnsToExport = new List <string>(); foreach (DataColumn column in dt.Columns) { string columnName = column.ColumnName.ToLower(); columnsToExport.Add(columnName); } List <Dictionary <string, string> > rowsToExport = new List <Dictionary <string, string> >(); foreach (DataRow row in dt.Rows) { Dictionary <string, string> newRow = new Dictionary <string, string>(); foreach (var column in columnsToExport) { newRow.Add(column, row[column].ToString()); } rowsToExport.Add(newRow); } result.RuleExecutionLogId = id; result.Columns = columnsToExport; result.Rows = rowsToExport; } } } } } catch { } } return(result); }
public async Task <RuleExecutionLogDetailBO> GetByRuleExecutionLogIdAsync(int id) { RuleExecutionLogDetailBO result = new RuleExecutionLogDetailBO { Columns = new List <string>(), Rows = new List <Dictionary <string, string> >() }; var ruleExecutionLog = await _queriesRuleExecutionLog.GetAsync(id); if (ruleExecutionLog != null && ruleExecutionLog.RuleDetailsDestinationId != null) { var existRule = await _queriesRule.GetAsync(ruleExecutionLog.RuleId); var existDatabaseEnvironment = await _queriesDatabaseEnvironments.GetAsync(ruleExecutionLog.DatabaseEnvironmentId); result.RuleName = existRule.Name; result.EnvironmentName = existDatabaseEnvironment.Name; result.ExecutionDateTime = ruleExecutionLog.ExecutionDate.ToLocalTime().ToString("MM/dd/yyyy HH:mm"); var catalog = await _queriesCatalog.GetAsync(ruleExecutionLog.RuleDetailsDestinationId.Value); if (catalog != null) { result.DestinationTable = catalog.Name; result.RuleDiagnosticSql = ruleExecutionLog.DiagnosticSql; Dictionary <string, string> columnsFromLog = new Dictionary <string, string>(); if (!string.IsNullOrEmpty(ruleExecutionLog.DetailsSchema)) { columnsFromLog = JsonConvert.DeserializeObject <Dictionary <string, string> >(ruleExecutionLog.DetailsSchema); } var reader = await _queries.GetByRuleExecutionLogIdAsync(id, catalog.Name); if (reader != null) { List <string> columnsToIgnore = new List <string>() { "id", "otherdetails", "ruleexecutionlogid" }; List <string> columnsToExport = new List <string>(); foreach (DataColumn column in reader.Columns) { string columnName = column.ColumnName.ToLower(); if (!columnsToIgnore.Contains(columnName) && (columnsFromLog.Count == 0 || columnsFromLog.ContainsKey(columnName))) { columnsToExport.Add(columnName); } } List <Dictionary <string, string> > rowsToExport = new List <Dictionary <string, string> >(); foreach (DataRow row in reader.Rows) { Dictionary <string, string> newRow = new Dictionary <string, string>(); foreach (var column in columnsToExport) { newRow.Add(column, row[column].ToString()); } string otherDetails = row.Field <string>("otherdetails").ToString(); if (!string.IsNullOrEmpty(otherDetails)) { Dictionary <string, string> jsonValues = JsonConvert.DeserializeObject <Dictionary <string, string> >(otherDetails); foreach (var element in jsonValues) { if (columnsToExport.Contains(element.Key) || columnsFromLog.ContainsKey(element.Key)) { newRow.Add(element.Key, element.Value); } } } rowsToExport.Add(newRow); } if (columnsFromLog.Count > 0) { columnsToExport = columnsFromLog.Select(rec => rec.Key).ToList(); } result.RuleExecutionLogId = id; result.Columns = columnsToExport; result.Rows = rowsToExport; } } } return(result); }