1. Register the transaction factory and provider with DI in Setup
// IConfiguration configuration;
var txnFactory = new SqlTransactionFactory(
configuration,
"MyDB",
config.GetConnectionString("MyDB"));
services.AddSingleton(txnFac); // for injecting the SqlTransactionFactory
services.AddSingleton(txnFac.GetDefault()); // for injecting the ISqlTransactionProvider
2. Settings in appsettings.json
Example appsettings.json
:point_right: All settings in PlinthMSSqlSettings are optional. The defaults are shown below.
SqlCommandTimeout: A TimeSpan formatted time for the default time for each SQL operation. Default is 50 seconds.
SqlRetryCount: If a transient error is detected, maximum number of retries after the initial failure. Default is 3. This allows up to 4 attempts.
SqlRetryInterval: If a transient error is detected, this is how long between retry attempts. Default is 200 milliseconds.
SqlRetryFastFirst: If true, upon the first transient error, the first retry will happen immediately. Subsequent transient errors will wait the SqlRetryInterval. Default is true.
DisableTransientRetry: If true, transient errors will not trigger retries. Default is false.
3. Transient Errors
It is very common on cloud hosted databases (especially on Azure SQL) to have the database return transient errors that will work perfectly if retried. These errors can be things like deadlocks, timeouts, throttling, and transport errors.
The framework accepts a function to execute the whole transaction. When a transient error occurs, the entire transaction is rolled back and the function is executed again.
:warning: Your code inside a transaction should be re-entrant. Anything that is performed that cannot be rolled back (such as sending an email), should be performed outside the transaction or be checked to confirm that it won't execute more than once.
4. Creating a Transaction
Below is an example controller that creates a transaction, executes a stored procedure, and returns the result.
[Route("api/[controller]")]
[ApiController]
public class MyThingController : Controller
{
private readonly ISqlTransactionProvider _txnProvider;
public MyThingController(ISqlTransactionProvider _txnProvider)
{
_txnProvider = txnProvider;
}
[HttpGet]
[Route("{thingId}")]
[ProducesResponseType(200)]
public async Task<ActionResult<MyThing>> GetMyThing(Guid thingId, CancellationToken ct)
{
var myThing = await _txnProvider.ExecuteTxnAsync(connection =>
{
return await connection.ExecuteQueryProcOneAsync(
"usp_GetMyThingById",
row => Task.FromResult(new MyThing
{
Field1 = row.GetInt("Field1"),
Filed2 = row.GetDateTimeNull("Field2")
... etc
}),
new SqlParameter("@ThingID", thingId)).Value;
}, ct);
if (myThing is null)
throw new LogicalNotFoundException($"MyThing {thingId} was not found");
return Ok(myThing);
}
}
5. Executing Stored Procedures with no Result Set
To execute a stored procedure that does not return a result set, use one of these three options. Typically used with DML procedures that insert/update/delete.
:point_right: All forms also have an overload that accepts a CancellationToken
This will execute the procedure, and return the number of rows modified
6. Executing Stored Procedures that return a Result Set
To execute a stored procedure returns a result set, use one of these three options. Typically used with SELECT queries.
:point_right: All forms also have an overload that accepts a CancellationToken
Calls the Func with a single row result and returns the output inside a SqlSingleResult<T> object.
Use .Value to get the result and .RowReturned to determine if a row was returned.
7. Special Connection Features
SetRollback(): Will mark this transaction for later rollback when the transaction function is complete
_WillBeRollingBack(): Determine if SetRollback() has been called on this transaction
IsAsync(): Determine if this transaction supports async operations
CommandTimeout {get; set;}: The default timeout for sql commands (in seconds)
8. Rollback and Post Commit Actions
These allow you to have code execute after a rollback or a commit occurs. Useful for cleaning up non-transaction items or taking actions after database operations are committed.
These will execute the action/func after the transaction has been committed
Common use case: Performing some action that should only occur if the database operations are confirmed.
9. Multiple Result Sets
Some stored procedures can actually return multiple result sets in a single call.
To execute and process each result set, use this method:
ExecuteQueryProcMultiResultSetAsync(string procName, Func<IAsyncMultiResultSet, Task> readerFunc, params SqlParameter[] parameters)
This allows for recording a sequence of stored procedure calls (without actually executing them) and then executing them all at one at a later time.
Example:
var deferred = _txnProvider.GetDeferred();
// no sql actions occur
deferred.ExecuteProc("usp_InsertThing". new SqlParameter("@ID", 5));
deferred.ExecuteProc("usp_InsertThing". new SqlParameter("@ID", 10));
await _txnProvider.ExecuteTxnAsync(connection =>
{
// now the sql actions are executed
await connection.ExecuteDeferredAsync(deferred);
});
11. Raw SQL Transactions
Normal transactions as shown above only allow for executing stored procedures. There are times and cases where executing a raw SQL statement is required. To do so, use ExecuteRawTxnAsync as shown in the below example:
var myThing = await _txnProvider.ExecuteRawTxnAsync(connection =>
{
return await connection.ExecuteRawQueryOneAsync(
"SELECT Field1, Field2 FROM MyThings WHERE ThingID = @ThingID",
row => Task.FromResult(new MyThing
{
Field1 = row.GetInt("Field1"),
Filed2 = row.GetDateTimeNull("Field2")
... etc
}),
new SqlParameter("@ThingID", thingId)).Value;
}, ct);
The methods are analogues of the methods in sections 5, 6 and 9.
ExecuteRawAsync for DML
ExecuteRawQueryListAsync for queries that return a list of results
ExecuteRawQueryOneAsync for queries that return a single result
ExecuteRawQueryMultiResultSetAsync for queries that return multiple result sets