Example #1
0
        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");
        }
Example #2
0
        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");
        }