public async Task BeginTransactionAsync()
{
/*~~~~~~~~~~~~~~~ SQL SERVER ~~~~~~~~~~~~~~~*/
/* Dispose */
if (_transaction != null)
{
await _transaction.RollbackAsync();
await _transaction.DisposeAsync();
}
if (_connection != null)
{
await _connection.CloseAsync();
await _connection.DisposeAsync();
}
/* Create */
_connection = new SqlConnection(_config.SqlServerConnectionString);
await _connection.OpenAsync();
var dbTransaction = await _connection.BeginTransactionAsync();
_transaction = (SqlTransaction)dbTransaction;
}
/// <summary>
/// Asynchronously rollbacks active transaction.
/// </summary>
public async Task RollBackTransactionAsync()
{
if (transaction != null)
{
await transaction.RollbackAsync();
}
}
public async Task RollbackAsync_should_rollback_transaction()
{
var transaction = NSubstitute.Substitute.For <IDbContextTransaction>();
var sut = new SqlTransaction(transaction);
await sut.RollbackAsync();
transaction.Received(1).RollbackAsync(Arg.Any <CancellationToken>());
}
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 InTransactionAsync(Func <SqlTransaction, Task> executeActions, CancellationToken cancellationToken)
{
using var connection = GetConnection();
SqlTransaction?transaction = null;
try
{
await OpenConnectionAsync(connection, cancellationToken);
using (transaction = connection.BeginTransaction())
{
await executeActions(transaction);
await transaction.CommitAsync(cancellationToken);
}
}
catch (Exception)
{
// Attempt to roll back the transaction.
// TODO: log
try
{
transaction?.RollbackAsync(cancellationToken);
}
catch (InvalidOperationException)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
// TODO: log
// TODO: escalate, notify?
}
throw;
}
}
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 RollbackAsync()
{
await transaction.RollbackAsync();
}
