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