public void RollbackTransaction(AppSqlTransaction transaction)
{
var trans = _transactions.SingleOrDefault(t => t.Equals(transaction));
if (trans == null)
{
return;
}
trans.Transaction.Rollback();
_transactions.Remove(trans);
}
public async Task <AppSqlTransaction> BeginTransactionAsync(string transactionName, CancellationToken cancellationToken = default)
{
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync(cancellationToken);
}
var transaction = new AppSqlTransaction {
Transaction = _connection.BeginTransaction(transactionName)
};
_transactions.Add(transaction);
return(transaction);
}
public async Task <T> ExecuteSingleAsync <T>(string procedureName, List <SqlParameter> parameters,
CancellationToken cancellationToken = default, AppSqlTransaction transaction = null) where T : class, new()
{
cancellationToken.ThrowIfCancellationRequested();
return((await ExecuteListAsync <T>(procedureName, parameters, cancellationToken, transaction)).SingleOrDefault());
}
public async Task <List <T> > ExecuteListAsync <T>(string procedureName, List <SqlParameter> parameters,
CancellationToken cancellationToken = default, AppSqlTransaction transaction = null) where T : class, new()
{
cancellationToken.ThrowIfCancellationRequested();
var result = new List <T>();
try
{
// NOTE: https://stackoverflow.com/questions/4439409/open-close-sqlconnection-or-keep-open/4439434#4439434
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync(cancellationToken);
}
var command = new SqlCommand(procedureName, _connection)
{
CommandType = CommandType.StoredProcedure,
Transaction = transaction?.Transaction ?? GetCurrentTransaction()?.Transaction
};
if (parameters?.Any() ?? false)
{
command.Parameters.AddRange(parameters.ToArray());
}
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
while (reader.Read())
{
result.Add(reader.ConvertToObject <T>());
}
}
if (!_transactions.Any())
{
_connection.Close();
}
}
catch (Exception e)
{
_reportService.Error(e.Message);
}
return(result);
}
public async Task <List <T> > ListAsync <T>(string queryString, List <SqlParameter> parameters,
CancellationToken cancellationToken = default, AppSqlTransaction transaction = null) where T : class, new()
{
cancellationToken.ThrowIfCancellationRequested();
var result = new List <T>();
try
{
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync(cancellationToken);
}
var command = new SqlCommand(queryString, _connection)
{
CommandType = CommandType.Text,
Transaction = transaction?.Transaction ?? GetCurrentTransaction()?.Transaction
};
if (parameters?.Any() ?? false)
{
command.Parameters.AddRange(parameters.ToArray());
}
using (var reader = await command.ExecuteReaderAsync(cancellationToken))
{
while (reader.Read())
{
result.Add(reader.ConvertToObject <T>());
}
}
if (!_transactions.Any())
{
_connection.Close();
}
}
catch (Exception e)
{
_reportService.Error(e.Message);
return(null);
}
return(result);
}
