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