public async Task <Guid> AddAsync(Email email, List <String> tags) { await using var connection = new SqlConnection(AdoHelper.ConnectionString); await connection.OpenAsync(); await using SqlTransaction transaction = connection.BeginTransaction(); try { var tagIds = await GetTagIds(tags, transaction); Guid emailId = await AddEmail(email, transaction); await AddEmailTags(emailId, tagIds, transaction); await transaction.CommitAsync(); return(emailId); } catch (Exception ex) { transaction.Rollback(); throw new Exception("Error was occured. Transaction declined.", ex); } }
/// <summary> /// Asynchronously commits active transaction. /// </summary> public async Task CommitTransactionAsync() { if (transaction != null && transaction.Connection != null) { await transaction.CommitAsync(); } }
public async Task CommitAsync_should_commit_transaction() { var transaction = NSubstitute.Substitute.For <IDbContextTransaction>(); var sut = new SqlTransaction(transaction); await sut.CommitAsync(); transaction.Received(1).CommitAsync(Arg.Any <CancellationToken>()); }
private async Task DiscardMessageAsync(SqlConnection connection, SqlTransaction transaction, string discardMessage, CancellationToken cancellationToken) { await transaction?.CommitAsync(cancellationToken); transaction?.Dispose(); connection?.Dispose(); _logger.LogTrace(discardMessage); }
public async Task <string> ExecSqlAsync(List <ParamSql> lst) { string result; try { using (this.conn = new SqlConnection(this._connectionString)) { bool flag = this.conn.State != ConnectionState.Open; if (flag) { await this.conn.OpenAsync(); } SqlTransaction sqlTransaction = this.conn.BeginTransaction(); try { this.cmd = new SqlCommand(); this.cmd.CommandType = CommandType.Text; this.cmd.Connection = this.conn; this.cmd.Transaction = sqlTransaction; foreach (ParamSql paramSql in lst) { this.cmd.CommandText = paramSql.Sql; foreach (SqlHelperParameter sqlHelperParameter in paramSql.Params) { this.cmd.Parameters.Add(this.GetParameter(sqlHelperParameter)); } await this.cmd.ExecuteNonQueryAsync(); } await sqlTransaction.CommitAsync(); result = string.Empty; } catch (Exception ex) { await sqlTransaction.RollbackAsync(); result = ex.Message; } finally { await sqlTransaction.DisposeAsync(); await this.cmd.DisposeAsync(); await this.conn.CloseAsync(); } } } catch (Exception ex2) { result = ex2.Message; } return(result); }
public async Task <string> ExecSqlAsync(ArrayList sqlList) { string result; using (this.conn = new SqlConnection(this._connectionString)) { bool flag = this.conn.State != ConnectionState.Open; if (flag) { await this.conn.OpenAsync(); } bool flag2 = sqlList.Count == 1; if (flag2) { result = await this.ExecSqlAsync(sqlList[0].ToString()); } else { SqlTransaction sqlTransaction = this.conn.BeginTransaction(); try { this.cmd = new SqlCommand(); this.cmd.CommandType = CommandType.Text; this.cmd.Connection = this.conn; this.cmd.Transaction = sqlTransaction; foreach (object obj in sqlList) { string commandText = (string)obj; this.cmd.CommandText = commandText; await this.cmd.ExecuteNonQueryAsync(); } await sqlTransaction.CommitAsync(); result = string.Empty; } catch (Exception ex) { await sqlTransaction.RollbackAsync(); result = ex.Message; } finally { await sqlTransaction.DisposeAsync(); } } } return(result); }
public async Task PopulateAsync(DbSchema[] schemas) { using (var conn = new SqlConnection(Connection.ConnectionString)) { await conn.OpenAsync(); this.Logger.Information($"Opened connection to {conn.Database} on {conn.DataSource}"); using (SqlTransaction tran = conn.BeginTransaction()) { try { foreach (var schema in schemas) { var cleanCmd = new SqlCommand($"TRUNCATE TABLE {schema.TableName}", conn, tran); await cleanCmd.ExecuteNonQueryAsync(); this.Logger.Information($"table {schema.TableName} truncated"); using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)) { bulkCopy.DestinationTableName = $"[dbo].{schema.TableName}"; foreach (var col in schema.Columns) { bulkCopy.ColumnMappings.Add(col.Name, col.Name); } await bulkCopy.WriteToServerAsync(schema.Records); } this.Logger.Information($"{schema.Records.Rows.Count} records have been imported into {schema.TableName}"); } await tran.CommitAsync(); this.Logger.Information($"All transactions have been committed."); } catch (Exception ex) { this.Logger.Error(ex, $"An error occured while doing operations. Rolling back all changes to {conn.Database} on {conn.DataSource}"); await tran.RollbackAsync(); throw; } } await conn.CloseAsync(); } }
public async Task CommitAsync() { try { await _transaction.CommitAsync(); } catch (Exception) { await _transaction.RollbackAsync(); throw; } finally { Dispose(); } await BeginTransactionAsync(); }
public async Task CreateOrUpdateUsers(params User[] users) { if (!users.Any()) { return; } if (users.Length == 1) { await CreateOrUpdateUser(users[0]); return; } using var connection = new SqlConnection(ConnectionString); await connection.OpenAsync(); SqlTransaction trans = connection.BeginTransaction(); await connection.ExecuteAsync(@$ " CREATE TABLE #TempUsers (Id bigint, Username nvarchar(60), JoinChannel bit) ", transaction : trans); await connection.ExecuteAsync(@$ " INSERT INTO #TempUsers (Id, Username, JoinChannel) VALUES (@Id, @Username, @JoinChannel) ", users, trans); await connection.ExecuteAsync(@$ " MERGE Users us USING #TempUsers tus ON us.Id = tus.Id WHEN MATCHED THEN UPDATE SET us.Username = tus.Username, us.JoinChannel = IIF(us.JoinChannel = 1, us.JoinChannel, tus.JoinChannel) WHEN NOT MATCHED THEN INSERT (Id, Username, JoinChannel) VALUES (tus.Id, tus.Username, tus.JoinChannel); ", transaction : trans); await trans.CommitAsync(); }
public async Task <StatisticResult> ExecuteAsync(string sqlExpression) { List <Dictionary <string, object> > results = new List <Dictionary <string, object> >(); using (SqlConnection connection = new SqlConnection(connectionString)) { SqlCommand command = new SqlCommand(sqlExpression, connection); await connection.OpenAsync(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable); command.Transaction = transaction; try { SqlDataReader reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { Dictionary <string, object> row = new Dictionary <string, object>(); for (int i = 0; i < reader.FieldCount; i++) { row.Add(reader.GetName(i), reader.GetValue(i)); } results.Add(row); } await reader.CloseAsync(); await transaction.CommitAsync(); } catch (Exception) { await transaction.RollbackAsync(); } return(new StatisticResult(results)); } }
public async Task <T> ExecuteSqlCommand <T>(string sqlExpression, Execution <T> executionFunction, params SqlParameter[] parameters) { SqlCommand command = CreateCommand(sqlExpression, parameters); using (SqlConnection connection = command.Connection) { await connection.OpenAsync(); SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable); command.Transaction = transaction; try { T item = await executionFunction.Invoke(command); await transaction.CommitAsync(); return(item); } catch (Exception ex) { await transaction.RollbackAsync(); return(default);
public async Task BulkInsert(List <LineItemDto> lineItemDtos, DateTime Date, string AccountID) { try{ DataTable sourceDt = new DataTable(); sourceDt.Columns.Add("Date", typeof(DateTime)); sourceDt.Columns.Add("AccountPayerAccountId", typeof(string)); sourceDt.Columns.Add("UsageAmount", typeof(decimal)); sourceDt.Columns.Add("UnblendedRate", typeof(decimal)); sourceDt.Columns.Add("UnblendedCost", typeof(decimal)); sourceDt.Columns.Add("UsageStartDate", typeof(DateTime)); sourceDt.Columns.Add("UsageEndDate", typeof(DateTime)); sourceDt.Columns.Add("ProductionID", typeof(string)); sourceDt.Columns.Add("LineItemType", typeof(string)); foreach (LineItemDto dto in lineItemDtos) { DataRow dr = sourceDt.NewRow(); dr["Date"] = dto.Date; dr["AccountPayerAccountId"] = dto.UsageAccountId; dr["UsageAmount"] = dto.UsageAmount; dr["UnblendedRate"] = dto.UnblendedRate; dr["UnblendedCost"] = dto.UnblendedCost; dr["UsageStartDate"] = dto.UsageStartDate; dr["UsageEndDate"] = dto.UsageEndDate; dr["ProductionID"] = dto.ProductID; dr["LineItemType"] = dto.LineItemType; sourceDt.Rows.Add(dr); } using (SqlConnection con = new SqlConnection(_connection.ConnectionString)) { con.Open(); using (SqlTransaction transaction = con.BeginTransaction()) { try{ using (var cmd = con.CreateCommand()) { cmd.CommandText = $"DELETE FROM LineItems_{Date.Month.ToString().PadLeft(2,'0')} WHERE Date = @Date AND AccountPayerAccountId=@AccountId"; cmd.Transaction = transaction; cmd.Parameters.AddWithValue("@Date", Date); cmd.Parameters.AddWithValue("@AccountId", AccountID); await cmd.ExecuteNonQueryAsync(); } using (SqlBulkCopy dest = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction)) { dest.DestinationTableName = $"dbo.LineItems_{Date.Month.ToString().PadLeft(2,'0')}"; dest.ColumnMappings.Add("Date", "Date"); dest.ColumnMappings.Add("AccountPayerAccountId", "AccountPayerAccountId"); dest.ColumnMappings.Add("UsageAmount", "UsageAmount"); dest.ColumnMappings.Add("UnblendedRate", "UnblendedRate"); dest.ColumnMappings.Add("UnblendedCost", "UnblendedCost"); dest.ColumnMappings.Add("UsageStartDate", "UsageStartDate"); dest.ColumnMappings.Add("UsageEndDate", "UsageEndDate"); dest.ColumnMappings.Add("ProductionID", "ProductionID"); dest.ColumnMappings.Add("LineItemType", "LineItemType"); await dest.WriteToServerAsync(sourceDt); } await transaction.CommitAsync(); }catch (Exception ex) { await transaction.RollbackAsync(); } } } }catch (Exception ex) { _logger.LogError($"Add Production got errors:{ex.Message}"); throw ex; } }
public async Task CommitAsync() { await transaction.CommitAsync(); }
public async Task <ImportResult> PersistAsync(IList <ConvertedData> aggregatedModelItems) { int batchOrder = Interlocked.Increment(ref _batchOrder); int concurrentThreads = Interlocked.Increment(ref _concurrentThreads); _logger.LogInformation( "Start Persisting batch {batchNumber}. Concurrent persisting tasks {concurrentThreads}" , batchOrder , concurrentThreads ); //await Task.Delay(10000); aggregatedModelItems ??= new List <ConvertedData>(); int itemCount = aggregatedModelItems.Count; ImportResult result = new ImportResult() { Batch = batchOrder, HasError = true, Data = aggregatedModelItems }; int answersCount = itemCount; IEnumerable <Category> categories; IEnumerable <Question> questions; IEnumerable <Answer> answers; if (aggregatedModelItems.Count > 0) { //Remove duplicated items categories = aggregatedModelItems.Select <ConvertedData, Category>(converted => converted.Category).DistinctBy(x => x.Id); questions = aggregatedModelItems.Select <ConvertedData, Question>(converted => converted.Question).DistinctBy(x => x.Id); //var aaa = aggregatedModelItems.Select<ConvertedData, Answer>(converted => converted.Answer).GroupBy(ans => ans.Id).Where(grp => grp.Count() > 1).Select(grp => grp); //Answers should be always different but ... answers = aggregatedModelItems.Select <ConvertedData, Answer>(converted => converted.Answer).DistinctBy(x => x.Id); answersCount = answers.Count(); _logger.LogDebug( "Analyzed {itemCount} elements. {answers.Count()} distinct answers, {categories.Count()} distinct categories and {questions.Count()} distinct questions on Thread {Thread.CurrentThread.ManagedThreadId}" , itemCount , answersCount , categories.Count() , questions.Count() , Thread.CurrentThread.ManagedThreadId ); try { using (SqlConnection destinationConnection = new SqlConnection(_importSettings.DbConnectionString)) { destinationConnection.Open(); using (SqlTransaction transaction = destinationConnection.BeginTransaction()) { try { //Recreate Temp Tables using (SqlCommand cmd = new SqlCommand(sqlDropAndRecreateTempTables, destinationConnection, transaction)) { cmd.CommandTimeout = 600; await cmd.ExecuteNonQueryAsync(); } //Fill Temp Tables using (SqlBulkCopy bulkCopy = new SqlBulkCopy( destinationConnection, SqlBulkCopyOptions.KeepIdentity, transaction)) { bulkCopy.BatchSize = _importSettings.BatchSize; bulkCopy.BulkCopyTimeout = 600; //Insert Categories bulkCopy.DestinationTableName = "#Categories"; bulkCopy.ColumnMappings.Add("Id", "Id"); using (IDataReader reader = categories.AsDataReaderOfObjects()) { try { await bulkCopy.WriteToServerAsync(reader); } finally { reader.Close(); } } //Insert Questions bulkCopy.DestinationTableName = "#Questions"; bulkCopy.ColumnMappings.Clear(); bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("CategoryId", "CategoryId"); bulkCopy.ColumnMappings.Add("Text", "Text"); using (IDataReader reader = questions.AsDataReaderOfObjects()) { await bulkCopy.WriteToServerAsync(reader); } //Insert Answers bulkCopy.DestinationTableName = "#Answers"; bulkCopy.ColumnMappings.Clear(); bulkCopy.ColumnMappings.Add("Id", "Id"); bulkCopy.ColumnMappings.Add("QuestionId", "QuestionId"); bulkCopy.ColumnMappings.Add("Text", "Text"); bulkCopy.ColumnMappings.Add("Timestamp", "Timestamp"); using (IDataReader reader = answers.AsDataReaderOfObjects()) { await bulkCopy.WriteToServerAsync(reader); } } //Add indexes on temp tables using (SqlCommand cmd = new SqlCommand(sqlCreateIndexes, destinationConnection, transaction)) { cmd.CommandTimeout = 600; await cmd.ExecuteNonQueryAsync(); } //Insert new records and drop temp tables using (SqlCommand cmd = new SqlCommand(sqlInsertNewRecords, destinationConnection, transaction)) { cmd.CommandTimeout = 1200; await cmd.ExecuteNonQueryAsync(); } //_logger.LogDebug("Committing Transaction"); await transaction.CommitAsync(); result.HasError = false; } catch { if (transaction != null) { transaction.Rollback(); } throw; } } } } catch (Exception ex) { result.HasError = true; result.OccuredException = ex; } finally { Interlocked.Decrement(ref _concurrentThreads); } Interlocked.Add(ref _persisted, answersCount); Interlocked.Add(ref _total, itemCount); } _logger.LogInformation("Items sent to database till now: distinct in block {_persisted} / total {_total}", _persisted, _total); return(result); }