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 <RuleTestResult> ExecuteRuleAsync(RuleBO rule, string connectionString, List <UserParamBO> userParams, int?timeout)
{
var stopWatch = System.Diagnostics.Stopwatch.StartNew();
RuleTestResult testResult;
try
{
if (!connectionString.ToLower().Contains("timeout") && timeout == null)
{
connectionString += " Connection Timeout = 60";
}
else if (timeout != null)
{
connectionString += " Connection Timeout = " + (timeout.Value * 60).ToString();
}
using (var conn = new SqlConnection(connectionString))
{
int execution = 0;
bool resultWithErrors = false;
await conn.OpenAsync();
string sqlToRun = Utils.GenerateSqlWithCount(rule.DiagnosticSql);
try
{
if (string.IsNullOrEmpty(sqlToRun))
{
sqlToRun = rule.DiagnosticSql;
using (var cmd = new SqlCommand(sqlToRun, conn))
{
if (timeout != null)
{
cmd.CommandTimeout = (timeout.Value * 60);
}
AddParameters(sqlToRun, cmd, userParams);
var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
execution++;
}
}
}
}
else
{
using (var cmd = new SqlCommand(sqlToRun, conn))
{
if (timeout != null)
{
cmd.CommandTimeout = (timeout.Value * 60);
}
AddParameters(sqlToRun, cmd, userParams);
execution = Convert.ToInt32(cmd.ExecuteScalar());
}
}
}
catch
{
sqlToRun = rule.DiagnosticSql;
using (var cmd = new SqlCommand(sqlToRun, conn))
{
if (timeout != null)
{
cmd.CommandTimeout = (timeout.Value * 60);
}
AddParameters(sqlToRun, cmd, userParams);
var reader = await cmd.ExecuteReaderAsync();
if (reader.HasRows)
{
while (reader.Read())
{
execution++;
}
}
}
}
resultWithErrors = execution > 0;
testResult = new RuleTestResult
{
Id = 0,
Rule = rule,
Result = execution,
Evaluation = !resultWithErrors,
Status = !resultWithErrors ? Status.Succeded : Status.Failed,
ErrorMessage = !resultWithErrors ? "" : rule.ErrorMessage,
ExecutedSql = rule.DiagnosticSql
};
}
}
catch (Exception e)
{
testResult = new RuleTestResult()
{
Rule = rule,
Result = -1,
Evaluation = false,
Status = Status.Error,
ErrorMessage = e.Message,
ExecutedSql = rule.DiagnosticSql
};
}
stopWatch.Stop();
testResult.ExecutionTimeMs = stopWatch.ElapsedMilliseconds;
return(testResult);
}