public async Task Save(List <TransactionModel> transactions)
{
int affectdRecords = 0;
using (var connection = new SqlConnection(_conntectionString))
{
SqlTransaction trans = null;
try
{
connection.Open();
trans = connection.BeginTransaction();
int chunkSize = 500;
var chunks = transactions.Select((x, j) => new { index = 1, value = x })
.GroupBy(x => x.index / chunkSize)
.Select(x => x.Select(v => v.value).ToList())
.ToList();
foreach (var chunk in chunks)
{
using (var sqlCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, trans)
{
DestinationTableName = "[dbo].[TRANSACTION]",
BatchSize = chunk.Count,
NotifyAfter = chunk.Count
})
{
sqlCopy.SqlRowsCopied += (sender, args) => { affectdRecords += (int)args.RowsCopied; };
sqlCopy.ColumnMappings.Add("TransactionIdentificator", "TransactionIdentificator");
sqlCopy.ColumnMappings.Add("Amount", "Amount");
sqlCopy.ColumnMappings.Add("CurrencyCode", "CurrencyCode");
sqlCopy.ColumnMappings.Add("TransactionDate", "TransactionDate");
sqlCopy.ColumnMappings.Add("Status", "TransactionStatus");
var reader = ObjectReader.Create(chunk);
{
await sqlCopy.WriteToServerAsync(reader);
}
}
}
trans.Commit();
}
catch (Exception ex)
{
trans?.Rollback();
trans?.Dispose();
_logger.Log(LogLevel.Error, ex.Message);
}
finally
{
trans?.Dispose();
}
}
_logger.Log(LogLevel.Information, $"Inserted Transactions: {affectdRecords} records");
}
public void BeginTransaction()
{
/*~~~~~~~~~~~~~~~ SQL SERVER ~~~~~~~~~~~~~~~*/
/* Dispose */
_transaction?.Rollback();
_transaction?.Dispose();
_connection?.Close();
_connection?.Dispose();
/* Create */
_connection = new SqlConnection(_config.SqlServerConnectionString);
_connection.Open();
_transaction = _connection.BeginTransaction();
}
public void BeginTransaction(IsolationLevel isolationLevel, Action <SqlTransaction> action)
{
var isConnectionClosed = Connection.State == ConnectionState.Closed;
if (isConnectionClosed)
{
Connection.Open();
}
Transaction = Connection.BeginTransaction(isolationLevel);
try
{
action(Transaction);
}
catch
{
Transaction.Rollback();
throw;
}
finally
{
Transaction?.Dispose();
Transaction = null;
if (isConnectionClosed)
{
Connection.Close();
}
}
}
public void Dispose()
{
_transaction?.Dispose();
_transaction = null;
_connection?.Dispose();
_connection = null;
}
public void Dispose()
{
_sqlConnection?.Dispose();
_sqlTrx?.Dispose();
_sqlConnection = null;
_sqlTrx = null;
}
public IDisposable TryAcquire <TLockCookie>(int timeoutMillis, ISqlSynchronizationStrategy <TLockCookie> strategy, IDisposable contextHandle)
where TLockCookie : class
{
if (contextHandle != null)
{
return(this.CreateContextLock(contextHandle).TryAcquire(timeoutMillis, strategy, contextHandle: null));
}
IDisposable result = null;
var connection = new SqlConnection(this.connectionString);
SqlTransaction transaction = null;
try
{
connection.Open();
// when creating a transaction, the isolation level doesn't matter, since we're using sp_getapplock
transaction = connection.BeginTransaction();
var lockCookie = strategy.TryAcquire(transaction, this.lockName, timeoutMillis);
if (lockCookie != null)
{
result = new LockScope(transaction);
}
}
finally
{
// if we fail to acquire or throw, make sure to clean up
if (result == null)
{
transaction?.Dispose();
connection.Dispose();
}
}
return(result);
}
public void Dispose()
{
_connection.Close();
_transaction?.Dispose();
_connection?.Dispose();
_isDisposed = true;
}
public IDisposable TryAcquire(int timeoutMillis, SqlApplicationLock.Mode mode, IDisposable contextHandle)
{
if (contextHandle != null)
{
return(this.CreateContextLock(contextHandle).TryAcquire(timeoutMillis, mode, contextHandle: null));
}
IDisposable result = null;
var connection = new SqlConnection(this.connectionString);
SqlTransaction transaction = null;
try
{
connection.Open();
// when creating a transaction, the isolation level doesn't matter, since we're using sp_getapplock
transaction = connection.BeginTransaction();
if (SqlApplicationLock.ExecuteAcquireCommand(transaction, this.lockName, timeoutMillis, mode))
{
result = new LockScope(transaction);
}
}
finally
{
// if we fail to acquire or throw, make sure to clean up
if (result == null)
{
transaction?.Dispose();
connection.Dispose();
}
}
return(result);
}
public async Task <IDisposable> TryAcquireAsync(int timeoutMillis, SqlApplicationLock.Mode mode, CancellationToken cancellationToken, IDisposable contextHandle = null)
{
if (contextHandle != null)
{
return(await this.CreateContextLock(contextHandle).TryAcquireAsync(timeoutMillis, mode, cancellationToken, contextHandle: null).ConfigureAwait(false));
}
IDisposable result = null;
var connection = new SqlConnection(this.connectionString);
SqlTransaction transaction = null;
try
{
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
// when creating a transaction, the isolation level doesn't matter, since we're using sp_getapplock
transaction = connection.BeginTransaction();
if (await SqlApplicationLock.ExecuteAcquireCommandAsync(transaction, this.lockName, timeoutMillis, mode, cancellationToken).ConfigureAwait(false))
{
result = new LockScope(transaction);
}
}
finally
{
// if we fail to acquire or throw, make sure to clean up
if (result == null)
{
transaction?.Dispose();
connection.Dispose();
}
}
return(result);
}
private async Task DiscardMessageAsync(SqlConnection connection, SqlTransaction transaction, string discardMessage, CancellationToken cancellationToken)
{
await transaction?.CommitAsync(cancellationToken);
transaction?.Dispose();
connection?.Dispose();
_logger.LogTrace(discardMessage);
}
/// <summary>
/// Disposing the unit of work closes the sql connection, making the connection available to the connection pool
/// for it to be used again.
/// </summary>
public void Dispose()
{
_transaction?.Dispose();
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
_connection.Dispose();
}
public void Dispose()
{
Transaction?.Dispose();
if (_connection != null && _connection.State != ConnectionState.Closed)
{
_connection.Close();
_connection.Dispose();
}
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
_transaction?.Dispose();
_connection?.Dispose();
}
}
public void Dispose()
{
if (transactionOpened)
CommitTransaction();
sqlTransaction?.Dispose();
sqlConnection?.Dispose();
ResetAllRepositories();
}
public void Close()
{
logger.StartMethod(MethodBase.GetCurrentMethod().Name);
tran?.Rollback();
tran?.Dispose();
con.Close();
con.Dispose();
}
public void Dispose()
{
if (!_committed)
{
RollbackChanges();
}
_transaction?.Dispose();
_connection?.Dispose();
}
public override void Dispose()
{
_transaction?.Dispose();
_connection.Dispose();
if (_deleteDatabase)
{
DeleteDatabase(_name);
}
}
public void Dispose()
{
if (_disposed)
{
return;
}
_sqlTransaction?.Dispose();
_disposed = true;
}
public void Dispose()
{
if (transaction?.Connection != null)
{
transaction?.Commit();
}
connection?.Close();
transaction?.Dispose();
connection?.Dispose();
}
public void Dispose()
{
_transaction?.Dispose();
if (_context != null)
{
_context.Close();
_context.Dispose();
}
_repositories = null;
}
public void Dispose()
{
if (!_disposedValue)
{
_disposedValue = true;
_transaction?.Dispose();
_connection?.Close();
_connection?.Dispose();
}
}
protected virtual void Dispose(bool disposing)
{
if (_disposedValue)
{
return;
}
if (disposing)
{
_transaction?.Dispose();
_connection?.Dispose();
}
_disposedValue = true;
}
public void Dispose()
{
if (_disposed)
{
return;
}
_disposed = true;
_transaction?.Dispose();
_connection?.Close();
_connection?.Dispose();
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
if (_ownsTransaction)
{
_transaction?.Dispose();
}
if (_ownsConnection)
{
_connection.Dispose();
}
}
}
protected virtual void Dispose(bool disposing)
{
if (!_disposedValue && disposing && _connection != null)
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection.Dispose();
_transaction?.Dispose();
}
_disposedValue = true;
}
public override async Task <TResult> ExecuteAsync <TResult>(Func <IDbConnection, IDbTransaction, CancellationToken, Task <TResult> > callback, IsolationLevel iso = IsolationLevel.ReadCommitted,
CancellationToken cctoken = new CancellationToken())
{
if (callback == null)
{
throw new ArgumentNullException(nameof(callback));
}
SqlConnection conn = null;
SqlTransaction tr = null;
try
{
conn = (SqlConnection)this.CreateDbConnection();
if (conn.State != ConnectionState.Open)
{
await conn.OpenAsync(cctoken);
}
tr = conn.BeginTransaction(iso);
TResult result = await callback(conn, tr, cctoken);
tr.Commit();
return(result);
}
catch (Exception ex)
{
try
{
tr?.Rollback();
}
catch (Exception cmex)
{
throw cmex;
}
throw ex;
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
tr?.Dispose();
}
}
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
transaction?.Dispose();
conn?.Dispose();
// TODO: dispose managed state (managed objects).
}
// TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
// TODO: set large fields to null.
disposedValue = true;
}
}
public void Dispose()
{
if (_errorOccured)
{
_sqlTransaction?.Rollback();
}
else
{
_sqlTransaction?.Commit();
}
_sqlTransaction?.Dispose();
_sqlBulkCopy.Close();
_sqlBulkCopy?.Close();
_sqlConnection?.Close();
_sqlConnection?.Dispose();
}
async Task <DapperDatabaseContext <TSaga> > CreateDatabaseContext(CancellationToken cancellationToken)
{
var connection = new SqlConnection(_options.ConnectionString);
SqlTransaction transaction = null;
try
{
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
transaction = connection.BeginTransaction(_options.IsolationLevel);
return(new DapperDatabaseContext <TSaga>(connection, transaction));
}
catch (Exception)
{
transaction?.Dispose();
connection.Dispose();
throw;
}
}
public async Task <IDisposable> TryAcquireAsync <TLockCookie>(int timeoutMillis, ISqlSynchronizationStrategy <TLockCookie> strategy, CancellationToken cancellationToken, IDisposable contextHandle = null)
where TLockCookie : class
{
if (contextHandle != null)
{
return(await this.CreateContextLock(contextHandle).TryAcquireAsync(timeoutMillis, strategy, cancellationToken, contextHandle: null).ConfigureAwait(false));
}
IDisposable result = null;
var connection = new SqlConnection(this.connectionString);
SqlTransaction transaction = null;
try
{
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
// when creating a transaction, the isolation level doesn't matter, since we're using sp_getapplock
transaction = connection.BeginTransaction();
var lockCookie = await strategy.TryAcquireAsync(transaction, this.lockName, timeoutMillis, cancellationToken).ConfigureAwait(false);
if (lockCookie != null)
{
result = new LockScope(transaction);
}
}
finally
{
// if we fail to acquire or throw, make sure to clean up
if (result == null)
{
transaction?.Dispose();
connection.Dispose();
}
}
return(result);
}