public async Task <DatabaseEnvironmentBO> UpdateAsync(DatabaseEnvironmentBO model)
{
var map = await this.GetTablesAndColumnsByConnectionString(model.GetConnectionString());
if (map != null)
{
model.MapTables = JsonConvert.SerializeObject(map, Formatting.Indented);
}
var environment = await this._databaseEnvironmentQueries.GetAsync(model.Id);
environment.Name = model.Name;
environment.User = model.User;
environment.DataSource = model.DataSource;
environment.ExtraData = model.ExtraData;
environment.Database = model.Database;
environment.SecurityIntegrated = model.SecurityIntegrated;
environment.MapTables = model.MapTables;
environment.Version = model.Version;
environment.TimeoutInMinutes = model.TimeoutInMinutes;
if (environment.SecurityIntegrated != null && environment.SecurityIntegrated.Value)
{
environment.User = string.Empty;
environment.Password = string.Empty;
}
else if (!string.IsNullOrEmpty(model.Password))
{
environment.Password = model.Password;
}
var result = await this._databaseEnvironmentCommands.UpdateAsync(environment);
return(MapEntityToModel(result));
}
public async Task <TableResult> ExecuteRuleDiagnosticByRuleLogIdAndEnvironmentIdAsync(int ruleLogId, DatabaseEnvironmentBO databaseEnvironment)
{
TableResult result = new TableResult();
var existLog = await _ruleExecutionLogQueries.GetAsync(ruleLogId);
var connectionString = databaseEnvironment.GetConnectionString();
try
{
if (!connectionString.ToLower().Contains("timeout") && databaseEnvironment.TimeoutInMinutes == null)
{
connectionString += " Connection Timeout = 60";
}
else if (databaseEnvironment.TimeoutInMinutes != null)
{
connectionString += " Connection Timeout = " + (databaseEnvironment.TimeoutInMinutes.Value * 60).ToString();
}
using (var conn = new SqlConnection(connectionString))
{
await conn.OpenAsync();
using (var cmd = new SqlCommand(existLog.DiagnosticSql, conn))
{
if (databaseEnvironment.TimeoutInMinutes != null)
{
cmd.CommandTimeout = (databaseEnvironment.TimeoutInMinutes.Value * 60);
}
AddParameters(existLog.DiagnosticSql, cmd, databaseEnvironment.UserParams);
var getReader = await cmd.ExecuteReaderAsync();
DataTable dt = new DataTable();
dt.Load(getReader);
result.Columns = dt.Columns.Count;
foreach (var dataColumn in dt.Columns)
{
result.ColumnsName.Add(dataColumn.ToString());
}
var informationAsList = (from x in dt.AsEnumerable() select x).ToList();
result.Information = Utils.Serialize(result.ColumnsName, informationAsList);
result.Rows = result.Information.Count;
}
}
}
catch (Exception ex)
{
result.MessageError = ex.Message;
}
return(result);
}
public async Task <List <RuleTestResult> > ExecuteRulesByEnvironmentIdAsync(List <RuleBO> rules, DatabaseEnvironmentBO databaseEnvironment)
{
var results = new List <RuleTestResult>();
var connectionString = databaseEnvironment
.GetConnectionString();
foreach (var rule in rules)
{
results.Add(await ExecuteRuleAsync(rule, connectionString, databaseEnvironment.UserParams, databaseEnvironment.TimeoutInMinutes));
}
return(results);
}
public async Task <ActionResult> TestConnection([FromBody] DatabaseEnvironmentBO model)
{
var result = await _databaseEnvironmentService
.TestConnectionAsync(model.GetConnectionString());
if (result != null)
{
return(Ok(result));
}
return(BadRequest());
}
public async Task <DatabaseEnvironmentBO> AddAsync(DatabaseEnvironmentBO model)
{
var map = await this.GetTablesAndColumnsByConnectionString(model.GetConnectionString());
if (map != null)
{
model.MapTables = JsonConvert.SerializeObject(map, Formatting.Indented);
}
var result = await this._databaseEnvironmentCommands.AddAsync(MapModelToEntity(model));
return(MapEntityToModel(result));
}
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 <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);
}