protected override async Task <bool> OnIsSuperUserAsync(int processId, string userId, CancellationToken cancellationToken, IDictionary <object, object> context)
{
try
{
var policy = _connectionFactory.PolicySelector(_logger);
var result = await policy.ExecuteAsync(async() =>
{
using (var connection = _connectionFactory.Create("raa"))
{
string query = $"SELECT dbo.FN_IS_PROCESS_SUPER_USER(@processId, @userId)";
var parameters = new DynamicParameters();
parameters.AddDynamicParams(new { processId, userId });
int temp = await connection.ExecuteScalarAsync <int>(query, parameters);
return(temp == 1);
}
});
return(result);
}
catch (Exception ex)
{
_logger.LogError($"Unable to check if the user with id {userId} is a super user. Reason: {ex}");
throw;
}
}
public int ExecuteNonQuery(string connectionString, string sqlStatement)
{
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new ArgumentNullException(nameof(connectionString));
}
if (string.IsNullOrWhiteSpace(sqlStatement))
{
throw new ArgumentNullException(nameof(sqlStatement));
}
using (IDbConnection dbConnection = _sqlConnectionFactory.Create(connectionString))
{
dbConnection.Open();
try
{
using (IDbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sqlStatement;
dbCommand.CommandType = CommandType.Text;
return(dbCommand.ExecuteNonQuery());
}
}
finally
{
dbConnection.Close();
}
}
}
/// <summary>
/// Asynchronously retrieves a list of <see cref="Process"/>.
/// </summary>
/// <param name="processIds">The list of process identification numbers that we are going to retrieve.</param>
/// <param name="limit">Maximun number of items that we are going to return per page.</param>
/// <param name="offset">Number of pages that will be skipped.</param>
/// <param name="cancellationToken">The optional token to monitor for cancellation requests.</param>
/// <param name="context">The optional execution context that applies to this operation.</param>
/// <returns>A <see cref="Task{TResult}"/> </returns>
protected override async Task <PagedResult <Process> > OnGetAsync(IList <int> processIds, int offset, int limit, CancellationToken cancellationToken = default(CancellationToken), IDictionary <object, object> context = null)
{
try
{
//int count = await GetProcessCountAsync(processIds);
int count = processIds.Count;
List <int> subList = processIds
.Skip(offset)
.Take(limit)
.ToList();
var result = await policy.ExecuteAsync(async() =>
{
using (var connection = _connectionFactory.Create("raa"))
{
IDictionary <int, Process> processList = await GetProcessList(subList, connection);
processList = PopulateItemsNotFound(subList, processList);
return(new PagedResult <Process>(offset, limit, count, processList.Values.ToList()));
}
});
return(result);
}
catch (Exception ex)
{
_logger.LogError($"Unable to retrieve process list. Reason: {ex}");
throw;
}
}
public IEnumerable <T> Execute <T>(IDynamicParameters parameters, Enum storedProcedureEnum)
{
using (var sqlConnection = _sqlConnectionFactory.Create())
{
var procedureName = storedProcedureEnum.GetSqlScriptInfo().Name;
return(sqlConnection.Query <T>(procedureName, parameters, commandType: CommandType.StoredProcedure));
}
}
/// <summary>
/// Execute a query with no results returned
/// </summary>
/// <param name="commandString">The SQL statement or stored procedure to execute</param>
/// <param name="param">Parameters object</param>
/// <param name="commandType">Specifies how the command string is interpreted</param>
public async Task ExecuteAsync(string commandString, object param = null, CommandType commandType = CommandType.StoredProcedure)
{
using (var con = connectionFactory.Create())
{
await con.OpenAsync();
await con.ExecuteAsync(commandString, param, commandType : commandType);
}
}
public async Task WriteBatch(IEnumerable <LogEvent> events, DataTable dataTable)
{
try
{
FillDataTable(events, dataTable);
using (var cn = _sqlConnectionFactory.Create())
{
await cn.OpenAsync().ConfigureAwait(false);
using (var copy = cn.CreateSqlBulkCopy(_disableTriggers,
string.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", _schemaName, _tableName)))
{
foreach (var column in dataTable.Columns)
{
var columnName = ((DataColumn)column).ColumnName;
copy.AddSqlBulkCopyColumnMapping(columnName, columnName);
}
await copy.WriteToServerAsync(dataTable).ConfigureAwait(false);
}
}
}
catch (Exception ex)
{
SelfLog.WriteLine("Unable to write {0} log events to the database due to following error: {1}", events.Count(), ex.Message);
}
finally
{
dataTable.Clear();
}
}
public async Task <ActionResult> RunAsync(
[HttpTrigger(AuthorizationLevel.Function, "get", "cleanup")] HttpRequest request)
{
await using var conn = _sqlConnectionFactory.Create();
await conn.ExecuteAsync("DELETE FROM sample;");
return(new OkResult());
}
public void WriteEvent(LogEvent logEvent)
{
try
{
using (var connection = _sqlConnectionFactory.Create())
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
var fieldList = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
var parameterList = new StringBuilder(") VALUES (");
var index = 0;
foreach (var field in _logEventDataGenerator.GetColumnsAndValues(logEvent))
{
if (index != 0)
{
fieldList.Append(',');
parameterList.Append(',');
}
fieldList.Append(field.Key);
parameterList.Append("@P");
parameterList.Append(index);
var parameter = new SqlParameter($"@P{index}", field.Value ?? DBNull.Value);
// The default is SqlDbType.DateTime, which will truncate the DateTime value if the actual
// type in the database table is datetime2. So we explicitly set it to DateTime2, which will
// work both if the field in the table is datetime and datetime2, which is also consistent with
// the behavior of the non-audit sink.
if (field.Value is DateTime)
{
parameter.SqlDbType = SqlDbType.DateTime2;
}
command.Parameters.Add(parameter);
index++;
}
parameterList.Append(')');
fieldList.Append(parameterList.ToString());
command.CommandText = fieldList.ToString();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
SelfLog.WriteLine("Unable to write log event to the database due to following error: {1}", ex.Message);
throw;
}
}
/// <summary>
/// Asynchronously retrieves a <see cref="PersonIdentification"/>.
/// </summary>
/// <param name="employeeId">The list of process identification numbers that we are going to retrieve.</param>
/// <param name="cancellationToken">The optional token to monitor for cancellation requests.</param>
/// <param name="context">The optional execution context that applies to this operation.</param>
/// <returns>A <see cref="Task{TResult}"/> </returns>
protected override async Task <PersonIdentification> OnGetByEmployeeIdAsync(string employeeId, CancellationToken cancellationToken = default(CancellationToken), IDictionary <object, object> context = null)
{
try
{
var policy = _connectionFactory.PolicySelector(_logger);
var result = await policy.ExecuteAsync(async() =>
{
using (var connection = _connectionFactory.Create("raa"))
{
string sqlStmnt = @"
SELECT EMPLID AS EmployeeId
, HANFORD_ID AS HanfordId
, NETWORK_DOMAIN AS Domain
, NETWORK_ID AS NetworkId
, CASE WHEN ACTIVE_SW = 'Y' THEN
1
ELSE
0
END as IsActive
FROM [opwhse].[dbo].[VW_PUB_PERSON]
WHERE EMPLID = @employeeId";
var parameters = new DynamicParameters();
parameters.AddDynamicParams(new { employeeId = employeeId });
var personIdentification = await connection.QuerySingleAsync <PersonIdentification>(sqlStmnt, parameters);
if (personIdentification == null)
{
throw new RowNotInTableException();
}
return(personIdentification);
}
});
return(result);
}
catch (Exception ex)
{
_logger.LogError($"Unable to retrieve requested item. Reason: {ex}");
throw;
}
}
public async ValueTask AddAsync(IEnumerable <SampleEntity> data)
{
await using var conn = _sqlConnectionFactory.Create();
await conn.ExecuteAsync(@"
INSERT INTO sample
(id, name, age, date)
VALUES
(@Id, @Name, @Age, @Date);", data);
}
public void Execute(string sql)
{
using (var connection = _sqlConnectionFactory.Create())
{
ServerConnection serverConnection = new ServerConnection(connection);
Server server = new Server(serverConnection);
server.ConnectionContext.ExecuteNonQuery(sql);
}
}
public async ValueTask AddAsync(IEnumerable <SampleEntity> data)
{
await using var conn = _sqlConnectionFactory.Create();
await conn.OpenAsync();
using var dataTable = CreateDataTable(data);
var sqlBulkCopy = new SqlBulkCopy(conn)
{
DestinationTableName = "sample"
};
await sqlBulkCopy.WriteToServerAsync(dataTable);
}
public void WriteEvent(LogEvent logEvent)
{
try
{
using (var connection = _sqlConnectionFactory.Create())
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandType = CommandType.Text;
var fieldList = new StringBuilder($"INSERT INTO [{_schemaName}].[{_tableName}] (");
var parameterList = new StringBuilder(") VALUES (");
var index = 0;
foreach (var field in _logEventDataGenerator.GetColumnsAndValues(logEvent))
{
if (index != 0)
{
fieldList.Append(',');
parameterList.Append(',');
}
fieldList.Append(field.Key);
parameterList.Append("@P");
parameterList.Append(index);
command.AddParameter($"@P{index}", field.Value);
index++;
}
parameterList.Append(')');
fieldList.Append(parameterList.ToString());
command.CommandText = fieldList.ToString();
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
SelfLog.WriteLine("Unable to write log event to the database due to following error: {1}", ex.Message);
throw;
}
}
public async ValueTask <IHealthCheckResult> CheckAsync(CancellationToken cancellationToken = new CancellationToken())
{
var stopwatch = new Stopwatch();
stopwatch.Start();
_logger.LogInformation($"SqlConnectionHealthCheck: CheckAsync Started - {stopwatch.ElapsedMilliseconds}ms");
_logger.LogInformation($"SqlConnectionHealthCheck: Started Creating SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
using (var sqlConnection = _sqlConnectionFactory.Create())
{
_logger.LogInformation($"SqlConnectionHealthCheck: Finished Creating SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
_logger.LogInformation($"SqlConnectionHealthCheck: Started Opening SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
try
{
await sqlConnection.OpenAsync(cancellationToken).ConfigureAwait(false);
}
catch (Exception ex)
{
return(HealthCheckResult.Unhealthy($"Could not connect to database - {ex.Message} - {stopwatch.ElapsedMilliseconds}ms"));
}
_logger.LogInformation($"SqlConnectionHealthCheck: Finished Opening SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
_logger.LogInformation($"SqlConnectionHealthCheck: Started Querying tenagree - {stopwatch.ElapsedMilliseconds}ms");
var result = await sqlConnection.QueryAsync <string>("SELECT TOP 1 tag_ref from tenagree WHERE tenagree.tag_ref IS NOT NULL").ConfigureAwait(false);
_logger.LogInformation($"SqlConnectionHealthCheck: Finished Querying tenagree - {stopwatch.ElapsedMilliseconds}ms");
var list = result?.ToList();
if (list == null || !list.Any())
{
_logger.LogInformation($"SqlConnectionHealthCheck: Started Closing SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
sqlConnection.Close();
_logger.LogInformation($"SqlConnectionHealthCheck: Finished Closing SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
return(HealthCheckResult.Unhealthy($"Could not get results from database - {stopwatch.ElapsedMilliseconds}ms"));
}
_logger.LogInformation($"SqlConnectionHealthCheck: Started Closing SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
sqlConnection.Close();
_logger.LogInformation($"SqlConnectionHealthCheck: Finished Closing SqlConnection - {stopwatch.ElapsedMilliseconds}ms");
_logger.LogInformation($"SqlConnectionHealthCheck: CheckAsync Finished - {stopwatch.ElapsedMilliseconds}ms");
return(list.Count > 0 ? HealthCheckResult.Healthy($"Successfully retrieved 1 record from database - {stopwatch.ElapsedMilliseconds}ms"): HealthCheckResult.Unhealthy($"Could not get a valid record from database - {stopwatch.ElapsedMilliseconds}ms"));
}
}
public void CreateTable(DataTable dataTable)
{
try
{
using (var conn = _sqlConnectionFactory.Create())
{
var sql = _sqlCreateTableWriter.GetSqlFromDataTable(_schemaName, _tableName, dataTable, _columnOptions);
using (var cmd = conn.CreateCommand(sql))
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
SelfLog.WriteLine($"Exception creating table {_tableName}:\n{ex}");
}
}
public async Task WriteBatch(IEnumerable <LogEvent> events, DataTable dataTable)
{
// Copy the events to the data table
FillDataTable(events, dataTable);
try
{
using (var cn = _sqlConnectionFactory.Create())
{
await cn.OpenAsync().ConfigureAwait(false);
using (var copy = _disableTriggers
? new SqlBulkCopy(cn)
: new SqlBulkCopy(cn, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.FireTriggers, null)
)
{
copy.DestinationTableName = string.Format(CultureInfo.InvariantCulture, "[{0}].[{1}]", _schemaName, _tableName);
foreach (var column in dataTable.Columns)
{
var columnName = ((DataColumn)column).ColumnName;
var mapping = new SqlBulkCopyColumnMapping(columnName, columnName);
copy.ColumnMappings.Add(mapping);
}
await copy.WriteToServerAsync(dataTable).ConfigureAwait(false);
}
}
}
catch (Exception ex)
{
SelfLog.WriteLine("Unable to write {0} log events to the database due to following error: {1}", events.Count(), ex.Message);
}
finally
{
// Processed the items, clear for the next run
dataTable.Clear();
}
}
protected override async Task <PagedResult <ProcessNode> > OnGetByIdsAsync(IList <int> processIds, int offset, int limit, CancellationToken cancellationToken = default(CancellationToken), IDictionary <object, object> context = null)
{
int count = 0;
string query = @"SELECT PROCESS_ID AS ProcessId
, NODE_NAME AS NodeName
, NODE_LABEL AS NodeLabel
, NODE_DATATYPE AS NodeDataType
, NODE_VALUE AS NodeValue
FROM XML_NODE
WHERE PROCESS_ID IN @Ids";
try
{
DynamicParameters parameters = new DynamicParameters();
parameters.AddDynamicParams(new { Ids = processIds });
var policy = _connectionFactory.PolicySelector(_logger);
var result = await policy.ExecuteAsync(async() =>
{
using (var connection = _connectionFactory.Create("raa"))
{
IEnumerable <ProcessNode> processNodes;
IDictionary <int, ProcessNodeResult> processNodeResults = new Dictionary <int, ProcessNodeResult>();
using (SqlMapper.GridReader multi = await cn.QueryMultipleAsync(query, parameters, commandType: CommandType.Text))
{
processNodes = await multi.ReadAsync <ProcessNode>();
}
if (processNodes != null)
{
count = processNodes.Count();
// Group the nodes together by processId.
foreach (var processNode in processNodes)
{
int processId = processNode.ProcessId;
if (!processNodeResults.ContainsKey(processId))
{
processNodeResults.Add(processId, new ProcessNodeResult()
{
ProcessId = processId,
Nodes = new List <Node>()
});
}
processNodeResults[processId].Nodes.Add(new Node()
{
NodeName = processNode.NodeName,
NodeLabel = processNode.NodeLabel,
NodeValue = processNode.NodeValue,
NodeDataType = processNode.NodeDataType
});
}
}
var result = processNodeResults
.Values
.ToList()
.Skip(offset *limit)
.Take(limit);
return(new PagedResult <ProcessNodeResult>(offset, limit, count, result));
}
});
return(result);
}
catch (Exception ex)
{
_logger.LogError($"Unable to retrieve process nodes list. Reason: {ex}");
throw ex;
}
}
public UnitOfWork(ISqlConnectionFactory configConnection)
{
_connection = configConnection.Create();
}
public UnitOfWork(ISqlConnectionFactory configConnection)
{
_connection = configConnection.Create();
}
