Handling SQL, HTTP and transient errors (retries with Polly) — Exception Handling & Custom Error Handling in .NET

8. Handling SQL, HTTP and transient errors (retries with Polly)

Intermediate★ Must knowLesson 8 of 9

SQL Server errors — inspect SqlException.Number:

Number	Meaning	Handling
2627 / 2601	Unique constraint / unique index violation	409 Conflict ("email already registered")
547	Foreign key / check constraint violation	400/409 with a clear message
1205	Deadlock victim	Retry the transaction
-2	Timeout	Retry carefully / optimise query
4060, 18456	Can't open database / login failed	Configuration problem — alert
40613, 40501, 40197 (Azure SQL)	Transient service errors	Retry with backoff (`EnableRetryOnFailure` in EF Core)

csharp

try { await db.SaveChangesAsync(); }
catch (DbUpdateException ex) when (ex.InnerException is SqlException { Number: 2627 or 2601 })
{
    throw new ConflictException("A customer with this email already exists.");
}

// EF Core built-in retry for transient errors
options.UseSqlServer(cs, sql => sql.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null));

HTTP calls — HttpRequestException (network/DNS/connection), non-success status codes (EnsureSuccessStatusCode() throws), TaskCanceledException (timeout). Use resilience pipelines:

csharp

builder.Services.AddHttpClient<PaymentClient>(c => c.BaseAddress = new("https://pay.example.com"))
    .AddStandardResilienceHandler();      // retry with backoff + jitter, circuit breaker, attempt and total timeouts

Retry only transient and idempotent operations; never retry a payment POST without an idempotency key. Combine with a circuit breaker so a failing dependency isn't hammered.