In ASP.NET Core 6.0, I am trying to update an Azure SQL database, and as well call a REST API for an external service, and if the external service call fails, I want to rollback the update.
My code is as follows:
public async Task<string> UpdateValue(MyObject newValue) { using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, TransactionScopeAsyncFlowOption.Enabled)) { await azureSqlRepository.UpdateAsync(newValue.id, newValue); await myHttpClient.PostAsJsonAsync(url, newValue); scope.Complete(); } } myHttpClient is registered as a typed HttpClient.
I am doing that because if the httpClient call fails, I want to rollback the changes to the databases.
The scope.Complete doesn't save the work to the Azure SQL database, even if the REST call succeeded.
Sometimes it updates, and sometimes it doesn't update, and when it doesn't update, it won't return any error message.
It works all the time on insert, but update doesn't update to Azure SQL database all the time.
It works on a local SQL Server, but not on Azure SQL.
I tried to wrap the http call with its own transacionscope and upress it as follows:
public async Task<string> UpdateValue(MyObject newValue) { using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, TransactionScopeAsyncFlowOption.Enabled)) { await azureSqlRepository.UpdateAsync(newValue.id, newValue); using (var innerTransaction = new transactionScope(TransactionScopeAsyncFlowOption.Supress) { await myHttpClient.PostAsJsonAsync(url, newValue); innerTransaction.Complete(); } scope.Complete(); } } But that throws an exception: A TransactionScope must be disposed on the same thread that it was created.
Then I tried to be a new transaction:
public async Task<string> UpdateValue(MyObject newValue) { using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, TransactionScopeAsyncFlowOption.Enabled)) { await azureSqlRepository.UpdateAsync(newValue.id, newValue); using (var innerTransaction = new transactionScope(TransactionScopeOption.RequireNew, TransactionScopeAsyncFlowOption.Enabled) { await myHttpClient.PostAsJsonAsync(url, newValue); innerTransaction.Complete(); } scope.Complete(); } } Thant didn't throw an exception, but it did not update anything (same as no inner transaction.
IDbContextTransactionin Entity Framework Core for manual transaction management. here is the code:public async Task<string> UpdateValue(MyObject newValue){using (var transaction = await azureSqlRepository.Database.BeginTransactionAsync()){ try {await azureSqlRepository.UpdateAsync(newValue.id, newValue); var response = await myHttpClient.PostAsJsonAsync(url, newValue); response.EnsureSuccessStatusCode(); await transaction.CommitAsync();} catch {await transaction.RollbackAsync(); throw; } }}DbTransactionparameter. In your business logic, open a database connection and start a transaction. Perform the database update within this transaction, then make the external service call. If the service call succeeds, commit the transaction; otherwise, roll it back. By this way you might ensures the database update is rolled back if the external service call fails, maintaining data consistency.