Plinth.Database.MSSql
1.7.1
Prefix Reserved
dotnet add package Plinth.Database.MSSql --version 1.7.1
NuGet\Install-Package Plinth.Database.MSSql -Version 1.7.1
<PackageReference Include="Plinth.Database.MSSql" Version="1.7.1" />
paket add Plinth.Database.MSSql --version 1.7.1
#r "nuget: Plinth.Database.MSSql, 1.7.1"
// Install Plinth.Database.MSSql as a Cake Addin #addin nuget:?package=Plinth.Database.MSSql&version=1.7.1 // Install Plinth.Database.MSSql as a Cake Tool #tool nuget:?package=Plinth.Database.MSSql&version=1.7.1
README
Plinth.Database.MSSql
Stored Procedure based mini-framework for Microsoft SQL Server
Provides Transaction management, stored procedure execution, result set handling, and transient error detection
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
👉 All settings in PlinthMSSqlSettings
are optional. The defaults are shown below.
{
"ConnectionStrings": {
"MyDB": "Data Source=...."
},
"PlinthMSSqlSettings": {
"SqlCommandTimeout": "00:00:50",
"SqlRetryCount": 3,
"SqlRetryInterval": "00:00:00.200",
"SqlRetryFastFirst": true,
"DisableTransientRetry": false
}
}
- 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 theSqlRetryInterval
. Default istrue
. - DisableTransientRetry: If
true
, transient errors will not trigger retries. Default isfalse
.
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.
⚠️ 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. 👉 When running an operation without a transaction, the function may still retry, depending on whether a call to ExecuteProc has occurred. Nothing will be rolled back. See section on executing without a transaction for more details.
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.
👉 All forms also have an overload that accepts a CancellationToken
ExecuteProcAsync(string procName, params SqlParameter[] parameters)
- This will execute the procedure, 👉 and fail if no rows were modified
ExecuteProcAsync(string procName, int expectedRows, params SqlParameter[] parameters)
- This will execute the procedure, and fail if the rows modified does not match
expectedRows
- This will execute the procedure, and fail if the rows modified does not match
ExecuteProcUncheckedAsync(string procName, params SqlParameter[] parameters)
- 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.
👉 All forms also have an overload that accepts a CancellationToken
ExecuteQueryProcAsync(string procName, params SqlParameter[] parameters)
- Returns an
IAsyncEnumerable<IResult>
which can be enumerated to extract objects from rows.
- Returns an
ExecuteQueryProcListAsync<T>(string procName, Func<IResult, Task<T>> readerFunc, params SqlParameter[] parameters)
- Returns a
List<T>
of objects returned from theFunc
called on each row returned. - 👉 Always returns a non-null
List<T>
that may be empty.
- Returns a
ExecuteQueryProcOneAsync(string procName, Func<IResult, Task> readerFunc, params SqlParameter[] parameters)
- Calls the
Func
with a single row result (if one found), returns true/false if row was found.
- Calls the
ExecuteQueryProcOneAsync<T>(string procName, Func<IResult, Task<T>> readerFunc, params SqlParameter[] parameters)
- Calls the
Func
with a single row result and returns the output inside aSqlSingleResult<T>
object. - Use
.Value
to get the result and.RowReturned
to determine if a row was returned.
- Calls the
7. Special Connection Features For Transactions
- 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.
Post Rollback Actions:
AddRollbackAction(string? desc, Action onRollback)
AddAsyncRollbackAction(string? desc, Func<Task> onRollbackAsync)
- These will execute the action/func after a rollback has completed
- Common use case: Undoing a non-transactional thing that should only exist if the transaction succeeded
Post Commit Actions:
AddPostCommitAction(string? desc, Action postCommit)
AddAsyncPostCommitAction(string? desc, Func<Task> postCommitAsync)
- 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. Running without a Transaction
Plinth as a general philosophy prefers "always correct, even if sometimes sub-optimal". Doing all database operations within a transaction ensures that if new SQL operations are added, they will always join the transaction with any other operations run within the function.
However, sometimes the overhead of starting and committing a transaction becomes an issue. If running only queries, or always just a single ExecuteProcAsync
, a transaction is not technically required.
ISqlTransactionProvider
contains several forms of ExecuteWithoutTxn()
which run a function to perform SQL operations, without an explicit transaction.
Below is example code that executes a stored procedure without a transaction
var myThing = await _txnProvider.ExecuteWithoutTxnAsync(connection =>
{
return await connection.ExecuteQueryProcOneAsync(
"usp_GetMyThingById",
row => Task.FromResult(row.GetInt("MyColumn")),
new SqlParameter("@ThingID", thingId)).Value;
}, ct);
Notes:
- The connection provided by
ExecuteWithoutTxnAsync
can be used to perform any number of ExecuteQueryProc type calls, but only one ExecuteProc type calls. An exception will be thrown upon the second ExecuteProc call - After an ExecuteProc call that modifies data, an exception will not trigger a rollback. Once the call completes, the data is committed.
- The function provided will be retried upon a transient error unless an ExecuteProc type call has been made
10. Error and Post Close Actions
These allow you to have code execute after a connection is closed due to error or successful completion of the callback. Useful for cleaning up items or taking actions after database operations are completed. These are analogous to those in section 8 but are not tied to a transaction lifecycle.
Post Error Actions:
AddErrorAction(string? desc, Action<Exception?> onError)
AddAsyncErrorAction(string? desc, Func<Exception?, Task> onErrorAsync)
- These will execute the action/func after an exception has been caught in the callback
- Common use case: Undoing an operation that should only exist if the callback succeeded
👉 These will be executed each time the callback executes, even if there are retries. There will be not retries if an ExecuteProc type call was made.
Post Close Actions:
AddPostCloseAction(string? desc, Action postClose)
AddAsyncPostCloseAction(string? desc, Func<Task> postCloseAsync)
- These will execute the action/func after the callback has completed successfully
- Common use case: Performing some action that should only occur if the entire set of database operations complete successfully
11. 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)
Example
await c.ExecuteQueryProcMultiResultSetAsync(
"usp_GetMultipleResults",
async (mrs) =>
{
var rs = await mrs.NextResultSetAsync();
await processSet1(rs);
rs = await mrs.NextResultSetAsync();
await processSet2(rs);
rs = await mrs.NextResultSetAsync();
await processSet3(rs);
},
new SqlParameter("@Int1", 10));
12. IDeferredSqlConnection
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);
});
13. 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 11.
ExecuteRawAsync
for DMLExecuteRawQueryListAsync
for queries that return a list of resultsExecuteRawQueryOneAsync
for queries that return a single resultExecuteRawQueryMultiResultSetAsync
for queries that return multiple result sets
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net6.0 is compatible. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. |
-
net6.0
- Microsoft.Data.SqlClient (>= 5.2.2)
- Microsoft.Extensions.Configuration.Binder (>= 9.0.0)
- Microsoft.SourceLink.Bitbucket.Git (>= 1.1.1)
- Plinth.Common (>= 1.7.1)
- Plinth.Serialization (>= 1.7.1)
- System.Runtime.Caching (>= 9.0.0)
- System.Text.Json (>= 9.0.0)
-
net8.0
- Microsoft.Data.SqlClient (>= 5.2.2)
- Microsoft.Extensions.Configuration.Binder (>= 9.0.0)
- Microsoft.SourceLink.Bitbucket.Git (>= 1.1.1)
- Plinth.Common (>= 1.7.1)
- Plinth.Serialization (>= 1.7.1)
- System.Runtime.Caching (>= 9.0.0)
- System.Text.Json (>= 9.0.0)
-
net9.0
- Microsoft.Data.SqlClient (>= 5.2.2)
- Microsoft.Extensions.Configuration.Binder (>= 9.0.0)
- Microsoft.SourceLink.Bitbucket.Git (>= 1.1.1)
- Plinth.Common (>= 1.7.1)
- Plinth.Serialization (>= 1.7.1)
- System.Runtime.Caching (>= 9.0.0)
- System.Text.Json (>= 9.0.0)
NuGet packages (3)
Showing the top 3 NuGet packages that depend on Plinth.Database.MSSql:
Package | Downloads |
---|---|
Plinth.Hangfire.MSSql
Plinth Hangfire Utilities for SQL Server |
|
Plinth.Storage.MSSql
SQL Server driver for Plinth.Storage |
|
Plinth.Database.Dapper.MSSql
Dapper extensions for plinth database framework for MS Sql Server |
GitHub repositories
This package is not used by any popular GitHub repositories.
Version | Downloads | Last updated |
---|---|---|
1.7.1 | 178 | 12/12/2024 |
1.7.0 | 174 | 11/12/2024 |
1.6.6 | 143 | 11/8/2024 |
1.6.5 | 848 | 8/31/2024 |
1.6.4 | 134 | 8/2/2024 |
1.6.3 | 1,304 | 5/15/2024 |
1.6.2 | 201 | 2/16/2024 |
1.6.1 | 4,904 | 1/5/2024 |
1.6.0 | 368 | 11/30/2023 |
1.5.10-b186.aca976b4 | 74 | 11/30/2023 |
1.5.9 | 1,855 | 11/29/2023 |
1.5.9-b174.64153841 | 85 | 11/23/2023 |
1.5.9-b172.dfc6e7bd | 71 | 11/17/2023 |
1.5.9-b171.4e2b92e2 | 75 | 11/4/2023 |
1.5.8 | 226 | 10/23/2023 |
1.5.7 | 1,664 | 7/31/2023 |
1.5.6 | 7,321 | 7/13/2023 |
1.5.5 | 303 | 6/29/2023 |
1.5.4 | 987 | 3/7/2023 |
1.5.3 | 426 | 3/3/2023 |
1.5.2 | 603 | 1/11/2023 |
1.5.2-b92.7c961f5f | 122 | 1/11/2023 |
1.5.0 | 839 | 11/9/2022 |
1.5.0-b88.7a7c20cd | 117 | 11/9/2022 |
1.4.7 | 4,571 | 10/20/2022 |
1.4.6 | 1,166 | 10/17/2022 |
1.4.5 | 1,344 | 10/1/2022 |
1.4.4 | 1,280 | 8/16/2022 |
1.4.3 | 1,084 | 8/2/2022 |
1.4.2 | 1,080 | 7/19/2022 |
1.4.2-b80.7fdbfd04 | 130 | 7/19/2022 |
1.4.2-b74.acaf86f5 | 125 | 6/15/2022 |
1.4.1 | 1,340 | 6/13/2022 |
1.4.0 | 1,190 | 6/6/2022 |
1.3.8 | 2,547 | 4/12/2022 |
1.3.7 | 1,158 | 3/21/2022 |
1.3.6 | 1,139 | 3/17/2022 |
1.3.6-b67.ca5053f3 | 141 | 3/16/2022 |
1.3.6-b66.4a9683e6 | 131 | 3/16/2022 |
1.3.5 | 1,157 | 2/23/2022 |
1.3.4 | 1,476 | 1/20/2022 |
1.3.3 | 855 | 12/29/2021 |
1.3.2 | 752 | 12/11/2021 |
1.3.1 | 674 | 11/12/2021 |
1.3.0 | 667 | 11/8/2021 |
1.2.3 | 1,870 | 9/22/2021 |
1.2.2 | 926 | 8/20/2021 |
1.2.1 | 1,394 | 8/5/2021 |
1.2.0 | 816 | 8/1/2021 |
1.2.0-b37.a54030b9 | 153 | 6/24/2021 |
1.1.6 | 3,512 | 3/22/2021 |
1.1.5 | 892 | 3/9/2021 |
1.1.4 | 2,033 | 2/27/2021 |
1.1.3 | 688 | 2/17/2021 |
1.1.2 | 744 | 2/12/2021 |
1.1.1 | 1,078 | 2/1/2021 |
1.1.0 | 760 | 12/16/2020 |
1.1.0-b27.b66c309b | 277 | 11/15/2020 |
1.0.12 | 1,694 | 10/18/2020 |
1.0.11 | 762 | 10/6/2020 |
1.0.10 | 1,038 | 9/30/2020 |
1.0.9 | 750 | 9/29/2020 |
1.0.8 | 931 | 9/26/2020 |
1.0.7 | 889 | 9/19/2020 |
1.0.6 | 824 | 9/3/2020 |
1.0.5 | 799 | 9/2/2020 |
1.0.4 | 1,149 | 9/1/2020 |
1.0.3 | 754 | 9/1/2020 |
1.0.2 | 836 | 8/29/2020 |
1.0.1 | 822 | 8/29/2020 |
1.0.0 | 814 | 8/29/2020 |
1.0.0-b1.c22f563d | 246 | 8/28/2020 |
net9.0 support