public async Task <RuleTestResult> ExecuteRuleByEnvironmentIdAsync(Guid ruleId, DatabaseEnvironmentBO databaseEnvironment)
{
int?ruleDetailsDestinationId = null;
var rule = await _ruleService.GetAsync(ruleId);
var executionLogs = await _ruleExecutionLogQueries.GetByRuleIdAsync(ruleId);
var connectionString = databaseEnvironment.GetConnectionString();
var stopWatch = System.Diagnostics.Stopwatch.StartNew();
RuleTestResult testResult = await ExecuteRuleAsync(rule, connectionString, databaseEnvironment.UserParams, databaseEnvironment.TimeoutInMinutes);
var containerParent = await _collectionQueries.GetAsync(rule.ContainerId);
if (containerParent.ParentContainerId != null)
{
var collectionParent = await _collectionQueries.GetAsync(containerParent.ParentContainerId.Value);
ruleDetailsDestinationId = collectionParent.RuleDetailsDestinationId;
}
RuleExecutionLog ruleExecutionLog = new RuleExecutionLog()
{
Id = 0,
Evaluation = testResult.Evaluation,
RuleId = ruleId,
StatusId = (int)testResult.Status,
Result = testResult.Result,
Response = testResult.Evaluation ? "Ok" : testResult.ErrorMessage,
DatabaseEnvironmentId = databaseEnvironment.Id,
ExecutedSql = testResult.ExecutedSql,
DiagnosticSql = rule.DiagnosticSql,
RuleDetailsDestinationId = ruleDetailsDestinationId
};
if (ruleExecutionLog.RuleDetailsDestinationId == null || ruleExecutionLog.RuleDetailsDestinationId.Value == 0)
{
ruleExecutionLog.RuleDetailsDestinationId = null;
}
testResult.LastExecuted = executionLogs.Any() ? executionLogs.FirstOrDefault().ExecutionDate : (DateTime?)null;
ruleExecutionLog.ExecutionTimeMs = stopWatch.ElapsedMilliseconds;
ruleExecutionLog.Result = testResult.Result;
var newRuleExecutionLog = await _ruleExecutionLogCommands.AddAsync(ruleExecutionLog);
testResult.TestResults = await _ruleService.GetTopResults(ruleId, databaseEnvironment.Id);
if (!testResult.Evaluation)
{
testResult.DiagnosticSql = rule.DiagnosticSql;
}
try
{
//Validate if the rule is going to any queue table
if (ruleDetailsDestinationId != null && ruleDetailsDestinationId.Value > 0)
{
var existCatalog = await _catalogQueries.GetAsync(ruleDetailsDestinationId.Value);
if (existCatalog != null)
{
int maxNumberResults = databaseEnvironment.MaxNumberResults.Value;
if (rule.MaxNumberResults != null)
{
maxNumberResults = rule.MaxNumberResults.Value;
}
await InsertDiagnosticSqlIntoDetails(rule, newRuleExecutionLog, connectionString, databaseEnvironment.UserParams, existCatalog.Name, maxNumberResults);
}
}
}
catch (Exception ex)
{
newRuleExecutionLog.Result = -1;
newRuleExecutionLog.Response = ex.Message;
await _ruleExecutionLogCommands.UpdateAsync(newRuleExecutionLog);
}
return(testResult);
}
public async Task <RuleExecutionLogDetailExportToTableBO> ExportToTableByRuleExecutionLogIdAsync(int id)
{
RuleExecutionLogDetailExportToTableBO result = new RuleExecutionLogDetailExportToTableBO();
var ruleExecutionLog = await _queriesRuleExecutionLog.GetAsync(id);
if (ruleExecutionLog != null && !string.IsNullOrEmpty(ruleExecutionLog.DetailsTableName))
{
result.TableName = ruleExecutionLog.DetailsTableName;
result.AlreadyExist = await _queries.ExistExportTableFromRuleExecutionLogAsync(ruleExecutionLog.DetailsTableName, "destination");
}
if (!result.AlreadyExist)
{
Persistence.Rules.Rule ruleFromLog = await _queriesRule.GetAsync(ruleExecutionLog.RuleId);
string ruleName = ruleFromLog.Name;
ruleName = Regex.Replace(ruleName, @"[^\w\.@-]", "_", RegexOptions.None, TimeSpan.FromSeconds(1.5));
string tableName = $"T_{id}-{ruleName}";
if (tableName.Length > 128)
{
tableName = tableName.Substring(0, 128);
}
Dictionary <string, string> columns = new Dictionary <string, string>();
columns = JsonConvert.DeserializeObject <Dictionary <string, string> >(ruleExecutionLog.DetailsSchema);
List <string> sqlColumns = new List <string>();
foreach (var column in columns)
{
if (column.Value == "string")
{
sqlColumns.Add(string.Format($"[{column.Key}] [nvarchar](max) NULL"));
}
else
{
sqlColumns.Add(string.Format($"[{column.Key}] [datetime2](7) NULL"));
}
}
string sqlCreate = $"CREATE TABLE [destination].[{tableName}]({string.Join(",", sqlColumns)}) ";
await _commandRuleExecutionLogDetail.ExecuteSqlAsync(sqlCreate);
result.TableName = tableName;
result.Created = await _queries.ExistExportTableFromRuleExecutionLogAsync(tableName, "destination");
if (result.Created)
{
var ruleExecutionLogInfo = await GetByRuleExecutionLogIdAsync(id);
DataTable infoToInsert = Utils.GetTableForSqlBulk(ruleExecutionLogInfo.Rows, columns);
await _commandRuleExecutionLogDetail.ExecuteSqlBulkCopy(infoToInsert, $"[destination].[{tableName}]");
ruleExecutionLog.DetailsTableName = tableName;
await _commandRuleExecutionLog.UpdateAsync(ruleExecutionLog);
}
}
return(result);
}
