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(); }