public async Task <ActionResult> UpdateAsync([FromBody] RuleBO model)
{
await _rulesService
.UpdateAsync(model);
return(NoContent());
}
private async Task InsertDiagnosticSqlIntoDetails(RuleBO rule, RuleExecutionLog ruleExecutionLog, string connectionString, List <UserParamBO> sqlParams, string tableName, int maxNumberResults)
{
string sqlToRun = Utils.GenerateSqlWithTop(rule.DiagnosticSql, maxNumberResults.ToString());
string columnsSchema = string.Empty;
var listColumnsFromDestination = await _edFiRuleExecutionLogDetailQueries.GetColumnsByTableAsync(tableName, "destination");
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = new SqlCommand(sqlToRun, conn))
{
AddParameters(sqlToRun, cmd, sqlParams);
var reader = await cmd.ExecuteReaderAsync();
Dictionary <string, string> listColumns = new Dictionary <string, string>();
listColumnsFromDestination.ForEach(rec => listColumns.Add(rec.Name, rec.Type));
DataTable tableToInsert = Utils.GetTableForSqlBulk(ruleExecutionLog.Id, reader, listColumns, out columnsSchema);
if (tableToInsert != null && tableToInsert.Rows.Count > 0)
{
if (ruleExecutionLog != null)
{
ruleExecutionLog.DetailsSchema = columnsSchema;
await _ruleExecutionLogCommands.UpdateAsync(ruleExecutionLog);
}
await _edFiRuleExecutionLogDetailCommands.ExecuteSqlBulkCopy(tableToInsert, $"[destination].[{tableName}]");
}
}
}
}
public async Task <RuleBO> UpdateAsync(RuleBO model)
{
var result = await this._ruleCommands
.UpdateAsync(MapModelToEntity(model));
await CreateUpdateTags(model, result);
return(result != null?MapEntityToModel(result) : null);
}
public async Task <ActionResult> AddAsync([FromBody] RuleBO model)
{
var rule = await _rulesService
.AddAsync(model);
if (rule != null)
{
return(CreatedAtAction("Get", new { id = rule.Id }, rule));
}
return(BadRequest());
}
private async Task CreateUpdateTags(RuleBO ruleBO, Rule ruleDB)
{
var listTags = await this._tagQueries.GetByRuleIdAsync(ruleDB.Id);
if (ruleBO.Tags != null && ruleBO.Tags.Count > 0)
{
foreach (var tag in listTags)
{
var existTag = ruleBO.Tags.FirstOrDefault(rec => rec.Id == tag.Id);
if (existTag == null)
{
await this._tagCommands.DeleteTagFromEntityAsync(tag.Id, ruleDB.Id);
}
}
foreach (var tag in ruleBO.Tags)
{
var existTag = listTags.FirstOrDefault(rec => rec.Id == tag.Id);
if (existTag == null)
{
if (tag.Id == -1)
{
var newTag = await this._tagCommands.AddAsync(new Tag
{
Name = tag.Name.ToUpper(),
Description = tag.Name.ToUpper(),
IsPublic = false
});
tag.Id = newTag.Id;
}
await this._tagCommands.AddTagToEntityAsync(new TagEntity
{
RuleId = ruleDB.Id,
TagId = tag.Id
});
}
}
}
else if (listTags != null && listTags.Count > 0)
{
foreach (var tag in listTags)
{
await this._tagCommands.DeleteTagFromEntityAsync(tag.Id, ruleDB.Id);
}
}
}
private RuleBO MapEntityToModel(Rule entity)
{
RuleBO model = new RuleBO
{
Id = entity.Id,
ContainerId = entity.ContainerId,
//CreatedByUserId = entity.CreatedByUserId,
//CreatedByUserName = entity.CreatedByUserName,
Description = entity.Description,
DiagnosticSql = entity.DiagnosticSql,
//Enabled = entity.Enabled,
ErrorMessage = entity.ErrorMessage,
ErrorSeverityLevel = entity.ErrorSeverityLevel,
Name = entity.Name,
Resolution = entity.Resolution,
RuleIdentification = entity.RuleIdentification,
Version = entity.Version,
Tags = new List <TagBO>(),
MaxNumberResults = entity.MaxNumberResults,
DateUpdated = entity.DateUpdated
};
model.Tags = _tagQueries.GetByRuleIdAsync(model.Id).Result.Select(rec => new TagBO(rec)).ToList();
if (entity.RuleExecutionLogs != null)
{
model.RuleExecutionLogs = entity.RuleExecutionLogs
.Select(rec => new RuleExecutionLogBO
{
DatabaseEnvironmentId = rec.DatabaseEnvironmentId,
DiagnosticSql = rec.DiagnosticSql,
Evaluation = rec.Evaluation,
ExecutedSql = rec.ExecutedSql,
ExecutionDate = rec.ExecutionDate,
ExecutionTimeMs = rec.ExecutionTimeMs,
Id = rec.Id,
Response = rec.Response,
Result = rec.Result,
RuleId = rec.RuleId,
StatusId = rec.StatusId
})
.ToList();
}
return(model);
}
public async Task <RuleBO> CopyToAsync(Guid ruleId, Guid containerId)
{
RuleBO result = new RuleBO();
RuleBO ruleToCopy = MapEntityToModel(await _ruleQueries.GetAsync(ruleId));
string ruleName = ruleToCopy.Name;
ruleToCopy.ContainerId = containerId;
var rulesFromContainer = await _ruleQueries.GetByContainerIdAsync(containerId);
if (rulesFromContainer != null && rulesFromContainer.Count > 0)
{
while (true)
{
var existRuleWithName = rulesFromContainer.FirstOrDefault(rec => rec.Name == ruleName);
if (existRuleWithName == null)
{
break;
}
ruleName += " - Copy";
}
}
ruleToCopy.Id = Guid.Empty;
ruleToCopy.Name = ruleName;
result = MapEntityToModel(await _ruleCommands.AddAsync(MapModelToEntity(ruleToCopy)));
var listTags = await _tagQueries.GetByRuleIdAsync(ruleId);
if (listTags != null && listTags.Count > 0)
{
foreach (var tag in listTags)
{
await _tagCommands.AddTagToEntityAsync(new TagEntity
{
RuleId = result.Id,
TagId = tag.Id
});
}
}
result.Tags = _tagQueries.GetByRuleIdAsync(result.Id).Result.Select(rec => new TagBO(rec)).ToList();
return(result);
}
private Rule MapModelToEntity(RuleBO model)
{
Rule entity = new Rule
{
Id = model.Id,
ContainerId = model.ContainerId,
//CreatedByUserId = model.CreatedByUserId,
Description = model.Description,
DiagnosticSql = model.DiagnosticSql,
//Enabled = model.Enabled,
ErrorMessage = model.ErrorMessage,
ErrorSeverityLevel = model.ErrorSeverityLevel,
Name = model.Name,
Resolution = model.Resolution,
RuleIdentification = model.RuleIdentification,
Version = model.Version,
MaxNumberResults = model.MaxNumberResults,
DateUpdated = model.DateUpdated
};
if (model.RuleExecutionLogs != null)
{
entity.RuleExecutionLogs = model.RuleExecutionLogs
.Select(rec => new RuleExecutionLog
{
DatabaseEnvironmentId = rec.DatabaseEnvironmentId,
DiagnosticSql = rec.DiagnosticSql,
Evaluation = rec.Evaluation,
ExecutedSql = rec.ExecutedSql,
ExecutionDate = rec.ExecutionDate,
ExecutionTimeMs = rec.ExecutionTimeMs,
Id = rec.Id,
Response = rec.Response,
Result = rec.Result,
RuleId = rec.RuleId,
StatusId = rec.StatusId
})
.ToList();
}
return(entity);
}
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);
}
