public async Task <string> TestConnectionByIdAsync(Guid id)
{
string result = string.Empty;
try
{
var existEnvironment = MapEntityToModel(await _databaseEnvironmentQueries.GetAsync(id));
string sqlConnection = existEnvironment.GetConnectionString();
if (!sqlConnection.ToLower().Contains("timeout"))
{
sqlConnection += " Connection Timeout=10";
}
using (var conn = new SqlConnection(sqlConnection))
{
await conn.OpenAsync();
await conn.CloseAsync();
await conn.DisposeAsync();
}
}
catch (Exception ex)
{
result = ex.Message;
}
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);
}
