protected async Task ExecuteNonQueryAsync(string sql, CommandType ct, params SqlParameter[] parameters)
{
foreach (var sqlParameter in parameters.Where(p => p.Value == null && p.SqlDbType != SqlDbType.Structured))
{
sqlParameter.Value = DBNull.Value;
}
using (var sqlCon = new SqlConnection(_dbConnStr))
{
await sqlCon.OpenAsync();
var executeTask = Task.Run(async() =>
{
using (var cmd = CreateCommand(sql, sqlCon, ct, parameters))
{
try
{
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
Commit();
}
finally
{
// Release the parameters to allow reuse in case of deadlock
cmd.Parameters.Clear();
}
}
});
for (int attemptNo = 0; attemptNo < DeadlockRetryCount + 1; ++attemptNo)
{
try
{
await executeTask;
return;
}
catch (SqlException e)
{
if (e.Number == 1205 && attemptNo < DeadlockRetryCount) // Deadlocks
{
OnDeadlockDetected?.Invoke(this, null);
Thread.Sleep(DeadlockRetryDelayMs);
}
else if ((e.Number == -2 || e.Number == 11) && attemptNo < TimeoutRetryCount) // Timeouts
{
OnDeadlockDetected?.Invoke(this, null);
Thread.Sleep(DeadlockRetryDelayMs);
}
else
{
Rollback();
throw;
}
}
}
}
// Should never reach this point
throw new Exception("INTERNAL ERROR - SQL retry failsafe activated");
}
protected async Task <DataTableRows> ExecuteProcedureOrSqlAsync(string sql, CommandType ct, params SqlParameter[] parameters)
{
foreach (var sqlParameter in parameters.Where(p => p.Value == null && p.SqlDbType != SqlDbType.Structured))
{
sqlParameter.Value = DBNull.Value;
}
using (var sqlCon = new SqlConnection(_dbConnStr))
{
await sqlCon.OpenAsync();
var executeTask = Task.Run(async() =>
{
using (var cmd = CreateCommand(sql, sqlCon, ct, parameters))
{
try
{
using (var reader = new DataSetReader(cmd))
{
return(await reader.GetRowsAsync().ConfigureAwait(false));
}
}
finally
{
Commit();
//Release the parameters to allow reuse in case of deadlock
cmd.Parameters.Clear();
}
}
});
var attemptNo = 0;
for (int failSafe = 0; failSafe < DeadlockRetryCount + 1; ++failSafe)
{
try
{
return(await executeTask);
}
catch (SqlException e)
{
if (e.Number == 1205 && attemptNo++ < DeadlockRetryCount)
{
OnDeadlockDetected?.Invoke(this, null);
Thread.Sleep(DeadlockRetryDelayMs);
}
else
{
Rollback();
throw;
}
}
}
}
// Should never reach this point
throw new Exception("INTERNAL ERROR - SQL retry failsafe activated");
}